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:

  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

  '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

  'Update DB
  Dim cmb As New SqlCommandBuilder(daa)
  cmb.ConflictOption = ConflictOption.CompareRowVersion
Catch ex As DBConcurrencyException
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.”

Über Peter Meinl

Perpetual Traveller, IT Consultant
Dieser Beitrag wurde unter Computers and Internet abgelegt und mit , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

2 Antworten zu 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

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s