Weaning Developers from the CommandBuilder

  • Article

 

William Vaughn
Beta V Corporation

March 2003

Applies to:
   Microsoft® ADO.NET

Summary: Examines the drawbacks of the ADO.NET CommandBuilder, and shows how to use the Visual Studio .NET DataAdapter Configuration Wizard (DACW) to write your own Command queries. (11 printed pages)

Download the source code for this article.

Contents

What the CommandBuilder Is Supposed to Do
How Does COM-Based ADO Classic Manage Updates?
How the ADO.NET CommandBuilder Works
What Are the Alternatives?
Creating Action Query Commands
Examining the Generated Code
Tuning the UpdateCommand
Tuning the DeleteCommand
Conclusion

This week I participated in a Microsoft-sponsored chat session (http://communities2.microsoft.com/home/chatsevents.aspx). I get a kick out of chatting with the Microsoft® .NET Framework developer team and the other participants brave enough to query a bunch of their peers. This session included a question from yet another developer challenged by the CommandBuilder. The public newsgroups usually have at least one thread a day on this subject, so I guess it's time someone tried to address the issues they raise.

What the CommandBuilder Is Supposed to Do

Let's lay a little foundation first, for those getting started with Microsoft® ADO.NET. ADO.NET uses new technology to expose updatable rowsets. Well, it's not all that new if you're familiar with the Microsoft® Visual Basic 6.0 Data Object Wizard. (Homer Simpson would pronounce it, "D'Oh!") Like the Data Object Wizard, the ADO.NET DataAdapter is used to manage four separate Command objects. The first is used to retrieve the rowset and the other three action commands are used to add a new row, change an existing row, or delete a specific row.

  • SelectCommand. Specifies the SQL code and parameters needed to fetch the rowset(s) based on one or more SELECT queries.
  • InsertCommand. Specifies the SQL code and parameters needed to insert a new row in the database.
  • DeleteCommand. Specifies the SQL code and parameters needed to delete a specific row from a database table.
  • UpdateCommand. Specifies the SQL code and parameters needed to change a specific row in a database table.

As you can see, ADO.NET developers are responsible to author all of the SQL code needed to fetch and change the data in the target database table. This is very different than the approach that ADO classic took as it created the action queries on the fly at run time. Yes, you can run the Microsoft® Visual Studio® .NET DataAdapter Configuration Wizard (DACW) to build some or all of these Command objects interactively. And that's another big difference. When you compare .NET development and COM Visual Studio 6.0 development, there are few, very few "black boxes" responsible for client-side processing because most everything is in source. This means that if you don't like what the wizard generates at design time, you can change it (at your own peril).

What the CommandBuilder can do is relieve you of the responsibility of writing your own action queries by automatically constructing the SQL code, ADO.NET Command objects, and their associated Parameters collections given a SelectCommand. Cool. And it works, but only in a very limited way, and for a price, as I'll describe in this article. And unlike the DACW, the code and commands created by the CommandBuilder are unseen—in another black box.

How Does COM-Based ADO Classic Manage Updates?

To better understand what ADO.NET does and does not do in comparison to ADO classic, let's take a closer look at what ADO classic does for you. COM-based ADO classic (ADOc) does not have a CommandBuilder, or at least not on the surface. What it does have is a black box routine that generates the action queries on the fly. ADOc expects the initial SELECT query to include extra metadata that describes the schema for each resultset returned. This slows down query generation server-side and makes updatable Recordset objects fatter than they have to be. Since ADOc is an OSFA (one size fits all; a generic interface designed with no specific backend in mind) OLE DB interface, some providers did not handle the ability to expose sufficient, consistent, or correct metadata. This meant it was tougher to take an application and get it to work with more than one backend.

What ADOc has that the ADO.NET CommandBuilder does not is the Update Criteria property (see Microsoft Knowledge Base article 190727). In my opinion, the lack of this feature cripples the capability of the CommandBuilder. Update Criteria permits developers to tune the way ADOc constructs the action Command SQL to test for concurrency collisions. That is, based on how the Update Criteria property is set, the SQL action query succeeds or fails to complete the action if changes have been made to the server-side row being updated. For example, the Update Criteria settings let you choose how the runtime-generated SQL is generated:

  • AdCriteriaKey. Use only the primary key. This means if the row exists, change or delete it.
  • adCriteriaAllCols. Construct the SQL code comparing all columns in the Recordset with their server-side counterparts. This is the approach the CommandBuilder takes.
  • adCriteriaUpdCols. (Default) Construct the SQL code using only the columns in the recordset that have been modified (touched).
  • adCriteriaTimeStamp. Construct the SQL code using the timestamp column (if available).

As you can see, ADOc and the Update Criteria property let you decide whether or not to include columns that were never (or should not be) touched. This is important because there are lots of situations where you fetch read-only columns in an otherwise updatable rowset. For example, your user might have permission to read the CurrentSalary column, but not to change it. Without some way to prevent ADO from creating a SET clause in the UPDATE statement, your application is going to have to use another approach. The most powerful of all concurrency validation techniques uses a server-generated TimeStamp column to detect post-fetch changes. By simply testing the TimeStamp, the server can instantly tell if the row has been modified since you last read it. The CommandBuilder is incapable of managing concurrency with the TimeStamp column.

Okay, so as not to belabor the point, the ADO.NET CommandBuilder does not support the Update Criteria property. It simply uses a brute-force method (similar to adCriteriaAllCols) to test for changes in server-side rows. That's a problem as ADOc developers used to having ADO handle this problem more intelligently will be disappointed.

Given that the ADOc approach required quite a bit of help from the fetched metadata, and that it didn't work particularly well with some providers, Microsoft found it easier to take a bare-bones approach with ADO.NET. This is understandable because many developers didn't expect ADO to construct the action queries anyway—they had already moved their action query logic to stored procedures. Having ADO.NET do more than absolutely necessary was also contrary to its charter: to be light, fast, and easy. It was also important to simplify the construction of .NET data providers, so it would not take an OLE DB brain surgeon to build them. This strategy plopped the responsibility for the action query SQL back in the lap of the ADO.NET developer.

How the ADO.NET CommandBuilder Works

The CommandBuilder expects you to provide a viable, executable, and simple SelectCommand associated with a DataAdapter. It also expects a viable Connection. That's because the CommandBuilder opens the Connection associated with the DataAdapter and makes a round trip to the server each and every time it's asked to construct the action queries. It closes the Connection when it's done. The code to build a CommandBuilder in your application is shown below:

Listing 1. Coding the CommandBuilder

Dim cn As SqlConnection
Dim da As SqlDataAdapter
Dim cb As SqlCommandBuilder
cn = New SqlConnection("data source=demoserver…")
da = New SqlDataAdapter("SELECT Au_ID, au_lname, City FROM authors", cn)
cb = New SqlCommandBuilder(da)        ' Build new CommandBuilder

What you can't see from this code is what's going on behind the scenes. However, even if you turn on the Microsoft® SQL Server™ Profiler (a really good idea when working with new code), you still can't tell that construction of the CommandBuilder did anything, at least not at first. That's because ADO.NET does not construct the action commands until they are touched or needed by the application. For example, if I add the following code to the example in Listing 1 (which extracts the SQL for the UpdateCommand):

MsgBox(cb.GetUpdateCommand.CommandText.ToString)

ADO.NET executes:

exec sp_executesql N' SET FMTONLY OFF; SET NO_BROWSETABLE ON;_
   SET FMTONLY ON;SELECT au_ID, au_lname, city FROM authors'

This round trip returns a resultset that includes the metadata for the SELECT statement. This is used to construct the three action queries (InsertCommand, UpdateCommand, and DeleteCommand) associated with the DataAdapter. That is, unless the SelectCommand is something more than a simple SELECT. That's right, the CommandBuilder cannot generate the action commands if the SelectCommand is too complex or contains a JOIN. There are several other limiting factors as well:

  • Your SelectCommand must retrieve at least one PrimaryKey or unique column as part of the query. Just having a TimeStamp column without a PrimaryKey of some kind won't work.
  • The SelectCommand cannot refer to SQL queries, stored procedures, or views that contain JOIN operators. This means your SELECT statement must refer to a single table.
  • The SelectCommand must refer to columns that permit read-write operations. You can't include columns or expressions that can't be written to for some reason.
  • The CommandBuilder makes no attempt nor does it provide any mechanism to fetch output arguments from the action query (including the Identity value).
  • If the CommandText, Connection, CommandTimeout or Transaction properties of the SelectCommand change, you have to notify the CommandBuilder by executing the RefreshSchema method, which takes another round trip.
  • The CommandBuilder-generated UpdateCommand or DeleteCommand will not change any row that's been modified since last read. This means you'll have to write your own action query to "force-through" an UPDATE or DELETE operation.
  • The CommandBuilder is designed to work with single, unrelated tables. That is you can't expect to update tables with Primary Key/Foreign Key relationships.
  • If your SelectCommand SQL columns contain special characters, such spaces, periods, quotes or non-alphanumeric characters, you can't use the CommandBuilder.

When the .NET Framework Version 1.1 shipped (along with Visual Studio .NET 2003) I discovered that CommandBuilder support was added to both the OracleClient and Odbc .NET Framework data providers. No, the CommandBuilder is not universally supported by all .NET Framework data providers, so don't be surprised when your provider balks when you try to use it.

What Are the Alternatives?

Okay, so it's tough to use the CommandBuilder when your SELECT queries get a bit more complex. What can you use instead? Well, there are lots of alternatives. What I've been doing lately is getting the DACW to help. To start with, I usually use stored procedures to perform both the SELECT and action queries. To setup a Command object to execute a stored procedure, you set the CommandText to the name of the stored procedure and the CommandType to CommandType.StoredProcedure—pretty much like ADOc.

Now the Parameters collection (which describes the input, RETURN value, and OUTPUT parameters) is another story. This is where the DACW can help. I walk through the wizard and get it to generate the entire Parameters collection. Nope, I don't use it as is. You can, but I often find it's necessary to tune it up a bit for the action queries. Listing 2 shows how to setup the initial SelectCommand.

Listing 2. Setting up a SelectCommand with input, OUTPUT, and RETURN value parameters

cn = New SqlConnection("server=betav10;database=biblio; " )
& " integrated security=sspi")
cmd = New SqlCommand("PriceByTitle", cn)
cmd.CommandType = CommandType.StoredProcedure
With cmd.Parameters
   .Add("@TitleWanted", SqlDbType.VarChar, 20)
   .Add("@TitlesFound", SqlDbType.Int).Direction =  _ 
