Manually Configuring Your DataAdapter Objects

Other Updating Concerns

You now know the basics of updating a database with the changes stored in a DataSet. If you're generating your own updating logic—whether in the form of INSERT, UPDATE, and DELETE queries or stored procedure calls—you'll need to know more than just the basics.

For example, how do you handle concurrency so that you don't accidentally overwrite another user's changes? How do you handle null values in your concurrency checks? How do you submit updates in a transaction? What part does the TableMappings collection for a DataAdapter play when submitting updates?

Would you like to know how to accomplish these tasks? Read on.

Optimistic Concurrency Options

When you build a multi-user database application that relies on optimistic concurrency to submit updates to your database, it is important to perform the appropriate optimistic concurrency checks in your update queries. Let's say you've built your application and two users request the same row of data and then they both attempt to update the same row of data. What happens next? That depends on how you construct your update queries.

You have four basic optimistic concurrency options in SQL update queries.

Include Only the Primary Key Columns

You can include only the primary columns in the SQL UPDATE and DELETE queries, which creates a "last in wins" updating scenario. Both update attempts will succeed. Obviously, the database won't maintain both sets of changes. There can be only one. The changes made by the last update will override the previous changes.

Here's a quick breakdown of the scenario:

• User A fetches the row.

• User B fetches the row.

• User B modifies the row and successfully submits the changes.

• User A modifies the row and successfully submits the changes, overwriting the changes that User B just submitted.

User A is not even aware that the contents of the row in the database have changed between the time of the initial query and the time the user submits the changes.

If the "last in wins" scenario is what you're looking for, this is the option for you. However, this option is not appropriate if you want to prevent users from unwittingly overwriting other users' changes.

The CommandBuilder object does not offer this optimistic concurrency option; the Data Adapter Configuration Wizard does. On the Advanced Options tab, deselect the Use Optimistic Concurrency check box.

Include All Columns in the WHERE Clause

What if you don't want to go with "last in wins" updating? Maybe you don't want User A's changes to overwrite changes made to the database between the time the user queries the database and submits the modified row.

The default behavior of both the CommandBuilder and the Data Adapter Configuration Wizard is to include all columns in the WHERE clause. Using this logic prevents your code from overwriting changes made by other users between the time your code retrieves the row and the time your code attempts to submit the pending change in the row.

Let's look at an example. Say that Users A and B retrieve the same row of customer data. User B makes a change to the ContactName column and submits the change. The application includes all columns in the WHERE clause of the query-based updates, so the UPDATE query looks like this:

UPDATE Customers     SET CustomerID = 'ABCDE', CompanyName = 'Original Company Name',          ContactName = 'New Contact', Phone = '800-555-1212'     WHERE CustomerID = 'ABCDE' AND            CompanyName = 'Original Company Name' AND            ContactName = 'Original Contact' AND            Phone = '800-555-1212'

Meanwhile, User A modifies the same row of customer data, changing the value of the CompanyName column. Because User A retrieved the row before User B submitted the change to the ContactName column, User A's UPDATE query will look like this:

UPDATE Customers     SET CustomerID = 'ABCDE', CompanyName = 'New Company Name',          ContactName = 'Original Contact', Phone = '800-555-1212'     WHERE CustomerID = 'ABCDE' AND            CompanyName = 'Original Company Name' AND            ContactName = 'Original Contact' AND            Phone = '800-555-1212'

Because the value of the ContactName column for this row of data has changed in the database, no row in the table satisfies all the criteria in the query's WHERE clause. Thus, the database does not modify the customer row. The DataAdapter queries the database to determine how many rows the query modified, discovers that the query did not successfully update the desired row, and marks the DataRow accordingly. We'll discuss identifying and resolving such conflicts in Chapter 11.

This is the concurrency option that the CommandBuilder object uses. The Data Adapter Configuration Wizard uses this concurrency option by default.

	Generally speaking, databases do not let you perform comparisons of two BLOB values. Because you can store many megabytes of data in a BLOB column, comparing them would be extremely inefficient—if it were even possible. Code-generation tools such as the CommandBuilder and the Data Adapter Configuration Wizard should not include BLOB columns in the WHERE clause of the query-based updates. Keep this in mind if you're generating your own updating logic.

Include the Primary Key and Timestamp Columns

You can simplify the WHERE clause of your query-based updates by relying on timestamp columns. The SQL Server timestamp column does not actually contain date and time information. Instead, it contains binary data that's unique within the database.

You can define a timestamp column on your SQL Server table, and any time the contents of a row changes, SQL Server will modify the value of the timestamp column for that row. We can add a timestamp column to the Customers table and change the query in the previous example to look like this:

UPDATE Customers     SET CustomerID = 'ABCDE', CompanyName = 'Original Company Name',          ContactName = 'New Contact', Phone = '800-555-1212'     WHERE CustomerID = 'ABCDE' AND            TimestampColumn = 0x00000000000000CC

Because the server will generate a new value for the timestamp column each time it updates a row, you can use a combination of the primary key and timestamp columns in the WHERE clause of your query-based updates to ensure that you don't overwrite another user's changes.

Most database systems support a similar data type. Some use a unique binary value, and others use a date/time value. Check your database system's documentation to determine the back end's data type and learn how you can force the database to update the value each time you modify the contents of a row.

Currently, neither the CommandBuilder nor the Data Adapter Configuration Wizard supports generating updating logic using this optimistic concurrency strategy.

	As of SQL Server 2000, rowversion is synonymous with the timestamp data type. The SQL Server documentation recommends using the rowversion keyword instead of timestamp. I've used the term timestamp in this book because, as of this writing, it is more widely recognized.

I prefer using the primary key and timestamp columns in my concurrency checks because this option yields much simpler updating logic and the database has fewer columns to examine per update attempt.

Include the Primary Key Columns and Modified Columns

By default, the ADO cursor engine includes only the primary key columns and the original values of modified columns in the WHERE clause of its query-based updates. The cursor engine also includes only the modified columns in the SET clause of UPDATE queries.

Let's look at our multi-user example using this updating strategy. Let's say that User A and User B retrieve the same row of customer data at the same time. They each modify a different column of data—User A changes the CompanyName column, and User B changes the ContactName column. User B submits the pending change to the ContactName column first. User B's UPDATE query looks like this:

UPDATE Customers     SET ContactName = 'New Contact'     WHERE CustomerID = 'ABCDE' AND            ContactName = 'Original Contact'

User A then submits the pending change to the CompanyName column using the following UPDATE query:

UPDATE Customers     SET CompanyName = 'New Company Name'     WHERE CustomerID = 'ABCDE' AND            CompanyName = 'Original Company Name'

The contents of the row will change from

CustomerID  CompanyName            ContactName ----------  ---------------------  ---------------- ABCDE       Original Company Name  Original Contact

to

CustomerID  CompanyName            ContactName ----------  ---------------------  ---------------- ABCDE       Original Company Name  New Contact

and finally to

CustomerID  CompanyName            ContactName ----------  ---------------------  ---------------- ABCDE       New Company Name       New Contact

Both updates will succeed, and the change made by User A will not overwrite changes made by User B.

The structure of the ADO.NET DataAdapter does not lend itself to this updating strategy because it requires that you change the structure of the query based on the columns that have been modified in the row that contains the pending change. The DataAdapter supplies values for the parameters in its query-based updates on a row-by-row basis, but it does not modify the actual structure of the parameterized query.

Theoretically, you could write code to dynamically change the structure of the appropriate Command object and use that code while handling the DataAdapter object's RowUpdating event. I think that this updating strategy has benefits, but the costs outweigh them.

Working with Null Values

The Customers table in the Northwind database contains a Region column that accepts strings of up to 15 characters and also accepts Null values. A number of rows in the Region column have a Null value. Many developers will try to use a query such as the following to retrieve those rows:

SELECT CustomerID, CompanyName, ContactName, Phone     FROM Customers WHERE Region = NULL

If you use this query in ADO.NET or run this query in SQL Query Analyzer, you'll find that it returns zero rows.

Null values are a special case in the database world, especially when it comes to comparing Null values in a query. According to ANSI standards, you can't compare Null values using the = operator. Instead, you must use IS NULL in your query. The following query returns the rows in the Customers table that have Null values for the Region column:

