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 DeleteCommand properties of a DataAdapter.

Setting the InsertCommand Property of a DataAdapter

The following example creates a SqlCommand object named myInsertCommand that contains an INSERT statement:

 SqlCommand myInsertCommand = mySqlConnection.CreateCommand(); myInsertCommand.CommandText =   "INSERT INTO Customers (" +   "  CustomerID, CompanyName, Address" +   ") VALUES (" +   "  @CustomerID, @CompanyName, @Address" +   ")"; myInsertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar,   5, "CustomerID"); myInsertCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar,   40, "CompanyName"); myInsertCommand.Parameters.Add("@Address", SqlDbType.NVarChar,   60, "Address");

The four parameters to the Add() method are as follows:

The name of the parameter
The .NET type of the parameter
The maximum length of the string that may be assigned to the parameter's value
The name of the corresponding database column that the parameter is bound to

Note

Commands and parameters are covered in Chapter 8, "Executing Database Commands."

As you can see from the previous code, the @CustomerID, @CompanyName, 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 =   DataRowVersion.Original; myUpdateCommand.Parameters["@OldAddress"].SourceVersion =   DataRowVersion.Original;  mySqlDataAdapter.UpdateCommand = myUpdateCommand;

There are two things to notice about this code:

The UPDATE statement's WHERE clause specifies parameters for CompanyID, CompanyName, and Address columns. This uses optimistic concurrency, which you'll learn about shortly.
A property named SourceVersion for the @OldCustomerID, @OldCompanyName and @OldAddress parameters is set to DataRowVersion.Original. This causes the values for these parameters to be set to the original DataRow column values before you change them.

These items determine the concurrency of the UPDATE, which you'll now learn about.

Concurrency

Concurrency determines how multiple users' modifications to the same row are handled. There are two types of concurrency that apply to a DataSet:

Optimistic Concurrency With optimistic concurrency, you can modify a row in a database table only if no one else has modified that same row since you loaded it into your DataSet. This is typically the best type of concurrency to use because you don't want to overwrite someone else's changes.
"Last One Wins" Concurrency With "last one wins" concurrency, you can always modify a row-and your changes overwrite anyone else's changes. You typically want to avoid using "last one wins" concurrency.

To use optimistic concurrency, you have to do the following in your UPDATE or DELETE statement's WHERE clause:

Include all the columns used in the original SELECT.
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.
Proposed	The proposed column value, which is set when you edit a DataRow using the BeginEdit() method.

Setting the DeleteCommand Property of a DataAdapter

The following example creates a SqlCommand object named myDeleteCommand that contains a DELETE statement and sets the DeleteCommand property of mySqlDataAdapter to myDeleteCommand:

 SqlCommand myDeleteCommand = mySqlConnection.CreateCommand(); myDeleteCommand.CommandText =   "DELETE FROM Customers " +   "WHERE CustomerID = @OldCustomerID " +   "AND CompanyName = @OldCompanyName " +   "AND Address = @OldAddress"; myDeleteCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar,   5, "CustomerID"); myDeleteCommand.Parameters.Add("@OldCompanyName", SqlDbType.NVarChar,   40, "CompanyName"); myDeleteCommand.Parameters.Add("@OldAddress", SqlDbType.NVarChar,   60, "Address"); myDeleteCommand.Parameters["@OldCustomerID"].SourceVersion =   DataRowVersion.Original; myDeleteCommand.Parameters["@OldCompanyName"].SourceVersion =   DataRowVersion.Original; myDeleteCommand.Parameters["@OldAddress"].SourceVersion =   DataRowVersion.Original; mySqlDataAdapter.DeleteCommand = myDeleteCommand;

Notice that the DELETE statement also uses optimistic concurrency.

This completes the setup of the DataAdapter object.

Adding a DataRow to a DataTable

In this section, you'll learn how to add a DataRow to a DataTable. Before you see this, 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():

 DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numOfRows =   mySqlDataAdapter.Fill(myDataSet, "Customers"); mySqlConnection.Close();

The int returned by the Fill() method is the number of rows retrieved from the database. The myDataSet object now contains a DataTable named Customers, which contains the rows retrieved by the following SELECT statement set earlier in the SelectCommand property of mySqlDataAdapter:

 SELECT CustomerID, CompanyName, Address FROM Customers ORDER BY CustomerID

