Effective Use of ADO.NET: Creating a Survey Application

This chapter is from the book

ASP.NET Solutions - 23 Case Studies: Best Practices for Developers

Learn More Buy

This chapter is from the book

This chapter is from the book 

ASP.NET Solutions - 23 Case Studies: Best Practices for Developers

Learn More Buy

In This Chapter:

• ADO.NET Overview

• The Survey Application

• Extending and Modifying the Survey Application

• Deploying the Survey Application

This chapter talks about effectively using ADO.NET. I'll spend the first part of the chapter introducing ADO.NET, mostly how it relates to the SQL Server managed provider. In the second half of the chapter, I'll walk you through a Survey application that was built using ADO.NET, and which focuses on a number of best practices along with some recommended design patterns.

Because this is an odd-numbered chapter, the featured program's (the Survey application's) source code is shown in VB.NET (VB). For the full C#.NET (C#) source code, just go to www.ASPNET-Solutions.com, follow the links to the examples for Chapter 3, and download the C# source code. (You can also download the VB.NET source code from a link on the same page.)

I have included a section toward the end of the chapter named “Extending and Modifying the Survey Application.” In this section, I talk about ways to enhance the application so that it's even more useful. I'll post any changes I make to the Survey application (including these suggestions) on the Web site. Check for them on the Chapter 3 page. The site also includes a forum for discussion about the Survey application. And if you want to send me your modifications, I'll gladly post them for other users.

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

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

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

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.

// 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#

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

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:

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

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

C#

VB

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.

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

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.

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.

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

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

C#

VB