ParameterDirection.Output
   .Add("@MaxPriceFound", SqlDbType.Decimal).Direction = _
  ParameterDirection.Output
   .Add("@MinPriceFound", SqlDbType.Decimal).Direction = _
ParameterDirection.Output
   .Add("@AvgPriceFound", SqlDbType.Decimal).Direction = _
ParameterDirection.Output
   .Add("@RETURN", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
End With

Creating Action Query Commands

Sure, the SelectCommand is pretty easy. It's the action queries that cause much of your grief. We could spend all day discussing the mechanics of setting up action queries to modify the litany of situations you might encounter in an average application, but I'm only going to have the space to show you how to approach building your own action Commands based on DACW-generated SQL. I'm also going to focus on a couple of typical problems—concurrency and identity management. That is, how does your application tell if a row has changed since it was last read and let ADO.NET know. And how do you know what the latest server-generated Identity value is?

The following example walks you through setting up TimeStamp concurrency, assuming that's what you're going to want to do (you should). It assumes you're running SQL Server (any version). For other DBMS backends like Oracle or DB2, you'll have to make some minor modifications to the code to accommodate your DBMS. Nope, even though we'll be using the DACW to initially build the SQL and Command objects, we won't be executing the code without a few modifications.

First, you're going to have to create the DemoTSConcurrency table and the GetDemoTSRows stored procedure so Visual Studio .NET and the DACW can see them. The compiled version of the sample application includes a menu option to do this for you. Be sure to right-click the Tables and Stored Procedures icons in the Server Explorer and click Refresh so that the IDE sees the newly created table and stored procedure. The GetDemoTSRows stored procedure looks like this:

Listing 3. GetDemoTSRows

CREATE PROCEDURE dbo.GetDemoTSRows
(@StateWanted Char(2) = 'WA')  
AS SELECT PID, Name, State, TimeStamp 
FROM DemoTSConcurrency    
WHERE State = @StateWanted  
RETURN

Once the table and stored procedure are created, start the DACW. Click Toolbox, click the Data tab, and click SqlDataAdapter. This launches the DACW and prompts you for the connection to use. Choose or build a connection to your DBMS (where the demo table and stored procedure were created).

Next, tell the DACW that you wish to specify SQL statements for your code. We'll use this approach because for this example we'll only be using a stored procedure for the SelectCommand. Sure, you can use stored procedures for all of the commands, but that's fodder for another article. We'll modify the SelectCommand in a minute to point to our stored procedure.

Click Query Builder. This tool can help you create SQL using drag-and-drop. Choose the DemoTSConcurrency table from the list and click Add then Close. The selected table now appears in the top pane of the Query Builder window.

Choose all of the columns in the DemoTSConcurrency table. No, don't simply click on the *—it makes your code harder to support. Once you select all of the columns, the SQL to be generated appears in the query window. Mimic the stored procedure input parameter by setting the SELECT criteria to filter on State by entering "= @StateWanted" in the Criteria column in the second Query Builder Pane. This ensures that the generated SQL will include the input parameter you'll pass to the stored procedure. When you're done, you should have filled out the Query Builder so it looks something like Figure 1. Click Okay to return to the DACW. Click Next, and Finish—you're done!

Figure1. DACW Query Builder filled out with SelectCommand query

Examining the Generated Code

Once the DACW has done its magic, your code might not look any different—until you drill into the "Windows Form Designer generated code" region. Click on the + sign next to this region marker and examine the code the DACW inserted into your project. We're going to strip out this code in a minute, so don't get nervous.

The first thing we need to do is build our own SqlDataAdapter from the generated code. Page down in the "Windows Form Designer generated code" region until you find SqlSelectCommand1 as shown in listing 4.

Listing 4. DACW-generated SelectCommand

'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT PID, Name, State, _
  TimeStamp FROM DemoTSConcurrency WHERE (State = " & _
  @StateWanted)"
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New _
 System.Data.SqlClient.SqlParameter("@StateWanted", _
 System.Data.SqlDbType.VarChar, 2, "State"))
        '

