Modifying Rows in a DataTable
In this section, you'll see the steps required to add, modify, and remove DataRow objects
from a DataTable and then push those changes to the database. The examples in this
section show how to add, modify, and delete rows in the Customers database table.

Note You'll find a complete program named AddModifyAndRemoveDataRows.cs in the
ch11 directory that illustrates the use of the methods shown in this section. This
program listing is omitted from this book for brevity.
Setting up a DataAdapter to Push Changes to the Database
In Chapter 10
, you saw that before you call the Fill() method of your DataAdapter to read
rows from the database, you first need to set the SelectCommand property of your
DataAdapter. For example:
SqlCommand mySelectCommand = mySqlConnection.CreateCommand();
mySelectCommand.CommandText =
"SELECT CustomerID, CompanyName, Address " +
"FROM Customers " +
"ORDER BY CustomerID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySelectCommand;
The SELECT statement is then run when you call the mySqlDataAdapter object's Fill()
method to retrieve rows from the Customers table into a DataSet.
Similarly, before you can push changes to the database, you must first set up your
DataAdapter with Command objects containing appropriate SQL INSERT, UPDATE,
and DELETE statements. You store these Command objects in your DataAdapter object's
InsertCommand, UpdateCommand, and DeleteCommand properties.
You push changes from your DataSet to the database using the Update() method of your
DataAdapter. When you add, modify, or remove DataRow objects from your DataSet and
then call the Update() method of your DataAdapter, the appropriate InsertCommand,
UpdateCommand, or DeleteCommand is run to push your changes to the database.
Let's take a look at how to set the InsertCommand, UpdateCommand, and

@Address parameters are bound to the CustomerID, CompanyName, and Address
columns in the database.
Next, the following example sets the InsertCommand property of mySqlDataAdapter to
myInsertCommand:
mySqlDataAdapter.InsertCommand = myInsertCommand;
Setting the UpdateCommand Property of a DataAdapter
The following example creates a SqlCommand object named myUpdateCommand that
contains an UPDATE statement and sets the UpdateCommand property of
mySqlDataAdapter to myUpdateCommand:
myUpdateCommand.CommandText =
"UPDATE Customers " +
"SET " +
" CompanyName = @NewCompanyName, " +
" Address = @NewAddress " +
"WHERE CustomerID = @OldCustomerID " +
"AND CompanyName = @OldCompanyName " +
"AND Address = @OldAddress";
myUpdateCommand.Parameters.Add("@NewCompanyName", SqlDbType.NVarChar,
40, "CompanyName");
myUpdateCommand.Parameters.Add("@NewAddress", SqlDbType.NVarChar,
60, "Address");
myUpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar,
5, "CustomerID");
myUpdateCommand.Parameters.Add("@OldCompanyName", SqlDbType.NVarChar,
40, "CompanyName");
myUpdateCommand.Parameters.Add("@OldAddress", SqlDbType.NVarChar,
60, "Address");
myUpdateCommand.Parameters["@OldCustomerID"].SourceVersion =
DataRowVersion.Original;
myUpdateCommand.Parameters["@OldCompanyName"].SourceVersion =

2. Set these column values to original values retrieved from the row in the table
before you changed the values.
When you do these two things in your UPDATE or DELETE statement's WHERE
clause, your statement first checks that the original row still exists before updating or
deleting the row. That way, you can be sure your changes don't overwrite anyone else's
changes. Of course, if the original row has been deleted by another user, then your
UPDATE or DELETE statement will fail.
To use "last one wins" concurrency, you just include the primary key and its value in the
WHERE clause of your UPDATE or DELETE statement. Since your UPDATE statement
doesn't check the original values, it simply overwrites anyone else's changes if the row
still exists. Also, a DELETE statement simply deletes the row-even if another user has
modified the row.
Returning to the previous code example that set the UpdateCommand property of
mySqlDataAdapter, you can see that all the columns are included in the WHERE clause
of the UPDATE. That satisfies the first requirement of using optimistic concurrency
shown earlier.
The second requirement is that you set the column in the WHERE clause to the original
row values. You do this by setting the SourceVersion property of the @OldCustomerID,
@OldCompanyName, and @OldAddress parameters to DataRowVersion.Original. At
runtime, this pulls the original values from the DataColumn objects in the DataRow
before you changed them and puts them in the UPDATE statement's WHERE clause.
Original is just one of the members of the System.Data.DataRowVersion enumeration;
the others are shown in Table 11.9
.
Table 11.9: DataRowVersion ENUMERATION MEMBERS
CONSTANT DESCRIPTION
Current The current column value.
Default The default column value.
Original The original column value.
Table 11.9: DataRowVersion ENUMERATION MEMBERS

let's populate a DataSet with the rows from the Customers table. The following code
creates a DataSet object named myDataSet and populates it by calling
mySqlDataAdapter.Fill():