Before jumping into the depths of ADO.NET, step back and make sure that you understand some of the common tasks you might perform programmatically within ADO.NET. This section looks at the process of selecting, inserting, updating, and deleting data.

Imports Microsoft.VisualBasic
Imports System.Collections.Generic

Imports System.Data
Imports System.Data.SqlClient

Public Class SelectingData
    Public Function GetCompanyNameData() As List(Of String)
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdString As String = "Select CompanyName from Customers"
        conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=
           |DataDirectory|NORTHWND.MDF;Integrated Security=True;
           User Instance=True") ' Put this string on one line in your code
        cmd = New SqlCommand(cmdString, conn)
        conn.Open()

        Dim myReader As SqlDataReader
        Dim returnData As List(Of String) = New List(Of String)
        myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        While myReader.Read()
            returnData.Add(myReader("CompanyName").ToString())
        End While

        Return returnData
    End Function
End Class

Public Sub InsertData()
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    Dim cmdString As String = "Insert Customers (CustomerID, _
       CompanyName, ContactName) Values ('BILLE', 'XYZ Company', 'Bill Evjen')"
    conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=
           |DataDirectory|NORTHWND.MDF;Integrated Security=True;
           User Instance=True") ' Put this string on one line in your code
    cmd = New SqlCommand(cmdString, conn)
    conn.Open()

    cmd.ExecuteNonQuery()
    conn.Close()
End Sub

Public Function UpdateEmployeeBonus() As Integer
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    Dim RecordsAffected as Integer
    Dim cmdString As String = "UPDATE Employees SET emp_bonus=1000 WHERE " & _
       "yrs_duty>=5"
    conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=
           |DataDirectory|NORTHWND.MDF;Integrated Security=True;
           User Instance=True") ' Put this string on one line in your code
    cmd = New SqlCommand(cmdString, conn)
    conn.Open()

    RecordsAffected = cmd.ExecuteNonQuery()
    conn.Close()

    Return RecordsAffected
End Function

Public Function DeleteEmployee() As Integer
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    Dim RecordsAffected as Integer
    Dim cmdString As String = "DELETE Employees WHERE LastName='Evjen'"
    conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=
           |DataDirectory|NORTHWND.MDF;Integrated Security=True;
           User Instance=True") ' Put this string on one line in your code
    cmd = New SqlCommand(cmdString, conn)
    conn.Open()

    RecordsAffected = cmd.ExecuteNonQuery()

conn.Close()

    Return RecordsAffected
End Function

The six core ADO.NET namespaces are shown in the following table. In addition to these namespaces, each new data provider can have its own namespace. As an example, the Oracle .NET data provider adds a namespace of System.Data.OracleClient (for the Microsoft-built Oracle data provider).

ADO.NET has three distinct types of classes commonly referred to as disconnected, shared, and data providers. The disconnected classes provide the basic structure for the ADO.NET Framework. A good example of this type of class is the DataTable class. The objects of this class are capable of storing data without any dependency on a specific data provider. The Shared classes form the base classes for data providers and are shared among all data providers. The data provider classes are meant to work with different kinds of data sources. They are used to perform all data-management operations on specific databases. The SqlClient data provider, for example, works only with the SQL Server database.

A data provider contains Connection, Command, DataAdapter, and DataReader objects. Typically, in programming ADO.NET, you first create the Connection object and provide it with the necessary information, such as the connection string. You then create a Command object and provide it with the details of the SQL command that is to be executed. This command can be an inline SQL text command, a stored procedure, or direct table access. You can also provide parameters to these commands if needed.

After you create the Connection and the Command objects, you must decide whether the command returns a result set. If the command doesn't return a result set, then you can simply execute the command by calling one of its several Execute methods. Conversely, if the command returns a result set, you must decide whether you want to retain the result set for future use without maintaining the connection to the database. If you want to retain the result set, then you must create a DataAdapter object and use it to fill a DataSet or a DataTable object. These objects are capable of maintaining their information in a disconnected mode. However, if you don't want to retain the result set, but rather to simply process the command in a swift fashion, then you can use the Command object to create a DataReader object. The DataReader object needs a live connection to the database, and it works as a forward-only, read-only cursor.

To better support the disconnected model as defined above, the ADO.NET components separate data access from data manipulation. This is accomplished via two main components: the DataSet and the .NET Data Provider. Figure 9-1 illustrates the concept of separating data access from data manipulation.

Figure 9.1. Figure 9-1

The DataSet is the core component of the disconnected architecture of ADO.NET. It is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, with XML data, or even to manage data local to an application such as an in-memory data cache. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint, and relation information about the data in the DataTable objects. It is basically an in-memory database, but what sets it apart is that it doesn't care whether its data is obtained from a database, an XML file, a combination of the two, or somewhere else. You can apply inserts, updates, and deletes to the DataSet and then push the changes back to the data source, no matter where the data source lives! This chapter offers an in-depth look at the DataSet object family.

The other core element of the ADO.NET architecture is the .NET Data Provider, whose components are designed for data manipulation (as opposed to data access with the DataSet). These components are listed in the following table.

The DataAdapter uses Command objects to execute SQL commands at the data source, both to load the DataSet with data and to reconcile changes made to the data in the DataSet with the data source. You will take a closer look at this later in the detailed discussion of the DataAdapter object.

The .NET Framework 3.5 ships with three .NET Data Providers: the SQL Server .NET Data Provider, the Oracle .NET Data Provider, and the OLE DB .NET Data Provider.

The rule of thumb when deciding which data provider to use is to first use a .NET Relational Database Management System (RDBMS)–specific data provider if it is available, and to use the .NET OLE DB Provider when connecting to any other data source. (Most RDBMS vendors are now producing their own .NET Data Providers in order to encourage .NET developers to use their databases.)

For example, if you were writing an application that uses SQL Server, then you would want to use the SQL Server .NET Data Provider. The .NET OLE DB Provider is used to access any data source exposed through OLE DB, such as Microsoft Access, Open DataBase Connectivity (ODBC), and so on. You will be taking a closer look at these later.

To connect to a specific data source, you use a data Connection object. To connect to Microsoft SQL Server 7.0 or later, you need to use the SqlConnection object of the SQL Server .NET Data Provider. You need to use the OleDbConnection object of the OLE DB .NET Data Provider to connect to an OLE DB data source, or the OLE DB Provider for SQL Server (SQLOLEDB) to connect to versions of Microsoft SQL Server earlier than 7.0.

Provider=msdaora;Data Source=MyOracleDB;UserId=myUsername;Password=myPassword;

Data Source=(local);Initial Catalog=pubs;Integrated Security=SSPI;

After establishing a connection, you can execute commands and return results from a data source (such as SQL Server) using a Command object. A Command object can be created using the Command constructor, or by calling the CreateCommand method of the Connection object. When creating a Command object using the Command constructor, you need to specify a SQL statement to execute at the data source, and a Connection object. The Command object's SQL statement can be queried and modified using the CommandText property. The following code is an example of executing a SELECT command and returning a DataReader object:

' Build the SQL and Connection strings.
Dim sql As String = "SELECT * FROM authors"
Dim connectionString As String = "Initial Catalog=pubs;" _
 & "Data Source=(local);Integrated Security=SSPI;"
' Initialize the SqlCommand with the SQL
' and Connection strings.
Dim command As SqlCommand = New SqlCommand(sql, _

New SqlConnection(connectionString))
' Open the connection.
command.Connection.Open()
' Execute the query, return a SqlDataReader object.
' CommandBehavior.CloseConnection flags the
' DataReader to automatically close the DB connection
' when it is closed.
Dim dataReader As SqlDataReader = _
    command.ExecuteReader(CommandBehavior.CloseConnection)

The CommandText property of the Command object executes all SQL statements in addition to the standard SELECT, UPDATE, INSERT, and DELETE statements. For example, you could create tables, foreign keys, primary keys, and so on, by executing the applicable SQL from the Command object.

The Command object exposes several Execute methods to perform the intended action. When returning results as a stream of data, ExecuteReader is used to return a DataReader object. ExecuteScalar is used to return a singleton value. In ADO.NET, the ExecuteRow method has been added, which returns a single row of data in the form of a SqlRecord object. ExecuteNonQuery is used to execute commands that do not return rows, which usually includes stored procedures that have output parameters and/or return values. (You will learn about stored procedures in a later section.)

When using a DataAdapter with a DataSet, Command objects are used to return and modify data at the data source through the DataAdapter object's SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties.

The InsertCommand, UpdateCommand, and DeleteCommand properties must be set before the Update method is called. You will take a closer look at this when you look at the DataAdapter object.

