ADO.NET Overview

Before you can use ADO.NET components, the appropriate namespaces must be included. The System.Data namespace always needs to be included because it contains the core database components. Next, depending on the source of your data, one of two namespaces needs to be included. For a direct SQL Server connection, the System.Data.SqlClient namespace should be used for best performance. For all other connection types, such as Access and Oracle, the System.Data.OleDb namespace is required. (An Oracle provider is now available at http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/940/msdncompositedoc.xml.)

The SqlClient data provider is fast. It's faster than the Oracle provider, and faster than accessing a database via the OleDb layer. It's faster because it accesses the native library (which automatically gives you better performance), and it was written with lots of help from the SQL Server team (who helped with the optimizations).

Managed Providers

Managed providers are a central part of the ADO.NET framework. Managed providers enable you to write language-independent components that can be called from C# and VB. Currently, managed providers come in two types: one for direct access to Microsoft SQL Server 7.0 and higher, and one for accessing data via an OLE DB layer. Both types use similar naming conventions, with the only difference being their prefixes.

The managed provider classes include Connection (SqlConnection class), Command (SqlCommand class), DataReader (SqlDataReader class), and DataAdapter (SqlDataAdapter class). The first two classes provide the same functionality that was found in ADO: creating a connection to a data source and then executing a command. A data reader has a close resemblance to a read-only, forward-only recordset that is very optimized. Last, the DataAdapter allows for the retrieval and saving of data between a DataSet and the data source. The DataSet is covered in Chapter 4.

Connection

To create a database connection, you need to include the appropriate namespaces in your application. This requires the data provider to be known, so either a SqlClient or OleDb namespace connection can be included for the best performance. The following code samples (Listings 3.1 and 3.2) show how both SqlClient and OleDb connections are made in C# and VB.

Listing 3.1 Using the SqlConnection Object

C#

 SqlConnection myConnection  =       new SqlConnection( "server=localhost;uid=sa;pwd=;database=pubs" ); myConnection.Open(); // Do Something with myConnection. myConnection.Close(); 

VB

 Dim myConnection As New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") myConnection.Open() ' Do something with myConnection. myConnection.Close() 

Listing 3.2 Using the OleDbConnection Object

C#

 OleDbConnection myConnection =   new OleDbConnection("Provider=SQLOLEDB.1;" +     "Data Source=localhost;uid=sa;pwd=;Initial Catalog=pubs" ); myConnection.Open(); // Do something the myConnection. myConnection.Close(); 

VB

 Dim myConnection As New _     OleDbConnection("Provider=SQLOLEDB.1;Data " + _     "Source=localhost;uid=sa;pwd=;Initial Catalog=pubs") myConnection.Open() ' Do something the myConnection. myConnection.Close() 

RECOMMENDED PRACTICE: The above connection strings are hard-coded into the source code. If at any time you need to change the connect strings (such as when the database server changes), you'll need to change the connection strings. If the connection strings are scattered all over the code, changing them will be difficult, and there's a chance you'll miss one.

The usual recommended practice is to store the connection string in the Web.config file that I discuss in detail in Chapter 7 in the section entitled "Retrieving the Database Connection String from Web.config." For this application, though, I use an application variable and initialize it in the Global. asax file. I chose to do it this way to give you an example of another way to store a connection string. The following code shows how to initialize an application variable in a Global.asax file:

C#

 protected void Application_Start(Object sender,     EventArgs e) {     Application["DBConnectionString"] =       "server=localhost;uid=sa;pwd=;database=Survey"; } 

VB

 Sub Application_Start(ByVal sender As Object, _     ByVal e As EventArgs)     Application("DBConnectionString") = _        "server=localhost;uid=sa;pwd=;database=Survey" End Sub 