SELECT CustomerID, CompanyName, ContactName, Phone     FROM Customers WHERE Region IS NULL

What do Null values have to do with submitting changes to your database using a DataAdapter? Let's take a quick look at the CommandText for the Command we created earlier to submit modified rows in the Order Details table:

UPDATE [Order Details]     SET OrderID = ?, ProductID = ?, Quantity = ?, UnitPrice = ?     WHERE OrderID = ? AND ProductID = ? AND            Quantity = ? AND UnitPrice = ?

None of the columns referenced in this query accepts Null values. As a result, the WHERE clause for this query is relatively simple. But what if the Quantity and UnitPrice columns were to allow Null values? Let's say you have a row that currently has a Null value in the Quantity column and you want to change that value to 20. If we replace the parameters with actual values, we get a query that looks like this:

UPDATE [Order Details]     SET OrderID = 12345, ProductID = 1, Quantity = 20, UnitPrice = 18     WHERE OrderID = 12345 AND ProductID = 1 AND           Quantity = Null AND UnitPrice = 18

In this scenario, the query will modify zero rows because of the Quantity = Null portion of the WHERE clause. The Quantity column for the desired row in the database is Null, but Null = Null evaluates to false, so the database does not modify the row.

So how do we change the WHERE clause of our queries to accommodate Null values in our concurrency check? If a particular column accepts Null values, we can replace the following portion of a query

ColumnName = ?

with

(ColumnName = ? OR ((ColumnName IS NULL) AND (? IS NULL)))

We want the clause to evaluate to true if the column and the parameter equate to the same non-Null value or if both the column and the parameter are Null.

Let's say your DataAdapter will query the Customers table for the CustomerID, CompanyName, ContactName, and Phone columns. Neither the CustomerID nor CompanyName columns accepts Null, but the ContactName and Phone columns do. As a result, you must perform Null checks in the WHERE clauses of your query-based updates. If you build your updating logic using the Data Adapter Configuration Wizard, you'll find that the wizard generates the following query to submit modified rows, complete with the appropriate Null checks:

UPDATE Customers     SET CustomerID = ?, CompanyName = ?, ContactName = ?, Phone = ?     WHERE (CustomerID = ?) AND (CompanyName = ?) AND           (ContactName = ? OR ((? IS NULL) AND (ContactName IS NULL)))           AND (Phone = ? OR ((? IS NULL) AND (Phone IS NULL)))

As I mentioned earlier, the Data Adapter Configuration Wizard does a very good job of generating updating logic. Even if you're going to generate your own logic, you might want to look at the code that the wizard generates in order to double-check your work.

Submitting Updates in Transactions

What if you want to submit all of your updates as a single unit of work so that either all of the updates succeed or none of them does? The simple answer is to wrap your updates in a transaction. However, the DataAdapter does not expose a Transaction property.

The DataAdapter does not actually submit the updates. It simply hands the work off to the Command objects in its UpdateCommand, InsertCommand, and DeleteCommand properties. The Command object exposes a Transaction property, so in order to submit the changes using the DataAdapter, you must set the Transaction property of the Command objects that the DataAdapter will use.

The following code snippet shows one way to accomplish this task:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _          "FROM [Order Details] WHERE OrderID = 10503 " & _          "ORDER BY ProductID" Dim tbl As New DataTable() Dim cn As New OleDbConnection(strConn) Dim da As New OleDbDataAdapter(strSQL, cn) 'Define updating logic for the DataAdapter. 'Open the connection and fetch the results of the query. cn.Open() da.Fill(tbl) 'Modify the contents of the DataTable. 'Create a new transaction. Dim txn As OleDbTransaction = cn.BeginTransaction() 'Set the Transaction property of the DataAdapter's Commands. da.UpdateCommand.Transaction = txn da.InsertCommand.Transaction = txn da.DeleteCommand.Transaction = txn 'Submit the changes. da.Update(tbl) 'Commit the changes and close the connection. txn.Commit() cn.Close()

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +          "FROM [Order Details] WHERE OrderID = 10503 " +          "ORDER BY ProductID"; DataTable tbl = new DataTable(); OleDbConnection cn = new OleDbConnection(strConn); OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn); //Define updating logic for the DataAdapter. //Open the connection and fetch the results of the query. cn.Open(); da.Fill(tbl); //Modify the contents of the DataTable. //Create a new transaction. OleDbTransaction txn = cn.BeginTransaction(); //Set the Transaction property of the DataAdapter's Commands. da.UpdateCommand.Transaction = txn; da.InsertCommand.Transaction = txn; da.DeleteCommand.Transaction = txn; //Submit the changes. da.Update(tbl); //Commit the changes and close the connection. txn.Commit(); cn.Close();