This section offers a quick look at how to use stored procedures, before delving into a more complex example later in the chapter demonstrating how you can build a reusable data-access component that also uses stored procedures. The motivation for using stored procedures is simple. Imagine you have the following code:

SELECT au_lname FROM authors WHERE au_id='172-32-1176'

If you pass that to SQL Server using ExecuteReader on SqlCommand (or any execute method, for that matter), SQL Server has to compile the code before it can run it, in much the same way that VB .NET applications have to be compiled before they can be executed. This compilation takes up SQL Server's time, so it is easy to deduce that if you can reduce the amount of compilation that SQL Server has to do, database performance should increase. (Compare the speed of execution of a compiled application against interpreted code.)

That's what stored procedures are all about: you create a procedure, store it in the database, and because the procedure is recognized and understood ahead of time, it can be compiled ahead of time and ready for use in your application.

Stored procedures are very easy to use, but the code to access them is sometimes a little verbose. The next section demonstrates some code that can make accessing stored procedures a bit more straightforward, but to make things clearer, let's start by building a simple application that demonstrates how to create and call a stored procedure.

Creating a Stored Procedure

To create a stored procedure, you can either use the tools in Visual Studio .NET or you can use the tools in SQL Server's Enterprise Manager if you are using SQL Server 2000, or in SQL Server Management Studio if you are using SQL Server 2005/2008. (Technically, you can use a third-party tool or just create the stored procedure in a good, old-fashioned SQL script.)

This example builds a stored procedure that returns all of the columns for a given author ID. The SQL to do this looks like this:

SELECT
   au_id, au_lname, au_fname, phone,
   address, city, state, zip, contract
FROM
    authors
WHERE
    au_id = whatever author ID you want

The "whatever author ID you want" part is important. When using stored procedures, you typically have to be able to provide parameters into the stored procedure and use them from within code. This is not a book about SQL Server, so this example focuses only on the principle involved. You can find many resources on the Web about building stored procedures (they have been around a very long time, and they are most definitely not a .NET-specific feature).

Variables in SQL Server are prefixed by the @ symbol, so if you have a variable called au id, then your SQL will look like this:

SELECT
    au_id, au_lname, au_fname, phone,
    address, city, state, zip, contract
FROM
    authors
WHERE
    au_id = @au_id

In Visual Studio 2008, stored procedures can be accessed using the Server Explorer. Simply add a new data connection (or use an existing data connection), and then drill down into the Stored Procedures folder in the management tree. A number of stored procedures are already loaded. The byroyalty procedure is a stored procedure provided by the sample pubs database developers. Figure 9-2 illustrates the stored procedures of the pubs database in Visual Studio 2008.

Figure 9.2. Figure 9-2

To create a new stored procedure, just right-click the Stored Procedures folder in the Server Explorer and select Add New Stored Procedure to invoke the Editor window.

A stored procedure can be either a single SQL statement or a complex set of statements. T-SQL supports branches, loops, and other variable declarations, which can make for some pretty complex stored procedure code. However, your stored procedure is just a single line of SQL. You need to declare the parameter that you want to pass in (@au_id) and the name of the procedure: usp_authors_Get_By_ID. Here's code for the stored procedure:

CREATE PROCEDURE usp_authors_Get_By_ID
    @au_id varchar(11)
AS
SELECT
    au_id, au_lname, au_fname, phone,
    address, city, state, zip, contract
FROM
    authors
WHERE
    au_id = @au_id

Click OK to save the stored procedure in the database. You are now able to access this stored procedure from code.

Calling the Stored Procedure

Calling the stored procedure is just a matter of creating a SqlConnection object to connect to the database, and a SqlCommand object to run the stored procedure.

The sample code for this chapter demonstrates a solution called Examples.sln, which includes a project called AdoNetFeaturesTest.

Note

For all of the data-access examples in this chapter, you need the pubs database, which can be downloaded from MSDN. In addition, be sure to run the examples.sql file — available with the code download for this chapter — in SQL Server 2005 Management Studio before running the code examples. This creates the necessary stored procedures and functions in the pubs database. You can also use the SQL Server Express Edition of the pubs database, PUBS.MDF, also found on MSDN.

Now you have to decide what you want to return by calling the stored procedure. In this case, you return an instance of the SqlDataReader object. The TestForm.vb file contains a method called GetAuthorSqlReader that takes an author ID and returns an instance of a SqlDataReader. Here is the code for the method:

Private Function GetAuthorSqlReader(ByVal authorId As String) As SqlDataReader
    ' Build a SqlCommand
    Dim command As SqlCommand = New SqlCommand("usp_authors_Get_By_ID", _
        GetPubsConnection())
    ' Tell the command we are calling a stored procedure
    command.CommandType = CommandType.StoredProcedure
    ' Add the @au_id parameter information to the command
    command.Parameters.Add(New SqlParameter("@au_id", authorId))
    ' The reader requires an open connection
    command.Connection.Open()
    ' Execute the sql and return the reader
    Return command.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Notice that in the SqlCommand's constructor call, you have factored out creating a connection to the pubs database into a separate helper method. This is used later in other code examples in your form.

Here is the code for the GetPubsConnection helper method:

Private Function GetPubsConnection() As SqlConnection
    ' Build a SqlConnection based on the config value.
    Return New _
        SqlConnection(ConfigurationSettings.AppSettings("dbConnectionString"))
End Function

The most significant thing this code does is grab a connection string to the database from the application's configuration file, app.config. Here is what the entry in the app.config file looks like:

<appSettings>
    <add key="dbConnectionString" value="data source=(local);initial
         catalog=pubs;Integrated Security=SSPI;" />
</appSettings>

Although the helper method does not do much, it is nice to place this code in a separate method. This way, if the code to get a connection to the databases needs to be changed, then the code only has to be changed in one place.

Accessing a stored procedure is more verbose (but not more difficult) than accessing a normal SQL statement through the methods discussed thus far. The approach is as follows:

1. Create a SqlCommand object.

2. Configure it to access a stored procedure by setting the CommandType property.

3. Add parameters that exactly match those in the stored procedure itself.

4. Execute the stored procedure using one of the SqlCommand object's Execute*** methods

There is no real need to build an impressive UI for this application, as we're about to add a button named getAuthorByIdButton that calls the GetAuthorSqlRecord helper method and displays the selected author's name. Here is the button's Click event handler:

Private Sub _getAuthorByIdButton_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles _getAuthorByIdButton.Click
    Dim reader As SqlDataReader = Me. GetAuthorSqlReader ("409-56-7008")
    If reader.Read()
        MessageBox.Show(reader("au_fname").ToString() & "  " _
            & reader("au_lname").ToString())
    End If

    reader.Close()
End Sub

This has hard-coded an author ID of 409-56-7008. Run the code now and you should see the result shown in Figure 9-3.

Figure 9.3. Figure 9-3

You can use the DataReader to retrieve a read-only, forward-only stream of data from the database. Using the DataReader can increase application performance and reduce system overhead because only one buffered row at a time is ever in memory. With the DataReader object, you are getting as close to the raw data as possible in ADO.NET; you do not have to go through the overhead of populating a DataSet object, which sometimes may be expensive if the DataSet contains a lot of data. The disadvantage of using a DataReader object is that it requires an open database connection and increases network activity.

After creating an instance of the Command object, a DataReader is created by calling the ExecuteReader method of the Command object. Here is an example of creating a DataReader and iterating through it to print out its values to the screen:

Private Sub TraverseDataReader()

    ' Build the SQL and Connection strings.
    Dim sql As String = "SELECT * FROM authors"
    Dim connectionString As String = "Initial Catalog=pubs;" _
        & "Data Source=(local);Integrated Security=SSPI;"

    ' Initialize the SqlCommand with the SQL query and connection strings.
    Dim command As SqlCommand = New SqlCommand(sql, _
        New SqlConnection(connectionString))
    ' Open the connection.
    command.Connection.Open()
    ' Execute the query, return a SqlDataReader object.
    ' CommandBehavior.CloseConnection flags the
    ' DataReader to automatically close the DB connection
    ' when it is closed.
    Dim reader As SqlDataReader = _
        command.ExecuteReader(CommandBehavior.CloseConnection)
    ' Loop through the records and print the values.
    Do While reader.Read
        Console.WriteLine(reader.GetString(1) & " " & reader.GetString(2))
    Loop
    ' Close the DataReader (and its connection).
    reader.Close()

End Sub

This code snippet uses the SqlCommand object to execute the query via the ExecuteReader method. This method returns a populated SqlDataReader object, which you loop through and then print out the author names. The main difference between this code and looping through the rows of a DataTable is that you have to stay connected while you loop through the data in the DataReader object; this is because the DataReader reads in only a small stream of data at a time to conserve memory space.