The lines are broken up a bit strangely, but we'll fix that. What we're mostly interested in is the DACW-generated Parameters collection. But before we can use it, we'll need to create a subroutine to generate our own DataAdapter (as shown in listing 5).

Listing 5. GenerateDataAdapter subroutine

Private Sub GenerateDataAdapter()


End Sub

Cut the SqlSelectCommand1 code from the DACW-generated code (shown in listing 4) into this GenerateDataAdapter subroutine. At this point we can clean up the code and make a few changes. First, we need to instantiate the SqlDataAdapter. The example uses "da" as the object variable. The first line of Listing 6 instantiates the DataAdapter and uses the New constructor to create the SelectCommand, set its CommandText (to point to our stored procedure), and point to the working connection. All that remains is to populate the Parameters collection. This is where the DACW code comes in handy.

In this case we simply make a minor adjustment to the location of the Parameters collection and use DACW-generated code. It includes not only the Parameter name (@StateWanted), but the DataType and length and the source column. But wait, the State column is defined as CHAR in the database, not VarChar. Apparently, the DACW didn't get this right so you'll need to fix it. Listing 6 shows the end result of our edits.

Listing 6. Tuned code leveraged from DACW-generated code

 Private Sub GenerateDataAdapter()
        '
        'SqlSelectCommand1
        '
 da = New SqlDataAdapter("GetDemoTSRows", cn)
 da.SelectCommand.CommandType = CommandType.StoredProcedure
 With da.SelectCommand.Parameters
  .Add(New System.Data.SqlClient.SqlParameter("@StateWanted", _ 
    System.Data.SqlDbType.Char, 2, "State"))
 End With
