14.9 Using CommandBuilder to Automatically Generate Commands

14.9 Using CommandBuilder to Automatically Generate Commands

You want to create an SqlDataAdapter that automatically creates the correct INSERT , DELETE , and UPDATE commands.

Technique

The SqlCommandBuilder class is designed to work in conjunction with an SqlDataAdapter to automatically create SQL statements to manipulate a database. Before you create an SqlCommandBuilder , create an SqlDataAdapter and ensure that you set the SelectCommand property. Once you do so, create a new SqlCommandBuilder instance, passing the SqlDataAdapter as a parameter to the constructor. The following code demonstrates how:

 [STAThread] static void Main(string[] args) {     SqlConnection connection;     SqlDataAdapter dataAdapter;     DataSet productsDS;     SqlCommandBuilder commandBuilder;     SqlCommand selectCommand;     string connectionString = "Integrated Security=SSPI; " +         Data Source=VCSMARKHSCH6;Initial Catalog=Northwind;";     string selectCmd = "SELECT * From Products";     // create new SqlConnection specifying the connection string     connection = new SqlConnection( connectionString );     // create new dataset object and data adapter used to fill it     productsDS = new DataSet();     selectCommand = new SqlCommand( selectCmd, connection );     dataAdapter = new SqlDataAdapter( selectCommand );     // create command builder     commandBuilder = new SqlCommandBuilder( dataAdapter );     // open the connection     connection.Open();     // fill the dataset from products table     dataAdapter.Fill(productsDS, "Products" );     // manipulate the database here     // update the database.  The SqlCommandBuilder will be used     // to create the UPDATE SQL command     dataAdapter.Update( productsDS );     // close the connection     connection.Close(); } 

Comments

When you create an SqlDataAdapter , you specify an initial SelectCommand using either its constructor or an SqlCommand object. To insert, update, or delete records from a database, you also have to create commands for the InsertCommand , UpdateCommand , and DeleteCommand properties. You can use the SqlCommandBuilder as a quick way of automatically generating these commands. It does so by looking at the SelectCommand that you specified to create a schema that is used to generate each command. Once a RowUpdating command is triggered, the SqlCommandBuilder generates the necessary statements. To view the generated commands from a console window, you can access the various commands, as shown in the following code:

 Console.WriteLine( "INSERT command: {0}\n", commandBuilder.GetInsertCommand().CommandText ); Console.WriteLine( "UPDATE command: {0}\n", commandBuilder.GetUpdateCommand().CommandText ); Console.WriteLine( "DELETE command: {0}\n", commandBuilder.GetDeleteCommand().CommandText ); 

When using an SqlCommandBuilder , you have to be aware of a few limitations that can cause it to fail and throw an exception in certain instances. First of all, the SelectCommand must return at least one column that is a primary key within the database. If it does not, an InvalidOperation exception is thrown and the command generation fails. Second, commands can only be generated for single table updates. Therefore, for any updates that utilize relationships or constraints such as a foreign-key constraint, the SqlCommandBuilder will be unable to generate the necessary commands. Finally, if at any time the SqlDataAdapter changes its SelectCommand , Connection , CommandTimeout , or Transaction properties, the generated commands become invalid. To recreate the SqlCommandBuilder commands for these cases, call the RefreshSchema method defined in the SqlCommandBuilder class.