To add a new row to a DataTable object, you use the following steps:

Use the NewRow() method of your DataTable to create a new DataRow.
Set the values for the DataColumn objects of your new DataRow. Note: you can set a DataColumn value to null using the SetNull() method of a DataRow. You can also check if a DataColumn contains null using the IsNull() method of a DataRow.
Use the Add() method through the Rows property of your DataTable to add your new DataRow to the DataTable.
Use the Update() method of your DataAdapter to push the new row to the database.

The following method, named AddDataRow(), uses these steps to add a new row to a DataTable:

 public static void AddDataRow(   DataTable myDataTable,   SqlDataAdapter mySqlDataAdapter,   SqlConnection mySqlConnection ) {   Console.WriteLine("\nIn AddDataRow()");   // step 1: use the NewRow() method of the DataTable to   // create a new DataRow   Console.WriteLine("Calling myDataTable.NewRow()");   DataRow myNewDataRow = myDataTable.NewRow();   Console.WriteLine("myNewDataRow.RowState = " +     myNewDataRow.RowState);   // step 2: set the values for the DataColumn objects of   // the new DataRow   myNewDataRow["CustomerID"] = "J5COM";   myNewDataRow["CompanyName"] = "J5 Company";   myNewDataRow["Address"] = "1 Main Street";   // step 3: use the Add() method through the Rows property   // to add the new DataRow to the DataTable   Console.WriteLine("Calling myDataTable.Rows.Add()");   myDataTable.Rows.Add(myNewDataRow);   Console.WriteLine("myNewDataRow.RowState = " +     myNewDataRow.RowState);   // step 4: use the Update() method to push the new   // row to the database   Console.WriteLine("Calling mySqlDataAdapter.Update()");   mySqlConnection.Open();   int numOfRows = mySqlDataAdapter.Update(myDataTable);   mySqlConnection.Close();   Console.WriteLine("numOfRows = " + numOfRows);   Console.WriteLine("myNewDataRow.RowState = " +     myNewDataRow.RowState);   DisplayDataRow(myNewDataRow, myDataTable); }

You'll notice I call the Open() and Close() methods of mySqlConnection around the call to the Update() method. You don't have to do this because the Update() method-like the Fill() method-will automatically open and then close mySqlConnection if it is currently closed. It is good programming practice, however, to explicitly include the Open() and Close() calls so that you can see exactly what is going on.

Note

In the ADO.NET disconnected model of data access, you should typically keep the connection to the database open for as short a period as possible. Of course, if you're making a lot of calls to the Update()or the Fill() method over a short time, you could keep the connection open and then close it when you're finished. That way, your code will have better performance. You might need to experiment with your own programs to find the right balance.

The Update() method is overloaded as follows:

 int Update(DataRow[] myDataRows) int Update(DataSet myDataSet) int Update(DataTable myDataTable) int Update(DataRow[] myDataRows, DataTableMapping myDataTableMapping) int Update(DataSet myDataSet, string dataTableName)

where dataTableName is a string containing the name of the DataTable to update. The int returned by the Update() method is the number of rows successfully updated in the database.

Going back to the previous AddDataRow() method, you'll also notice the inclusion of Console .WriteLine() calls that display the RowState property of myNewDataRow. The RowState property is set to one of the constants defined in the System.Data.DataViewRowState enumeration. Table 11.10 shows the constants defined in the DataRowState enumeration.

Table 11.10: DataRowState ENUMERATION MEMBERS
CONSTANT	DESCRIPTION
Added	The DataRow has been added to the DataRowCollection of the DataTable.
Deleted	The DataRow has been removed from the DataTable.
Detached	The DataRow isn't part of the DataTable.
Modified	The DataRow has been modified.
Unchanged	The DataRow hasn't been modified.

AddDataRow() calls a method named DisplayDataRow(), which displays the DataColumn values for the DataRow passed as the first parameter. DisplayDataRow() is defined as follows:

 public static void DisplayDataRow(   DataRow myDataRow,   DataTable myDataTable) {   Console.WriteLine("\nIn DisplayDataRow()");   foreach (DataColumn myDataColumn in myDataTable.Columns)   {     Console.WriteLine(myDataColumn + "= " +       myDataRow[myDataColumn]);   } }

In the previous AddDataRow() method, you saw that it displays the RowState property of myNewDataRow at various points. The output from AddDataRow() and its call to DisplayDataRow() is as follows:

 In AddDataRow() Calling myDataTable.NewRow() myNewDataRow.RowState = Detached Calling myDataTable.Rows.Add() myNewDataRow.RowState = Added Calling mySqlDataAdapter.Update() numOfRows = 1 myNewDataRow.RowState = Unchanged In DisplayDataRow() CustomerID = J5COM CompanyName = J5 Company Address = 1 Main Street

Let's examine this run in detail:

After myDataTable.NewRow() is called to create myNewDataRow, its RowState is Detached, which indicates myNewDataRow isn't yet part of myDataTable.
Next, myDataTable.Rows.Add() is called to add myNewDataRow to myDataTable. This causes the RowState of myNewDataRow to change to Added, which indicates myNewDataRow is now part of myDataTable.
Finally, mySqlDataAdapter.Update() is called to push the new row to the database. This causes the RowState of myNewDataRow to change to Unchanged.

Behind the scenes, the Update() method runs the INSERT statement in the mySqlDataAdapter .InsertCommand property to add the new row to the Customers table. The int returned by the Update() statement is the number of rows affected by the method call. In this example, one is returned since one row was added.

Modifying a DataRow in a DataTable

To modify a DataRow in a DataTable, you use the following steps:

Set the PrimaryKey property of your DataTable. You need to set this to find the DataRow in the next step.
Use the Find() method to locate the DataRow that you want to modify in your DataTable. You locate the DataRow using the value of its primary key column.
Change the DataColumn values for your DataRow.
Use the Update() method of your DataAdapter object to push the modified row to the database.

The following method, named ModifyDataRow(), uses these steps to modify the row that was previously added by the AddDataRow() method:

 public static void ModifyDataRow(   DataTable myDataTable,   SqlDataAdapter mySqlDataAdapter,   SqlConnection mySqlConnection ) {   Console.WriteLine("\nIn ModifyDataRow()");   // step 1: set the PrimaryKey property of the DataTable   myDataTable.PrimaryKey =     new DataColumn[]     {       myDataTable.Columns["CustomerID"]     };   // step 2: use the Find() method to locate the DataRow   // in the DataTable using the primary key value   DataRow myEditDataRow = myDataTable.Rows.Find("J5COM");   // step 3: change the DataColumn values of the DataRow   myEditDataRow["CompanyName"] = "Widgets Inc.";   myEditDataRow["Address"] = "1 Any Street";   Console.WriteLine("myEditDataRow.RowState = " +     myEditDataRow.RowState);   Console.WriteLine("myEditDataRow[\" CustomerID\", " +     "DataRowVersion.Original] = " +     myEditDataRow["CustomerID", DataRowVersion.Original]);   Console.WriteLine("myEditDataRow[\" CompanyName\", " +     "DataRowVersion.Original] = " +     myEditDataRow["CompanyName", DataRowVersion.Original]);   Console.WriteLine("myEditDataRow[\" Address\", " +     "DataRowVersion.Original] = " +     myEditDataRow["Address", DataRowVersion.Original]);   Console.WriteLine("myEditDataRow[\" CompanyName\", " +     "DataRowVersion.Current] = " +     myEditDataRow["CompanyName", DataRowVersion.Current]);   Console.WriteLine("myEditDataRow[\" Address\", " +     "DataRowVersion.Current] = " +     myEditDataRow["Address", DataRowVersion.Current]);   // step 4: use the Update() method to push the modified   // row to the database   Console.WriteLine("Calling mySqlDataAdapter.Update()");   mySqlConnection.Open();   int numOfRows = mySqlDataAdapter.Update(myDataTable);   mySqlConnection.Close();   Console.WriteLine("numOfRows = " + numOfRows);   Console.WriteLine("myEditDataRow.RowState = " +     myEditDataRow.RowState);   DisplayDataRow(myEditDataRow, myDataTable); }

Setting the primary key in step 1 doesn't have to be done inside the ModifyDataRow() method. You could, for example, set the primary key immediately after calling the Fill() method in the Main() method of the AddModifyAndRemoveDataRows.cs program. The reason I set the primary key in ModifyDataRow() is that you can see all the steps together in this method.

Notice in step 3 of this method the original values for the CustomerID, CompanyName, and Address DataColumn objects are displayed using the DataRowVersion.Original constant. These are the DataColumn values before they are changed. The current values for the CompanyName and Address DataColumn objects are also displayed using the DataRowVersion.Current constant. These are the DataColumn values after they are changed.

The output from ModifyDataRow() and its call to DisplayDataRow() is as follows:

 In ModifyDataRow() myEditDataRow.RowState = Modified myEditDataRow["CustomerID", DataRowVersion.Original] = J5COM myEditDataRow["CompanyName", DataRowVersion.Original] = J5 Company myEditDataRow["Address", DataRowVersion.Original] = 1 Main Street myEditDataRow["CompanyName", DataRowVersion.Current] = Widgets Inc. myEditDataRow["Address", DataRowVersion.Current] = 1 Any Street Calling mySqlDataAdapter.Update() numOfRows = 1 myEditDataRow.RowState = Unchanged In DisplayDataRow() CustomerID = J5COM CompanyName = Widgets Inc. Address = 1 Any Street

Notice that the CompanyName and Address DataColumn objects of myEditDataRow are changed. The RowState property of myEditDataRow changes to Modified after its CompanyName and Address are changed, and then to Unchanged after mySqlDataAdapter.Update() is called.

Marking Your Modifications

You can use the BeginEdit() method to mark the beginning of a modification to a DataRow. For example:

 myEditDataRow.BeginEdit(); myEditDataRow["CompanyName"] = "Widgets Inc."; myEditDataRow["Address"] = "1 Any Street";

You then use either the EndEdit() or CancelEdit() methods to mark the end of the modification to the DataRow. EndEdit() commits the modification; CancelEdit() rejects the modification and restores the DataRow to its original state before the edit began.

The following example calls the EndEdit() method of myEditDataRow to commit the changes made in the previous example:

 myEditDataRow.EndEdit();

Removing a DataRow from a DataTable

To remove a DataRow from a DataTable, you use the following steps:

Set the PrimaryKey property for your DataTable object.
Use the Find() method to locate your DataRow.
Use the Delete() method to remove your DataRow.
Use the Update() method to push the delete to the database.

The following method, named RemoveDataRow(), uses these steps to remove the DataRow that was previously modified by the ModifyDataRow() method:

 public static void RemoveDataRow(   DataTable myDataTable,   SqlDataAdapter mySqlDataAdapter,   SqlConnection mySqlConnection ) {   Console.WriteLine("\nIn RemoveDataRow()");   // step 1: set the PrimaryKey property of the DataTable   myDataTable.PrimaryKey =     new DataColumn[]     {       myDataTable.Columns["CustomerID"]     };   // step 2: use the Find() method to locate the DataRow   DataRow myRemoveDataRow = myDataTable.Rows.Find("J5COM");   // step 3: use the Delete() method to remove the DataRow   Console.WriteLine("Calling myRemoveDataRow.Delete()");   myRemoveDataRow.Delete();   Console.WriteLine("myRemoveDataRow.RowState = " +     myRemoveDataRow.RowState);   // step 4: use the Update() method to remove the deleted   // row from the database   Console.WriteLine("Calling mySqlDataAdapter.Update()");   mySqlConnection.Open();   int numOfRows = mySqlDataAdapter.Update(myDataTable);   mySqlConnection.Close();   Console.WriteLine("numOfRows = " + numOfRows);   Console.WriteLine("myRemoveDataRow.RowState = " +     myRemoveDataRow.RowState); }

The output from RemoveDataRow() is as follows:

 In RemoveDataRow() Calling myRemoveDataRow.Delete() myRemoveDataRow.RowState = Deleted Calling mySqlDataAdapter.Update() numOfRows = 1 myRemoveDataRow.RowState = Detached

Notice that the RowState property of myRemoveDataRow is set to Deleted after myRemoveData .Delete() is called, and then to Detached after mySqlDataAdapter.Update() is called-meaning that myRemoveDataRow is no longer part of the DataTable.

Note

You'll find a complete program named AddModifyAndRemoveDataRows.cs in the ch11 directory that illustrates the use of the AddDataRow(), ModifyDataRow(), and RemoveDataRow() methods. This program listing is omitted from this book for brevity.