Professional ASP.NET 1.1 [Electronic resources] نسخه متنی

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:

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:

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.