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

About Peter Meinl

IT Consultant
This entry was posted in Computers and Internet and tagged , , . Bookmark the permalink.

2 Responses to The SQLCommandBuilder is way Better than its Reputation

  1. Pingback: DB Concurrency Control with .NET – Overview « DuongTienLam's Blog

  2. Pingback: DB Concurrency Control with .NET – Details « DuongTienLam's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s