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
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.
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.
- 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.”