ADO.NET : Connecting to a Data Provider

The following set of statements establishes a connection to a SQL Server Express database named MyDatabase running on the system named MySystem, using the active Microsoft Windows login account for its security access:

Dim theDatabase As System.Data.SqlClient.SqlConnection
Dim connectionString As String = _
"Data Source=MySystem\SQLEXPRESS;" & _
"Initial Catalog=MyDatabase;Integrated Security=true"

theDatabase = New SqlClient.SqlConnection(connectionString)
theDatabase.Open( )
' ---- Perform database processing here, then…
theDatabase.Close( )
theDatabase.Dispose( )



ADO.NET includes several different database libraries. The most generic library, found in the System.Data namespace, defines the core classes used to manage database sets in memory. There are distinct classes for tables, columns, and rows of data; classes that let you establish relationships between the tables; and classes that let you bundle tables and relationships in one large "data set." You will probably use these classes quite a bit in your code, but they know nothing of database connections or how to communicate with any external data source (other than specially formatted XML files).

To connect a provider to a data source, you create a connection object using a valid connection string and then use the Open() method to establish the connection. ADO.NET connection strings are similar to those used in OLE DB and ADO, and building them can be tricky. Connection strings are semicolon-delimited sets of connection parameters, with each entry taking the form parameter=value. The choice of parameters and values varies by connection type and desired features. The connection string used here includes three parameters (DataSource, InitialCatalog, and Integrated Security):

Data Source=MySystem\SQLEXPRESS;Initial Catalog=MyDatabase;
Integrated Security=true



Setting Integrated Security to true tells SQL Server to use the current Windows user's authentication information to connect to the database. If your database uses SQL Server's built-in authentication system, you can use the following connection string (for user "sa" and password "abc"):

Data Source=MySystem\SQLEXPRESS;Initial Catalog=MyDatabase;
User ID=sa;Password=abc



Each provider includes a " connection string builder class" (it's found at System.Data.SqlClient. SqlConnectionStringBuilder for the SQL Server provider), and although you can use it, it is simply a string-concatenation tool that attaches the semicolon-delimited parts you provide. You still need to know what each of the parameters and values should be.

The documentation installed with Visual Studio includes an article named "Working with Connection Strings" that includes common parameter names and values. If you look in the online help index for "connection strings [ADO.NET]," the "Working with Connection Strings" article is one of the results. For Oracle connection strings using Oracle's own provider, consult your Oracle documentation or their web site.

Once you have a valid connection string, use it as an argument to the connection object's constructor:

Dim theDatabase As System.Data.SqlClient.SqlConnection
Dim connectionString As String = _
"Data Source=MySystem\SQLEXPRESS;" & _
"Initial Catalog=MyDatabase;Integrated Security=true"
theDatabase = New SqlClient.SqlConnection(connectionString)



Establish the connection by using the Open() method:

theDatabase.Open()



You don't need to close the connection until you are truly finished interacting with the database. When you use the Open() method, ADO.NET opens the connection only long enough to verify the connection. It then closes the connection, waiting for you to issue a SQL statement before it opens the connection again.

When you are really ready to close the connection, use the Close() method:

theDatabase.Close()



To make your connection string see the site http://www.connectionstrings.com provides many useful examples of ADO.NET connection strings