Both managed provider connection strings look similar. In fact, the OleDb connection string is exactly the same as its predecessor in ADO, which should be obvious if you are familiar with programming in ADO. Now look at the differences. The SQL Server managed provider uses the private protocol called tabular data stream that is designed to work with SQL Server 7.0 and later. It does not use OLE DB, ADO, or ODBC. You can use an OleDb connection to SQL Server, but if you do, you will see performance degradation. The SQL Server connection also supports a variety of connection string keywords. Table 3.1 shows the OLE DB providers that are available in ADO.NET.

Command

The Command object allows direct interaction with the data through the database connection. The example shown in Listing 3.3 returns all rows from the Publishers table in Microsoft's Pubs database and loads them into a SqlDataReader using the Command object's ExecuteReader() method. The SqlDataReader enables the information to be accessed and processed accordingly.

Table 3.1. OLE DB Providers

Driver	Provider
SQLOLEDB	SQL OLE DB Provider (for SQL Server 6.5 and earlier)
MSDAORA	Oracle OLE DB Provider
JOLT	Jet OLE DB Provider

Listing 3.3 Using the Command Object

C#

 SqlConnection myConnection  = new SqlConnection( "server=localhost;uid=sa;pwd=;database=pubs" ); SqlCommand myCommand = new SqlCommand( "SELECT * FROM Publishers", myConnection ); myConnection.Open(); myReader = myCommand.ExecuteReader(); while( myReader.Read() ) {   // Do something with the data. } myReader.Close(); myConnection.Close(); 

VB

 Dim myConnection as new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") Dim myCommand as new _   SqlCommand("SELECT * FROM Publishers", myConnection) myConnection.Open() myReader = myCommand.ExecuteReader() While myReader.Read()   // Do something with the data. End While myReader.Close() myConnection.Close() 

In the example, the System.Data and System.Data.SqlClient namespaces must be included to get the correct SQL methods. Next, a SqlConnection is created to the Pubs database. A SQL SELECT statement and the reference to the Connection object are passed as SqlCommand parameters. The last declaration is a SqlDataReader that allows processing of the data fetched from the database. Finally, the connection and SqlDataReader are closed.

The example shown uses the SQL managed provider. However, if a connection to another database is required and the connection is using the OLE DB provider, then simply change the SQL command references to OleDb commands, and the remaining code will be the same.

RECOMMENDED PRACTICE: Garbage collection is non-deterministic. For this reason, you should always close ADO.NET objects, such as theSqlDataReader.

The best way to do that is in the finally block of a try/catch/finally construct, as follows:

 // Declare objects here. try {   // Open objects and use them here. } catch { } finally {   // If objects are open here, close them. } 

DataReader

The DataReader object provides an easy and efficient way to parse a series of records, or even one record. The DataReader object behaves as a read-only, forward-only stream returned from the database, and only one record at a time is ever in memory. However, the DataReader object is not intended to handle large, complex relationships between tables and records, nor does it have the capability to pass data back to a database a responsibility best left to the DataSet and DataRelation objects. In the previous example, the SqlDataReader was used to contain the data returned from the server. In this example, shown in Listing 3.4, I've expanded the code to display all the data from the Authors table.

Listing 3.4 Displaying Data from the Authors Table

C#

[View full width]

 SqlConnectiion myConnection = new SqlConnection("server=localhost;  uid=sa;pwd=;database=pubs" ); SqlCommand myCommand = null; SqlDataReader myReader = null; SqlDataReader myReader = null; try {   myConnection.Open();   myReader = myCommand.ExecuteReader();   myCommand =  new SqlCommand( "SELECT * FROM Authors ",     myConnection );   Response.Write( "<table border=1>" );   while( myReader.Read() )   {     Response.Write("<tr>");     for( int i=0; i<myReader.FieldCount; i++ )     {       Response.Write( "<td>" + myReader[i].ToString() + "</td>" );       Response.Write( "</tr>" );     }   }   Response.Write( "</table>" ); } catch { } finally {   if ( myReader != null )   {      myReader.Close();   }   if( myConnection.State == ConnectionState.Open )   {      myConnection.Close();   }   } } 

