Updating Data from a DataSet Object

In previous chapters you've regularly used a DataSet object to store data extracted from a database, or to hold data that you've created dynamically using code. You also looked at the ways to edit and modify the data that the DataSet contains. This section looks in detail at how to get those changes back into a data source such as a relational database.

ADO.NET includes the DataAdapter object, which is used to provide the connection between a data store and a disconnected DataSet . This object was seen in action in Chapter 8, but only so far as collecting rows from a database and pushing them into a DataSet . To understand how the update process works for a DataSet , we need to examine the DataAdapter object in more depth.

Inside the DataAdapter Object

In order to understand and take advantage of many of the features of the .NET disconnected data model, especially when we discuss at concurrent data update issues later in this chapter, you must be comfortable with what's going on behind the scenes when you use the DataSet and DataAdapter objects to push changes made to the data back into a data source.

The full chain of objects that are required to pull data from a data store into a DataSet , and push the changes back into the data store after updating is shown in Figure 10-9. You can see the four main objects involved in the process “the Connection , Command , DataAdapter , and DataSet :

Figure 10-9:

The DataSet Object Chain

The four objects in the schematic were briefly discussed in Chapter 8. Here is a detailed look at how the whole process works:

• The Connection object defines the way that the data store will communicate with Command objects, using a connection string and the appropriate data store provider such as SQL TDS, OLE-DB, or the ODBC driver.

• The Command object performs the task of executing the SQL statement, query, or stored procedure, etc. against the data store via the Connection object. It contains details about that SQL statement, query or stored procedure, and the way that it should be processed .

• The DataAdapter object is the bridge between the DataSet and the Command objects. It specifies the organization of the tables within the DataSet through table and column mappings, and is responsible for managing the whole process of fetching data from the data source and pushing it back to the data source.

• The DataSet is the disconnected data storage and processing unit that actually holds the data. It does so using one or more tables, and ( optionally ) relationships between these tables.

Notice in the schematic that there are four Command objects involved in the process. Why? You only need one to fill a DataSet from a data store “a suitable SelectCommand such as a SQL SELECT statement or the equivalent stored procedure (or table name ). However, to be able to update the original data, you need the other three “ UpdateCommand , InsertCommand , and DeleteCommand .

All four commands share the same Connection object; they all have a reference to it in their Connection property. This technique consumes far fewer resources (and hence is more efficient) than using four different ones, and works because the DataAdapter only processes one command at a time. Connections to a data store are limited, and using the same one reduces the demands of the application considerably.

Creating the Necessary Objects

Of course, in most of the examples, you don't explicitly create all these objects every time you want to access a data store. But that doesn't mean they don't exist. In fact many are automatically created in the background when required, as you perform various data access processes. Allowing the system to create them on demand also reduces the code you have to write, and can provide marginally better performance.

For example, when simply extracting data you usually create a Connection object, a DataAdapter object, and a DataSet object “and then use the Fill method of the DataAdapter to get the data into the DataSet :

  Dim objConnect As New OleDbConnection(strConnectString)   Dim objDataAdapter As New OleDbDataAdapter(strSQLStatement, objConnect)   Dim objDataSet As New DataSet()   objDataAdapter.Fill(objDataSet, "  table-name  ")  

However, behind the scenes, when the constructor for the DataAdapter is executed, a Command object is created using the SQL statement and the connection object. This new Command object is then assigned to the SelectCommand property of the DataAdapter object.

You can even dispense with creating a Connection object yourself. Just pass the connection string itself into the constructor for the DataAdapter object:

  Dim objDataAdapter As New OleDbDataAdapter(strSQLStatement, strConnectString)  

Again, behind the scenes, the DataAdapter constructor is creating a new Command object by calling its constructor with the SQL statement and (this time) the connection string. Then the Command object constructor creates a new Connection object using the connection string. The whole process still takes place to create the chain of four objects, even if you don't specifically code this.

Specifying the SelectCommand

At minimum, when creating a DataAdapter object to Fill a Dataset , only the SelectCommand is required and this must always be provided. As you've seen, this is usually specified as a string (the SQL statement, query string, table name, or stored procedure name) in the constructor for the object. Of course, there's nothing to stop you creating a Command object directly and assigning this to the SelectCommand property of the DataAdapter :

  Dim objConnect As New OleDbConnection(strConnectString)   Dim objCommand As New OleDbCommand(strSQLStatement, objConnect)   Dim objDataAdapter As New OleDbDataAdapter(objCommand)  

Or, in an even more verbose way:

  Dim objConnect As New OleDbConnection(strConnectString)   Dim objCommand As New OleDbCommand(strSQLStatement, objConnect)   Dim objDataAdapter As New OleDbDataAdapter()   objDataAdapter.SelectCommand = objCommand  

While it's hard to see when you could use the last of these, it could be a useful technique when you already have a DataAdapter that you want to reuse by just changing the SelectCommand to reference a different Command object.

Specifying the Other Commands

To be able to fill a DataSet , you only need a SelectCommand “but to push the changes back to the data store, you must provide the appropriate Command objects for the UpdateCommand , DeleteCommand , and InsertCommand properties of the DataAdapter .

You don't always need all three, for example if you are only changing existing rows within the data source (if the DataSet object only contains modified rows, and no added or deleted rows), you only need to specify a suitable Command object for the UpdateCommand property of the DataAdapter . The same logic applies if you are only deleting rows or inserting new rows. However, if the DataSet contains modified, deleted, and added rows, you have to specify suitable Command objects for all the matching DataAdapter properties.

What is a suitable Command object? It's pretty obvious that this is a Command with a connection specified to the appropriate data store (via its associated Connection object), and which specifies a suitable SQL statement or stored procedure that will add, delete, or update the rows. We'll show some examples later in this section in more detail. However, ADO.NET can also help out by generating suitable SQL statements automatically for us.

