Connection string for connecting to data sources

A connection string is a string which is used to gain access to different data sources from a VB .Net or a C# .Net application. The data sources can be MS SQL Server, MS Access or SQL Server on remote server. A connection string is made of many parts and one of them is the Provider. A provider allows us to specify the driver which can access the datasource in binary form. With the provider, we can setup a connection from an application and gain access to the datasource. In a connection string, we specify the appropriate provider depending on the datasource we wish to access. Note that when we need to connect to sql server we need not mention the provider in the connection string. You will see this, when we write the connection string for connecting a VB. Net application or C# application to an Sql Server database.

Connection String in a n-tier application

All commercial applications are designed based on the N-Tier concept. Which simply means partitoning your application into components. Where does connection string fit in a tier based application. The picture given shows where you should place the connection string in your application.

If you are not aware of how to develop a components based or 3 tier application this book gives you all the steps of developing such an application - Database programming using visual basic, c# (c sharp) sql server

Connection string to connect to Sql Server database

VB 2005

Given below is the connection string for connecting a Visual Basic. Net application to an Sql Server database.

Dim connectionString As String = “Data Source=SYS2;” + “integrated security=SSPI;” + “database=FinAccounting”

C# 2005

Given below is the connection string for connecting a C#. Net application to an Sql Server database.

string connectionString = “Data Source=SYS2;” + “integrated security=SSPI;” + “database=FinAccounting”;

In the above example, Data Source is the machine name of the computer that is running sql server, in this case SYS2. ‘Integrated security’ indicates that current user's windows credentials (username and password) are being used to access and connect to sql server. The possible values for Integrated Security are ‘true’, ‘yes’, and ‘sspi’, which specify a secure connection.

In the above code, "database" refers to the name of the specific database that we would like to access. A Connection string also has other properties like connection pooling, length of timeout period and security options. Note that we can use and implement a connection string without using these other properties. Connection string is a property of the connection object. While using the connection string, we need to instantiate the Connection object. We will see how to do this in the coming topic.

Connection string to connect to a Microsoft access database

To connect to a Microsoft access database, use the following connection string.

Dim myConn As OleDbConnection  
  = New OleDbConnection ()
MyConn.ConnectionString = 
  “Provider = Microsoft.Jet.OLEDB.4.0; Data “ & _
    “Source = C : \ data \ northwind.mdb ; 
     User Id = guest; “ & _
  “Password = isw3083”

Observe that in the above connection string, we have specified a provider name in the connection string. We need to do this when we use a OLEDB .Net data provider.

Giving away username and password in the connection string code is vulnerable to phishing. An option to overcome this problem is by using the Windows integrated security. Consider the following connection string.

string connectionString = “Data Source=SYS2;” + “integrated security=SSPI;” + “database=FinAccounting”;

By setting integrated security as SSPI, we are specifying that windows integrated security is the security option chosen.

Connection string to connect to Sql Server database which is on a remote server

Below is an example of a connection string used to access a sql server database which is on the remote web server on the internet, using C# code in an .aspx file.

string connectionString = "Data Source=201.73.323.39;database=FinAccounting;User
ID=sssabc123;Password=scss456;";

In the above code, we assign the IP address of the server to the ‘Data Source’, assign the name of the database, assign the username and the password. The above code is used to connect to a database hosted on a web server.

Let us see, what is a connection object, as the connection string is a property of the Connection Object.

Connection Object

The Connection object establishes a connection to the database. Two of the most common Connection objects used are OleDbConnection and SqlConnection. Both the SqlConnection and OleDbConnection are namespaces and are inherited from the IdbConnection object. The commonly used property of the Connection object is the Connection string property.

ConnectionString property

This property is used to provide information, such as the data source and database name, that is used to establish the connection with the database.

There are two ways of setting the Connection string property.

Using a parameterized Constructor
Using the default constructor

Setting the Connection String property using a parameterized Constructor

Visual basic 2005


 Imports System.Data
 Imports System.Data.SqlClient

 Public Sub GetData()

   Dim connectionString As String = “Data Source=SYS2;” +
   “integrated security=SSPI;” + “database=FinAccounting”

   Dim myConnection As Sqlconnection = 
     New SqlConnection(connectionString)

   myConnection.Open()

 End Sub

In this method, we are taking advantage of the SqlConnection object’s parameterized constructor to set the ConnectionString property directly, at the same time as it is instantiated.

Setting the Connection String property using the default constructor

In this method, we use the default constructor and then later set the ConnectionString property separately.

Visual basic 2005

 Imports System.Data
 Imports System.Data.SqlClient

 Public Sub GetData()

  Dim myConnection As Sqlconnection
   = New SqlConnection()

  myConnection.ConnectionString=“Data Source=SYS2;” 
  + “integrated security=SSPI;”
   + “database=FinAccounting”

  myConnection.Open()

End Sub

Note that the connection string must always be set before the connection is opened, and cannot be changed after the connection is open.

Storing Connection string in a APP.config file

The Connection string contains sensitive data like username, password, IP address of the server. When we place this sensitive data in an .aspx file for a web application or visual basic or C# files, we run the risk of exposing this data to a hacker.