End Sub

One down and three to go. To rebuild the InsertCommand, DeleteCommand and UpdateCommand code blocks, we'll need to repeat the process—but using a slightly different technique. For purposes of this example, we're using the DACW-generated SQL, but not all of the Parameters because we're using TimeStamp concurrency checking. Taking the three blocks of code used to create the action commands, we end up with the code shown in listing 7. Let's step through each of these one at a time as each command has unique issues to address.

First, the InsertCommand does not need to include the TimeStamp column in its CommandText. The DACW should have known better because the server creates the TimeStamp value—just like an Identity column. This means that the DACW-generated code would probably not work anyway. We don't have to worry testing the previous values when doing concurrency checks for the INSERT as we're using an Identity column to make sure this new row is unique. However, we do need to retrieve the new Identity value after the row is inserted. The DACW adds another SELECT for this purpose.

Listing 7. The DataAdapter InsertCommand as tuned

' InsertCommand
' Instantiate new DA InsertCommand
da.InsertCommand = New SqlCommand(Nothing, cn)   
With da.InsertCommand
  .CommandText = "INSERT INTO DemoTSConcurrency(Name, State) " _
  & "VALUES (@Name, @State)" _
  & " SELECT PID, Name, State, TimeStamp FROM DemoTSConcurrency " _
  & " WHERE (PID = SCOPE_IDENTITY())"