Command Builder Objects and Auto-generated Commands

ADO.NET tries to make your life easier when you use a DataSet to update a data store by providing CommandBuilder objects, such as the SqlCommandBuilder for use with SQL TDS and the OleDbCommandBuilder for use with an OLE-DB provider. These objects can create suitable auto-generated commands for use when pushing changes back to a data store via a DataAdapter object.

All you have to do is create a CommandBuilder object, specifying as the parameter to its constructor the DataAdapter you want to use it with:

  Dim objCommandBuilder As New OleDbCommandBuilder(objDataAdapter)  

Then, when you call the Update method of this DataAdapter , it will automatically use the CommandBuilder to create the INSERT , DELETE , and UPDATE commands for the process, and assign them to the InsertCommand , DeleteCommand , and UpdateCommand properties of the DataAdapter . Once the Update methods ends, the InsertCommand , DeleteCommand , and UpdateCommand properties are set back to Nothing ( null in C#).

However, if the InsertCommand , DeleteCommand , or UpdateCommand properties are not Nothing ( null ) when the Update method is called, the CommandBuilder does not replace any existing statement. This means you can assign your own custom SQL statements or stored procedure details to one or more of these properties before calling the Update method. In this case, the specified SQL statement or stored procedure is used for that part of the Update process, and will remain assigned to the InsertCommand , DeleteCommand , or UpdateCommand property afterwards.

Notice that you can provide a SQL statement or stored procedure for one or two of the InsertCommand , DeleteCommand , and UpdateCommand properties and allow the CommandBuilder to automatically set the remaining ones.

The CommandBuilder also exposes three methods that you can use if you want to retrieve the auto-generated commands for the current operation. While you probably don't need to use these methods in your applications, they are useful for displaying the auto-generated commands in your example pages. Remember that the CommandBuilder sets the InsertCommand , DeleteCommand , and UpdateCommand properties back to Nothing once the Update process completes, so you can't access these properties to see the auto-generated commands it used.

In your example pages, you can take advantage of the GetDeleteCommand , GetInsertCommand , GetUpdateCommand methods of the CommandBuilder to assign the auto-generated commands to the InsertCommand , DeleteCommand , and UpdateCommand properties of the DataAdapter before you call the Update method. You can then access them afterwards as shown in the following code:

  'create a CommandBuilder instance for the current DataAdapter   Dim objCommandBuilder As New OleDbCommandBuilder(objDataAdapter)   'set the update, insert and delete commands for the DataAdapter   objDataAdapter.DeleteCommand = objCommandBuilder.GetDeleteCommand()   objDataAdapter.InsertCommand = objCommandBuilder.GetInsertCommand()   objDataAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand()   'call the Update method   objDataAdapter.Update(objDataSet)   'read back the auto-generated commands   strDeleteCommand = objDataAdapter.DeleteCommand   strInsertCommand = objDataAdapter.InsertCommand   strUpdateCommand = objDataAdapter.UpdateCommand  

The CommandBuilder creates and returns Command objects that specify the appropriate SQL statements for an Update process through the DataAdapter it is attached to. It can figure these out by looking at the SelectCommand property of the DataAdapter , the table structure, and table and column mappings. You'll see what the SQL statements that these methods create look like in the next example in this chapter. In the meantime, however, you should be aware of a few limitations of the auto-generated command feature:

• The rows in a table in the DataSet must have originally come from a single table, and can be used only to update a table of the same format ( generally the same source table).

• The source table must have a primary key defined (it can be a multiple-column primary key), or it must have at least one column that contains unique values. This column (or columns ) must be included in the rows that are returned by the SELECT statement or query that is used for the SelectCommand .

• Table names that include special characters such as spaces, periods, quotation marks, or other non- alphanumeric characters cannot be used (even if delimited by square brackets). However, fully qualified table names that do include the period character (such as dbo.BookList ) can be used.

Of course, you can create your own command strings if required, rather than using the auto-generated commands provided by the CommandBuilder , and have the DataAdapter use these instead of the auto-generated ones. In later examples, you'll see how this is useful when working with stored procedures that perform the updates to the data store, and with custom SQL statements.

The DeriveParameters Method

One other useful feature that the CommandBuilder provides is the ability to automatically create appropriate Parameter objects. This includes both the situation when we are using stored procedures to update the data source, as well as when you are using them to extract data from a data store.

The DeriveParameters method of the CommandBuilder object takes as its single parameter a reference to a Command object, and returns this Command object with its Parameters collection populated with the appropriate Parameter objects. All that's required then is to fill in the values:

  'create the Connection, Command and DataAdapter   Dim objConnect As New OleDbConnection(  ConnectionString  )   Dim objCommand As New OleDbCommand(  SQLStatement  , objConnect)   Dim objDataAdapter As New OleDbDataAdapter(objCommand)   'create a CommandBuilder for this DataAdapter   Dim objCommandBuilder As New OleDbCommandBuilder(objDataAdapter)   'derive the parameters and set their values   objCommandBuilder.DeriveParameters(objCommand)   objCommand.Parameters("  param1-name  ").Value =  thevalue1    objCommand.Parameters("  param2-name  ").Value =  thevalue2    ...etc...  

However, be aware that the DeriveParameters method requires an extra call to the data store to get information about the parameters, and so is generally inefficient. You might use it during development to find out what parameters are required (you can iterate through the Parameters collection examining them after calling DeriveParameters ), but you should avoid using it in release code unless absolutely necessary.

Using the DataAdapter.Update Method

The example page Updating Data with a DataAdapter and DataSet Object ( update-with-dataset.aspx ) demonstrates the simplest way to use a DataAdapter object to update the source data with changes made to the rows stored in a DataSet object. This example simply reads in a rowset from the BookList table in your WroxBooks sample database, changes some of the rows, then pushes the changes back into the data store. As shown in Figure 10-10, the code in the page deletes or removes four rows from the original table, modifies values in three other rows, and adds a new row. You can see this by comparing the contents of the table in the two DataGrid controls on the page:

Figure 10-10:

As you can see from the note at the bottom of the page, the code uses a connection-based transaction to prevent the changes being permanently applied to the source data. If they were, the example page would fail to work the next time, as some of the rows would have been deleted and primary key violations would occur due to the new row already being present in the source table. However, you can change the code to commit the transaction to verify that it actually works and does update the original data.

You can also see the auto-generated commands that are used by the DataAdapter to update the source data. It's obvious that these are SQL statements, with question-mark characters as placeholders for the values used to update the table in our target data source. We'll look at them in more detail shortly.

The Code for the 'Updating with a DataAdapter' Example

As shown in the following code, the SELECT statement used is simple enough “it just selects a subset of the rows in your BookList table. Then you can use the now familiar technique to create and fill the DataSet with your source data. This is covered in detail in previous chapters, so we're simply listing the code here:

  strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '07645437%' " _   & "OR ISBN LIKE '07645438%'"   Dim objDataSet As New DataSet()   Dim objConnect As New OleDbConnection(strConnect)   Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect)   Try     objDataAdapter.Fill(objDataSet, "Books")   Catch objError As Exception   outError.innerHTML = "* Error while accessing data.<br />" _   & objError.Message & "<br />" & objError.Source   Exit Sub   End Try  

In your example, you want to be able to see which rows have been changed, and the Update method also depends on this information to be able to correctly update the original data in your database. One way to fix the current state of all the rows in all the tables in a DataSet (as seen in the previous chapter) is to call the AcceptChanges method to accept all the changes that have been made to the DataSet .

In fact, in your example it's not strictly necessary because the Fill method automatically sets the status of all the rows to Unchanged . However (as shown in the following code) it illustrates the process, and would be necessary if you had made any changes since you originally filled the DataSet that you don't want to flush back into the database. In later examples, we'll take advantage of this.

You'll also need to refer to the Books table in your DataSet in several places within your code, so create this reference next. Then you can display the contents of the Books table that is currently held in your DataSet . Simply bind the default view of the table to a DataGrid control named dgrResult1 that is declared elsewhere in the HTML section of the page.

  'accept the changes to "fix" the current state of the DataSet contents   objDataSet.AcceptChanges()     'declare a variable to reference the Books table   Dim objTable As DataTable = objDataSet.Tables("Books")     'display the contents of the Books table before changing data   dgrResult1.DataSource = objTable.DefaultView   dgrResult1.DataBind() 'and bind (display) the data  

Changing the Rows in the DataSet

Now you're ready to make some changes to the data. The following code shows that you can use exactly the same technique as in the previous chapter examples. After making these changes to the Books table in your DataSet display the contents again. Notice that you have to use a date string that is in the correct format for the column in your table. In the example where the value of a parameter object is set, use the format " yyyy-mm-dd " as this is a suitable format for the SQL DateTime field. Here you're using the format " mm-dd-yyyy " as this is the format of the ADO.NET table column.

  'now change some records in the Books table   objTable.Rows(0).Delete()   objTable.Rows(1)("Title") = "Amateur Theatricals for Windows 2000"   objTable.Rows(2).Delete()   objTable.Rows(3).Delete()   objTable.Rows(4)("PublicationDate") = "01-01-2002" 'see note below   objTable.Rows.Remove(5)     'notice that using the Remove method on row 5 (rather than marking   'it as deleted) means that the next row then becomes row 5   objTable.Rows(5)("ISBN") = "200000000"   'add a new row using an array of values   Dim objValsArray(2) As Object   objValsArray(0) = "200000001"   objValsArray(1) = "Impressionist Guide to Painting Computers"   objValsArray(2) = "05-02-2002" 'see note below   objTable.Rows.Add(objValsArray)     'display the contents of the Books table after changing the data   dgrResult2.DataSource = objTable.DefaultView   dgrResult2.DataBind() 'and bind (display) the data  

