CommandBuilder in ADO.NET

C# CommandBuilder

The C# CommandBuilder in ADO.NET helps developers generate update, delete., and insert commands on a single database table for a data adapter. 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 a 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

Creating a CommonedBuider object is simple. 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 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 a 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 a CommandBuilder by passing a 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 populates 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 Referesh of the CommandBuilder should be called if the SelectCommand of the associated DataAdapter changes. The Refresh rebuilds the other command structures (InsertCommand, DeleteCommand, UpdateCommand) of the DataAdapter.

Conclusion

The code example in this article shows how to use a CommandBuilder in ADO.NET.