' Setup Parameters collection. All are input parameters
  .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", _
     System.Data.SqlDbType.VarChar, 50, "Name"))
  .Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", _
     System.Data.SqlDbType.Char, 2, "State"))     ' Changed from VarChar
End With

Tuning the UpdateCommand

The UpdateCommand poses a few new challenges. In this case we test concurrency by comparing the Original TimeStamp (managed by ADO.NET using the DataRowVersion.Original) with the server-side TimeStamp value. If it's different, we know the row has changed since we last fetched it. As with the InsertCommand, the UpdateCommand includes a second SELECT that returns the current row contents. Frankly, this is pretty silly, as it must match what we just wrote there. In (pretty typical) situations where you don't write all of the columns, this would be useful, but here it's just a waste of time. I was able to strip out several other parameters from the DACW-generated code to get the result shown in Listing 8.

Listing 8. The DataAdapter UpdateCommand as tuned

  '
  'SqlUpdateCommand1
   '
' Instantiate new DA UpdateCommand
da.UpdateCommand = New SqlCommand(Nothing, cn) 
  With da.UpdateCommand
    .CommandText = "UPDATE DemoTSConcurrency SET Name = " _
& "@Name, State = @State " _
     & " WHERE (PID = @PID) AND (TimeStamp = @Original_TimeStamp); " _
     .Parameters.Add(New System.Data.SqlClient.SqlParameter("@PID", _
        System.Data.SqlDbType.Int, 4, "PID"))
     .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", _
        System.Data.SqlDbType.VarChar, 50, "Name"))
     .Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", _
        System.Data.SqlDbType.Char, 2, "State"))
     .Parameters.Add(New System.Data.SqlClient.SqlParameter( _
       "@Original_TimeStamp", _
        System.Data.SqlDbType.VarBinary, 8, _
         System.Data.ParameterDirection.Input, _
        False, CType(0, Byte), CType(0, Byte), "TimeStamp", _
        System.Data.DataRowVersion.Original, Nothing))
   End With

Tuning the DeleteCommand

The DeleteCommand is really pretty simple. In this case we simply pass in the original PID (unique row identifier) and the original TimeStamp to test for existence of the row.

Listing 9. The DataAdapter DeleteCommand as tuned

        'SqlDeleteCommand1
        '
da.DeleteCommand = New SqlCommand(Nothing, cn) ' Instantiate new DA DeleteCommand
With da.DeleteCommand
  .CommandText = "DELETE FROM DemoTSConcurrency " _
    & " WHERE (PID = @Original_PID) AND (TimeStamp = @Original_TimeStamp)"
   .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PID", _
   System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
   CType(0, Byte), CType(0, Byte), "PID",  _
   System.Data.DataRowVersion.Original, Nothing))
.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
   "@Original_TimeStamp", _
   System.Data.SqlDbType.VarBinary, 8, _
 System.Data.ParameterDirection.Input, False, _
   CType(0, Byte), CType(0, Byte), "TimeStamp",  _
   System.Data.DataRowVersion.Original, Nothing))
End With

Conclusion

The example included with this article implements these DataAdapter commands. I hope this tutorial makes it easier for you to wean yourself from the addictive DACW and CommandBuilder. There are also some twelve-step programs you can try along with some third-party query-generators that can help get you and your team over the learning curve.

About the Author

William (Bill) Vaughn is president of Beta V Corporation based in Redmond, Washington. He provides training, mentoring, and consulting services to clients around the globe, specializing in Visual Basic and SQL Server data access architecture and best practices. William's latest books are ADO.NET and ADO Examples and Best Practices for Visual Basic Programmers—2nd Edition and the C# version ADO.NET Examples and Best Practices for C# Programmers. Both are available from Apress. William is also the author of many articles and training courses and is a top-rated speaker at computer conferences worldwide. You can reach him at billva@nwlink.com.