Creating the Auto-Generated Commands

OK, so now you can update your data source. The first step in this part of the process is to create the commands that the DataAdapter will use to push the changes into the database. You can use a CommandBuilder to create the three Command objects it requires, and assign these to the appropriate properties of the DataAdapter so that you can retrieve and display them afterwards.

  'create command builder commands to update insert and delete rows   Dim objCommandBuilder As New OleDbCommandBuilder(objDataAdapter)     'set the update, insert and delete commands for the DataAdapter   'this is only required because we want to access them afterwards   'if omitted, commands are set to null when update completes   objDataAdapter.DeleteCommand = objCommandBuilder.GetDeleteCommand()   objDataAdapter.InsertCommand = objCommandBuilder.GetInsertCommand()   objDataAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand()  

Pushing the Changes Back into the Data Source

As your example uses a transaction (so that you can re-run the page) you have to explicitly open the connection to the database. If you weren't using a transaction, you could remove the Open method call as well (the DataAdapter automatically opens the connection when you call the Update method, then closes it afterwards). Then, as shown in the following code, make a call to the BeginTransaction method of the connection.

Next (only because you're using a transaction in your example) you have to explicitly enroll all the Command objects into the transaction. Then you can call the Update method of the DataAdapter to push all the changes you've made to the rows in the DataSet back into the data source automatically. Notice that the name of the table that contains the changes we want to push back into the data source is specified.

Normally that's all you would need to do. However, you are performing the update within a transaction so that you can roll it back again afterwards “ allowing you to run the same page again without getting the errors that would occur from inserting and deleting the same rows again. So finish off by rolling back this transaction.

  'start a transaction so we can roll back changes if required   objConnect.Open()   objConnect.BeginTransaction()     'attach the current transaction to all the Command objects   'must be done after setting Connection property   objDataAdapter.DeleteCommand.Transaction = objTransaction   objDataAdapter.InsertCommand.Transaction = objTransaction   objDataAdapter.UpdateCommand.Transaction = objTransaction     'perform the update on the original data   objDataAdapter.Update(objDataSet, "Books")   objTransaction.Rollback()  

Viewing the Auto-Generated Commands

The example page displays the auto-generated commands that were created by the CommandBuilder object so that you can see what they look like. The code is placed at the end of the page, extracting the command strings and placing them in <div> elments located within the HTML section of the page.

  'display the SQL statements that the DataSet used   'these are created by the CommandBuilder object   outInsert.InnerText = objDataAdapter.InsertCommand.CommandText   outDelete.InnerText = objDataAdapter.DeleteCommand.CommandText   outUpdate.InnerText = objDataAdapter.UpdateCommand.CommandText  

If you examine these command strings (shown again in Figure 10-11), you can see that they are outline or pseudo SQL statements, containing question-mark placeholders where the values from each row are inserted when the statements are executed. Notice how they only perform the action on the source table if the row has not been changed by another process in the meantime (that is, while the DataSet was holding the rows). The DataSet is a disconnected data repository, and so the original rows could have been updated, existing rows deleted, or new rows added with the same primary key by another user or process.

Figure 10-11:

Later in this chapter you'll be looking in detail at how ADO.NET manages concurrent updates to a data store, and how you can manage them yourself. In the meantime, there are a few other issues that you need to look at when using the Update method of the DataAdapter object.

Checking How Many Rows Were Updated

The Update method returns the number of rows that were updated in the source table. While you didn't take advantage of this in your examples, it's pretty easy to do. Simply declare an Integer variable and assign the result of the Update method to it:

  Dim intRowsUpdated As Integer   intRowsUpdated = objDataAdapter.Update(objDataSet, "table-name")  

Specifying the Tables When Updating Data

As you've seen, the DataAdapter object's Update method provides a really easy and efficient way to update the source data. If you have more than one table in the DataSet , simply call the method once for each table to automatically update the source data with all the changes to rows in that table. The changes are applied in the order that the rows exist within the table in the DataSet .

There is one point to watch out for, however. If the source data tables contain foreign keys, in other words there are enforceable relationships between the tables then the order that the tables are processed can cause errors to occur. It all depends on the type of updates you're carrying out, and the rules or triggers you have inside the source database.

For example, if your DataSet contained rows that originally came from the BookList , AuthorList , and BookPrices tables, you could add a new book to the Books table in the DataSet and add matching rows (based in the ISBN that acts as the primary and foreign keys) to the Authors and Prices tables in the DataSet .

When you execute the Update method, however, it will only work if the Books table is the first one to be processed. If you try to process the Authors or Prices table first, the database will report an error because there will be no parent row with an ISBN value to match the newly inserted child rows. You are trying to insert orphan rows into the database table, and thus breaking referential integrity rules.

In other words, to insert a new book in our example, you would have to use:

  objDataAdapter.Update(objDataSet, "Books")   objDataAdapter.Update(objDataSet, "Authors")   objDataAdapter.Update(objDataSet, "Prices")  

However, if you have deleted a book and all its child rows from the Authors and Prices tables in the DataSet , the opposite applies. You can't delete the parent row while there are child rows in the database table, unless the database contains rules or triggers that cascade the deletes to remove the child rows.

And if it does, the delete operations carried out for the child tables would fail, because the rows would have already been deleted. This means that you probably want to process the Books table in your DataSet last rather than first:

  objDataAdapter.Update(objDataSet, "Authors")   objDataAdapter.Update(objDataSet, "Prices")   objDataAdapter.Update(objDataSet, "Books")  

But if you have carried out both insert and delete operations on the tables, neither method will work correctly. In this case, you need to process the updates in a more strictly controlled order. Let's look at what this involves when we examine concurrency issues later on in this chapter (in the section Marshalling the Changed Rows in a DataSet ). First, we'll briefly examine some of the other ways that you can use the Update method.

Automatically Updating the Default Table in a DataSet

If you have created a table mapping in the DataSet for the default table, you can execute the Update method without specifying the table name. We discussed how to create table mappings in the previous chapter. Basically, create a variable to hold a TableMapping object and then call the Add method of the DataAdapter object's TableMappings collection to create the new table mapping. Specify the string " Table " to indicate that you are creating a default table mapping, and the name of the table:

  Dim objTableMapping As DataTableMapping   objTableMapping = objDataAdapter.TableMappings.Add("Table", "DefaultBookList")  

Now you can call the Update method without specifying the name of the table:

  objDataAdapter.Update(objDataSet)  

An error occurs if this mapping does not exist when the Update method is called without specifying the name of a table.

Updating Subsets of Rows from a Table

The DataAdapter object's Update method can also be used to push changes from a collection or array of DataRow objects into the data source. All the rows must come from the same source table, and there must be a default table mapping set up as described in the previous section. The updates are then processed in the order that they exist in the array.

To create an array of DataRow objects you can use the All property of a table's Rows collection:

  Dim arrRows() As DataRow   arrRows = objDataSet.Tables(0).Rows.All  

Then you can push the changes in this array of rows into the data source using the Update method and specifying this array:

  objDataAdapter.Update(arrRows)  

This technique is useful if you have an array of rows that contain our changed records, rather than one that contains all the rows in the original table.

Updating from a DataSet Using Stored Procedures

Near the start of the chapter, we showed you how to use stored procedures within a database to update the source data. In that example, you used a Command object to execute the stored procedures. Meanwhile, the previous example showed how to use the auto-generated commands with a DataSet to update data automatically.

Of course, you don't have to use auto-generated commands with a DataSet . Instead you can use your own custom SQL statements or stored procedures to do the same thing. Just create the appropriate Command objects for the InsertCommand , DeleteCommand , and UpdateCommand properties of the DataAdapter , and call the Update method as before. Then your custom SQL statements or stored procedures are used to push the changes back into the data store.

The previous example also updated only a single table (a pre-requisite when using the auto-generated commands). However, often you have a more complex task to accomplish when updating the source data. For example, the rows in the table in your DataSet might have originally been created from several source tables, perhaps by using a JOIN statement in the SQL query or some complex stored procedure. This was demonstrated at the beginning of the previous chapter, where you had a table containing data drawn from both the BookList and the BookAuthors tables in your sample database. When you come to push changes to data like this back into your database, you need to use some process that can disentangle the values in each row and perform a series of staged updates to the original tables, thereby maintaining integrity within the database.

The example page Updating Complex Data with a DataSet and Stored Procedures ( complex - dataset - update.aspx ) shown in Figure 10-12 demonstrates all of these techniques and features.

Figure 10-12:

It extracts some data from the sample database using a stored procedure that joins two tables, and displays it in a DataGrid . Then it changes some of the rows in the original table and displays the data again. Finally, it pushes the changes back into the data source using stored procedures that we've provided within the database.

At the top of the page you can see the values of the four Command objects' CommandText properties. The SelectCommand is a stored procedure named GetBookprices that takes a single parameter (the ISBN) “ which we provide inline. This stored procedure joins the BookList and BookPrices tables, and returns a rowset containing values from both tables.

  CREATE PROCEDURE GetBookPrices   @ISBN varchar(10) AS   SELECT BookList.ISBN, BookList.Title, BookPrices.Currency, BookPrices.Price   FROM BookList JOIN BookPrices ON BookList.ISBN = BookPrices.ISBN   WHERE BookList.ISBN LIKE @ISBN  

The other three commands shown in Figure 10-12 are obviously not auto-generated SQL statements, and they don't contain the question-mark placeholders. They are of course the names of three stored procedures within the sample database, and the names of the parameters are added to the display as well “these are not actually part of the command strings.

At the bottom of the page is a note about the transaction that is used to prevent the updates being permanently committed to the data store so that you can re-run the page (without this the updates to the source data would prevent the page from working next time).

The Update Stored Procedures for the Example Page

Your DataSet table holds rows that are created from two different tables in the database, and so the auto-generated commands from a CommandBuilder cannot be used to persist inserts , deletes, or updates that are made to rows in the table in the DataSet . Instead you can use three stored procedures.

The BookPriceUpdate stored procedure takes as parameters the ISBN of the book (which is the primary key in the BookList table and part of the primary key in the BookPrices table), the name of the currency in the BookPrices table (which is the other half of the primary key in this table), and the actual value for the Price column in the BookPrices table. It uses these values to update the matching row in the BookPrices table:

  CREATE PROCEDURE BookPriceUpdate   @ISBN varchar(10),   @Currency varchar(3),   @Price money   AS   UPDATE BookPrices SET Price=@Price WHERE Currency=@Currency AND ISBN = @ISBN  

The BookPriceInsert stored procedure takes as parameters the ISBN, title, currency, and price values that it will use to insert a new row into the BookList table and a new row into the BookPrices table. Note that, as shown in the following code, it first checks to see if a book with the specified ISBN already exists in the BookList table (as it might if we are only inserting a price in a different currency). In this case, it just inserts the new BookPrices row.

  CREATE PROCEDURE BookPriceInsert   @ISBN varchar(10),   @Title varchar(100),   @Currency varchar(3),   @Price money   AS   SELECT ISBN FROM BookList WHERE ISBN = @ISBN   IF @@ROWCOUNT = 0   INSERT INTO BookList(ISBN, Title) VALUES (@ISBN, @Title)   INSERT INTO BookPrices(ISBN, Currency, Price) VALUES (@ISBN, @Currency,@Price)  

Finally, the BookPriceDelete stored procedure takes only two parameters “the ISBN of the book and the name of the currency for the row it will delete in the BookPrices table (see the following code). However, if there are no price rows left for this book after deleting the specified one, it also deletes the matching row from BookList table. OK, so it's a pretty contrived example, but it demonstrates the way that you can use stored procedures to manipulate multiple tables from the Update method of the DataAdapter .

  CREATE PROCEDURE BookPriceDelete   @ISBN varchar(10),   @Currency varchar(3)   AS   DELETE FROM BookPrices   WHERE ISBN = @ISBN AND Currency = @Currency   SELECT ISBN FROM BookPrices WHERE ISBN=@ISBN   IF @@ROWCOUNT = 0   DELETE FROM BookList WHERE ISBN=@ISBN  

The Code for the 'Updating with Stored Procedures' Example

So, all you need to do now is use these three stored procedures as the command text for the Command objects in the DataAdapter object's UpdateCommand , InsertCommand , and DeleteCommand properties. The first part of the code in the page simply fills the DataSet from the database using the same techniques as discussed in earlier examples and earlier chapters, so we aren't repeating that here.

Next, the code changes some of the values in the rows in the DataSet , deleting the first row, updating the price in the second row, and adding a new row:

  'declare a variable to reference the Books table   Dim objTable As DataTable = objDataSet.Tables("Books")   'change some rows in the DataSet table   'delete the first row   objTable.Rows(0).Delete()   'update price in the second row   objTable.Rows(1)("Price") = 299.99   'add a new row using an array of values   Dim objValsArray(3) As Object   objValsArray(0) = "200000001"   objValsArray(1) = "Impressionist Guide to Painting Computers"   objValsArray(2) = "USD"   objValsArray(3) = "29.99"   objTable.Rows.Add(objValsArray)  

Using Dynamic Parameters with a Stored Procedure

The important point to note in this example is that you're specifying which columns will provide the values for the parameters when the Command is executed, rather than specifying actual values for the parameters. You are creating a dynamic parameters that are the equivalent to the question-mark placeholders you saw in the SQL statements for the update, delete, and insert command in the previous example. The appropriate one of these commands will be executed for each row in the DataSet table that has been modified (has a RowState property value of DataRowState.Modified ), deleted (has a RowState property value of DataRowState.Deleted ), or inserted (has a RowState property value of DataRowState.Added ).

To specify a dynamic parameter, set the SourceColumn property of the Parameter object to the name of the column from which the value for the parameter will come. However, you'll recall that each column can expose four different values (the DataRowVersion ): Original , Current , Default , and Proposed . You can specify which of these values you want the parameter to use by setting the SourceVersion property of the Parameter object as well.

This means you can specify the Original value of the column as the parameter value (useful if it is being used to look up or match a value with the original value of that column in the source table), or the Current value of the column if you are updating that column in the table. In other words, you would specify that the parameter should use the Original value of this column from each row when it's part of the SQL WHERE clause (and so should match the existing value in the database tables) or the Current value when it's part of the SET clause.

The UpdateCommand and the Dynamic Parameters

So let's get on and build the necessary Command objects. Let's start with the one for the UpdateCommand . Create a new Command object and specify that the CommandType is a stored procedure. Then you can create the parameters that to be used with this Command object.

The first parameter is used to match the ISBN code, and so it uses the Original value of that column. The code is similar for the remaining two parameters ( Currency and Price ). However, while the Currency parameter also uses the Original value of the column, the Price must use the Current version of the data for this column in the rows, because this value will be used to update the original rows in the database table. It will become part of the SET clause in the SQL statement that is executed by the stored procedure.

Then, once all the parameters are ready, you can specify that this Command object be used as the update command by assigning it to the DataAdapter object's UpdateCommand property.

  ' create the UpdateCommand and parameters   Dim objUpdateCommand As New OleDbCommand("BookPriceUpdate", objConnect)   objUpdateCommand.CommandType = CommandType.StoredProcedure   'now create the Parameter objects and add to the Command object   Dim objParam As OleDbParameter   objParam = objUpdateCommand.Parameters.Add("ISBN", OleDbType.VarChar, 10)   objParam.Direction = ParameterDirection.Input   objParam.SourceColumn = "ISBN"   objParam.SourceVersion = DataRowVersion.Original 'used in SQL WHERE clause     objParam = objUpdateCommand.Parameters.Add("Currency", OleDbType.VarChar, 3)   objParam.Direction = ParameterDirection.Input   objParam.SourceColumn = "Currency"   objParam.SourceVersion = DataRowVersion.Original    'used in SQL WHERE clause     objParam = objUpdateCommand.Parameters.Add("Price", OleDbType.Double)   objParam.Direction = ParameterDirection.Input   objParam.SourceColumn = "Price"   objParam.SourceVersion = DataRowVersion.Current 'used in SQL SET clause   'now specify this Command object as the UpdateCommand   objDataAdapter.UpdateCommand = objUpdateCommand  

The InsertCommand and the Dynamic Parameters

The InsertCommand requires four parameters, as shown in the following code. Note that in this case the stored procedure uses the ISBN value in the SET clause of the SQL statement rather than the WHERE clause, to set the value of the newly inserted rows, so it must use the Current value of the column and not the Original value.

The remaining three parameters are the Title that is placed, along with the ISBN, into the new row in the BookList table; and the currency and price to be instered, along with the ISBN, into the BookPrices table. Finally you can specify that this Command object is the insert command by assigning it to the DataAdapter object's InsertCommand property.

  Dim objInsertCommand As New OleDbCommand("BookPriceInsert", objConnect)   objInsertCommand.CommandType = CommandType.StoredProcedure     objParam = objInsertCommand.Parameters.Add("ISBN", OleDbType.VarChar, 10)   objParam.Direction = ParameterDirection.Input   objParam.SourceColumn = "ISBN"   objParam.SourceVersion = DataRowVersion.Current 'used in SQL SET clause     objParam = objInsertCommand.Parameters.Add("Title", OleDbType.VarChar, 100)   objParam.Direction = ParameterDirection.Input   objParam.SourceColumn = "Title"   objParam.SourceVersion = DataRowVersion.Current 'used in SQL SET clause     objParam = objInsertCommand.Parameters.Add("Currency", OleDbType.VarChar, 3)   objParam.Direction = ParameterDirection.Input   objParam.SourceColumn = "Currency"   objParam.SourceVersion = DataRowVersion.Current 'used in SQL SET clause     objParam = objInsertCommand.Parameters.Add("Price", OleDbType.Double)   objParam.Direction = ParameterDirection.Input   objParam.SourceColumn = "Price"   objParam.SourceVersion = DataRowVersion.Current 'used in SQL SET clause     objDataAdapter.InsertCommand = objInsertCommand  

The DeleteCommand and the Dynamic Parameters

The third and final stored procedure is used to delete rows from the source table(s). It requires just two parameters (the ISBN and the currency) and these take their values from the Original row values.

Otherwise, the code to create them is very similar to that you've just been using with the other Command objects.

  Dim objDeleteCommand As New OleDbCommand("BookPriceDelete", objConnect)   objDeleteCommand.CommandType = CommandType.StoredProcedure     objParam = objDeleteCommand.Parameters.Add("ISBN", OleDbType.VarChar, 10)   objParam.Direction = ParameterDirection.Input   objParam.SourceColumn = "ISBN"   objParam.SourceVersion = DataRowVersion.Original 'used in SQL WHERE clause     objParam = objDeleteCommand.Parameters.Add("Currency", OleDbType.VarChar,3)   objParam.Direction = ParameterDirection.Input   objParam.SourceColumn = "Currency"   objParam.SourceVersion = DataRowVersion.Original 'used in SQL WHERE clause     objDataAdapter.DeleteCommand = objDeleteCommand  

Displaying the Command Properties

Now that the three new Command objects are ready, you can display the CommandText and the parameters for each one in the page. Notice that you can iterate through the Parameters collection with a For Each construct to get the values.

  'get stored procedure name and source column names for each parameter   Dim strSQL As String = objDataAdapter.UpdateCommand.CommandText   For Each objParam In objDataAdapter.UpdateCommand.Parameters   strSQL &= " @" & objParam.SourceColumn & ","   Next   strSQL = Left(strSQL, Len(strSQL) 1) 'remove trailing comma   outUpdate.InnerText = strSQL 'and display it   ...   'repeat the process for the Insert command   ...   'repeat the process for the Delete command   ...  

Executing the Update

Simply call the Update method of the DataAdapter to push your changes into the database via the stored procedures in exactly the same way as you did in previous examples. As in earlier examples, this page uses a transaction to make it repeatable, so the code is a little more complex than is actually required simply to push those changes into the database. Basically, all you need is:

  objDataAdapter.Update(objDataSet, "Books")  

The code to create the transaction is the same as used in the previous example, and you can use the [view source] link at the bottom of the page to see it. To prove that the updates do actually get carried out, you can also change the code so that the transaction is committed, or remove the transaction code altogether.

Using the NOCOUNT Statement in Stored Procedures

One point to be aware of when using stored procedures with the Update method is that the DataAdapter decides whether the update succeeded or failed based on the number of rows that are actually changed by the SQL statement(s) within the stored procedure.

When a SQL INSERT , UPDATE , or DELETE statement is executed (directly or inside a stored procedure) the database returns the number of rows that were affected. If there are several SQL statements within a stored procedure, it adds up the number of affected rows for all the statements and returns this value. If the returned value for the number of rows affected is zero, the DataAdapter will assume that the process ( INSERT , UPDATE , or DELETE ) failed. However, if any other value (positive or negative) is returned, the DataAdapter assumes that the process was successful.

In most cases this is fine and it works well, especially when you use CommandBuilder -created SQL statements rather than stored procedure to perform the updates. But if a stored procedure executes more than one statement, it may not always produce the result you expect. For example, if the stored procedure deletes child rows from one table and then deletes the parent row in a different table, the 'rows affected' value will be the sum of all the deletes in both tables. However, if the delete succeeds in the child table but fails in the parent table, the 'rows affected' value will still be greater than zero. So, in this case, the DataAdapter will still report success, when in actual fact it should report a failure.

To get around this problem, you can use the NOCOUNT statement within a stored procedure. When NOCOUNT is ON , the number of rows affected is not added to the return value. You could use it to prevent the deletes to the child rows from being included in your 'rows affected' return value.

  SET NOCOUNT ON   DELETE FROM ChildTable WHERE KeyValue = @param-value   SET NOCOUNT OFF   DELETE FROM ParentTable WHERE KeyValue = @param-value  

Update Events in the DataAdapter

In the previous chapter you saw how to write event handlers for several events that occur for a row in a table when that row is updated. In the examples, the row was held in a DataTable object within a DataSet , and the events occurred when the row was updated. There is another useful series of events that can be handled, but this time they occur when you come to push the changes back into the original data store using a DataAdapter object.

The DataAdapter exposes two events: the RowUpdating event occurs before an attempt is made to update the row in the data source, and the RowUpdated event occurs after the row has been updated (or after an error has been detected “a topic we'll look at later). This means that you can monitor the updates as they take place for each row when you use the Update method of the DataAdapter .

Handling the RowUpdating and RowUpdated Events

The example page Handling the DataAdapter's RowUpdating and RowUpdated Events ( rowupda ted-event.aspx ) demonstrates how you can use these events to monitor the update process in a DataAdapter object. When you open the page, shown in Figure 10-13, you see the now familiar DataGrid objects containing the data before and after it has been updated by code within the page. You can also see the SQL SELECT statement that is used to extract the data, and the three auto-generated statements that are used to perform the update. This page uses exactly the same code as the earlier DataAdapter.Update example to extract and edit the data, and to push the changes back into the database. The extra features can be seen once you scroll down, as shown in Figure 10-14.

Figure 10-13:

Figure 10-14:

The remainder of the page contains three sets of output that is generated by the handlers you've provided for the RowUpdating and RowUpdated events ”one each for a deleted row, an updated row, and a row added to the DataSet table.

Attaching the Event Handlers

One difference between the code in this page, and the code used in the earlier examples, is the addition of two event handlers. Attach these event handlers, which are named OnRowUpdating and OnRowUpdated to the DataAdapter object's RowUpdating and RowUpdated properties. In VB.NET, you can use the AddHandler statement for this:

  AddHandler objDataAdapter.RowUpdating, _   New OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating)   AddHandler objDataAdapter.RowUpdated, _   New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)  

