SqlCommandBuilder and DbCommandBuilder - Generating Update Insert Delete Queries From Select Query
by David Hayden ( Florida .NET Developer )
SqlCommandBuilder is a useful implementation of DbCommandBuilder that can help you generate update, insert and delete queries for a table given a select query for that same table. DbCommandBuilder is used by Visual Studio to implement a number of rapid application development features via much of the drag and drop functionality and wizards involving TableAdapters, etc. However, SqlCommandBuilder is a standalone class and can be used in your applications to create queries with almost no effort.
SqlCommandBuilder
SqlCommandBuilder is the SQL Server specific version of DbCommandBuilder for interfacing with SQL Server. I am going to work with it just because almost everything I do is in SQL Server.
Let's jump right into an example of using SqlCommandBuilder to show how easy it is to use and how much functionality you get for almost no work. Shown below is all the code you need to generate update, insert, and delete commands ( queries ) using SqlCommandBuilder for the Categories Table in the Northwind Database.
string connectionString = "...Northwind...";
string selectSql = "Select * FROM Categories";
SqlDataAdapter adapter =
new SqlDataAdapter(selectSql, connection);
SqlCommandBuilder builder = new SqlCommandBuilder();
builder.DataAdapter = adapter;
builder.ConflictOption = ConflictOption.OverwriteChanges;
string updateCommandSql =
builder.GetUpdateCommand(true).CommandText;
string insertCommandSql =
builder.GetInsertCommand(true).CommandText;
string deleteCommandSql =
builder.GetDeleteCommand(true).CommandText;
At the bare minimum, SqlCommandBuilder needs an SqlDataAdapter with a proper Select Query for the table you want to generate additional queries. You can either set the SqlCommandBuilder.DataAdapter Property or pass the SqlDataAdapter ( in this case ) via the SqlCommandBuilder's constructor.
By default, SqlCommandBuilder uses ConflictOption.CompareAllSearchableValues for its level of conflict resolution during updates in optimistic concurrency. Since my contrived application happens to be a single user application, I changed the setting to ConflictOption.OverwriteChanges which means the last update wins ( See Optimistic Concurrency using ADO.NET and SQL Server ).
Last, I called the various methods to get the update, insert, and delete commands. I passed each method the value of true so the methods would use the column names as the paramete values to make things a bit easier to read. The result is as follows:
UPDATE [Categories]
SET
[CategoryName] = @CategoryName,
[Description] = @Description,
[Picture] = @Picture
WHERE
(([CategoryID] = @Original_CategoryID))
INSERT INTO [Categories]
([CategoryName], [Description], [Picture])
VALUES
(@CategoryName, @Description, @Picture)
DELETE FROM
[Categories]
WHERE
(([CategoryID] = @Original_CategoryID))
Keep in mind that the above is just showing the SQL Text, the SqlCommandBuilder actually builds commands, SqlCommand's to be exact, with the Connection and Parameters properties properly set. You just need to add the values to the parameters.
SqlCommandBuilder Under the Hood
You might be wondering how SqlCommandBuilder pulls all this off. One might think it is doing string manipulation, but SqlCommandBuilder actually reads schema information about the table in question to help construct the proper insert, update, and delete commands for the table.
When you ask for one of the update, delete, or insert commands for the first time, SqlCommandBuilder goes out and issues your select statement, but only asking for the schema and key information - no data.
DbCommandBuilder has a GetSchemaTable that is overriden a bit by the SqlCommandBuilder version, but below is essentially the request:
protected virtual DataTable
GetSchemaTable(DbCommand sourceCommand)
{
DataTable table1;
using (IDataReader reader1 =
sourceCommand.ExecuteReader(CommandBehavior.KeyInfo
| CommandBehavior.SchemaOnly))
{
table1 = reader1.GetSchemaTable();
}
return table1;
}
It only needs to do this once and it happens on the very first request for an update, delete, or insert command.
You can see the command in a trace using SqlProfiler. Shown below is the actual commands being sent to the database server:
SET FMTONLY OFF;
SET NO_BROWSETABLE ON;
SET FMTONLY ON;
Select * FROM Categories
SET FMTONLY OFF;
SET NO_BROWSETABLE OFF;
Conclusion
SqlCommandBuilder is a pretty cool class in ADO.NET 2.0 that performs a number of services within the framework and Visual Studio IDE to help with code generation. You can use it in your applications for code generation and to simplifying your code.
Source: David Hayden ( Florida .NET Developer )
ADO.NET 2.0 Tutorials: