Using the Data Adapter Configuration Wizard to Generate Updating Logic

Questions That Should Be Asked More Frequently

1. Q. So the DataAdapter can fill a DataSet with the results of a query and submit changes stored in a DataSet to my database. Do I have to use the same object to accomplish both tasks? I'm working with multi-tiered applications, and it sounds like I need to keep my DataAdapter objects alive in the middle tier between calls from the client application. Is that the case?

2. A. You can use the same DataAdapter to fill your DataSet and submit changes to your database, but that's not a requirement.

   Say your middle-tier object has two simple methods—one to return a new DataSet and one to submit the pending changes in your DataSet to your database. You could use separate DataAdapter objects for each of the methods. When you're simply filling a DataSet, the DataAdapter does not need updating logic. Conversely, if you're using a DataAdapter only to submit updates, the DataAdapter does not require you to define a SelectCommand.

   The DataAdapter really needs Command objects defined for only the Command objects it will need to execute. For example, if you know that your DataAdapter will submit only new rows (rather than modify or delete existing rows), you will need only an InsertCommand. Because the DataAdapter will not execute the Command objects stored in its SelectCommand, UpdateCommand, and DeleteCommand properties, you won't need to set those properties.

   The one caveat to this rule is if you use a CommandBuilder object to define the updating logic in your DataAdapter. The CommandBuilder cannot generate your updating logic if you do not have a SelectCommand defined for the DataAdapter.

3. Q. I want to fill a DataTable based on the results of a join query, modify the data in the DataTable, and then submit the changes back to the database using a DataAdapter. Neither the Data Adapter Configuration Wizard nor the CommandBuilder object will build this logic for me. What should I do?

4. A. My first recommendation is to reread the section on join queries in Chapter 7.

   The reason that neither component generates updating logic is that it's not clear what modifying the data returned by a join query truly means. Let's say that we take the query we've been using to retrieve order detail information and change it slightly so that the query also returns the name of the product in the line item:

   SELECT D.OrderID, P.ProductName, D.ProductID,         D.Quantity, D.UnitPrice      FROM [Order Details] D, Products P     WHERE D.OrderID = 10503 AND D.ProductID = P.ProductID     ORDER BY P.ProductID

   When we fetch the results of this query into a DataTable and modify a row, how do we want to change the contents of the database? The answer is clear to us. We want to modify the corresponding row in the Order Details table in the database. But that's not clear to the Data Adapter Configuration Wizard or the CommandBuilder object.

   The ADO cursor engine generates updating logic for you automatically, even with join queries, but this logic has frustrated most developers. If you generate an ADO Recordset using this query and modify only columns that correspond to the Order Details table, the ADO cursor engine will try to modify only the corresponding row in the Order Details table.

   But if you want to change the product that the line item references and you change both the ProductID column (Order Details table) and the ProductName column (Products table) so that the row of data looks correct on your screen, the ADO cursor engine will try to modify the ProductID column in the Order Details table and the ProductName in the Products table. Chances are that isn't what you want the ADO cursor engine to do.

   Thankfully, ADO.NET is not a black box technology like ADO. You can supply your own updating logic. In this scenario, you want to submit changes only to the Order Details table, so you can define your updating logic to ignore changes stored in the ProductName column.

   How can you generate that updating logic? Neither the CommandBuilder nor the Data Adapter Configuration Wizard will be of much help with the actual join query. But you can temporarily leave off the ProductName column, use either tool to generate the desired updating logic, and then re-add the ProductName column to your query. Devious, yet effective.

   Let's get back to my first recommendation. The section inChapter 7 titled "Using DataRelation Objects in Expression-Based DataColumn Objects" includes a code snippet that shows how you can use multiple DataTable objects and a DataRelation to simulate the results of a join query. The other major benefit to this approach is that you've greatly simplified your updating logic. The data in each DataTable corresponds to a single table in your database. The CommandBuilder and Data Adapter Configuration Wizard can generate the appropriate updating logic for you.

5. Q. You talked about optimistic concurrency only. How do I handle pessimistic concurrency in ADO.NET?

6. A. With pessimistic concurrency, you lock the row before you start making your changes. Because the contents of the DataSet are disconnected from the database, there is no simple way to lock the data in the database before you modify a row in a DataSet. However, you can achieve similar functionality using transactions.

   Let's say you want to lock data in your database before the user modifies data on the screen to ensure that the user's changes will succeed. You could open a transaction and lock those rows in the database so that other users cannot modify those rows by issuing the following query within the transaction:

   SELECT * FROM [Order Details] HOLDLOCK WHERE OrderID = 10503

   	This query was written specifically for SQL Server 2000. Not all databases support this query syntax. If you're working with another database, see your database's documentation for information on how to lock data in a query.

   This approach has some major drawbacks. What if your user forgets to click the Submit Changes button in your application and strolls off to the kitchen to grab a donut and some caffeine? Those rows in the database are locked. The more data you lock and the longer you maintain those locks, the less scalable your application becomes.

   It's time for me to confess. I've made some mistakes in my life. I used a similar approach in an application years ago, but not because I was young or needed the money. It was because the users requested this "feature." They didn't want to run into a situation where the changes they made on the screen could not be committed to the database and they would have to reenter those changes later.

   One employee, whom I'll call Steve (half the people in the company were named Steve) would repeatedly forget to commit his changes. When other users could not modify data in the database, they'd find me and then I'd have to find Steve, which sometimes took a while. Trying to explain that this was the functionality they asked for didn't make anyone any happier.

   Hey, I was in college. That's when kids are supposed to experiment with things like pessimistic locking. I learned my lesson, and no one got hurt. Not even Steve.

