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.
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 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.
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
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.
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.
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
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.
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.
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
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.
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.
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
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
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()
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()
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.
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")
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.
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.
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.
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).
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
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
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"
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.
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
'' 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 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 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
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
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
...
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.
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
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 .
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.
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);
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
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
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
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
''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
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.