You are writing an application that interacts with a database, and you need to connect to it to run some queries.
Use a Connection object and a "connection string" to establish the connection you will use for queries and updates.
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 to a database, you must use one of the providers included in ADO.NET. Each provider connects to a specific database or data-communication standard. Four providers ship with .NET, each appearing in a specific namespace, as shown in Table 13-1.
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:
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:
Although it's not an official Microsoft resource, the http://www.connectionstrings.com web site provides many useful examples of ADO.NET connection strings. The site is a little out of date, but it's still the best place we've found so far to locate details on all the various connection-string parameters.