The Read method of the DataReader object is used to obtain a row from the results of the query. Each column of the returned row may be accessed by passing the name or ordinal reference of the column to the DataReader; or, for best performance, the DataReader provides a series of methods that enable you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). Using the typed accessor methods when the underlying data type is known reduces the amount of type conversion required (converting from type Object) when retrieving the column value.

The DataReader provides a nonbuffered stream of data that enables procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data; only one row of data is cached in memory at a time. You should always call the Close method when you are through using the DataReader object, as well as close the DataReader object's database connection; otherwise, the connection will not be closed until the garbage collector gets around to collecting the object.

Note how you use the CommandBehavior.CloseConnection enumeration value on the SqlDataReader.ExecuteReader method. This tells the SqlCommand object to automatically close the database connection when the SqlDataReader.Close method is called.

In ADO.NET, additional support enables Command objects to execute their commands asynchronously, which can result in a huge perceived performance gain in many applications, especially in Windows Forms applications. This can come in very handy, especially if you ever have to execute a long-running SQL statement. This section examines how this functionality enables you to add asynchronous processing to enhance the responsiveness of an application.

The SqlCommand object provides three different asynchronous call options: BeginExecuteReader, BeginExecuteNonQuery, and BeginExecuteXmlReader. Each of these methods has a corresponding "end" method — that is, EndExecuteReader, EndExecutreNonQuery, and EndExecuteXmlReader. Now that you are familiar with the DataReader object, let's look at an example using the BeginExecuteReader method to execute a long-running query.

In the AdoNetFeaturesTest project, I have added a Button and an associated Click event handler to the form that will initiate the asynchronous call to get a DataReader instance:

Private Sub _testAsyncCallButton_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles _testAsyncCallButton.Click

        ' Build a connection for the async call to the database.
        Dim connection As SqlConnection = GetPubsConnection()
        connection.ConnectionString &= "Asynchronous Processing=true;"

        ' Build a command to call the stored procedure.
        Dim command As New SqlCommand("usp_Long_Running_Procedure", _
           connection)

        ' Set the command type to stored procedure.
        command.CommandType = CommandType.StoredProcedure

        ' The reader requires an open connection.
        connection.Open()

' Make the asynchronous call to the database.
        command.BeginExecuteReader(AddressOf Me.AsyncCallback, _
        command, CommandBehavior.CloseConnection)
    End Sub

The first thing you do is reuse your helper method GetPubsConnection to get a connection to the pubs database. Next, and this is very important, you append the statement Asynchronous Processing=true to your Connection object's connection string. This must be set in order for ADO.NET to make asynchronous calls to SQL Server.

After getting the connection set, you then build a SqlCommand object and initialize it to be able to execute the usp_Long_Running_Procedure stored procedure. This procedure uses the SQL Server 2005 WAITFOR DELAY statement to create a 20-second delay before it executes the usp_Authors_Get_All stored procedure. As you can probably guess, the usp_authors_Get_All stored procedure simply selects all of the authors from the authors table. The delay is added simply to demonstrate that while this stored procedure is executing, you can perform other tasks in your Windows Forms application. Here is the SQL code for the usp_Long_Running_Procedure stored procedure:

CREATE PROCEDURE usp_Long_Running_Procedure
AS
SET NOCOUNT ON

WAITFOR DELAY '00:00:20'
EXEC usp_authors_Get_All

The last line of code in the Button's Click event handler is the call to BeginExecuteReader. In this call, the first thing you are passing in is a delegate method (Me.AsyncCallback) for the System.AsyncCallback delegate type. This is how the .NET Framework calls you back once the method is finished running asynchronously. You then pass in your initialized SqlCommand object so that it can be executed, as well as the CommandBehavior value for the DataReader. In this case, you pass in the CommandBehavior.CloseConnection value so that the connection to the database will be closed once the DataReader has been closed. You will look at the DataReader in more detail in the next section.

Now that you have initiated the asynchronous call, and have defined a callback for your asynchronous call, let's look at the actual method that is being called back, the AsyncCallback method:

Private Sub AsyncCallback(ByVal ar As IAsyncResult)
    ' Get the command that was passed from the AsyncState of the IAsyncResult.
    Dim command As SqlCommand = CType(ar.AsyncState, SqlCommand)
    ' Get the reader from the IAsyncResult.
    Dim reader As SqlDataReader = command.EndExecuteReader(ar)
    ' Get a table from the reader.
    Dim table As DataTable = Me.GetTableFromReader(reader, "Authors")
    ' Call the BindGrid method on the Windows main thread,
    ' passing in the table.
    Me.Invoke(New BindGridDelegate(AddressOf Me.BindGrid), _
        New Object() {table})
End Sub

The first line of the code is simply retrieving the SqlCommand object from the AsyncState property of the IAsyncResult that was passed in. Remember that when you called BeginExecuteReader earlier, you passed in your SqlCommand object. You need it so that you can call the EndExecuteReader method on the next line. This method gives you your SqlDataReader. On the next line, you then transform the SqlDataReader into a DataTable (covered later when the DataSet is discussed).

The last line of this method is probably the most important. If you tried to just take your DataTable and bind it to the grid, it would not work, because right now you are executing on a thread other than the main Windows thread. The helper method named BindGrid can do the data binding, but it must be called only in the context of the Windows main thread. To bring the data back to the main Windows thread, it must be marshaled via the Invoke method of the Form object. Invoke takes two arguments: the delegate of the method you want to call and (optionally) any parameters for that method. In this case, you define a delegate for the BindGrid method, called BindGridDelegate. Here is the delegate declaration:

Private Delegate Sub BindGridDelegate(ByVal table As DataTable)

Notice how the signature is exactly the same as the BindGrid method shown here:

Private Sub BindGrid(ByVal table As DataTable)
    ' Clear the grid.
    Me._authorsGridView.DataSource = Nothing
    ' Bind the grid to the DataTable.
    Me._authorsGridView.DataSource = table
End Sub

Here is another look at the call to the form's Invoke method:

Me.Invoke(New BindGridDelegate(AddressOf Me.BindGrid), _
    New Object() {table})

You pass in a new instance of the BindGridDelegate delegate and initialize it with a pointer to the BindGrid method. As a result, the .NET worker thread that was executing your query can now safely join up with the main Windows thread.

Each .NET Data Provider included with the .NET Framework has a DataAdapter object. The OLE DB .NET Data Provider includes an OleDbDataAdapter object, and the SQL Server .NET Data Provider includes a SqlDataAdapter object. A DataAdapter is used to retrieve data from a data source and populate DataTable objects and constraints within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Data Provider to connect to a data source, and Command objects to retrieve data from, and resolve changes to, the data source from a DataSet object.

This differs from the DataReader, in that the DataReader uses the Connection object to access the data directly, without having to use a DataAdapter. The DataAdapter essentially decouples the DataSet object from the actual source of the data, whereas the DataReader is tightly bound to the data in a read-only fashion.

The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. A nice, convenient way to set the DataAdapter's SelectCommand property is to pass in a Command object in the DataAdapter's constructor. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to the modifications made to the data in the DataSet. The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. It also adds or refreshes rows in the DataSet to match those in the data source. The following example code demonstrates how to fill a DataSet object with information from the authors table in the pubs database:

Private Sub TraverseDataSet()
    ' Build the SQL and Connection strings.
    Dim sql As String = "SELECT * FROM authors"
    Dim connectionString As String = "Initial Catalog=pubs;" _
        & "Data Source=(local);Integrated Security=SSPI;"

    ' Initialize the SqlDataAdapter with the SQL
    ' and Connection strings, and then use the
    ' SqlDataAdapter to fill the DataSet with data.
    Dim adapter As New SqlDataAdapter(sql, connectionString)
    Dim authors As New DataSet
    adapter.Fill(authors)

    ' Iterate through the DataSet's table.
    For Each row As DataRow In authors.Tables(0).Rows
        Console.WriteLine(row("au_fname").ToString _
            & " " & row("au_lname").ToString)
    Next

    ' Print the DataSet's XML.
    Console.WriteLine(authors.GetXml())
    Console.ReadLine()

End Sub

Note how you use the constructor of the SqlDataAdapter to pass in and set the SelectCommand, as well as pass in the connection string in lieu of a SqlCommand object that already has an initialized Connection property. You then just call the SqlDataAdapter object's Fill method and pass in an initialized DataSet object. If the DataSet object is not initialized, then the Fill method raises an exception (System.ArgumentNullException).

