Storing Data in Memory in Microsoft ADO.NET 4

  • 10/25/2010
Contents
This chapter from Microsoft ADO.NET 4 Step by Step introduces the DataRow class and its role in data storage within each data table.

After completing this chapter, you will be able to:

  • Explain how a DataTable stores data

  • Add new data rows to a table

  • Examine, update, and remove existing values in a table row

  • Explain how ADO.NET differentiates between pending and final data values

  • Integrate data verification code into your DataTable object

Adding columns to a DataTable is an essential step in managing data in ADO.NET, but the columns themselves contain no data. To store actual data values in an ADO.NET table, you must use the DataRow class. After you place one or more data rows in a table, the real work of managing application-specific information begins. This chapter introduces the DataRow class and its role in data storage within each data table.

NOTE

The exercises in this chapter all use the same sample project, a simple editor of DataRow records within a single DataTable. Although you will be able to run the application after each exercise, the expected results for the full application might not appear until you complete all exercises in the chapter.

Adding Data

Adding new data rows to a table is a three-step process:

  1. Create a new row object.

  2. Store the actual data values in the row object.

  3. Add the row object to the table.

Creating New Rows

The DataColumn objects you add to a DataTable let you define an unlimited number of column combinations. One table might manage information on individuals, with textual name fields and dates for birthdays and driver-license expirations. Another table might exist to track the score in a baseball game, and contain no names or dates at all. The type of information you store in a table depends on the columns included in that table, along with the name, data type, and field constraints for each column.

The DataRow class lets you store a single row of data in a table. However, a row of data that tracks customers or medical patients is not the same as a row that tracks baseball scores. The columns differ in number, data types, and even their names and positions. Therefore, each ADO.NET DataRow must be configured to work with a specific DataTable and its collection of DataColumn instances.

The DataTable class includes the NewRow method to generate table-specific data rows. Whenever you want to add a new row of data to a table, the first step always involves generating a new DataRow with the NewRow method.

C#
DataRow oneRow = someTable.NewRow();

Visual Basic
Dim oneRow As DataRow = someTable.NewRow()

The generated row includes information about each data column defined for the table. Typically, the data associated with each column in the new row is initially NULL, the database state for an unassigned field. However, if a DataColumn definition includes a DefaultValue setting, that initial value will appear immediately in the generated row for the named column. Also, any column that has its AutoIncrement and related fields set (typically a primary key field) will include generated sequential values for that column.

Defining Row Values

The DataRow class includes an Item property that provides access to each defined column, by name, zero-based index number, or reference to the physical DataColumn instance. When writing code with a specific table format in mind, programmers generally use the column name method because it makes clear which field is being referenced in a code statement.

C#
oneRow.Item["ID"] = 123;         // by column name
oneRow.Item[0] = 123;            // by column position
DataColumn whichColumn = someTable.Columns[0];
oneRow.Item[whichColumn] = 123;  // by column instance

Visual Basic
oneRow.Item("ID") = 123         ' by column name
oneRow.Item(0) = 123            ' by column position
Dim whichColumn As DataColumn = someTable.Columns(0)
oneRow.Item(whichColumn) = 123  ' by column instance

Because Item is the default member for the DataRow class, you can omit the name when referencing row values, as shown here:

C#
oneRow["ID"] = 123;

Visual Basic
oneRow("ID") = 123

Visual Basic includes a special “exclamation point” syntax that condenses the statement even more, but you can use it only with column names, not with column indexes.

Visual Basic
oneRow!ID = 123

NOTE

Members of the Item class are defined as the generic Object type; they are not strongly typed to the data type defined for the columns. This means that you can store data of an incorrect type in any field during this assignment phase. Errors will not be reported until you attempt to add the DataRow object to the table’s Rows collection, as described in the “Storing Rows in a Table” section of this chapter on page 40.

As you assign values to a row, they become available immediately for use in other expressions.

C#
orderData["Subtotal"] = orderRecord.PreTaxTotal;
orderData["SalesTax"] = orderRecord.PreTaxTotal * orderRecord.TaxRate;
orderData["Total"] = orderData["Subtotal"] + orderData["SalesTax"];