It's slightly more challenging to submit changes in a transaction if you're relying on the CommandBuilder object to generate your updating logic. The CommandBuilder does not actually generate the updating logic when you instantiate it. If you instantiate a CommandBuilder object and later call DataAdapter.Update, the CommandBuilder will not actually build the updating logic until you call the DataAdapter object's Update method. This behavior poses a slight problem if you want to use the CommandBuilder to submit changes in a transaction.

If you use code that looks like the following, ADO.NET will throw an exception when you try to submit the pending changes:

Visual Basic .NET

Dim strConn, strSQL As String  Dim tbl As New DataTable() Dim cn As New OleDbConnection(strConn) Dim da As New OleDbDataAdapter(strSQL, cn) Dim cb As New OleDbCommandBuilder(da) cn.Open() da.Fill(tbl) Dim txn As OleDbTransaction = cn.BeginTransaction() da.Update(tbl) txn.Commit() cn.Close()

Visual C# .NET

string strConn, strSQL;  DataTable tbl = new DataTable(); OleDbConnection cn = new OleDbConnection(strConn); OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); cn.Open(); da.Fill(tbl); OleDbTransaction txn = cn.BeginTransaction(); da.Update(tbl); txn.Commit(); cn.Close();

When you call DataAdapter.Update, the CommandBuilder will fetch the required metadata from the database using the DataAdapter object's SelectCommand. We have not associated the Command object in the SelectCommand property with the newly created transaction. As a result, the CommandBuilder cannot use the SelectCommand and the CommandBuilder throws an exception.

If we add the following line of code just before the call to the DataAdapter object's Update method, our code will succeed:

da.SelectCommand.Transaction = txn

However, this means that the CommandBuilder fetches schema information from your database within a transaction. Generally speaking, you want to touch as little data as possible in your database during a transaction. A more palatable option is to force the CommandBuilder to generate updating logic before starting the transaction. We can accomplish this by calling the CommandBuilder object's GetUpdateCommand (or GetInsertCommand or GetDeleteCommand) method.

We can then associate the Command objects that the CommandBuilder generated with our new Transaction object using the following code, and the DataAdapter will submit updates within the transaction:

Visual Basic .NET

Dim strConn, strSQL As String  Dim tbl As New DataTable() Dim cn As New OleDbConnection(strConn) Dim da As New OleDbDataAdapter(strSQL, cn) Dim cb As New OleDbCommandBuilder(da) cn.Open() cb.GetUpdateCommand() da.Fill(tbl) Dim txn As OleDbTransaction = cn.BeginTransaction() cb.GetUpdateCommand.Transaction = txn cb.GetInsertCommand.Transaction = txn cb.GetDeleteCommand.Transaction = txn da.Update(tbl) txn.Commit() cn.Close()

Visual C# .NET

string strConn, strSQL;  DataTable tbl = new DataTable(); OleDbConnection cn = new OleDbConnection(strConn); OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); cn.Open(); cb.GetUpdateCommand(); da.Fill(tbl); OleDbTransaction txn = cn.BeginTransaction(); cb.GetUpdateCommand().Transaction = txn; cb.GetInsertCommand().Transaction = txn; cb.GetDeleteCommand().Transaction = txn; da.Update(tbl); txn.Commit(); cn.Close();

Using the TableMappings Collection

In Chapter 5, you learned how the DataAdapter object's TableMappings collection affects how the DataAdapter populates a DataSet via the Fill method. In the following code, calling the Fill method of the DataAdapter creates a new DataTable whose TableName property is set to Table:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _          "FROM [Order Details] WHERE OrderID = 10503 " & _          "ORDER BY ProductID" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds)

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +          "FROM [Order Details] WHERE OrderID = 10503 " +          "ORDER BY ProductID"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds);