Ever since ADO.NET 2.0, a significant performance improvement was made in the way that the DataAdapter updates the database. In ADO.NET 1.x, the DataAdapter's Update method would loop through each row of every DataTable object in the DataSet and subsequently make a trip to the database for each row being updated. In ADO.NET 2.0, batch update support was added to the DataAdapter. This means that when the Update method is called, the DataAdapter batches all of the updates from the DataSet in one trip to the database.

Now let's take a look at a more advanced example. Here, you use a DataAdapter to insert, update, and delete data from a DataTable back to the pubs database:

Private Sub _batchUpdateButton_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles _batchUpdateButton.Click

        ' Build insert, update, and delete commands.

' Build the parameter values.
        Dim insertUpdateParams() As String = {"@au_id", "@au_lname", _
            "@au_fname", _
            "@phone", "@address", "@city", "@state", "@zip", "@contract"}

The preceding code begins by initializing a string array of parameter names to pass into the BuildSqlCommand helper method:

' Insert command.
Dim insertCommand As SqlCommand = _
    BuildSqlCommand("usp_authors_Insert", _
    insertUpdateParams)

Next, you pass the name of the stored procedure to execute and the parameters for the stored procedure to the BuildSqlCommand helper method. This method returns an initialized instance of the SqlCommand class. Here is the BuildSqlCommand helper method:

Private Function BuildSqlCommand(ByVal storedProcedureName As String, _
        ByVal parameterNames() As String) As SqlCommand
    ' Build a SqlCommand.
    Dim command As New SqlCommand(storedProcedureName, GetPubsConnection())
    ' Set the command type to stored procedure.
    command.CommandType = CommandType.StoredProcedure
    ' Build the parameters for the command.
    ' See if any parameter names were passed in.
    If Not parameterNames Is Nothing Then
        ' Iterate through the parameters.
        Dim parameter As SqlParameter = Nothing
        For Each parameterName As String In parameterNames
            ' Create a new SqlParameter.
            parameter = New SqlParameter()
            parameter.ParameterName = parameterName
            ' Map the parameter to a column name in the DataTable/DataSet.
            parameter.SourceColumn = parameterName.Substring(1)
            ' Add the parameter to the command.
            command.Parameters.Add(parameter)
        Next
    End If
    Return command
End Function

This method first initializes a SqlCommand class and passes in the name of a stored procedure; it then uses the GetPubsConnection helper method to pass in a SqlConnection object to the SqlCommand. The next step is to set the command type of the SqlCommand to a stored procedure. This is important because ADO.NET uses this to optimize how the stored procedure is called on the database server. You then check whether any parameter names have been passed (via the parameterNames string array); if so, you iterate through them. While iterating through the parameter names, you build up SqlParameter objects and add them to the SqlCommand's collection of parameters.

The most important step in building up the SqlParameter object is setting its SourceColumn property. This is what the DataAdapter later uses to map the name of the parameter to the name of the column in the DataTable when its Update method is called. An example of such a mapping is associating the @au_id parameter name with the au_id column name. As shown in the code, the mapping assumes that the stored procedure parameters all have exactly the same names as the columns, except for the mandatory @ character in front of the parameter. That's why when assigning the SqlParameter's SourceColumn property value, you use the Substring method to strip off the @ character to ensure that it maps correctly.

You then call the BuildSqlCommand method two more times to build your update and delete SqlCommand objects:

' Update command.
Dim updateCommand As SqlCommand = _
    BuildSqlCommand("usp_authors_Update", _
    insertUpdateParams)

' Delete command.
Dim deleteCommand As SqlCommand = _
    BuildSqlCommand("usp_authors_Delete", _
    New String() {"@au_id"})

Now that the SqlCommand objects have been created, the next step is to create a SqlDataAdapter object. Once the SqlDataAdapter is created, you set its InsertCommand, UpdateCommand, and DeleteCommand properties with the respective SqlCommand objects that you just built:

' Create an adapter.
Dim adapter As New SqlDataAdapter()

' Associate the commands with the adapter.
adapter.InsertCommand = insertCommand
adapter.UpdateCommand = updateCommand
adapter.DeleteCommand = deleteCommand

The next step is to get a DataTable instance of the authors table from the pubs database. You do this by calling the GetAuthorsSqlReader helper method to first get a DataReader and then the GetTableFromReader helper method to load a DataTable from a DataReader:

' Get the authors reader.
Dim reader As SqlDataReader = GetAuthorsSqlReader()
' Load a DataTable from the reader.
Dim table As DataTable = GetTableFromReader(reader, "Authors")

Once you have your DataTable filled with data, you begin modifying it so you can test the new batch update capability of the DataAdapter. The first change to make is an insert in the DataTable. In order to add a row, you first call the DataTable's NewRow method to give you a DataRow initialized with the same columns as your DataTable:

' Add a new author to the DataTable.
Dim row As DataRow = table.NewRow

Once that is done, you can set the values of the columns of the DataRow:

row("au_id") = "335-22-0707"
row("au_fname") = "Bill"

row("au_lname") = "Evjen"
row("phone") = "800-555-1212"
row("contract") = 0

Then you call the Add method of the DataTable's DataRowCollection property and pass in the newly populated DataRow object:

table.Rows.Add(row)

Now that there is a new row in the DataTable, the next test is to update one of its rows:

' Change an author in the DataTable.
table.Rows(0)("au_fname") = "Updated Name!"

Finally, you delete a row from the DataTable. In this case, it is the second-to-last row in the DataTable:

' Delete the second to last author from the table
table.Rows(table.Rows.Count - 2).Delete()

Now that you have performed an insert, update, and delete action on your DataTable, it is time to send the changes back to the database. You do this by calling the DataAdapter's Update method and passing in either a DataSet or a DataTable. Note that you are calling the GetChanges method of the DataTable; this is important, because you only want to send the changes to the DataAdapter:

' Send only the changes in the DataTable to the database for updating.
adapter.Update(table.GetChanges())

To prove that the update worked, you get back a new DataTable from the server using the same technique as before, and then bind it to the grid with your helper method to view the changes that were made:

' Get the new changes back from the server to show that the update worked.
      reader = GetAuthorsSqlReader()
      table = GetTableFromReader(reader, "Authors")
      ' Bind the grid to the new table data.
      BindGrid(table)
  End Sub

The SQL Server .NET Data Provider uses Tabular Data Stream (TDS) to communicate with the SQL Server. This offers a great performance increase, as TDS is SQL Server's native communication protocol. As an example of how much of an increase you can expect, when I ran some simple tests accessing the authors table of the pubs database, the SQL Server .NET Data Provider performed about 70 percent faster than the OLE DB .NET Data Provider.

The SQL Server .NET Data Provider is lightweight and performs very well, thanks to not having to go through the OLE DB or ODBC layer. What it actually does is establish a network connection (usually sockets-based) and drag data from this directly into managed code and vice versa.

To use this provider, you need to include the System.Data.SqlClient namespace in your application. Note that it works only for SQL Server 7.0 and later. I highly recommend using the SQL Server .NET Data Provider any time you are connecting to a SQL Server 7.0 and later database server. The SQL Server .NET Data Provider requires the installation of MDAC 2.6 or later.

The OLE DB .NET Data Provider uses native OLE DB through COM interop to enable data access. The OLE DB .NET Data Provider supports both manual and automatic transactions. For automatic transactions, the OLE DB .NET Data Provider automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services. The OLE DB .NET Data Provider does not support OLE DB 2.5 interfaces. OLE DB Providers that require support for OLE DB 2.5 interfaces will not function properly with the OLE DB .NET Data Provider. This includes the Microsoft OLE DB Provider for Exchange and the Microsoft OLE DB Provider for Internet Publishing. The OLE DB .NET Data Provider requires the installation of MDAC 2.6 or later. To use this provider, you need to include the System.Data.OleDb namespace in your application.

An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. The DataTableCollection contains all of the DataTable objects in a DataSet.

A DataTable is defined in the System.Data namespace and represents a single table of memory-resident data. It contains a collection of columns represented by the DataColumnCollection, which defines the schema and rows of the table. It also contains a collection of rows represented by the DataRowCollection, which contains the data in the table. Along with the current state, a DataRow retains its original state and tracks changes that occur to the data.

A DataSet contains relationships in its DataRelationCollection object. A relationship (represented by the DataRelation object) associates rows in one DataTable with rows in another DataTable. The relationships in the DataSet can have constraints, which are represented by UniqueConstraint and ForeignKeyConstraint objects. It is analogous to a JOIN path that might exist between the primary and foreign key columns in a relational database. A DataRelation identifies matching columns in two tables of a DataSet.

