Using Data Sets and Data Adapters - Beginning C# 5.0 Databases - page 292

How It Works
You add an INSERT statement.
string ins = @"insert into HumanResources.Department
(Name,GroupName, ModifiedDate)
values(@Name, @GroupName, @ModifiedDate)";
Create a command for the INSERT query.
// create command
SqlCommand cmd = new SqlCommand(ins, conn);
Then you configure the command parameters. The three columns for which you'll provide values
are each mapped to a named command parameter. You don't supply the primary key value since it's
generated by SQL Server.
// Map parameters
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50,"Name");
cmd.Parameters.Add("@GroupName",SqlDbType.NVarChar,50,"GroupName");
cmd.Parameters.Add("@ModifiedDate",SqlDbType.DateTime,25,"ModifiedDate");
Finally, you set the data adapter's InsertCommand property with the command to insert into the
Department table so it will be the SQL the data adapter executes when you call its Update method. You
then call Update on the data adapter to propagate the change to the database. Here you add only one
row, but since the SQL is parameterized, the data adapter will look for all new rows in the
HumanResources.Department data table and submit inserts for all of them to the database.
// Insert department
da.InsertCommand = cmd;
da.Update(ds, "HumanResources.Department");
Figure 15-12 shows the new row, and if you check with Database Explorer or the SSMS with SQL
Server 2012, you'll see the row has been propagated to the database. Microsoft Development is now in
the Department table.
Command Builders
Although it's straightforward, it's a bit of a hassle to code SQL statements for the UpdateCommand ,
InsertCommand , and DeleteCommand properties, so each data provider has its own command builder. If a
data table corresponds to a single database table, you can use a command builder to automatically
generate the appropriate UpdateCommand , InsertCommand , and DeleteCommand properties for a data
adapter. This is all done transparently when a call is made to the data adapter's Update method.
To be able to dynamically generate INSERT , DELETE , and UPDATE statements, the command builder
uses the data adapter's SelectCommand property to extract metadata for the database table. If any changes
are made to the SelectCommand property after invoking the Update method, you should call the
RefreshSchema method on the command builder to refresh the metadata accordingly.
To create a command builder, you create an instance of the data provider's command builder class,
passing a data adapter to its constructor. For example, the following code creates a SQL Server
command builder:
SqlDataAdapter da = new SqlDataAdapter();
SqlCommandBuilder cb = new SqlCommandBuilder(da);