Using ADO.NET Command Objects to Submit Updates

Using the CommandBuilder Object to Generate Updating Logic

The ADO.NET object model not only allows you to define your own updating logic, but it also provides dynamic updating logic generation similar to that of the ADO cursor engine, using the CommandBuilder object. If you instantiate a CommandBuilder object and associate it with a DataAdapter object, the CommandBuilder will attempt to generate updating logic based on the query contained in the DataAdapter object's SelectCommand.

To demonstrate how the CommandBuilder works, I'll use it to generate updating logic for our sample code that queries the Order Details table. The following code snippet instantiates an OleDbCommandBuilder, supplying an OleDbDataAdapter in the constructor. It then writes the text of the Command that the CommandBuilder generated to submit new rows.

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 cb As New OleDbCommandBuilder(da) Console.WriteLine(cb.GetInsertCommand.CommandText)

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); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); Console.WriteLine(cb.GetInsertCommand().CommandText);

You'll notice that the text of this query looks remarkably similar to the queries we built earlier in the chapter to submit new rows, as shown here:

INSERT INTO Order Details( OrderID , ProductID , Quantity , UnitPrice )  VALUES ( ? , ? , ? , ? )

How the CommandBuilder Generates Updating Logic

The logic that the CommandBuilder uses to generate UPDATE, INSERT, and DELETE queries isn't terribly complex. Like the ADO cursor engine, the CommandBuilder queries the database to determine base table and column names as well as key information for the results of your query. The CommandBuilder can generate updating logic if all of the following are true:

• Your query returns data from only one table.

• That table has a primary key.

• The primary key is included in the results of your query.

As we discussed earlier, the primary key ensures that the query-based updates that the CommandBuilder generates can update one row at most. Why does the CommandBuilder place a restriction on the number of tables referenced in the results of your query? We'll discuss this later in the chapter.

The CommandBuilder object uses the DataAdapter object's SelectCommand to fetch the metadata necessary for the updating logic. Actually, we discussed this feature briefly in Chapter 4. The Command object's ExecuteReader allows you to request this type of metadata with the results of your query. The following code snippet demonstrates this feature:

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 cmd As New OleDbCommand(strSQL, cn) cn.Open() Dim rdr As OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly Or _                          CommandBehavior.KeyInfo) Dim tbl As DataTable = rdr.GetSchemaTable rdr.Close() cn.Close() Dim row As DataRow Dim col As DataColumn For Each row In tbl.Rows     For Each col In tbl.Columns         Console.WriteLine(col.ColumnName & ": " & row(col).ToString)     Next col     Console.WriteLine() Next row

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); OleDbCommand cmd = new OleDbCommand(strSQL, cn); cn.Open(); OleDbDataReader rdr; rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly _                          CommandBehavior.KeyInfo); DataTable tbl = rdr.GetSchemaTable(); rdr.Close(); cn.Close(); foreach (DataRow row in tbl.Rows) {     foreach (DataColumn col in tbl.Columns)         Console.WriteLine(col.ColumnName + ": " +                            row[col].ToString());     Console.WriteLine(); }

If you run this code, you'll see all the data that the CommandBuilder needs for each column in order to generate updating logic. What's the name of the column? What are the base table and base column names for the column? Is the column part of the base table's primary key? Does the column contain a long data type (large text or binary)? What is the scale and precision of that floating point column? And so on.

Benefits and Drawbacks of Using the CommandBuilder

You can see the two major benefits of using the CommandBuilder object if you compare the code snippet that created the CommandBuilder with the code that we used to generate our own updating logic. Using the CommandBuilder object requires less code. It also allows you to generate updating logic without requiring in-depth knowledge of the SQL syntax for UPDATE, INSERT, and DELETE queries.

The CommandBuilder can also be helpful if you're having problems generating your own updating logic. If the CommandBuilder can generate the updating logic successfully, you can check the value of the CommandText property of the Command objects it generated or the various properties on the Parameter objects it constructed.

The CommandBuilder is also extremely handy in any application where you need to support updating but you won't know the structure of your queries at design time.

Like the ADO cursor engine, the CommandBuilder generates updating logic for you automatically at run time. As a result, the CommandBuilder is also subject to the same problems and limitations as the ADO cursor engine.

The CommandBuilder does not offer the best possible run-time performance. You can supply your own updating logic in code in less time than it takes the CommandBuilder to request and process the metadata required to generate similar updating logic. The CommandBuilder doesn't offer options to let you control the updating logic that they generate. You can't specify the type of optimistic concurrency you want to use. A CommandBuilder will not help you submit updates using stored procedures.

If only there were a way to generate updating logic quickly and easily at design time....