1. Q. What if I want to submit updates when my DataSet contains BLOB columns?

2. A. The short answer is that you should take the initial query and break it out into separate queries—one that retrieves your non-BLOB columns and one that retrieves just the primary key columns and your BLOB column.

   Because the structure of the queries that the DataAdapter uses to submit changes is static, the values from all columns are used in the SET clause of the UpdateCommand object's CommandText, even if only one column contains changed data. This is a small nuisance for most queries (and a necessary evil when you submit changes using a stored procedure), but it can be a major pain if you're working with BLOB columns. Why?

   Let's say we're working with employee information and the database contains an Employees table with columns for the employee's name, identification number, title, and photo. The photo column holds large amounts of binary information—the contents of a JPEG file.

   If you have a DataTable that contains all of these columns and you modify just the title column for a row, the DataAdapter will include the current values for all columns in the query to update the row in the database. This means that even though you've only modified a small string-based column, you'll still pass the binary contents of the employee photo across the wire to your database.

   Another approach is to split the data into separate tables, as shown in Figure 10-11. The figure shows two DataTable objects linked by a DataRelation. The parent DataTable includes the main columns for the Employees table—EmployeeID, LastName, and FirstName. The child DataTable contains the BLOB column Photo as well as the EmployeeID column in order to maintain the relationship to the parent DataTable.

   Figure 10-11

   Splitting a DataTable based on a BLOB column.

   If you use a DataSet with this architecture with separate DataAdapter objects for each DataTable, a change to the Title column will result in a query-based update that does not include the Photo column. The only time the contents of the Photo column will be sent back to the database in this architecture is when the contents of the Photo column change.

   Of course, this entire discussion would be moot if you were to store the binary data in a file and use the database to maintain the location of the file.

3. Q. I tried using the code earlier in the chapter that showed how to submit changes to the Order Details table using the CommandBuilder, but my code failed with an error about "Incorrect syntax near the keyword 'Order.'" Did I do something wrong?

4. A. I hate to answer a question with a question, but why did you want to put a space in your table name? I've yet to hear a developer say, "Boy, I sure am glad we put spaces in our table and column names. That made my life so much easier." But I digress.

   As of this writing, the CommandBuilder object does not query the database to determine the characters that the database uses to delimit table and column names that are reserved words or that contain spaces and other bad characters. If you use the CommandBuilder to generate updating logic for a query that includes such table or column names, your update attempts will fail unless you specify values for the CommandBuilder object's QuotePrefix and QuoteSuffix properties.

   What if you're not comfortable supplying values for those properties because you're writing code that must run successfully against various back ends? If you're working with the OLE DB .NET Data Provider, you might be able to use the OleDbConnection object's GetOleDbSchemaTable method to fetch the proper delimiters from the database. I've tested the following code, and it's worked successfully using the Microsoft OLE DB providers that communicate with SQL Server, Oracle, and Access:

   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 cn As New OleDbConnection(strConn) Dim da As New OleDbDataAdapter(strSQL, cn) cn.Open() Dim cb As New OleDbCommandBuilder(da) Dim tblSchema As DataTable tblSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.DbInfoLiterals, _                                    New Object() {}) cn.Close() tblSchema.PrimaryKey = New DataColumn() _                        {tblSchema.Columns("LiteralName")} Dim row As DataRow row = tblSchema.Rows.Find("Quote_Prefix") If Not row Is Nothing Then     cb.QuotePrefix = row("LiteralValue") End If row = tblSchema.Rows.Find("Quote_Suffix") If Not row Is Nothing Then     cb.QuoteSuffix = row("LiteralValue") End If

   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"; OleDbConnection cn = new OleDbConnection(strConn); OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn); cn.Open(); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable tblSchema; tblSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.DbInfoLiterals,                                     new object[] {}); cn.Close(); tblSchema.PrimaryKey = new DataColumn[]                         {tblSchema.Columns["LiteralName"]}; DataRow row; row = tblSchema.Rows.Find("Quote_Prefix"); if (row != null)      cb.QuotePrefix = row["LiteralValue"]; row = tblSchema.Rows.Find("Quote_Suffix"); if (row != null)      cb.QuoteSuffix = row["LiteralValue"];

   Of course, you can avoid such problems by making sure your table and column names do not require delimiters.

5. Q. When I submit new rows using an ADO.NET DataAdapter, I see null values in the new row in my database rather than the default values I defined for those columns in my database. When I submitted changes via ADO, I got default values. What gives?

6. A. SQL Server, and other databases, lets you define default values for columns in your database. As we saw in Chapter 6, the DefaultValue property of the ADO.NET DataColumn object is not an exact match for this functionality. So ADO.NET will not generate your database's default values for you automatically. There's also another factor involved.

   Databases generate default values for columns in your new rows if you use an INSERT query that omits the column or specifies the keyword DEFAULT rather than a value for the column. ADO.NET will not omit the column from its updating logic, nor will it use the keyword DEFAULT.

   ADO.NET's predecessor, ADO, generates dynamic updates on a per-row basis. When submitting updates, it omitted unmodified columns from the INSERT statements that it generated. Thus, the new rows in your database that you create with ADO might automatically contain the default values, whereas the ones you create with ADO.NET will not.

   The simplest solution in ADO.NET is to add code to your application so that when you create a new row, you automatically supply the desired default values for your columns.