Relationships enable you to see what links information within one table to another. The essential elements of a DataRelation are the name of the relationship, the two tables being related, and the related columns in each table. Relationships can be built with more than one column per table, with an array of DataColumn objects for the key columns. When a relationship is added to the DataRelationCollection, it may optionally add ForeignKeyConstraints that disallow any changes that would invalidate the relationship.

DataSet (as well as DataTable and DataColumn) has an ExtendedProperties property. ExtendedProperties is a PropertyCollection in which a user can place customized information, such as the SELECT statement that is used to generate the result set, or a date/time stamp indicating when the data was generated. Because the ExtendedProperties contains customized information, this is a good place to store extra user-defined data about the DataSet (or DataTable or DataColumn), such as a time when the data should be refreshed. The ExtendedProperties collection is persisted with the schema information for the DataSet (as well as DataTable and DataColumn). The following code is an example of adding an expiration property to a DataSet:

Private Shared Sub DataSetExtended()

    ' Build the SQL and Connection strings.
    Dim sql As String = "SELECT * FROM authors"
    Dim connectionString As String = "Initial Catalog=pubs;" _
        & "Data Source=(local);Integrated Security=SSPI;"

    ' Initialize the SqlDataAdapter with the SQL
    ' and Connection strings, and then use the
    ' SqlDataAdapter to fill the DataSet with data.

Dim adapter As SqlDataAdapter = _
        New SqlDataAdapter(sql, connectionString)
    Dim authors As New DataSet
    adapter.Fill(authors)

    ' Add an extended property called "expiration."
    ' Set its value to the current date/time + 1 hour.
    authors.ExtendedProperties.Add("expiration", _
        DateAdd(DateInterval.Hour, 1, Now))

    Console.Write(authors.ExtendedProperties("expiration").ToString)
    Console.ReadLine()

End Sub

This code begins by filling a DataSet with the authors table from the pubs database. It then adds a new extended property, called expiration, and sets its value to the current date and time plus one hour. You then simply read it back. As you can see, it is very easy to add extended properties to DataSet objects. The same pattern also applies to DataTable and DataColumn objects.

The ADO.NET DataSet is a memory-resident representation of the data that provides a consistent relational programming model, regardless of the source of the data it contains. A DataSet represents a complete set of data, including the tables that contain, order, and constrain the data, as well as the relationships between the tables. The advantage to using a DataSet is that the data it contains can come from multiple sources, and it is fairly easy to get the data from multiple sources into the DataSet. In addition, you can define your own constraints between the DataTables in a DataSet.

There are several methods for working with a DataSet, which can be applied independently or in combination:

Here is a typical usage scenario for a DataSet object:

The design of the ADO.NET DataSet makes this scenario fairly easy to implement. Because the DataSet is stateless, it can be safely passed between the server and the client without tying up server resources such as database connections. Although the DataSet is transmitted as XML, Web services and ADO.NET automatically transform the XML representation of the data to and from a DataSet, creating a rich, yet simplified, programming model.

In addition, because the DataSet is transmitted as an XML stream, non-ADO.NET clients can consume the same Web service consumed by ADO.NET clients. Similarly, ADO.NET clients can interact easily with non-ADO.NET Web services by sending any client DataSet to a Web service as XML and by consuming any XML returned as a DataSet from the Web service. However, note the size of the data; if your DataSet contains a large number of rows, then it will eat up a lot of bandwidth.

Private Sub BuildDataSet()

    Dim customerOrders As New Data.DataSet("CustomerOrders")
    Dim customers As Data.DataTable = customerOrders.Tables.Add("Customers")
    Dim orders As Data.DataTable = customerOrders.Tables.Add("Orders")
    Dim row As Data.DataRow

    With customers
        .Columns.Add("CustomerID", Type.GetType("System.Int32"))
        .Columns.Add("FirstName", Type.GetType("System.String"))
        .Columns.Add("LastName", Type.GetType("System.String"))
        .Columns.Add("Phone", Type.GetType("System.String"))
        .Columns.Add("Email", Type.GetType("System.String"))
    End With

    With orders
        .Columns.Add("CustomerID", Type.GetType("System.Int32"))
        .Columns.Add("OrderID", Type.GetType("System.Int32"))
        .Columns.Add("OrderAmount", Type.GetType("System.Double"))
        .Columns.Add("OrderDate", Type.GetType("System.DateTime"))
    End With

    customerOrders.Relations.Add("Customers_Orders", _
    customerOrders.Tables("Customers").Columns("CustomerID"), _
    customerOrders.Tables("Orders").Columns("CustomerID"))

row = customers.NewRow()
    row("CustomerID") = 1
    row("FirstName") = "Bill"
    row("LastName") = "Evjen"
    row("Phone") = "555-1212"
    row("Email") = "[email protected]"
    customers.Rows.Add(row)

    row = orders.NewRow()
    row("CustomerID") = 1
    row("OrderID") = 22
    row("OrderAmount") = 0
    row("OrderDate") = #11/10/1997#
    orders.Rows.Add(row)

    Console.WriteLine(customerOrders.GetXml())
    Console.ReadLine()

End Sub

<CustomerOrders>
  <Customers>
    <CustomerID>1</CustomerID>
    <FirstName>Bill</FirstName>
    <LastName>Evjen</LastName>
    <Phone>555-1212</Phone>
    <Email>[email protected]</Email>
</Customers>
<Orders>
  <CustomerID>1</CustomerID>
  <OrderID>22</OrderID>
  <OrderAmount>0</OrderAmount>
  <OrderDate>1997-11-10T00:00:00.0000</OrderDate>
</Orders>
</CustomerOrders>

A DataSet is made up of a collection of tables, relationships, and constraints. In ADO.NET, DataTable objects are used to represent the tables in a DataSet. A DataTable represents one table of in-memory relational data. The data is local to the .NET application in which it resides, but can be populated from a data source such as SQL Server using a DataAdapter.

The DataTable class is a member of the System.Data namespace within the .NET Framework class library. You can create and use a DataTable independently or as a member of a DataSet, and DataTable objects can be used by other .NET Framework objects, including the DataView. You access the collection of tables in a DataSet through the DataSet object's Tables property.

The schema, or structure, of a table is represented by columns and constraints. You define the schema of a DataTable using DataColumn objects as well as ForeignKeyConstraint and UniqueConstraint objects. The columns in a table can map to columns in a data source, contain calculated values from expressions, automatically increment their values, or contain primary key values.

If you populate a DataTable from a database, then it inherits the constraints from the database, so you don't have to do all of that work manually. A DataTable must also have rows in which to contain and order the data. The DataRow class represents the actual data contained in the table. You use the DataRow and its properties and methods to retrieve, evaluate, and manipulate the data in a table. As you access and change the data within a row, the DataRow object maintains both its current and original state.

You can create parent-child relationships between tables within a database, such as SQL Server, using one or more related columns in the tables. You create a relationship between DataTable objects using a DataRelation, which can then be used to return a row's related child or parent rows.

One of the main complaints developers had about ADO.NET 1.x was related to the performance of the DataSet and its DataTable children — in particular, when they contained a large amount of data. The performance hit comes in two different ways. The first way is the time it takes to actually load a DataSet with a lot of data. As the number of rows in a DataTable increases, the time to load a new row increases almost proportionally to the number of rows. The second way is when the large DataSet is serialized and remoted. A key feature of the DataSet is the fact that it automatically knows how to serialize itself, especially when you want to pass it between application tiers. Unfortunately, the serialization is quite verbose and takes up a lot of memory and network bandwidth. Both of these performance problems have been addressed since ADO.NET 2.0.

Private Sub _serializationButton_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles _serializationButton.Click
    ' Get the authors reader.
    Dim reader As SqlDataReader = GetAuthorsSqlReader()
    ' Load a DataTable from the reader
    Dim table As DataTable = GetTableFromReader(reader, "Authors")

Using fs As New FileStream("c:authors.dat", FileMode.Create)
        table.RemotingFormat = SerializationFormat.Binary
        Dim format As New BinaryFormatter()
        format.Serialize(fs, table)
    End Using

    ' Tell the user what happened.
    MessageBox.Show("Successfully serialized the DataTable!")
End Sub

Private Sub _loadFromReaderButton_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles _loadFromReaderButton.Click

    ' Get the authors reader.
    Dim reader As SqlDataReader = GetAuthorsSqlReader()

    ' Load a DataTable from the reader.
    Dim table As DataTable = GetTableFromReader(reader, "Authors")

    ' Bind the grid to the table.
    BindGrid(table)
    End Sub