So, we place this code in the application configuration files. Configuration files are used to store information about application settings. These files are also referred to as Application Configuration Files and provide security to the data contained in them. We place the connection string in App.config file for windows applications and web.config file for web applications.

After we write the data source information in a program, it will be difficult to change the data source information. If we do so, it will create problems when we deploy the programs from test server to production servers.

Till .Net was released this information was stored in .ini files or in system registry. It is not an easy job to open the registry, locate entries and make appropriate changes. .NET gives a simple and easy solution for this problem - the Application Configuration File.

APP.config files

In an APP.config file, information is stored as Key-Value pairs. Each application can have a configuration file, which is actually an XML file. Any text editor can be used to open the configuration file and change values. The application will load the values from this configuration file. There is no need change your code every time there is change in data source or any other information stored in the configuration file.

Click here for More information on Connection strings

Connection string in app.config for Windows applications

Windows applications in VS.NET use the name app.config by default for the configuration file. App.config files are not automatically created when you create a Windows application. If you need a configuration file for your application, open your project in VS.NET, go to the 'Solution Explorer' and right click on the project name. Choose Add -> Add new item from the menu and select 'Application Configuration file' from the list of choices. This will create an app.config file for you in the application root. The app.config file with the connection string will have the following content.

 < ?xml version="1.0" encoding="utf-8" ?>
 < configuration>
	< appSettings>
	  < add key="DatabasePath" 
	  value="data source=localhost;
	  initial catalog=FinAccounting;
	  Integrated Security=true;"/>
    < /appSettings>

For the App.config file to be read, place the following code in your windows application.

  Imports System.Configuration
  Imports System.Data.SqlClient

  Public Class Form1
    Dim dbpath As String = 
	ConfigurationSettings.AppSettings("DatabasePath")
    Dim str_sql_user_select As String 
	= "SELECT * FROM AccountsTable"
    Dim comUserSelect As SqlCommand
    Dim myreader As SqlDataReader

    Private Sub Form1_Load(ByVal sender As System.Object,
	 ByVal e As System.EventArgs) Handles  MyBase.Load

        Dim myConnection As SqlConnection 
		  = New SqlConnection(dbpath)
        comUserSelect = New SqlCommand(str_sql_user_select, myConnection)
        TextBox1.Text = ""
        TextBox2.Text = ""
        myConnection.Open()
        myreader = comUserSelect.ExecuteReader
        If (myreader.Read = True) Then
            TextBox1.Text = myreader(0)
            TextBox2.Text = myreader(1)
        Else
            MsgBox("You have reached eof")
        End If

    End Sub
End Class

ConfigurationSettings class is the class used to access the contents of the configuration file. Since this class is part of the namespace System.Configuration, we have to use the fully qualified name System.Configuration.ConfigurationSettings. As a shortcut, you can use the Imports directive on top of the file as shown above.

Note: When you compile your application, VS.NET will automatically create a file called .exe.config in your bin\debug folder. The contents of the app.config will be automatically copied to this new config file when you compile the application. When you deliver the application to the end user, you have to deliver the exe and this new config file called .exe.config and NOT the app.config. Users can modify the data in .exe.config file and application will read the data from the config file, when restarted.

Connection string in web.config for Web applications

Web applications use a similar concept as in windows apllications, but the name of the config file is web.config. A couple of things to note are:

-web.config is created automatically by VS.NET when you create any web project.

-When you compile the web application, web.config is NOT renamed or copied to the BIN folder.

-web.config has several default entries in it to support web/IIS configuration & security.

-You can add the <appSettings>section in the web.config and add your key/value pairs in that section.

-You can have separate web.config files for each directory in your web application, in addition to the one in the root. For each web page, by default, system will look for a web.config in the same folder as the page and if not found, then looks in the parent folder. The web.config file with the connection string will have the following content.

  < ?xml version="1.0"?>
  < configuration>
	< appSettings>
		< add key="DatabasePath" 
		  value="server=localhost;
		database=FinAccounting;
		Integrated Security=true;"/>
	< /appSettings>
  < /configuration>

For the web.config file to be read, place the following code in your web application as shown below.

  Imports System.Configuration
  Imports System.Data.SqlClient

  Partial Class _Default
    Inherits System.Web.UI.Page

    Dim dbpath As String 
	 = ConfigurationSettings.AppSettings("DatabasePath")
    Dim str_sql_user_select As String 
	 = "SELECT * FROM AccountsTable"
    Dim comUserSelect As SqlCommand
    Dim myreader As SqlDataReader

    Protected Sub Page_Load
	(ByVal sender As Object, ByVal e As System.EventArgs)
	  Handles Me.Load

        Dim myConnection As SqlConnection 
		 = New SqlConnection(dbpath)
        comUserSelect = New 
		 SqlCommand(str_sql_user_select, myConnection)
        TextBox1.Text = ""
        TextBox2.Text = ""
        myConnection.Open()
        myreader = comUserSelect.ExecuteReader
        If (myreader.Read = True) Then
            TextBox1.Text = myreader(0)
            TextBox2.Text = myreader(1)
        Else
            MsgBox("You have reached eof")
        End If

    End Sub
  End Class