What is a SqlCommandBuilder in ADO.NET

SqlCommandBuilder class in ADO.NET provides the feature of reflecting the changes made to a DataSet or an instance of the SQL server data. When an instance of the SqlCommandBuilder class is created, it automatically generates Transact-SQL statements for the single table updates that occur. The object of the SqlCommandBuilder acts as a listener for RowUpdating events, whenever the DataAdapter property is set.

The SqlCommandBuilder object automatically generates the values contained within the SqlDataAdapter's InsertCommand, UpdateCommand and DeleteCommand properties based on the initial SelectCommand. The advantage here is that you will not need to write SqlCommand & SqlParameter Types explicitly.

Basically the command builder object builds these objects on the fly. The command builder object actually reads the metadata of the method called. After the builder object reads the underlying schema of the adapter's method, it autogenerates an underlying insert, update & delete command object. See code example below...

//Code below in C#...
DataSet ds = new DataSet();
SqlConnection cn = new SqlConnection("strSomeConnectionString");
//Autogenerate Insert, Update & Delete commands
SqlDataAdapter da = new SqlDataAdapter("Select from t_Something", cn);
SqlCommandBuilder scb = new SqlCommand(da);


.......................................................................................................................................................
CommandBuilder generates insert/update/delete commands for Data adapter based on select command. Automatic creation of insert/update/delete commands hinders performance. In case one knows the contents of insert/update/delete, should create those explicitly. Better to create explicit stored procedures for insert/update/delete and assign those.

The CommandBuilder uses SelectComand property of DataAdapter to determine values for other commands. If there is change in SelectCommand of DataAdapter, remember to call ReferhScheme to update the command properties.

CommandBuilder only generates a command for data adapter’s Command property if command property is null. By default command properties are null for data adapter. If you explicitly set a command property, the CommandBuilder does not overwrite it. You need to set the command property to null to allow CommandBuilder to generate a command for Command property.



// Create a new Connection and SqlDataAdapter

SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind");
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("Select * from Customers", myConnection);
DataSet myDataSet = new DataSet();
DataRow myDataRow;

// Create command builder. This line automatically generates the update commands for you, so you don't
// have to provide or create your own.
SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);

// Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
// key & unique key information to be retrieved unless AddWithKey is specified.
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

mySqlDataAdapter.Fill(myDataSet, "Customers");

myDataRow = myDataSet.Tables["Customers"].NewRow();
myDataRow["CustomerId"] = "NewID";
myDataRow["ContactName"] = "New Name";
myDataRow["CompanyName"] = "New Company Name";

myDataSet.Tables["Customers"].Rows.Add(myDataRow);

myDataSet.Tables["Customers"].Rows[0]["ContactName"]="Peach";

DataRow myDataRow1 = myDataSet.Tables["Customers"].Rows.Find("ALFKI");
myDataRow1["ContactName"]="Peach";

myDataSet.Tables["Customers"].Rows[0].Delete();

/* Before submitting the update back to the database, you need to setup the InsertCommand, UpdateCommand, and DeleteCommand to reconcile the changes to the database. For limited scenarios you can use the SqlCommandBuilder to automatically generate those for you, as is shown in the following example: */

SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);

mySqlDataAdapter.Update(myDataSet, "Customers");