Storing Data in Memory in Microsoft ADO.NET 4

Examining and Changing Data

After adding a data row to a table, you can process it as a table member. For instance, you can iterate through the table’s Rows collection, examining the stored column values as you pass through each record. The following code adds up the sales tax for all records in the allSales table:

C#
decimal totalTax = 0m;
foreach (DataRow scanRow in someTable.Rows)
    if (!DBNull.Value.Equals(scanRow["SalesTax"]))
        totalTax += (decimal)scanRow["SalesTax"];

Visual Basic
Dim totalTax As Decimal = 0@
For Each scanRow As DataRow In someTable.Rows
    If (IsDBNull(scanRow!SalesTax) = False) Then _
        totalTax += CDec(scanRow!SalesTax)
Next scanRow

Because each row’s collection of items is not strongly typed, you might need to cast or convert each field to the target data type before using it.

Because of this lack of strong typing, be careful when assigning new values to any row already included in a table. For example, code that assigns a string value to an integer column will compile without error, but will generate a runtime error.

Modifying Existing Rows in a DataTable: C#

1. Open the source code view for the AccountDetail form. Locate the AccountDetail_Load routine.

2. Add the following code, which fills in the form’s display fields with content from an existing DataRow instance:

   if (AccountEntry != null)
   {
       AccountID.Text = string.Format("{0:0}", AccountEntry["ID"]);
       ActiveAccount.Checked = (bool)AccountEntry["Active"];
       if (DBNull.Value.Equals(AccountEntry["FullName"]) == false)
           AccountName.Text = (string)AccountEntry["FullName"];
       if (DBNull.Value.Equals(AccountEntry["AnnualFee"]) == false)
           AnnualFee.Text = string.Format("{0:0.00}",
               (decimal)AccountEntry["AnnualFee"]);
       if (DBNull.Value.Equals(AccountEntry["StartDate"]) == false)
           StartDate.Text = string.Format("{0:d}",
               (DateTime)AccountEntry["StartDate"]);
   }

3. Locate the ActOK_Click routine. In the Try block, just after the “Save the changes in the record” comment, you’ll find the following code line:

   workArea.BeginEdit();

   Just after that line, add the following code, which updates an existing DataRow instance with the user’s input:

   workArea["Active"] = ActiveAccount.Checked;
   if (AccountName.Text.Trim().Length == 0)
       workArea["FullName"] = DBNull.Value;
   else
       workArea["FullName"] = AccountName.Text.Trim();
   if (AnnualFee.Text.Trim().Length == 0)
       workArea["AnnualFee"] = DBNull.Value;
   else
       workArea["AnnualFee"] = decimal.Parse(AnnualFee.Text);
   if (StartDate.Text.Trim().Length == 0)
       workArea["StartDate"] = DBNull.Value;
   else
       workArea["StartDate"] = DateTime.Parse(StartDate.Text);

Modifying Existing Rows in a DataTable: Visual Basic

1. Open the source code view for the AccountDetail form. Locate the AccountDetail_Load routine.

2. Add the following code, which fills in the form’s display fields with content from an existing DataRow instance:

   If (AccountEntry IsNot Nothing) Then
       AccountID.Text = CStr(AccountEntry!ID)
       ActiveAccount.Checked = CBool(AccountEntry!Active)
       If (IsDBNull(AccountEntry!FullName) = False) Then _
           AccountName.Text = CStr(AccountEntry!FullName)
       If (IsDBNull(AccountEntry!AnnualFee) = False) Then _
           AnnualFee.Text = Format(CDec(AccountEntry!AnnualFee), "0.00")
       If (IsDBNull(AccountEntry!StartDate) = False) Then _
           StartDate.Text = Format(CDate(AccountEntry!StartDate), "Short Date")
   End If

3. Locate the ActOK_Click routine. In the Try block, just after the “Save the changes in the record” comment, you’ll find the following code line:

   workArea.BeginEdit()

   Just after that line, add the following code, which updates an existing DataRow instance with the user’s input:

   workArea!Active = ActiveAccount.Checked
   If (AccountName.Text.Trim.Length = 0) _
       Then workArea!FullName = DBNull.Value _
       Else workArea!FullName = AccountName.Text.Trim
   If (AnnualFee.Text.Trim.Length = 0) _
       Then workArea!AnnualFee = DBNull.Value _
       Else workArea!AnnualFee = CDec(AnnualFee.Text)
   If (StartDate.Text.Trim.Length = 0)
       Then workArea!StartDate = DBNull.Value _
       Else workArea!StartDate = CDate(StartDate.Text)