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 code snippets demonstrate how to establish and open a connection using each .NET data provider.

NOTE: This example uses one of the overloaded versions of the SqlConnection constructor. For other examples that may be available, see the individual overload topics.

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 ( ) 

The following example uses an SqlConnection to connect to an SQL Server™ database and displays some of its properties.

 Show me 

NOTE: This example uses one of the overloaded versions of the OleDbConnection constructor. For other examples that may be available, see the individual overload topics.

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 example uses an OleDbConnection to connect to an MS Access database and displays some of its properties.

 Show me 

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" ) );

The connection string parameter typically specifies the name, location, and type of database you want to access ( along with any other parameters it may require ), and is passed as a series of argument=value statements 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 SqlConnection.ConnectionString and the OleDbConnection.ConnectionString properties in the namespace library.

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 ( );