In C# you can do the same using:

  objDataAdapter.RowUpdating += new OleDbRowUpdatingEventHandler(OnRowUpdating);   objDataAdapter.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);  

The OnRowUpdating Event Handler

When the DataAdapter comes to push the changes to a row into the data store, it first raises the RowUpdating event, which will now execute your event handler named OnRowUpdating . The code receives two parameters, a reference to the object that raised the event, and a reference to a RowUpdatingEventArgs object.

Of course, as you're using the objects from the System . Data . OleDb namespace in this example, you actually get an OleDbRowUpdatingEventArgs object. If you were using the objects from, for example, the System . Data . SqlClient namespace you would get a reference to a SqlDbRowUpdatingEventArgs object. The RowUpdatingEventArgs object provides a series of fields or properties that contain useful information about the event, as shown in the table:

Property	Description
StatementType	A value from the StatementType enumeration indicating the type of SQL statement that will be executed to update the data. Can be Insert , Update , or Delete .
Row	This is a reference to the DataRow object that contains the data being used to update the data source.
Status	A value from the UpdateStatus enumeration that reports the current status of the update and allows it and subsequent updates to be cancelled. Possible values are: Continue , SkipCurrentRow , SkipAllRemainingRows , and ErrorsOccurred .
Command	This is a reference to the Command object that will execute the update.
TableMapping	A reference to the DataTableMapping that will be used for the update.

