Command Builder in ADO.NET

Command Builder in ADO.Net helps us to generate update, delete., and insert commands on a single database table for a data adapter. ADO.NET Data Adapter is used to manage four separate Command objects. The Insert Command , the Update Command , and the Delete Command properties of the SqlDataAdapter object update the database with the data modifications that are run on a Data Set object. The SqlCommand objects that are assigned to these properties can be created manually in code or automatically generated by using the SqlCommandBuilder object.
The SqlCommandBuilder opens the Connection associated with the Data Adapter and makes a round trip to the server each and every time it’s asked to construct the action queries. It closes the Connection when it’s done.

Creating a Command Builder Object in ADO.Net

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

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");

 

Output

 

Demo Code

Download