VB

 Dim myConnection as new _   SqlConnection("server=localhost;uid=sa;pwd=;database=pubs" ) Dim myCommand as new _   SqlCommand( "SELECT * FROM Authors ", myConnection ) Dim myReader As SqlDataReader = nothing Try   myConnection.Open()   MyReader = myCommand.ExecuteReader()   Response.Write( "<table border=1>" )   While myReader.Read()     Response.Write("<tr>")     Dim i as Integer     For i=0 To MyReader.FieldCount-1       Response.Write( "<td>" + myReader(i).ToString() + "</td>" )       Response.Write( "</tr>" )     Next   End While   Response.Write( "</table>" ) Catch Finally   If myReader <> nothing Then    myReader.Close()   End If   If myConnection.State = ConnectionState.Open Then     myConnection.Close()   End If End Try 

The output of this example can be seen in Figure 3.1, which creates an HTML table for displaying the data. From the code, you will first notice the MoveNext() method is not part of the while loop for the SqlDataReader. The SqlDataReader's Read() method automatically advances the cursor and initially sets the cursor to the beginning of the data. To create the table dynamically, we use the FieldCount property of the DataReader to determine the number of columns, which allows sequencing through each column to get its value. Once all the data has been parsed, the Read() method will return a null. An alternate method to use to check for more data is the HasMoreResults property. This method is useful if you need to check for more records within a loop condition without advancing the record pointer.

Figure 3.1. An HTML Representation of the Authors Table

CAUTION: One of the most common errors my students make is using a data reader when they're looking only for a single record. They almost always try to retrieve data from the DataReader before they call the Read() method. Remember: You must call the Read() method before you get any data from a DataReader.

The DataReader also contains a variety of Get methods that enable you to access field values, such as GetInt(), GetDouble(), GetInt32(), and GetString(), in native formats. To determine which one to use, the GetFieldType property can be called to get the appropriate column type. Then the correct Get method can be called to fetch the column data in its native format. To see the property type of each column, I could add the following code to my write statement:

 myReader[i].GetFieldType.ToString(); 

Figure 3.2 shows the column-type name added to the output of the previous example by using the added statement.

Figure 3.2. An HTML Representation of the Authors Table with Column Data Types Shown

The DataReader (unlike classic ADO) does not use the MoveFirst(), MoveNext(), and MoveLast() commands, or the EOF property. The initial call to the DataReader object's Read() command positions the record cursor at the beginning of the data and advances it after each subsequent call until all the data is processed. After all the data is processed, the Read() method returns a Boolean value. Moving the cursor back to the beginning is not permitted remember, the DataReader is forward only. The DataSet object now provides bi-directional movement through the data.

Parameter Binding with SQL Commands

Another feature of the SqlCommand object is its ability to easily bind parameter data for SQL statements and stored procedures. Each parameter has four key pieces of information: the name, the type, its data size, and the direction of the parameter.

For the SQL Server Managed provider, the parameter construction uses actual names of the parameters, just like regular T-SQL syntax uses. For example, the following code contains a single ID parameter that is passed to the SELECT command:

 SELECT * FROM Authors WHERE au_id=@ID 

To return values, I need to add parameters to the SELECT statement:

 SELECT @Fname=au_fname, @Lname=au_lname FROM Authors WHERE au_id=@ID 

Now I have one input and two output parameters. The code to bind the parameters to the SELECT command starts with a standard SQL connection, followed by the SQL SELECT statement, and finally a set of parameter bindings. The following code illustrates how the binding process works:

C#

 SqlConnection myConnection = new SqlConnection( "server=localhost;uid=sa;pwd=;database=pubs" ); SqlCommand myCommand =   new SqlCommand(     "SELECT @Fname=au_fname, @Lname=au_lname_FROM " +     "Authors WHERE au_id=@ID", myConnection ); myCommand.Parameters.Add( "@ID", SqlDbType.VarChar, 11 ); myCommand.Parameters["@ID"].Direction = ParameterDirection.Input; myCommand.Parameters["@ID"].Value = "172-32-1176"; myCommand.Parameters.Add( "@Fname", SqlDbType.VarChar, 20 ); myCommand.Parameters["@Fname"].Direction = ParameterDirection.Output; myCommand.Parameters.Add( "@Lname", SqlDbType.VarChar, 40 ); myCommand.Parameters["@Lname"].Direction = ParameterDirection.Output; myConnection.Open(); myCommand.Execute(); Response.Write( "First Name " + myCommand.Parameters["@Fname"].Value. ToString() + "<br>" ); Response.Write( "Last Name " + myCommand.Parameters["@Lname"].Value. ToString() ); myConnection.Close(); 

VB

 Dim myConnection as new _   SqlConnection( "server=localhost;uid=sa;pwd=;database=pubs" ) Dim myCommand as New _   SqlCommand( "SELECT @Fname=au_fname, @Lname=au_lname FROM " + _     "Authors WHERE au_id=@ID", myConnection ) myCommand.Parameters.Add( "@ID", SqlDbType.VarChar, 11 ) myCommand.Parameters("@ID").Direction = ParameterDirection.Input myCommand.Parameters("@ID").Value = "172-32-1176" myCommand.Parameters.Add( "@Fname", SqlDbType.VarChar, 20 ) myCommand.Parameters("@Fname").Direction = ParameterDirection.Output myCommand.Parameters.Add( "@Lname", SqlDbType.VarChar, 40 ) myCommand.Parameters("@Lname").Direction = ParameterDirection.Output myConnection.Open() myCommand.Execute() Response.Write( "First Name " + _   myCommand.Parameters("@Fname").Value.ToString() + "<br>" ) Response.Write( "Last Name " + _   myCommand.Parameters("@Lname").Value.ToString() ) myConnection.Close() 

Notice in the example that the names of the parameters must match the names declared in the SQL SELECT statement. Otherwise, the parameters do not match up correctly. The data types are standard SQL types.

RECOMMENDED PRACTICE: If your query will return only a single record (rowset), then using a SqlDataReader object into which the data will be retrieved is unnecessary overhead. Using bound parameters instead will cause your code to execute faster.

NOTE: In the examples I use in this book in which parameters are added to a SqlCommand, I access the parameters by name. For instance, I might call a parameter @ID or @Name. You can alternatively use ordinals, which are zero-based numbers that identify a particular parameter from the collection. Using ordinals as opposed to names will give you a performance boost because a name lookup isn't performed during parameter access. I have been down this road, though, too many times to advise you to use ordinals. I have seen my students get into too many situations in which the ordinals got mixed up, and they ended up using the wrong ones. Consider this choice carefully. If performance is important, use ordinals; otherwise, keep with names.

The size value is necessary only for fields that contain an actual size. For values such as numeric, this value can be omitted. Finally, the direction value indicates how the parameter will be used. Table 3.2 shows the four different direction values.

Stored Procedures and Parameter Binding

Calling stored procedures and binding parameter data work much like the SQL EXEC statement. This section shows how to call stored procedures, pass parameters in and out, and return the exit value of the stored procedure. I will create a stored procedure, pass values in and out of the procedure, and access the stored procedure's return value.

First, I have to create a stored procedure that does all this. For this example I'll take the SELECT statement used in the "Parameter Binding with SQL Commands" section and create a stored procedure in the Microsoft SQL Server Pubs database, as shown below.

 Create Procedure sp_GetAuthor     @ID varchar(11),     @Fname varchar(20) output,     @Lname varchar(40) output 

Table 3.2. Direction Values for Parameterized Queries