Private Function GetTableFromReader(ByVal reader As SqlDataReader, _
    ByVal tableName As String) As DataTable
    ' Create a new DataTable using the name passed in.
    Dim table As New DataTable(tableName)
    ' Load the DataTable from the reader.
    table.Load(reader)
    ' Close the reader.
    reader.Close()
    Return table
End Function

Now you get to declare your constructors for the StoredProcedureHelper class. This is where you can really take advantage of method overloading, and it gives you a way to pass data to your class upon instantiation. First, you declare a default constructor:

'' ' <summary>
'' ' Default constructor.
'' ' </summary>
Public Sub New()

End Sub

The default constructor is provided in case users want to pass data to your class through public properties instead of through constructor arguments.

The next constructor you create allows a database connection string to be passed into it. By abstracting the database connection string out of this component, you give users of your component more flexibility regarding how they store and retrieve their database connection strings. Here is the code for the constructor:

`´` <summary>
`´` Overloaded constructor.
`´` </summary>
`´` <param name="connectionString">The connection string to the
`´` SQL Server database.</param>
Public Sub New(ByVal connectionString As String)
    Me._connectionString = connectionString
End Sub

The only difference between this constructor and the default constructor is that you are passing in a database connection string.

In the next constructor, you pass in both a database connection string and a string of XML representing the stored procedure parameters for the stored procedures you want to call:

`´` <summary>
`´` Overloaded constructor.
`´` </summary>
`´` <param name="connectionString">The connection string to the
`´` SQL Server database.</param>
`´` <param name="spParamXml">A valid XML string which conforms to
`´` the correct schema for stored procedure(s) and their
`´` associated parameter(s).</param>
Public Sub New(ByVal connectionString As String, ByVal spParamXml As String)
    Me.New(connectionString)
    Me._spParamXml = spParamXml
    Me._spParamXmlDoc = New XmlDocument
    Try
        Me._spParamXmlDoc.LoadXml(spParamXml)
        Me._spParamXmlNode = Me._spParamXmlDoc.DocumentElement
    Catch e As XmlException
        LogError(e)
        Throw New Exception(ExceptionMsg, e)
    End Try
End Sub

This constructor sets the database connection string by calling the first overloaded constructor. This handy technique enables you to avoid writing duplicate code in your constructors. The constructor then loads the stored procedure parameter configuration into a private XmlDocument instance variable as well as a private XmlNode instance variable.

The remaining constructors enable you to pass in combinations of database connection strings as well as either a valid XmlDocument instance representing the stored procedure parameters or a valid XmlNode instance that represents the stored procedure parameters.

Now let's look at the properties of your class. Your object contains the following properties: ConnectionString, SpParamXml, and SpParamXmlDoc. These properties are provided as a courtesy in case the user of your object does not want to supply them via a constructor call. The ConnectionString property provides the same functionality as the first overloaded constructor you looked at. The SpParamXml property enables the user of the object to pass in a valid XML string representing the stored procedures' parameter metadata. All of the properties are read-write. The SpParamXmlDoc property enables users to pass in an XmlDocument instance representing the stored procedures' parameter metadata.

Here is the code for the SpParamXml property:

`´` <summary>
`´` A valid XML string which conforms to the correct schema for
`´` stored procedure(s) and their associated parameter(s).
`´` </summary>
Public Property SpParamXml() As String

Get
        Return Me._spParamXml
    End Get
    Set(ByVal Value As String)
        Me._spParamXml = Value
        ' Set the XmlDocument instance to null, since
        ' an XML string is being passed in.
        Me._spParamXmlDoc = Nothing
        Try
            Me._spParamXmlDoc.LoadXml(Me._spParamXml)
            Me._spParamXmlNode = Me._spParamXmlDoc.DocumentElement
        Catch e As XmlException
            LogError(e)
            Throw New Exception(ExceptionMsg)
        End Try
    End Set
End Property

Note that this property resets the XmlDocument instance to Nothing before trying to load the document. This is done in case it was already set in one of the overloaded constructors, or from a previous call to this property. It also sets the XmlNode instance to the DocumentElement property of the XmlDocument instance, thus keeping them both in sync.

In this case, rather than have the user of this class be responsible for populating the Parameters collection of a Command object, you will abstract it out into an XML structure. The structure is very simple; it basically enables you to store the metadata for one or more stored procedures at a time. This has a huge advantage because you can change all of the parameters on a stored procedure without having to recompile the project. Shown here is the XML structure for the metadata:

<StoredProcedures>
 <StoredProcedure name>
  <Parameters>
   <Parameter name size datatype direction isNullable sourceColumn />
  </Parameters>
 </StoredProcedure>
</StoredProcedures>

Here is what some sample data for the XML structure looks like:

<?xml version="1.0"?>
<StoredProcedures>
 <StoredProcedure name="usp_Get_Authors_By_States">
  <Parameters>
   <Parameter name="@states" size="100" datatype="VarChar"
    direction="Input" isNullabel="True" />
   <Parameter name="@state_delimiter" size="1" datatype="Char"
    direction="Input" isNullabel="True" />
  </Parameters>
 </StoredProcedure>
</StoredProcedures>

The valid values for the direction attribute are Input, Output, ReturnValue, and InputOutput. These values map directly to the System.Data.Parameter enumeration values. The valid values for the data type attribute are BigInt, Binary, Bit, Char, DateTime, Decimal, Float, Image, Int, Money, NChar, NText, NVarChar, Real, SmallDateTime, SmallInt, SmallMoney, Text, Timestamp, TinyInt, UniqueIdentifier, VarBinary, VarChar, and Variant. These values map directly to the System.Data.SqlDbType enumeration values.

That completes our look at the stored procedure XML structure the class expects, as well as the public properties and public constructors for the class. Now let's turn our attention to the public methods of your class.

`´` <summary>
`´` Executes a stored procedure with or without parameters and returns a
`´` populated DataSet object.
`´` </summary>
`´` <param name="spName">The name of the stored procedure to execute.</param>
`´` <param name="dataSetName">An optional name for the DataSet instance.</param>
`´` <param name="paramValues">A name-value pair of stored procedure parameter
`´` name(s) and value(s).</param>
`´` <returns>A populated DataSet object.</returns>
Public Function ExecSpReturnDataSet(ByVal spName As String, _
                ByVal dataSetName As String, _
                ByVal paramValues As IDictionary) As DataSet
    Dim command As SqlCommand = Nothing
    Try
        ' Get the initialized SqlCommand instance.
        command = GetSqlCommand(spName)
        ' Set the parameter values for the SqlCommand.
        SetParameterValues(command, paramValues)
        ' Initialize the SqlDataAdapter with the SqlCommand object.
        Dim sqlDA As New SqlDataAdapter(command)

        ' Initialize the DataSet.
        Dim ds As New DataSet()

        If Not (dataSetName Is Nothing) Then
            If dataSetName.Length > 0 Then
                ds.DataSetName = dataSetName
            End If
        End If

        ' Fill the DataSet.
        sqlDA.Fill(ds)

        ' Return the DataSet.

Return ds
    Catch e As Exception
        LogError(e)
        Throw New Exception(ExceptionMsg, e)
    Finally
        ' Close and release resources.
        DisposeCommand(command)
    End Try
End Function

`´` <summary>
`´` Initializes a SqlCommand object based on a stored procedure name
`´` and a SqlTransaction instance. Verifies that the stored procedure
`´` name is valid, and then tries to get the SqlCommand object from
`´` cache. If it is not already in cache, then the SqlCommand object
`´` is initialized and placed into cache.
`´` </summary>
`´` <param name="transaction">The transaction that the stored
`´` procedure will be executed under.</param>
`´` <param name="spName">The name of the stored procedure to execute.</param>
`´` <returns>An initialized SqlCommand object.</returns>
Public Function GetSqlCommand(ByVal transaction As SqlTransaction, _
    ByVal spName As String) As SqlCommand

    Dim command As SqlCommand = Nothing

    ' Get the name of the stored procedure.
    If spName.Length < 1 Or spName.Length > 127 Then
        Throw New ArgumentOutOfRangeException("spName", _
            "Stored procedure name must be from 1 - 128 characters.")
    End If

    ' See if the command object is already in memory.
    Dim hashKey As String = Me._connectionString & ":" & spName
    command = CType(_commandParametersHashTable(hashKey), SqlCommand)
    If command Is Nothing Then
        ' It was not in memory.
        ' Initialize the SqlCommand.
        command = New SqlCommand(spName, GetSqlConnection(transaction))

        ' Tell the SqlCommand that you are using a stored procedure.
        command.CommandType = CommandType.StoredProcedure

        ' Build the parameters, if there are any.
        BuildParameters(command)

        ' Put the SqlCommand instance into memory.