If we want our new DataTable to have a TableName of Order Details, we can change our code in one of two ways. The first option is to use the overloaded Fill method to supply the desired TableName:

Visual Basic .NET

  Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Order Details")

Visual C# .NET

  OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Order Details");

The other option is to add an entry to the DataAdapter object's TableMappings collection so that the DataAdapter knows that it's associated with the Order Details DataTable:

Visual Basic .NET

  Dim da As New OleDbDataAdapter(strSQL, strConn) da.TableMappings.Add("Table", "Order Details") Dim ds As New DataSet() da.Fill(ds)

Visual C# .NET

  OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); da.TableMappings.Add("Table", "Order Details"); DataSet ds = new DataSet(); da.Fill(ds);

The TableMappings collection has a similar effect when you submit updates. If you supply just a DataSet object in the DataAdapter object's Update method, the DataAdapter will rely on its TableMappings collection to determine which DataTable in the DataSet to examine:

Visual Basic .NET

  Dim da As New OleDbDataAdapter(strSQL, strConn) da.TableMappings.Add("Table", "Order Details") 'Define updating logic. Dim ds As New DataSet() da.Fill(ds) 'Modify a series of rows. da.Update(ds)

Visual C# .NET

  OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); //Define updating logic. da.TableMappings.Add("Table", "Order Details"); DataSet ds = new DataSet(); da.Fill(ds); //Modify a series of rows. da.Update(ds);

If you have not populated the DataAdapter object's TableMappings collection, you must either use the Update method that accepts a DataSet and a table name or use the Update method that accepts a DataTable object:

Visual Basic .NET

  Dim da As New OleDbDataAdapter(strSQL, strConn) 'Define updating logic. Dim ds As New DataSet() da.Fill(ds, "Order Details") 'Modify a series of rows. da.Update(ds, "Order Details") 'or  Dim da As New OleDbDataAdapter(strSQL, strConn) 'Define updating logic. Dim tbl As New DataTable() da.Fill(tbl) 'Modify a series of rows. da.Update(tbl)

Visual C# .NET

  OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); //Define updating logic. DataSet ds = new DataSet(); da.Fill(ds, "Order Details"); //Modify a series of rows. da.Update(ds, "Order Details"); //or  OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); //Define updating logic. DataTable tbl = new DataTable(); da.Fill(tbl); //Modify a series of rows. da.Update(tbl);

As a basic rule, you should use the same logic to control the DataTable you're referencing in both DataAdapter.Fill and DataAdapter.Update.

The Best Way to Update

ADO.NET gives you many options for submitting changes. You can generate updating logic at run time using CommandBuilder objects. You can supply your own updating logic in code, submitting changes via INSERT, UPDATE, or DELETE queries or via stored procedure calls. You can also use the Data Adapter Configuration Wizard to generate such code easily at design time. Which of these options is right for you?

The answer really depends on the parameters of your application. You could get the best performance by configuring your DataAdapter objects to submit updates via stored procedure calls. However, if your application must work with databases, such as Microsoft Access, that don't support stored procedures, that solution is not appropriate. You'd be better off using INSERT, UPDATE, and DELETE queries. You'll need to consider such factors when deciding what's appropriate for your application.

From a general standpoint, I strongly recommend submitting changes via stored procedures whenever possible. If the ability to work with multiple back ends is a greater priority, use query-based updates (INSERT, UPDATE, DELETE) instead. Regardless of which option you choose, generate your own updating logic. Use the Data Adapter Configuration Wizard or a similar code-generation tool to save development time, but avoid generating updating logic at run time whenever possible. If you remember only one thing from this chapter, remember this: Don't rely on CommandBuilder objects in your applications unless absolutely necessary.

There are a number of more advanced updating scenarios that we've yet to discuss. How do I fetch newly generated autoincrement values? How do I submit changes from a DataSet that contains new and deleted rows to multiple related tables? How do I detect and handle failed update attempts? How can I use ADO.NET with distributed transactions? We'll cover these and other more advanced updating scenarios in the next chapter.