NET Data Providers

.NET Data Providers are used for connecting to a RDBMS-specific database (such as SQL Server or Oracle), executing commands, and retrieving results. Those results are either processed directly (via a DataReader) or placed in an ADO.NET DataSet (via a DataAdapter) in order to be exposed to the user in an ad hoc manner, combined with data from multiple sources, or passed around between tiers. NET Data Providers are designed to be lightweight, to create a minimal layer between the data source and the .NET programmer’s code, and to increase performance while not sacrificing any functionality. Currently, the .NET Framework supports three Data Providers: the SQL Server .NET Data Provider (for Microsoft SQL Server 7.0 or later), the Oracle .NET Data Provider, and the OLE DB .NET Data Provider. Most RDBMS vendors are now producing their own .NET Data Providers in order to encourage .NET developers to use their databases.

Connection Object

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.

Connection String Format - OleDbConnection

For the OLE DB .NET Data Provider, the connection string format is the same as the connection string format used in ADO, with the following exceptions:

• The Provider keyword is required.

• The URL, Remote Provider, and Remote Server keywords are not supported.

Here is an example OleDbConnection connection string connecting to an Oracle database (note this is all one line):

 Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

Connection String Format - SqlConnection

The SQL Server .NET Data Provider supports a connection string format that is similar to the OLE DB (ADO) connection string format. The only thing that you need to omit, obviously, is the provider name-value pair, since you know you are using the SQL Server .NET Data Provider. Here is an example of a SqlConnection connection string:

 data source=(local);initial catalog=pubs;Integrated Security=SSPI;

Command Object

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 2.0, 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’ll 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.

Important 

Note that the DataAdapter object’s SelectCommand property must be set before the Fill method is called.

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.

Using Stored Procedures with Command Objects

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’s 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. (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 isn’t 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’ve been around a very long time, and they’re 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 2005, 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 10-2 illustrates the stored procedures of the pubs database in Visual Studio 2005.

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

Figure 10-2

Click OK to save the stored procedure in the database. You’re 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.

Important 

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.

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 to grab a connection string to the database from the application’s configuration file, the app.config file. 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 doesn’t 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:

• Create a SqlCommand object.

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

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

• Execute the stored procedure using one of the SqlCommand object’s Execute*** methods

There’s 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()         MsgBox(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 10-3.

Figure 10-3

DataReader Object

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 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.

Important 

At this point, an obvious design question is whether to use the DataReader or the DataSet. The answer depends upon performance. If you want high performance, and you are only going to access the data you are retrieving once, then the DataReader is the way to go. If you need access to the same data multiple times, or if you need to model a complex relationship in memory, then the DataSet is the way to go. As always, test each option thoroughly before deciding which one is the best.

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 datatypes (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). Using the typed accessor methods when the underlying datatype 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 won’t 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.

Important 

If your command contains output parameters or return values, they will not be available until the DataReader is closed.

Executing Commands Asynchronously

In ADO.NET 2.0, 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 new 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. As you have just finished covering 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 2.0 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.

DataAdapter Objects

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 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 SqlDataAdapter’s constructor 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).

In 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 that was 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 in which 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 make sure 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 then go ahead and set the values of the columns of the DataRow:

  row("au_id") = "335-22-0707" row("au_fname") = "Tim" row("au_lname") = "McCarthy" row("phone") = "760-930-0075" 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 see 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 

SQL Server .NET Data Provider

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 networking connection (usually sockets-based) and drags data from this directly into managed code and vice versa.

Important 

This is very important, as going through the OLE DB or ODBC layers means that the CLR has to marshal (convert) all of the COM datatypes to .NET CLR datatypes each time data is accessed from a data source. When using the SQL Server .NET Data Provider, everything runs within the .NET CLR, and the TDS protocol is faster than the other network protocols previously used for SQL Server.

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 SQL Server .NET Data Provider anytime 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.

OLE DB .NET Data Provider

The OLE DB .NET Data Provider uses native OLE DB through COM Interop (see Chapter 20 for more details) 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.