Me._commandParametersHashTable(hashKey) = command
    Else
        ' It was in memory, but you still need to set the
        ' connection property.
        command.Connection = GetSqlConnection(transaction)
    End If

    ' Return the initialized SqlCommand instance.
    Return command
End Function

`´` <summary>
`´` Overload. Initializes a SqlCommand object based on a stored
`´` procedure name, with no SqlTransaction instance.
`´` Verifies that the stored procedure name is valid, and then tries
`´` to get the SqlCommand object from cache. If it is not already in
`´` cache, then the SqlCommand object is initialized and placed into cache.
`´` </summary>
`´` <param name="spName">The name of the stored procedure to execute.</param>
`´` <returns>An initialized SqlCommand object.</returns>
Public Function GetSqlCommand(ByVal spName As String) As SqlCommand
    ' Return the initialized SqlCommand instance.
    Return GetSqlCommand(Nothing, spName)
End Function

`´` <summary>
`´` Traverses the SqlCommand's SqlParameters collection and sets the values
`´` for all of the SqlParameter(s) objects whose direction is not Output and
`´` whose name matches the name in the dictValues IDictionary that was
`´` passed in.
`´` </summary>
`´` <param name="command">An initialized SqlCommand object.</param>
`´` <param name="dictValues">A name-value pair of stored procedure parameter
`´` name(s) and value(s).</param>
Public Sub SetParameterValues(ByVal command As SqlCommand, _
    ByVal dictValues As IDictionary)
    If command Is Nothing Then
        Throw New ArgumentNullException("command", _
            "The command argument cannot be null.")
    End If
    ' Traverse the SqlCommand's SqlParameters collection.
    Dim parameter As SqlParameter
    For Each parameter In command.Parameters
        ' Do not set Output parameters.
        If parameter.Direction <> ParameterDirection.Output Then
            ' Set the initial value to DBNull.
            parameter.Value = TypeCode.DBNull
            ' If there is a match, then update the parameter value.
            If dictValues.Contains(parameter.ParameterName) Then
                parameter.Value = dictValues(parameter.ParameterName)
            Else
                ' There was not a match.
                ' If the parameter value cannot be null, throw an exception.
                If Not parameter.IsNullable Then
                    Throw New ArgumentNullException(parameter.ParameterName, _
                        "Error getting the value for the " _
                        & parameter.ParameterName & " parameter.")
                End If
            End If
        End If
    Next parameter
End Sub

' Initialize the SqlDataAdapter with the SqlCommand object.
Dim sqlDA As New SqlDataAdapter(command)

' Try to set the name of the DataSet.
If Not (dataSetName Is Nothing) Then
    If dataSetName.Length > 0 Then
        ds.DataSetName = dataSetName
    End If
End If

' Fill the DataSet.
sqlDA.Fill(ds)

' Return the DataSet.
Return ds

Catch e As Exception
    LogError(e)
    Throw New Exception(ExceptionMsg, e)

Finally
    ' Close and release resources
    DisposeCommand(command)

`´` <summary>
`´` Disposes a SqlCommand and its underlying SqlConnection.
`´` </summary>
`´` <param name="command"></param>
Private Sub DisposeCommand(ByVal command As SqlCommand)
    If Not (command Is Nothing) Then
        If Not (command.Connection Is Nothing) Then
            command.Connection.Close()
            command.Connection.Dispose()
        End If
        command.Dispose()
    End If
End Sub

`´` <summary>
`´` Finds the parameter information for the stored procedure from the
`´` stored procedures XML document and then uses that information to
`´` build and append the parameter(s) for the SqlCommand's
`´` SqlParameters collection.
`´` </summary>
`´` <param name="command">An initialized SqlCommand object.</param>
Private Sub BuildParameters(ByVal command As SqlCommand)

' See if there is an XmlNode of parameter(s) for the stored procedure.
If Me._spParamXmlNode Is Nothing Then
    ' No parameters to add, so exit.
    Return
End If

' Clear the parameters collection for the SqlCommand
command.Parameters.Clear()

' Get the node list of <Parameter>'s for the stored procedure.
Dim xpathQuery As String = "//StoredProcedures/StoredProcedure[@name='" _
    & command.CommandText & "']/Parameters/Parameter"
Dim parameterNodes As XmlNodeList = Me._spParamXmlNode.SelectNodes(xpathQuery)

Dim parameterNode As XmlElement
For Each parameterNode In parameterNodes
    ' Get the attribute values for the <Parameter> element.

    ' Get the attribute values for the <Parameter> element.

' name
    Dim parameterName As String = parameterNode.GetAttribute("name")
    If parameterName.Length = 0 Then
        Throw New ArgumentNullException("name", "Error getting the 'name' " _
            & "attribute for the <Parameter> element.")
    End If

    ' size
    Dim parameterSize As Integer = 0
    If parameterNode.GetAttribute("size").Length = 0 Then
        Throw New ArgumentNullException("size", "Error getting the 'size' " _
            & "attribute for the <Parameter> element.")
    Else
        parameterSize = Convert.ToInt32(parameterNode.GetAttribute("size"))
    End If

    ' datatype
    Dim sqlDataType As SqlDbType
    If parameterNode.GetAttribute("datatype").Length = 0 Then
        Throw New ArgumentNullException("datatype", "Error getting the " _
             & "'datatype' attribute for the <Parameter> element.")
    Else
        sqlDataType = CType([Enum].Parse(GetType(SqlDbType), _
            parameterNode.GetAttribute("datatype"), True), SqlDbType)
    End If

    ' direction
    Dim parameterDirection As ParameterDirection = parameterDirection.Input
    If parameterNode.GetAttribute("direction").Length > 0 Then
        parameterDirection = CType([Enum].Parse(GetType(ParameterDirection), _
            parameterNode.GetAttribute("direction"), True), ParameterDirection)
    End If
End If

' Get the optional attribute values for the <Parameter> element.
' isNullable
Dim isNullable As Boolean = False
Try
    If parameterNode.GetAttribute("isNullable").Length > 0 Then
        isNullable = Boolean.Parse(parameterNode.GetAttribute("isNullable"))
    End If
Catch
End Try

' sourceColumn  -  This must map to the name of a column in a DataSet.
Dim sourceColumn As String = ""
Try
    If parameterNode.GetAttribute("sourceColumn").Length > 0 Then

sourceColumn = parameterNode.GetAttribute("sourceColumn")
    End If
Catch
End Try

' Create the parameter object.  Pass in the name, datatype,
' and size to the constructor.
Dim sqlParameter As SqlParameter = New SqlParameter(parameterName, _
    sqlDataType, parameterSize)

'Set the direction of the parameter.
sqlParameter.Direction = parameterDirection

' If the optional attributes have values, then set them.
' IsNullable
If isNullable Then
    sqlParameter.IsNullable = isNullable
End If

' SourceColumn
sqlParameter.SourceColumn = sourceColumn

' Add the parameter to the SqlCommand's parameter collection.
        command.Parameters.Add(sqlParameter)
    Next parameterNode
End Sub

`´` <summary>
`´` Executes a stored procedure with or without parameters and returns a
`´` SqlDataReader instance with a live connection to the database. It is
`´` very important to call the Close method of the SqlDataReader as soon
`´` as possible after using it.
`´` </summary>

`´` <param name="spName">The name of the stored procedure to execute.</param>
`´` <param name="paramValues">A name-value pair of stored procedure parameter
`´` name(s) and value(s).</param>
`´` <returns>A SqlDataReader object.</returns>
Public Function ExecSpReturnDataReader(ByVal spName As String, _
    ByVal paramValues As IDictionary) As SqlDataReader
  Dim command As SqlCommand = Nothing
  Try
    ' Get the initialized SqlCommand instance.
    command = GetSqlCommand(spName)

    ' Set the parameter values for the SqlCommand.
    SetParameterValues(command, paramValues)
    ' Open the connection.
    command.Connection.Open()

    ' Execute the sp and return the SqlDataReader.
    Return command.ExecuteReader(CommandBehavior.CloseConnection)
Catch e As Exception
    LogError(e)
    Throw New Exception(ExceptionMsg, e)

End Try

End Function

`´` <summary>
`´` Executes a stored procedure with or without parameters and returns an
`´` XmlReader instance with a live connection to the database. It is

