Jan Schreuder on .Net

Custom SqlCommandBuilder

About a year ago, I felt I had to write my own SqlCommandBuilder object, because I had some issues with the one provided by the .Net framework:

1. You need to assign a SqlDataAdapter, a SqlCommand object with a select statement and a SqlDatabase connection in order for the SqlCommandBuilder to generate the Insert, Delete or Update commands.
2. The SqlCommandBuilder is registered as a listener for RowUpdating events whenever you set the DataAdapter property. So the commands are only generated when an attempt is made to update the database using the SqlDataAdapter.
3. The SqlCommandBuilder requires a round-trip to the server to get the required metadata. And since it is a listener on RowUpdating events, it does so ever ytime you call the DataAdapter.Update method, which has a major impact on the performance.
4. When you have an auto-identifier column as the primary key, the value inserted for this column is not returned by the insert command created by the CommandBuilder object. That means that any parent-child relations that use that primary key are not properly updated.
5. The Update and Delete commands have very large and complex "where-clauses" to ensure the correct row is affected. The commands are unaware of primary keys. So if there is a proper primary key, the updates and deletes are relatively slow.

So last year, I decided to "roll my own" command builder object. This custom made version only needs a database connection and the name of the table to create the CUD commands. The command objects are made available as properties of the object, allowing you to cache them. This eliminates the need for a round-trip for every call to DataAdapter.Update. In addition, it also creates the select command you normally need to add to the DataAdapter. You can find that post here, as well as the code I used at that time.

Today I finally found some time to convert it to .Net 2.0. With this version, it is now possible to do something like this:

void UpdateDataSet(DataSet dataSet)

{

    // Create the database connection

    using (SqlConnection connection = new SqlConnection("Server=servername;Database=database;uid=username;pwd=password"))

    {

        foreach (DataTable dataTable in dataSet.Tables)

        {

            // Create a new data adapter 

            using (SqlDataAdapter dataAdapter = new SqlDataAdapter())

            {

                // Use the custom command builder to create commands for the 

                using (LCommandBuilder cm = new LCommandBuilder(connection, dataTable.TableName))

                {

                    dataAdapter.UpdateCommand = LCommandBuilder.PrepareCommand(cm.UpdateCommand, connection);

                    dataAdapter.InsertCommand = LCommandBuilder.PrepareCommand(cm.InsertCommand, connection);

                    dataAdapter.DeleteCommand = LCommandBuilder.PrepareCommand(cm.DeleteCommand, connection);

                }

                // Update the dataset changes in the data source

                dataAdapter.Update(dataSet, dataTable.TableName);

                // Commit all the changes made to the DataSet

                dataTable.AcceptChanges();

            }

        }

    }

}

There's no need to create stored procedures for these basic actions on the database, as this custom command builder will create queries and command objects to take care of those tasks. I've added a number of constructors to allow the object to be used in transactions as well, as you can see in this sample:

void UpdateDataSet(SqlTransaction transaction, DataSet dataSet)

{

    // Create the database connection

    foreach (DataTable dataTable in dataSet.Tables)

    {

        // Create a new data adapter 

        using (SqlDataAdapter dataAdapter = new SqlDataAdapter())

        {

            // Use the custom command builder to create commands for the 

            using (LCommandBuilder cm = new LCommandBuilder(transaction, dataTable.TableName))

            {

                dataAdapter.UpdateCommand = LCommandBuilder.PrepareCommand(cm.UpdateCommand, transaction);

                dataAdapter.InsertCommand = LCommandBuilder.PrepareCommand(cm.InsertCommand, transaction);

                dataAdapter.DeleteCommand = LCommandBuilder.PrepareCommand(cm.DeleteCommand, transaction);

            }

            // Update the dataset changes in the data source

            dataAdapter.Update(dataSet, dataTable.TableName);

            // Commit all the changes made to the DataSet

            dataTable.AcceptChanges();

        }

    }

}

As Marc Jacobi was kind enough to point out, these examples will not work if you have parent-child relations in your dataset. Unless you are absolutely sure that the tables are in the correct order. ;-)

Further improvements in this version:

1. The SqlParameter objects which are created for the columns in the table are assigned with the following meta data: Column size, SqlDBType, Scale and Precision. The last two only for those types to which they apply.
2. A helper class, DataSetHelper, is added. This class uses the command builder object to persist data from a DataSet or DataTable object in the database.
3. Support for use in transactions has been improved.

There is still room for improvement though. The following is still on my to-do list:

1. Support for OleDb.
2. Improve the way meta data is retrieved for tables.

You can download a project with the code here. The project will build a class library which you can use in your project, as long as you read the disclaimers in the code and here.