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

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 Access database:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
"C:Program FilesMicrosoft ExpressionWeb 2WebDesigner1033FPNWIND.MDB";

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, as you know you are using the SQL Server .NET data provider. Here is an example of an SqlConnection connection string:

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

Alternately, you can use a connection-string format that is more specific to SQL Server. This sample would connect to the same database as the previous one:

Server=(local);Database=AdventureWorks;Trusted Connection=true;

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 an 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 query As String = "SELECT * FROM ErrorLog"
Dim connectionString As String = "Data Source=.SQLEXPRESS;" +
                                 "Initial Catalog=AdventureWorks;" +
                                 "Integrated Security=True"

'Initialize the SqlCommand with the SQL and Connection strings.
Dim command As SqlCommand = New SqlCommand(query,
                                           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. 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 later in this chapter.) Finally, the ExecuteXmlReader returns an XmlReader, which can be used to read a block of XML returned from the database. (You will see how this is used in the XML chapter.)

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.

Using Stored Procedures with Command Objects

The motivation for using stored procedures is simple. Imagine you have the following SQL query:

SELECT Name FROM [Purchasing].[Vendor] WHERE [CreditRating] = 1

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.

One other benefit of using stored procedures in your code is that it is generally safer. When using SQL without stored procedures, there is always the temptation to build the SQL statement by concatenating strings. With this, there is the danger—particularly if some of those strings are user generated—that the resulting SQL is invalid or malicious; for example, if you had a text box where a user could type in search criteria, that you then concatenated into a query, using code such as:

Dim query As String = "SELECT Name FROM [Purchasing].[Vendor] WHERE " +
                      "[NAME] LIKE '%" + query + "%'''

This looks innocent enough, but you could get into trouble very quickly if the user entered something like:

Acme;delete * from systables;

Using stored procedures can help prevent an attack like this from happening.

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, you'll 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–2012. (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 columns from the Sales.CreditCard database for a given ID. The SQL to do this looks like the following:

SELECT     
  CardType, CardNumber, ExpMonth, ExpYear
FROM         
  Sales.CreditCard
WHERE 
 (CreditCardID = 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 CreditCardId, then your SQL will look like this:

SELECT     
  CardType, CardNumber, ExpMonth, ExpYear
FROM         
  Sales.CreditCard
WHERE     
  (CreditCardID = @CreditCardId)

In Visual Studio, 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.

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 (@CreditCardId) and the name of the procedure: Sales.CreditCards_GetById. Here's code for the stored procedure:

CREATE PROCEDURE Sales.CreditCards_GetById 
  @CreditCardId tinyint
AS
BEGIN
  SELECT     
    CardType, CardNumber, ExpMonth, ExpYear
  FROM         
    Sales.CreditCard
  WHERE     
    (CreditCardID = @CreditCardId)
END
GO

Click the Save icon 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 an SqlConnection object to connect to the database, and an SqlCommand object to run the stored procedure.

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. Pass in the ID of the record in the @CreditCardId parameter by adding a new SQLParameter (code file: StoredProcedureExample):

Imports System.Data.SqlClient

Module Main

  Sub Main()
    'Build the SQL and Connection strings.
    Dim storedProcedure As String = "Sales.CreditCards_GetById"
    Dim connectionString As String = "Data Source=.SQLEXPRESS;" +
                                    "Initial Catalog=AdventureWorks;" +
                                    "Integrated Security=True"

    'Initialize the SqlCommand with the SQL and Connection strings.
    Dim command As SqlCommand = New SqlCommand(storedProcedure,
                                               New SqlConnection(connectionString))

    command.CommandType = CommandType.StoredProcedure

    'Add the @CreditCardId parameter information to the command
    command.Parameters.Add(New SqlParameter("@CreditCardId", 1)

    'Open the connection.
    command.Connection.Open()

    Dim reader As SqlDataReader = command.ExecuteReader

    While reader.Read
      Console.WriteLine(reader.GetString(0) + "|" + reader.GetString(1) + "|" +
                        reader.GetByte(2).ToString + "|" + 
                        reader.GetInt16(3).ToString)
    End While

    reader.Close()

    Console.ReadLine()
  End Sub

End Module

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

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:

The benefit to using stored procedures is that it prevents SQL injection attacks, they are precompiled, and it's easy to add security so that only certain roles has access to it.

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 (code file: DataReaderExample):

Imports System.Data.SqlClient

Module Main

  Sub Main()
    Dim connection As SqlConnection
    Dim command As SqlCommand

    Console.WriteLine("Loading records...")

    'Update to match the location of AdventureWorks on your computer
    Dim cmdString As String = "Select Name from [Purchasing].[Vendor]"
    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True")
    command = New SqlCommand(cmdString, connection)
    connection.Open()

    Dim reader As SqlDataReader
    reader = command.ExecuteReader(CommandBehavior.CloseConnection)

    While reader.Read()
      Console.WriteLine(reader("Name").ToString())
    End While

    reader.Close()

    Console.WriteLine("Press ENTER to exit")
    Console.ReadLine()

  End Sub

End Module

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 vendor 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 loaded 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 be open until the garbage collector gets around to collecting the object. Alternately, use the Using statement to automatically close the database connection at the end of the Using clause.

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.

Executing Commands Asynchronously

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 WPF 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, EndExecuteNonQuery, and EndExecuteXmlReader. Now that you are familiar with the DataReader object, you'll now look at an example using the BeginExecuteReader method to execute a long-running query.

In the AsyncExample project, a button and an associated Click event handler have been added to the form that will initiate the asynchronous call to get a DataReader instance (code file: AsyncExample):

  Private Sub LoadDataAsycButton_Click(sender As Object,
                                       e As EventArgs) Handles  
                                       LoadDataAsycButton.Click
    Dim connection As SqlConnection
    Dim command As SqlCommand

    'update to match the location of AdventureWorks on your computer
    Dim cmdString As String = "Long_Running_Procedure"
    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True; " +
                                   "Asynchronous Processing=true;")
    command = New SqlCommand(cmdString, connection)

    'Set the command type to stored procedure
    command.CommandType = CommandType.StoredProcedure
    connection.Open()

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

First, 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 an SqlCommand object and initialize it to be able to execute the Long_Running_Procedure stored procedure. This procedure simulates a long-running query by using the SQL Server WAITFOR DELAY statement to create a 20-second delay before it executes the HumanResources.Employees_Get stored procedure. As you can probably guess, the HumanResources.Employees_Get stored procedure simply selects all of the employees from the HumanResources.Employees 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 stored procedures:

CREATE PROCEDURE HumanResources.Employees_Get 
AS
BEGIN
  SELECT * From [HumanResources].[Employee]
END
GO

CREATE PROCEDURE Long_Running_Procedure
AS
SET NOCOUNT ON
WAITFOR DELAY '00:00:20'
EXEC HumanResources.Employees_Get
GO

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, you'll now look at the actual method that is being called back, the AsyncCallback method (code file: AsyncExample):

  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 = New DataTable()
    table.Load(reader)

    'Call the BindGrid method on the Windows main thread,
    'passing in the table.
    Me.Invoke(New BindGridDelegate(AddressOf Me.BindGrid), New Object() {table})

    reader.Close()

  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.MainDataGridView.DataSource = Nothing

  'Bind the grid to the DataTable.
  Me.MainDataGridView.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. 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 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 HumanResources.Employee table (code file: DataAdapterExample):

Imports System.Data.SqlClient

Module Main
  Sub Main()
    Dim connection As SqlConnection

    Console.WriteLine("Loading records...")

    'Update to match the location of AdventureWorks on your computer
    Dim cmdString As String = "SELECT * From [HumanResources].[Employee]"
    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True")

    '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(cmdString, connection)
    Dim employees As New DataSet
    adapter.Fill(employees)

    'Iterate through the DataSet's table.
    For Each row As DataRow In employees.Tables(0).Rows
      Console.WriteLine(row("LoginID").ToString)
    Next

    Console.WriteLine("Press ENTER to get DataSet xml")
    Console.ReadLine()

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

    Console.WriteLine("Press ENTER to exit")
    Console.ReadLine()
  End Sub
End Module

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 an 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 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 database (code file: DataAdapterAdvancedExample):

Private Sub BatchUpdateButton_Click(sender As Object,
                                    e As EventArgs) 
                                    Handles BatchUpdateButton.Click
    'Build insert, update, and delete commands.
    'Build the parameter values.
    Dim updateParams() As String = {"@DocumentID", "@FileName", "@FileExtension",
                                    "@Revision", "@ChangeNumber", "@Status",
                                    "@ModifiedDate"}
    Dim insertParams() As String = {"@Title", "@FileName", "@FileExtension",
                                    "@Revision", "@ChangeNumber", "@Status",
                                    "@ModifiedDate"}

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("[Production].[uspInsertDocument]",
                      insertParams)

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
    Dim connection As SqlConnection
    Dim command As SqlCommand

    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True; " +
                                   "Asynchronous Processing=true;")

    command = New SqlCommand(storedProcedureName, connection)

    '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 an SqlCommand class and passes in the name of a stored procedure. 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 parameter name to the name of the column in the DataTable when its Update method is called. An example of such a mapping is associating the @DocumentID parameter name with the DocumentID 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("[Production].[uspUpdateDocument]",
                      updateParams)

    'Delete command.
    Dim deleteCommand As SqlCommand =
      BuildSqlCommand("[Production].[uspDeleteDocument]",
                      New String() {"@DocumentID"})

Now that the SqlCommand objects have been created, the next step is to create an 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 Production.Document table from the database:

    Dim command As SqlCommand
    Dim connection As SqlConnection

    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True; " +
                                   "Asynchronous Processing=true;")
    command = New SqlCommand(cmdString, connection)

    connection.Open()

    'Get the Documents reader.
    Dim reader As SqlDataReader
    reader = command.ExecuteReader(CommandBehavior.Default)

    'Load a DataTable from the reader.
    Dim table As DataTable = New DataTable()
    table.Load(reader)

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 document to the DataTable.
    Dim row As DataRow = table.NewRow()

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

    row("Title") = "Test Document " + New Random().Next(100, 999).ToString
    row("FileName") =  
             My.Computer.FileSystem.CombinePath(Environment.GetFolderPath(
             Environment.SpecialFolder.MyDocuments), "TestFile" +
             New Random().Next(10000, 99999).ToString)
    row("FileExtension") = ".docx"
    row("Revision") = New Random().Next(0, 999).ToString
    row("ChangeNumber") = New Random().Next(1, 999).ToString
    row("Status") = 1
    row("ModifiedDate") = DateTime.Now.ToString
    table.Rows.Add(row)

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 revison number in the DataTable.
    table.Rows(0)("Revision") = Integer.Parse(table.Rows(0)("Revision")) + 1

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 document 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 = command.ExecuteReader(CommandBehavior.CloseConnection)

    'Load a DataTable from the reader.
    table.Load(reader)

    '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 that can be up to 70 percent faster than the OLE DB .NET data provider.

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. Use the SQL Server .NET data provider anytime you are connecting to an 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 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.