`´` very important to call the Close method of the XmlReader as soon
`´` as possible after using it. Only use this method when calling stored
`´` procedures that return XML results (FOR XML ...).
`´` </summary>
`´` <param name="spName">The name of the stored procedure to execute.</param>
`´` <param name="paramValues">A name-value pair of stored procedure parameter
`´` name(s) and value(s).</param>
`´` <returns>An XmlReader object.</returns>
Public Function ExecSpReturnXmlReader(ByVal spName As String, _
    ByVal paramValues As IDictionary) As XmlReader
    Dim command As SqlCommand = Nothing
    Try
       ' Get the initialized SqlCommand instance.
       command = GetSqlCommand(spName)
       ' Set the parameter values for the SqlCommand.
       SetParameterValues(command, paramValues)

       ' Open the connection.
       command.Connection.Open()
       ' Execute the sp and return the XmlReader.
       Return command.ExecuteXmlReader()
   Catch e As Exception
       LogError(e)
       Throw New Exception(ExceptionMsg, e)
   End Try
End Function

`´` <summary>
`´` Executes a stored procedure with or without parameters that
`´` does not return output values or a resultset.
`´` </summary>
`´` <param name="transaction">The transaction that the stored procedure
`´` will be executed under.</param>
`´` <param name="spName">The name of the stored procedure to execute.</param>
`´` <param name="paramValues">A name-value pair of stored procedure parameter
`´` name(s) and value(s).</param>
Public Sub ExecSp(ByVal spName As String, ByVal paramValues As IDictionary)

Dim command As SqlCommand = Nothing
    Try
        ' Get the initialized SqlCommand instance.
        command = GetSqlCommand(transaction, spName)
        ' Set the parameter values for the SqlCommand.
        SetParameterValues(command, paramValues)
        ' Run the stored procedure.
        RunSp(command)
    Catch e As Exception
        LogError(e)
        Throw New Exception(ExceptionMsg, e)
    Finally
        ' Close and release resources.
        DisposeCommand(command)
    End Try
End Sub

`´` <summary>
`´` Opens the SqlCommand object's underlying SqlConnection and calls
`´` the SqlCommand's ExecuteNonQuery method.
`´` </summary>
`´` <param name="command">An initialized SqlCommand object.</param>
Private Sub RunSp(ByRef command As SqlCommand)
    ' Open the connection.
    command.Connection.Open()

    'a Execute the stored procedure.
    command.ExecuteNonQuery()
End Sub

`´` <summary>
`´` Executes a stored procedure with or without parameters and returns an

`´` IDictionary instance with the stored procedure's output parameter
`´` name(s) and value(s).
`´` </summary>
`´` <param name="transaction">The transaction that the stored procedure
`´` will be executed under.</param>
`´` <param name="spName">The name of the stored procedure to execute.</param>
`´` <param name="paramValues">A name-value pair of stored procedure parameter
`´` name(s) and value(s).</param>
`´` <returns>An IDictionary object.</returns>
Public Function ExecSpOutputValues(ByVal transaction As SqlTransaction, _
                                   ByVal spName As String, _
                                   ByVal paramValues As IDictionary) As IDictionary
    Dim command As SqlCommand = Nothing
    Try
        ' Get the initialized SqlCommand instance.
        command = GetSqlCommand(transaction, spName)
        ' Set the parameter values for the SqlCommand.
        SetParameterValues(command, paramValues)

        ' Run the stored procedure.
        RunSp(command)
        ' Get the output values.
        Dim outputParams As New Hashtable()
        Dim param As SqlParameter
        For Each param In command.Parameters
            If param.Direction = ParameterDirection.Output _
                Or param.Direction = ParameterDirection.InputOutput Then
                outputParams.Add(param.ParameterName, param.Value)
            End If
        Next param
        Return outputParams
    Catch e As Exception
        LogError(e)
        Throw New Exception(ExceptionMsg, e)
    Finally
        ' Close and release resources.
        DisposeCommand(command)
    End Try
End Function

Now that you have built your data-access component, it is time to test it. A nice way to do that is to call the ExecSpReturnDataSet method, take the DataSet object that was created, and then bind the DataSet to a DataGrid. You also get to see how easily the DataSet and the DataGrid control integrate together.

This exercise uses a Windows Application project called SqlServerWrapperTestHarness, added to the Examples solution. It contains references to System, System.Data, System.Drawing, System.Windows.Forms, and System.Xml, as well as a project reference to the SqlServerWrapper project. Added to the project is a form named TestForm.vb with two buttons, one for testing the ExecSpReturnDataSet method and one for testing the ExecSpReturnSqlRecord method. In this example, you will be looking only at the code for testing the ExecSpReturnDataSet method. Figure 9-4 shows what the test form looks like.

Figure 9.4. Figure 9-4

Figure 9-5 shows what your references should look like.

Here is the code for the declarations and private members of the form:

Option Explicit On
Option Strict On
Imports SqlServerWrapper
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Configuration

Public Class TestForm
    Inherits System.Windows.Forms.Form

    Private _helper As StoredProcedureHelper = Nothing

These declarations should look pretty familiar by now. Note that you are declaring a private variable (_helper) for the StoredProcedureHelper class that you are using so you can get to the class from other parts of the form instead of just a Button Click event handler.

Figure 9.5. Figure 9-5

Next, you initialize the _helper variable in the form's Load event handler:

Private Sub TestForm_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
    ' Set the SQL connection string
    Dim connectionString As String = _
       ConfigurationSettings.AppSettings("dbConnectionString")

    ' Call the SqlServer wrapper constructor and
    ' pass the DB connection string and the stored procedures config.
    helper = New StoredProcedureHelper(connectionString, _
        CType(ConfigurationSettings.GetConfig("StoredProcedureSettings"), _
        XmlNode))
    End Sub

As in the earlier examples, this code begins by retrieving a connection string to the pubs database from the app.config file. You then create a new instance of the StoredProcedureHelper and assign it to the _helper class variable. During the constructor call to the StoredProcedureHelper class, you first pass in your connection string, and then you pass in an XmlNode of the stored procedure metadata for the StoredProcedureHelper class to consume. Note that you are passing the stored procedure metadata in to your class via the GetConfig method of the ConfigurationSettings class. This is because you have created a section inside of your app.config file called StoredProcedureSettings, and you have configured a SectionHandler to let the .NET Framework application configuration functionality consume your XML and give it back to you as an XmlNode. Here is what this section looks like inside of the app.config file:

<configSections>
    <section name="StoredProcedureSettings"
    type="SqlServerWrapper.StoredProcedureSectionHandler, SqlServerWrapper" />

</configSections>
  <StoredProcedureSettings>
    <StoredProcedures>
      <StoredProcedure name="usp_Get_Authors_By_States">
        <Parameters>
          <Parameter name="@states" datatype="VarChar" direction="Input"
           isNullabel="false" size="100" />
          <Parameter name="@state_delimiter" datatype="Char" direction="Input"
           isNullabel="false" size="1" />
        </Parameters>
      </StoredProcedure>
      <StoredProcedure name="usp_Get_Author_By_ID">
        <Parameters>
          <Parameter name="@au_id" datatype="VarChar" direction="Input"
           isNullabel="false" size="11" />
        </Parameters>
      </StoredProcedure>
    </StoredProcedures>
  </StoredProcedureSettings>

This is nice because you don't need to include a separate XML file for your project; you just integrate seamlessly into the app.config file. Note how you are defining what class in what assembly will handle consuming your <StoredProcedureSettings> section in the <section> element. In order for this to work, the class defined must implement the System.Configuration.IConfigurationSectionHandler interface. Here is the code for your section handler:

Option Explicit On
Option Strict On
Imports System
Imports System.Configuration
Imports System.Xml
Imports System.Xml.Serialization
Imports System.Xml.XPath

Public Class StoredProcedureSectionHandler
    Implements IConfigurationSectionHandler

    Public Function Create(ByVal parent As Object, _
        ByVal configContext As Object, _
        ByVal section As System.Xml.XmlNode) As Object _
            Implements IConfigurationSectionHandler.Create
        Return section("StoredProcedures")
    End Function
End Class

This code is pretty simple; you just return the XML node named StoredProcedures to the caller of your handler.

Back to your Button's Click event handler, once you have the StoredProcedureHelper class instance fully initialized, you then create the parameter values for the stored procedure you want to execute and pass these arguments to the ExecSpReturnDataSet method:

' Add the two parameter name-values.
Dim params As New Hashtable

params.Add("@states", "CA")
params.Add("@state_delimiter", "^")

' Execute the sp, and get the DataSet object back.
Dim ds As DataSet = _helper.ExecSpReturnDataSet("usp_Get_Authors_By_States", _
    "", params)

The last step is to actually bind the data to the form's grid:

' Bind the DataGrid to the DataSet object.
dgdAuthors.SetDataBinding(ds.Tables(0), Nothing)

The results should look like the dialog shown in Figure 9-6.

Figure 9.6. Figure 9-6