CommandBuilder in ADO.NET

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

CommandBuilder: Easing the Work of the Programmer

Sometimes creating SQL statements could be lengthy job when dealing with many columns in a table. A CommandBuilder object reduces the burden of creating SQL statements for you. In other words, the CommandBuilder helps you to generate update, delete., and insert commands on a single database table for a data adapter. Similar to other objects, each data provider has a command builder class. The OleDbCommandBuilder, SqlCommonBuilder, and OdbcCommandBuilder classes represent the CommonBuilder object in the OleDb, Sql, and ODBC data providers. These classes also work in pretty similar fashion. Once you know how to use OleDbCommandBuilder, you can use SqlCommandBuilder and OdbcCommandBuilder in a similar way. I'll use OleDbCommandBuilder class in this example.

Creating a Command Builder Object

Creating a CommonedBuider object is pretty simply. You pass a DataAdapter as an argument of the CommandBuilder constructor. For example:

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

SqlCommandBuilder Members

The DataAdapter property of a CommonBuilder represents the DataProvider attached to a CommonBuilder object for which automatic SQL statements are generated. The GetDeleteCommon, 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 the sqlCommandBuilder

Now you'll see how to use the SqlCommandBuilder in an application. You can use OleDbCommand Builder and ODBCCommandBuilder classes in same way. Listing 5-54 shows how to use SqlCommandBuilder. As you can see, 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 cause the UPDATE, INSERT, and DELETE commands to be generated for the adapter:

            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

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

            // Create a dataset object
            DataSet ds = new DataSet("Employee Set");
            adapter.Fill(ds, "Employees");

Now insert a new DataRow into the DataTable in memory and popular a row with your desired value using DataTable'sAddNew 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
            DataTable EmployeeTable = ds.Tables["Employees"];
            DataRow row = EmployeeTable.NewRow();
            row["firstName"] = "Rodney";
            row["LastName"] = "Dangerfield";
            row["Title"] = "comedian";
            EmployeeTable.Rows.Add(row);

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

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

Listing 5-54 shows the full source code of how to create and use a CommandBuilder object.

Listing 5-54. Creating and using the SqlCommandBuilder class

            //Create a connection object
            string ConnectionString = "Integrated Security = SSPI;" +
            "Initial Catalog = Northwind;" + "Data Source = localhost;";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // open the connection
            conn.Open();

            // Create a data adapter object
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Employee ORDER by EmployeeID", conn);

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

            // Create a data Set object
            DataSet ds = new DataSet("EmployeeSet");
            adapter.Fill(ds, "Employees");

            // Create a data table object and add a new row
            DataTable EmployeeTable = ds.Tables["Employees"];
            DataRow row = EmployeeTable.NewRow();
            row["FirstName"] = "Rodney";
            row["LastName"] = "DangerField";
            row["Title"] = "Comedian";
            EmployeeTable.Rows.Add(row);

            // update data adapter
            adapter.Update(ds, "Emlpoyees");
            MessageBox.Show(row["FirstName"].ToString().Trim() + " "
            + row["LastName"].ToString().Trim() + " Added to Employees");

As you can see from listing 5-54, you didn't have to figure out how to create the InsertCommand for Employee table because the CommandBuilder did it for you. All you had to do was add row to the DataSet and invoke and Update on the DataAdapter. You may argue that the InsertCommand is automatically generated by VS.NET anyway by 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 run-time.

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

Conclusion

Hope this article would have helped you in understanding CommandBuilder in ADO.NET. See my other articles on the website on ADO.NET.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.