Connecting to a Database

for RuBoard

In ADO.NET, as in many object-based data-access APIs, you use a connection object to establish a connection with a database. In the ADO.NET SQL Server provider, the object is called SqlConnection; in the OLE DB provider, it's cleverly called OleDbConnection. Both objects are conceptually identical, although their implementation details differ slightly. The main difference between the two objects has to do with the syntax of the connection string. The connection string is required to tell the object how to get to the database that you're interested in.

NOTE

The SqlConnection class is a member of the System.Data.SqlClient namespace. The OleDbConnection class is a member of System.Data.OleDb namespace. A full list of members of both namespaces can be found in the reference section at the end of this chapter.

To connect to a SQL Server database using the SqlConnection object, use code like that shown in Listing 11.1.

Listing 11.1 Opening a Connection Using the SQL Server Provider

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>   void Page_Load(Object Sender, EventArgs e)   {     SqlConnection cn;     cn = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs;");     cn.Open();     Response.Write("Opened connection to " + cn.Database + "<BR>");     Response.Write("SQL Server version " + cn.ServerVersion);   } </SCRIPT> 

This code opens the connection and displays information about the connection status. (You may need to change the server, database, uid, or pwd parameters of the connection string in this code to match the configuration of your system.)

As in ADO.old, one of the most important steps to establishing a connection is providing adequate and accurate information in the connection string that you pass to the connection object. For SQL Server, this information almost invariably includes a server name, database name , user ID, and password; additional settings can appear in the connection string, depending on your configuration and which database you are using.

ADO.old veterans will note that the syntax of the ADO.NET connection string is identical to that of ADO.old's Connection object; this is one of the few areas where the techniques you use of ADO.old and ADO.NET intersect.

To do the same thing using the OLE DB provider, use the code shown in Listing 11.2.

Listing 11.2 Opening a Connection Using the OLE DB Provider

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.OleDb' %> <SCRIPT runat='server'>   void Page_Load(Object Sender,EventArgs e)   {     OleDbConnection cn;     cn = new OleDbConnection("provider=SQLOLEDB;server=localhost;" +                 "uid=sa;pwd=;database=pubs;");     cn.Open();     Response.Write("Opened connection to " + cn.Database + "<BR>");     Response.Write("SQL Server version " + cn.ServerVersion);   } </SCRIPT> 

This should produce the same result that the previous example produced.

This OLE DB version of the code differs only slightly from the SQL Server version of the code. It imports the System.Data.OleDb namespace instead of System.Data.SqlClient, and it uses an OleDbConnection object instead of a SqlConnection. The connection string is also different: The OleDbConnection object requires a provider= clause to specify the type of OLE DB data source you want to use.

The capability to specify different OLE DB providers in the connection string is a powerful feature; it means that you can use ADO.NET to gain access to OLE DB data sources today, even if the database vendors don't yet support Microsoft.NET with providers of their own. If you can get to a data source using ADO.old, you can get to it using the ADO.NET OLE DB provider.

After you're done with your connection object, you should close it using its Close method. This will free up the computational resources devoted to that connection. In addition, you should handle any exceptions that are thrown by the Open method whenever you attempt to open a database connection.

Listing 11.3 contains a more complete example of opening a connection to a data source in ADO.NET, including explicit use of the Open and Close methods and error handling. Note that the structure of the error handler is such that the Close method is executed only if the Open method is successful.

Listing 11.3 Opening a Database Connection with Error Handling

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>   void Page_Load(Object Sender, EventArgs e)   {     SqlConnection cn;     cn = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs;");     try     {       cn.Open();       Response.Write("Opened connection to " + cn.Database + "<BR>");       Response.Write("SQL Server version " + cn.ServerVersion);       cn.Close();     }     catch(SqlException sx)     {       Response.Write("Connection failed: " + sx.Message);     }   } </SCRIPT> 

The easiest way to test this code is to do something to make your connection string invalid, such as change the server name to a server that doesn't exist, or get rid of the connection string entirely. If the connection works, you'll see the name of the database and version of the server. (Note that SQL Server 2000 is considered to be SQL Server version 8.0 for versioning purposes.) If it fails, you'll get the error message contained in the Message property of the SqlException object.

for RuBoard