Visual Basic
orderData!Subtotal = orderRecord.PreTaxTotal
orderData!SalesTax = orderRecord.PreTaxTotal * orderRecord.TaxRate
orderData!Total = orderData!Subtotal + orderData!SalesTax

Fields with no default or auto-increment value are automatically set to NULL. If for any reason you need to set a field to NULL from a non-NULL state, assign it with the value of .NET’s DBNull class.

C#
oneRow["Comments"] = System.DBNull.Value;

Visual Basic
oneRow!Comments = System.DBNull.Value

As mentioned in Chapter 2, “Building Tables of Data” you can test field values in C# using the DBNull.Value.Equals method or in Visual Basic with the IsDBNull function. The DataRow class includes its own IsNull method; it is functionally equivalent to the methods from Chapter 2. Instead of passing the IsNull method a field value to test, you pass it the column’s name, the column’s position, or an instance of the column.

C#
if (oneRow.IsNull("Comments"))...

Visual Basic
If (oneRow.IsNull("Comments") = True)...

NOTE

System.DBNull is not the same as null in C#, or Nothing in Visual Basic. Those keywords indicate the absence of an object’s value. System.DBNull.Value is an object that presents a value.

Storing Rows in a Table

After you have assigned all required data values to the columns in a new row, add that row to the DataTable using the table’s Rows.Add method.

C#
someTable.Rows.Add(oneRow);

Visual Basic
someTable.Rows.Add(oneRow)

An overload of the Add method lets you skip the formal row-object creation process; instead, you supply the final field values directly as arguments. All provided values must appear in the same order and position as the table’s DataColumn instances.

C#
// ----- Assumes column 0 is numeric, 1 is string.
someTable.Rows.Add(new Object[] {123, "Fred"});

Visual Basic
' ----- Assumes column 0 is numeric, 1 is string.
someTable.Rows.Add(123, "Fred");

Whichever method you employ, the Add process tests all data values to be added to the table for data type compliance before adding the row. If the new row contains any values that can’t be stored in the target column-specific data type, the Add method throws an exception.

Adding Rows to a DataTable: C#

  1. Open the “Chapter 3 CSharp” project from the installed samples folder. The project includes two Windows.Forms classes: AccountManager and AccountDetail.

  2. Open the source code view for the AccountManager form. Locate the AccountManager_Load event handler. This routine creates a custom DataTable instance with five columns: ID (a read-only, auto-generated long integer), FullName (a required 30-character unique string), Active (a Boolean), AnnualFee (an optional decimal), and StartDate (an optional date).

  3. Add the following statements just after the “Build some sample data rows” comment. These rows add new DataRow objects to the table using the Rows.Add alternative syntax:

    CustomerAccounts.Rows.Add(new Object[] {1L, "Blue Yonder Airlines", true,
    500m, DateTime.Parse("1/1/2007")});
CustomerAccounts.Rows.Add(new Object[] {2L, "Fourth Coffee", true, 350m,
    DateTime.Parse("7/25/2009")});
CustomerAccounts.Rows.Add(new Object[] {3L, "Wingtip Toys", false});

Adding Rows to a DataTable: Visual Basic

  1. Open the “Chapter 3 VB” project from the installed samples folder. The project includes two Windows.Forms classes: AccountManager and AccountDetail.

  2. Open the source code view for the AccountManager form. Locate the AccountManager_Load event handler. This routine creates a custom DataTable instance with five columns: ID (a read-only, auto-generated long integer), FullName (a required 30-character unique string), Active (a Boolean), AnnualFee (an optional decimal), and StartDate (an optional date).

  3. Add the following statements just after the “Build some sample data rows” comment. These rows add new DataRow objects to the table using the Rows.Add alternative syntax:

    CustomerAccounts.Rows.Add({1&, "Blue Yonder Airlines", True, 500@, #1/1/2007#})
CustomerAccounts.Rows.Add({2&, "Fourth Coffee", True, 350@, #7/25/2009#})
CustomerAccounts.Rows.Add({3&, "Wingtip Toys", False})
Save to your account

This chapter is from the book