Home > Data Access in Web Forms > Connecting to a Database

Securing Connection Strings

The very first step in accessing database information is to establish a connection with the database source.

ADO.NET provides both the SqlConnection and OleDbConnection classes for use with the appropriate data source.

To set up a connection, you initialize an instance of the appropriate Connection class using one of the methods provided by its constructor.

Basically, a connection is instantiated using a given connection string, which can be specified either after or while declaring the connection object, but before issuing a call to open the connection. The connection is then activated by explicitly calling the Open method.

When the method successfully completes, the connection is live and you can then issue commands against the data source and process the results.

The following topics demonstrate how to establish and open a connection using each .NET data provider.

The below code snippet initializes a new instance of the SqlConnection class with the specified ConnectionString property.

String connectString = "server=localhost; uid=sa; pwd=; database=pubs";
SqlConnection myConn = new SqlConnection ( connectString );
myConn.Open ( );

Dim connectString As String = "server=localhost; uid=sa; pwd=; database=pubs"
Dim myConn As New SqlConnection ( connectString ) 
myConn.Open ( )

For detailed information on connection string syntax, see SqlConnection.ConnectionString property.

The following samples demonstrate using an SqlConnection to connect to an SQL Server™ database. The first example simply displays the connection properties. For security purposes, though, the information in the ConnectionString and DataSource properties had been hidden.

Run Sample | View Source

Run Sample | View Source

Notice in the examples above a call to a method named getConnection instead of explicitly declaring the connection string property.

SqlConnection myConn = new SqlConnection ( getConnection ( "aspnet" ) );

The method is defined in an external script file, and in this workshop is called each time an SqlConnection is needed. This is so to avoid exposing connection strings on the page that can leave the application open to a malicious data breach.

For more about this, see Securing Connection Strings.

The below code snippet initializes a new instance of the OleDbConnection class with the specified ConnectionString property.

String connectString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=pubs.mdb"
OleDbConnection myConn = new OleDbConnection ( connectString );
myConn.Open ( );

Dim connectString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=pubs.mdb"
Dim myConn As new OleDbConnection ( connectString ) 
myConn.Open ( ) 

The following samples demonstrate using an OleDbConnection to connect to an MS Access database. The first example simply displays the connection properties.

Run Sample | View Source

Run Sample | View Source

For detailed information on connection string syntax, see OleDbConnection.ConnectionString property.

For the Jet Provider, if the data source does not reside in the same directory as the calling page, the complete path from the application root to the data source must be specified using the Server.MapPath property in the connection string. For example:

OleDbConnection myConn = new OleDbConnection ( 
   "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Server.MapPath ( "~/app_data/dbtutor.mdb" ) );

Or by using the special keyword |DataDirectory| that points to the application’s default data directory, which in most cases is the App_Data folder residing as a subfolder of the application root.

OleDbConnection myConn = new OleDbConnection ( 
   "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=|DataDirectory|dbtutor.mdb" );

You must always close the Connection when you are finished using it to release it from memory and conserve resources.

Connections are not implicitly released when the Connection object falls out of scope or is reclaimed by garbage collection.

This can be done using either the Close or Dispose methods of the Connection object.

sqlConn.Close ( );

or

sqlConn.Dispose ( );

The ConnectionString property specifies the name and location of the database you want to access, along with any other parameters it may require, and is passed as a series of key=value parameter pairs separated by semicolons.

NOTE: The connection string MUST NOT contain any spaces, either before or after the equal ( = ) signs within the string, and must be written as a single line without any line breaks. The code examples shown here are done so only for readability.

For valid connection string formats, see the SQL ConnectionString and the OleDB ConnectionString properties in the namespace library.

NOTE: Embedding connection strings in application code can lead to security vulnerabilities and maintenance problems.

The next section provides information on how to secure connection strings used in your application.

Securing Connection Strings