vkinfotek

connection string for connecting to data sources

list of questions answered in this article

Connection String Treeview (click and learn)



A connection string (hereafter CS) 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.

It 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.

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 CS. You will see this, when we write the CS 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 CS fit in a tier based application. The picture given shows where you should place the CS in your application.


CS to connect to sql server database

VB 2005

Given below is the CS for connecting a Visual Basic. Net application to an Sql Server database.
 Dim conString As String = "Data Source=SYS2;" +"integrated security=SSPI;" + "database=FinAccounting"


C# 2005

Given below is the CS for connecting a C#. Net application to an Sql Server database.
string conString = "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 CS 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. CS is a property of the connection object. While using the CS, we need to instantiate the Connection object. We will see how to do this in the coming topic.

CS to connect to a Microsoft access database

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

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 code, 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 code vulnerable to phishing. An option to overcome this problem is by using the Windows integrated security. Consider the following code.
<br /><br /> 
string conString = "Data Source=SYS2;" + "integrated security=SSPI;" + "database=FinAccounting";
<br /><br /> 
By setting integrated security as SSPI, we are specifying that windows integrated security is the security option chosen.

If you are searching for information on how to connect to various databases such as sql server, oracle, excel, access and mySql visit this site - connectionstrings





connection string to connect to sql server database which is on a remote server


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

<br /><br /> 
string conString = "Data Source=201.73.323.39;database=FinAccounting;User<br /> ID=sssabc123;Password=scss456;";<br />
<br /><br /> 

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.

In which layer do you place the connection string?

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 CS property.

  1. Using a parameterized Constructor
  2. Using the default constructor

setting the CS property using a parameterized Constructor

visual basic 2005

Imports System.Data
 Imports System.Data.SqlClient
 
 Public Sub GetData()
 
   Dim conString 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 CS 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 CS must always be set before the connection is opened, and cannot be changed after it is open.

storing CS in a APP.config file


The CS 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 CS 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.

CS 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 CS 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(&quotDatabasePath&quot)
    Dim str_sql_user_select As String 
	= &quotSELECT * FROM AccountsTable&quot
    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 = &quot&quot
        TextBox2.Text = &quot&quot
        myConnection.Open()
        myreader = comUserSelect.ExecuteReader
        If (myreader.Read = True) Then
            TextBox1.Text = myreader(0)
            TextBox2.Text = myreader(1)
        Else
            MsgBox(&quotYou have reached eof&quot)
        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.

CS 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






Authorship
By Bharati Karedla