Direction	Description
Input	The parameter is an input parameter.
InputOutput	The parameter is capable of both input and output.
Output	The parameter is an output parameter.
ReturnValue	The parameter represents a return value.

 AS     SELECT @Fname = NULL     SELECT @LName = NULL     SELECT @Fname=au_fname, @Lname=au_lname FROM authors WHERE au_id=@ID     if(@Fname IS NULL)       return -100     else       return 0 

To illustrate the return value parameter, I have included an error condition in the stored procedure. When the SELECT statement fails, a 100 is returned after the procedure checks the @Fname value for null. The initialization of the two output parameters is a precaution in the event a value is passed.

RECOMMENDED PRACTICE: Use nVarChar whenever possible. It looks like this may not make sense for the Pubs database, but nVarChar is better in most cases. Essentially, using NVarChar makes internationalization much easier and is inexpensive to do up front.

If you don't use nVarChar whenever you can, then you must have custom installations of SQL Server that use a different alphabet for each language you need to support. I've seen successful sales or company growth turn this simple mistake into something very expensive.

The SQL Server parameter binding works exactly the same as the SQL command statement. The only parameter addition is the binding to reference the stored procedure's return value.

C#

 SqlConnection myConnection =       new SqlConnection( "server=localhost;uid=sa;pwd=;database=pubs" ); SqlCommand myCommand =   new SqlCommand("sp_GetAuthor", myConnection ); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add( "@ID", SqlDbType.VarChar, 11 ); myCommand.Parameters["@ID"].Direction = ParameterDirection.Input; myCommand.Parameters["@ID"].Value = List1.SelectedItem.Text; myCommand.Parameters.Add( "@Fname", SqlDbType.VarChar, 20 ); myCommand.Parameters["@Fname"].Direction = ParameterDirection.Output; myCommand.Parameters.Add( "@Lname", SqlDataType.VarChar, 40 ); myCommand.Parameters["@Lname"].Direction = ParameterDirection.Output; myCommand.Parameters.Add( "RETURN_VALUE", SqlDbType.Int ); myCommand.Parameters["RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; myConnection.Open(); myCommand.ExecuteNonQuery(); string strFirstName = myCommand.Parameters["@Fname"].Value.ToString(); string strLastName = myCommand.Parameters["@Lname"].Value.ToString(); strError = myCommand.Parameters["RETURN_VALUE"].Value.ToString(); myConnection.Close(); 

VB

 Dim myConnection as _  new SqlConnection( "server=localhost;uid=sa;pwd=;database=pubs" ) Dim myCommand as new SqlCommand("sp_GetAuthor", myConnection ) myCommand.CommandType = CommandType.StoredProcedure myCommand.Parameters.Add( "@ID", SqlDbType.VarChar, 11 ) myCommand.Parameters("@ID").Direction = ParameterDirection.Input myCommand.Parameters("@ID").Value = List1.SelectedItem.Text myCommand.Parameters.Add( "@Fname", SqlDbType.VarChar, 20 ) myCommand.Parameters("@Fname").Direction = ParameterDirection.Output myCommand.Parameters.Add( "@Lname", SqlDataType.VarChar, 40 ) myCommand.Parameters("@Lname").Direction = _   ParameterDirection.Output myCommand.Parameters.Add( "RETURN_VALUE", SqlDbType.Int ) myCommand.Parameters("RETURN_VALUE").Direction = ParameterDirection. ReturnValue myConnection.Open() myCommand.ExecuteNonQuery() string strFirstName = myCommand.Parameters("@Fname").Value.ToString() string strLastName = myCommand.Parameters("@Lname").Value.ToString() strError = myCommand.Parameters("RETURN_VALUE").Value.ToString() myConnection.Close() 

RECOMMENDED PRACTICE: Stored procedures are almost always preferred over ad hoc queries in your code. The following list summarizes the reasons:

• Stored procedures execute faster than ad hoc SQL because SQL Server has already compiled the procedures and created a plan.

• Stored procedures give you a single place to make changes or fix bugs when queries need changes.

• Stored procedures offer an abstraction to the application code under circumstances in which data access is separated from code.