The example page's event handler collects the statement type by querying the StatementType enumeration (one of the values Delete , Insert , Select , or Update ) and uses this value to decide where to get the row values for display. If it's an Insert statement, the Current value of the ISBN column in the row will contain the new primary key for that row, and the Original value will be empty. However, if it's an Update or Delete statement, the Original value will be the primary key of the original row in the database that corresponds to the row in your DataSet .

So, you can extract the primary key of the row that is about to be pushed into the database and display it, along with the statement type, in your page:

  Sub OnRowUpdating(objSender As Object, _   objArgs As OleDbRowUpdatingEventArgs)     'get the text description of the StatementType   Dim strType = System.Enum.GetName(objArgs.StatementType.GetType(), _   objArgs.StatementType)     'get the value of the primary key column "ISBN"   Dim strISBNValue As String     Select Case strType   Case "Insert"   strISBNValue = objArgs.Row("ISBN", DataRowVersion.Current)   Case Else   strISBNValue = objArgs.Row("ISBN", DataRowVersion.Original)   End Select     'add result to display string   gstrResult &= strType & " action in RowUpdating event " _   & "for row with ISBN='" & strISBNValue & "'<br />"     End Sub  

The OnRowUpdated Event Handler

After the row has been updated in the database, or when an error occurs, your OnRowUpdated event handler will be executed. In this case, you get a reference to a RowUpdatedEventArgs object instead of a RowUpdatingEventArgs object. It exposes the same five properties as the RowUpdatingEventArgs class, plus two more useful fields, as shown in the following table:

Property	Description
Errors	An Error object containing details of any error that was generated by the data provider when executing the update.
RecordsAffected	The number of rows that were changed, inserted, or deleted by execution of the SQL statement. Expect one ( 1 ) on success and zero or -1 if there is an error.

So, in your OnRowUpdated event handler, you can provide information about what happened after the update. Collect the statement type again; also collect all the Original and Current values from the columns in the row. Of course, if it is an Insert statement there won't be any Original values, as the row has been added to the table in the DataSet since the DataSet was originally filled. Likewise, there won't be any Current values if this row has been deleted in the DataSet .

And this time you can also include details about the result of the update. You can query the RecordsAffected value to see if a row was updated (as we expect), and if not include the error message from the Errors field.

  'event handler for the RowUpdated event   Sub OnRowUpdated(objSender As Object, objArgs As OleDbRowUpdatedEventArgs)     'get the text description of the StatementType   Dim strType = System.Enum.GetName(objArgs.StatementType.GetType(), _   objArgs.StatementType)   'get the value of the columns   Dim strISBNCurrent, strISBNOriginal, strTitleCurrent As String   Dim strTitleOriginal, strPubDateCurrent, strPubDateOriginal As String     Select Case strType   Case "Insert"   strISBNCurrent = objArgs.Row("ISBN", DataRowVersion.Current)   strTitleCurrent = objArgs.Row("Title", DataRowVersion.Current)   strPubDateCurrent = objArgs.Row("PublicationDate", _   DataRowVersion.Current)   Case "Delete"   strISBNOriginal = objArgs.Row("ISBN", DataRowVersion.Original)   strTitleOriginal = objArgs.Row("Title", DataRowVersion.Original)   strPubDateOriginal = objArgs.Row("PublicationDate", _   DataRowVersion.Original)   Case "Update"   strISBNCurrent = objArgs.Row("ISBN", DataRowVersion.Current)   strTitleCurrent = objArgs.Row("Title", DataRowVersion.Current)   strPubDateCurrent = objArgs.Row("PublicationDate", _   DataRowVersion.Current)   strISBNOriginal = objArgs.Row("ISBN", DataRowVersion.Original)   strTitleOriginal = objArgs.Row("Title", DataRowVersion.Original)   strPubDateOriginal = objArgs.Row("PublicationDate", _   DataRowVersion.Original)   End Select     'add result to display string   gstrResult &= strType & " action in RowUpdated event:<br />" _   & "* Original values: ISBN='" & strISBNOriginal & "' " _   & "Title='" & strTitleOriginal & "' " _   & "PublicationDate='" & strPubDateOriginal & "'<br />" _   & "* Current values: ISBN='" & strISBNCurrent & "' " _   & "Title='" & strTitleCurrent & "' " _   & "PublicationDate='" & strPubDateCurrent & "'<br />"   'see if the update was successful   Dim intRows = objArgs.RecordsAffected   If intRows > 0 Then   gstrResult &= "* Successfully updated " & intRows.ToString() _   & " row<p />"   Else   gstrResult &= "* Failed to update row <br />" _   & objArgs.Errors.Message & "<p />"   End If     End Sub  

AcceptChanges and the Update Process

One important point to bear in mind is how the update process affects the Original and Current values of the rows in the tables in a DataSet . Once the DataAdapter . Update process is complete (in other words all the updates for all the rows have been applied), the AcceptChanges method is called for those rows automatically. So, after an update, the Current values in all the rows are moved to the Original values.

However, during the update process (as you can see from the example), the Current and Original values are available in both the RowUpdating and the RowUpdated events. Therefore you can use these events to monitor changes and report errors (more in a later example).

The techniques used in this section of the chapter (and in earlier examples) work fine in circumstances where there are no concurrent updates taking place on the source data. In other words, there is only ever one user reading from and writing to any particular row in the tables at any one time. However, concurrency rears its ugly head in many applications and can cause all kinds of problems if you aren't prepared for it.