The SQLCommandBuilder is way Better than its Reputation

There has always been a lot of criticism about the DBCommandbuilder (ex: Weaning Developers from the CommandBuilder).

However, if you know its limitations, there are many scenarios where the DBCommandbuilder is quite useful, like:

• Managing lookup tables
• Single-table updates
• Prototyping

The SQLCommandBuilder works as follows:

• It registers itself as a listener for RowUpdating events when its DataAdapter
  property is set.
• It creates SQL INSERT, UPDATE and DELETE commands on-the-fly.
• It adds optimistic concurrency control checks to UPDATE commands.
• For UPDATE commands it creates SET expressions only for columns that were changed, thus reducing network traffic, and preventing triggers from firing unnecessarily.

Usage example:

Try
  'Read
  Dim daa As New SqlDataAdapter("SELECT * from Inventories WHERE ProductType='Tool'", ConfigurationManager.ConnectionStrings("ConcurrencyTest_SQLServer").ConnectionString)
  Dim inventories As New DataTable
  daa.MissingSchemaAction = MissingSchemaAction.AddWithKey
  daa.Fill(inventories)

  'update in memory
  inventories.Rows.Find("Hammer")!QuantityInStock += 200 'using Bang notation
  'add new in memory
  Dim helicopter = inventories.NewRow
  helicopter!ProductId = "Helicopter"
  helicopter!ProductType = "Vehicle"
  helicopter!QuantityInStock = 0
  inventories.Rows.Add(helicopter)

  'Update DB
  Dim cmb As New SqlCommandBuilder(daa)
  cmb.ConflictOption = ConflictOption.CompareRowVersion
  daa.Update(inventories)
Catch ex As DBConcurrencyException
 Throw
End Try

In many applications one can easily use the DBCommandBuilder for 80 to 90% of all DB updates.

Yes, the DBCommandBuilder needs a DB roundtrip to get the metadata for creating commands, but this overhead

• only happens the first time it needs to create a command
• is often neglectable
• can be minimized by scoping the DBCommandBuilder object widely
• and omits the boring effort of specifying data types and length’s redundantly in code.

Yes, the DBCommandBuilder only works for single-table updates, but when using DataSets it often makes sense to read data into single tables instead of joining, omitting the relational overhead of repeating the values of the one-side in each row returned.

The SQLCommandBuilder supports different ConflictOptions to detect optimistic update conflicts:

• OverwriteChanges (= no control)
• CompareAllSearchableValues (compares the values of all searchable columns)
• CompareRowVersion (compares the value the column with datatype RowVersion (~Timestamp)

I prefer using CompareRowVersion. See DB Concurrency Control with .NET-Details.

The ODP OracleCommanbuilder does not support ConflictOption. I submitted a feature request for the Oracle Data Provider for .NET. The MS OracleCommandBuilder is marked as deprecated.

For my next projects I am considering using OData Services (even running the OData service on the same machine as the requesting service) for lookup table management instead of a DBCommandBuilder based solution.

The Visual Studio TableAdpaterManager even supports hierarchical updates automatically understanding the relationships between tables: “The TableAdapterManager uses the foreign-key relationships that relate data tables to determine the correct order to send the Inserts, Updates, and Deletes from a dataset to the database without violating the foreign-key constraints (referential integrity) in the database.”

About these ads