CommandBuilder: Easing the Work of Programmers

Sometimes creating SQL statements can be a lengthy job when dealing with many columns in a table. A CommandBuilder object reduces the burden of creating SQL statement for you. In other words, the CommandBuilder object helps you generate UPDATE, DELETE, and INSERT commands on a single database table for a DataAdapter. The OleDbCommandBuilder, SqlCommandBuilder, and OdbcCommandBuilder classes represent the CommandBuilder object in the OleDb, Sql, and Odbc data providers (respectively).

Creating a CommandBuilder Object

Creating a CommandBuilder object is pretty simple. You pass a DataAdapter as an argument of the CommandBuilder contructor. For example:

 // Create a command builder object SqlCommandBuilder builder = new SqlCommandBuilder(adapter); 

Using SqlCommandBuilder Members

The DataAdapter property of a CommandBuilder represents the DataProvider attached to a CommandBuilder object for which automatic SQL statements are generated. The GetDeleteCommand, GetUpdateCommand, and GetInsertCommand methods return the Delete, Update, and Insert commands in the form of a Command object. The RefreshSchema method refreshes the database schema.

Using SqlCommandBuilder

Now you'll see how to use the SqlCommandBuilder in an application. You can use the OleDbCommandBuilder and OdbcCommandBuilder classes in the same way.

As usual, you create a connection to the database and use it to create the adapter object. The adapter is constructed with the initial query for the Employees table as well as with the database connection.

Next, you construct the CommandBuilder by passing the DataAdapter into its constructor. The act of creating the CommandBuilder automatically causes the UPDATE, INSERT, and DELETE commands to be generated for the adapter. The following code creates two SqlCommandBuilder objects:

   Dim adapter As SqlDataAdapter = New SqlDataAdapter(SQL, conn)     Dim builder1 As SqlCommandBuilder = New SqlCommandBuilder()     builder1.DataAdapter = adapter     Dim builder2 As SqlCommandBuilder = _     New SqlCommandBuilder(adapter) 

Next, fill the DataSet using the adapter and create an instance of the Employees DataTable from the DataSet:

 Dim ds As DataSet = New DataSet("EmployeeSet") adapter.Fill(ds, "Employees") 

Now, insert a new DataRow into the DataTable in memory and populate a row with your desired values using the DataTable's AddNew method. After that you call the DataRowCollection.Add method to add the row to the DataTable:

 ' Create a data table object and add a new row     Dim EmployeeTable As DataTable = ds.Tables("Employees")     Dim row As DataRow = EmployeeTable.NewRow()     row("FirstName") = "Rodney"     row("LastName") = "DangerField"     row("Title") = "Manager "     EmployeeTable.Rows.Add(row) 

Finally, you call DataAdapter's Update method to update the DataTable changes to the data source:

 // Update data adapter adapter.Update(ds, "Employees"); 

Listing 4-34 shows the full source code for creating and using a CommandBuilder object.

Listing 4-34: Creating and Using the SqlCommandBuilder Class

     Dim ConnectionString As String = "Integrated Security=SSPI;" & _          "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString)     Dim SQL As String = "SELECT * FROM Employees"     ' open a connection     conn.Open()     Dim adapter As SqlDataAdapter = New SqlDataAdapter(SQL, conn)     Dim builder1 As SqlCommandBuilder = New SqlCommandBuilder()     builder1.DataAdapter = adapter     Dim builder2 As SqlCommandBuilder = _     New SqlCommandBuilder(adapter)     ' Create a command builder object     Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)     ' Create a dataset object     Dim ds As DataSet = New DataSet("EmployeeSet")     adapter.Fill(ds, "Employees")     ' Create a data table object and add a new row     Dim EmployeeTable As DataTable = ds.Tables("Employees")     Dim row As DataRow = EmployeeTable.NewRow()     row("FirstName") = "Bill"     row("LastName") = "Harvey"     row("Title") = "President"     EmployeeTable.Rows.Add(row)     ' Update data adapter     adapter.Update(ds, "Employees")     ' Dispose     conn.Close()     conn.Dispose() 

As you can see from Listing 4-34, you didn't have to figure out how to create InsertCommand for the Employees table because the CommandBuilder did it for you. All you had to do was add a row to the DataSet and invoke an Update on the DataAdapter. You may argue that the InsertCommand is automatically generated by VS .NET with the DataAdapter configurer, but the CommandBuilder works with the SelectCommand you choose for the adapter, so you can change the SelectCommand on the fly and reuse the CommandBuilder at runtime.

Note that you should call the RefreshSchema method of the CommandBuilder if the SelectCommand of the associated DataAdapter changes. The RefreshSchema rebuilds the other command structures (InsertCommand, DeleteCommand, UpdateCommand) of the DataAdapter.