Using CommandBuilder to create dynamic command objects
At the moment, I'm working on a conversion between 2 databases. I was looking for a way of avoiding the tedious job of creating command objects for each of the tables I needed to insert data into. In most projects I worked on over the last couple of years, I was able to use code generation to do this. But not in this project. And I did not have the time to write new templates, or evaluate existing ones.
So I went back to another option which I used before I had access to code generation, the CommandBuilder object. The following example shows you how this works on the Customers table in the Northwind database:
SqlConnection dcNorthwind = new SqlConnection();
SqlDataAdapter daCustomers = new SqlDataAdapter("Select * From Customers". dcNorthwind);
SqlCommandBuilder cb = new SqlCommandBuilder(daCustomers);
SqlCommand cmdInsert = cb.GetInsertCommand();
SqlCommand cmdDelete = cb.GetDeleteCommand();
SqlCommand cmdUpdate = cb.GetUpdateCommand();
The CommandBuilder object builds the commands for insert, update and delete using the results of the SQL query. Using this I created the following generic method to create the command objects I needed:
public static void BuildCommandObjects(string conString, string tableName,
ref SqlCommand insertCmd, ref SqlCommand updateCmd, ref SqlCommand deleteCmd)
{
if ((conString == null) || (conString.Trim().Length == 0)) throw new ArgumentNullException( "conString" );
if ((tableName == null) || (tableName.Length == 0)) throw new ArgumentNullException( "tableName" );
try
{
using (SqlConnection sqlConnection = new SqlConnection(conString))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter("Select * from " + tableName, sqlConnection))
{
using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter))
{
insertCmd = cmdBuilder.GetInsertCommand();
updateCmd = cmdBuilder.GetUpdateCommand();
deleteCmd = cmdBuilder.GetDeleteCommand();
}
}
}
}
catch (SqlException ex)
{
throw new MyException(string.Format("Building command objects for table {0} failed", tableName), ex);
}
}
The SqlCommand objects created by this procedure can be used in the UpdateDataSet procedure from the Microsoft.ApplicationBlock.Data.SqlHelper class. Creating objects in this manner comes at a price, as usual. And that price is database performance. Another issue is that when you have an auto-identify column as your primary key, the value is not return by the insert command. More details on performance problems and limitations can be found in this MSDN article. However, for the project I work on now, these limitations are not really a problem.
And then there is a big advantage as well:
The command object is truly dynamic. A change in your table automatically changes the insert, update and delete commands. So no need to change your code, or re-generate it.
Using the CommandBuilder object was ideal for my application. I could write the code to convert data from one table to another, without worrying about the insert statements for the target tables. I did not need to re-write some of the code after the table was changed. And those changes include type and size changes for some of the columns. However, if wether you should use CommandBuilder or code-generation really depends on your project.