The DataTable, DataRow, and DataColumn Objects

I've been discussing data tables and how to bind columns to visual controls. Now I want to describe how to programmatically retrieve or write values from and to rows and columns in a table. I will also describe how to create our own tables on the fly from a program.

As we saw in Chapter 11, the DataTable object is a central object in the ADO.NET library. We saw how objects that use the DataTable object include the DataSet and DataView objects. It will probably come as no surprise that the DataRow and DataColumn objects are primary components of a DataTable. A programmer will use the DataRow object with its properties and methods to manipulate values in the table. The DataRow object is used to retrieve and evaluate values as well as to insert, delete, and update records. Of course, a record is represented as a DataRow. Internally, the DataRowCollection represents the actual DataRow objects in the DataTable. The DataColumnCollection, of course, contains the DataColumn objects that describe the schema of the DataTable. We can use the DataColumnCollection object's overloaded Item property to read or write a value to a DataColumn.

Examining the DataTable Schema

If we want to examine the fields and data types of those fields in a table, we can do so by using the DataColumnCollection. By its name, we can tell that the collection contains each of the DataColumn objects in the table. We dimension a variable, dccCollection, to hold the collection, as well as the variable dcDataColumn to hold each DataColumn object as we iterate through the collection. We will stick with the Employees table from the Northwind database in this example. If we wrote a program and used an SqlDataAdapter object to retrieve all the fields from the Employees table, the following code would let us iterate through each of the fields and look at its data type.

Dim dccCollection As DataColumnCollection Dim dcDataColumn As DataColumn Dim sDescription As String Dim tEmployeesTable As DataTable tEmployeesTable = DataSet11.Tables("Employees") dccCollection = tEmployeesTable.Columns For Each dcDataColumn In dccCollection sDescription += "Name: " & dcDataColumn.ColumnName & _ CtrlChrs.Tab & CtrlChrs.Tab & " DataType: " & _ dcDataColumn.DataType.ToString & CtrlChrs.CrLf Next MessageBox.Show(sDescription, "Employees Table Column " & _ "Collection", MessageBoxButtons.OK, _ MessageBoxIcon.Information)

When we run this code, we can see that the fields are listed along with their data types, as you can see in Figure 12-4. Again, each of these fields can be accessed individually and is stored in the DataColumnCollection of the table.

Figure 12-4

The fields in the Employees table.

Now that we've seen the columns of a data table and their respective data types, let's take a look at the contents of the columns. We can easily do this by examining the Item property of a DataRow object. The Item property is passed in the name of the column to be displayed.

Dim drDataRow As DataRow SqlDataAdapter1.Fill(DataSet11, "Employees") tEmployeesTable = DataSet11.Tables("Employees") sDescription = "There are " & tEmployeesTable.Rows.Count & _ " rows in the Employees table" & CtrlChrs.CrLf For Each drDataRow In tEmployeesTable.Rows sDescription += "ID: " & drDataRow.Item("EmployeeID") & _ CtrlChrs.Tab & "Last Name: " & _ drDataRow.Item("LastName")& CtrlChrs.CrLf Next MessageBox.Show(sDescription, "Employees Table Data Rows", _ MessageBoxButtons.OK, MessageBoxIcon.Information)

We can see the two fields that we retrieved by column name in Figure 12-5.

Figure 12-5

The contents of the ID and the LastName columns in the Employees table.

It's straightforward to select any column within a row if we know its name. But if you don't know the column name you want to access but do know its position within the row, you can pass the ordinal value of the column. So, if we printed out drDataRow(0), the contents of the first column of our table would be printed.

Building a Table Programmatically

At times, you'll need to build a table on the fly or you'll need to search for specific records in a data table. Let's spend a moment looking at how to do these chores because the technique for doing so with Visual Basic .NET and ADO.NET is different from classic ADO. Here's the code we'll be examining.

Dim dtDataTable as DataTable ' Create a new DataTable dtDataTable = New DataTable("tbLanguageTable") ' Declare a variable for the DataRow object Dim dcDataRow As DataRow ' Create new DataColumn, set DataType, ColumnName ' and add it to our new DataTable dcDataColumn = New DataColumn() With dcDataColumn .DataType = System.Type.GetType("System.Int32") .ColumnName = "Sequence" .ReadOnly = True .AutoIncrement = True End With ' Finally, add the Column to the DataColumnCollection dtDataTable.Columns.Add(dcDataColumn) ' Create second column dcDataColumn = New DataColumn() With dcDataColumn .DataType = System.Type.GetType("System.String") .ColumnName = "ParentItem" .AutoIncrement = False .ColumnName = "Language" .ReadOnly = False .Unique = False End With ' Add the column to the table dtDataTable.Columns.Add(dcDataColumn) ' Make the ID column the primary key column Dim PrimaryKeyColumns(0) As DataColumn PrimaryKeyColumns(0) = dtDataTable.Columns("Sequence") dtDataTable.PrimaryKey = PrimaryKeyColumns dcDataRow = dtDataTable.NewRow() dcDataRow(1) = "VB .NET" dtDataTable.Rows.Add(dcDataRow) dcDataRow = dtDataTable.NewRow() dcDataRow(1) = "C#" dtDataTable.Rows.Add(dcDataRow) dcDataRow = dtDataTable.NewRow() dcDataRow(1) = "C++" dtDataTable.Rows.Add(dcDataRow) Dim dsDataSet As DataSet ' Instantiate the DataSet variable dsDataSet = New DataSet() ' Add the new DataTable to the DataSet dsDataSet.Tables.Add(dtDataTable)

How the Code Works

We first create a new reference variable named dtDataTable of type DataTable. The dtDataTable variable is a handle to our new but currently empty table.

' Create a new DataTable dtDataTable = New DataTable("tbLanguageTable") ' Declare a variable for the DataRow object Dim dcDataRow As DataRow

The new table is empty of any content. Let's add two columns to it. The first column, to be named Sequence, will hold an Integer type and will increment itself automatically. Because this DataColumn object increments itself, you want to make it read-only.

' Create new DataColumn, set DataType, ColumnName ' and add it to our new DataTable dcDataColumn = New DataColumn() With dcDataColumn .DataType = System.Type.GetType("System.Int32") .ColumnName = "Sequence" .ReadOnly = True .AutoIncrement = True End With ' Finally add the Column to the DataColumnCollection dtDataTable.Columns.Add(dcDataColumn)

Using a shorthand syntax, we can both add and define a column by writing this statement:

dtDataTable.Columns.Add("Sequence", _ Type.GetType("System.Int32"))

Of course, we still have to set the other properties, such as ReadOnly and AutoIncrement.

Next we create another column and set the primary key. Both of these tasks are self-explanatory. Now we're ready to add some data, so let's add three rows of data to the two columns we've defined. Because the Sequence field (field 0) is auto-incrementing and read-only, it will take care of itself. Using the NewRow method of our table object, we create a new row and add a language value in the second field (field 1) of each. Then we add each new row to the Rows collection of our table.


	The maximum number of rows that a DataTable object can store is 16,777,216.

Notice that adding a row to a DataTable by using the NewRow method returns a new DataRow object. The NewRow method returns a new row following the schema of our DataTable. The definition of the table's schema is contained in the table's DataColumnCollection. The DataColumnCollection is accessed through the Columns property.

dcDataRow = dtDataTable.NewRow() dcDataRow(1) = "VB .NET" dtDataTable.Rows.Add(dcDataRow) dcDataRow = dtDataTable.NewRow() dcDataRow(1) = "C#" dtDataTable.Rows.Add(dcDataRow) dcDataRow = dtDataTable.NewRow() dcDataRow(1) = "C++" dtDataTable.Rows.Add(dcDataRow)

Next we create a new data set and add our table to it. We learned how to do this in Chapter 10.

Dim dsDataSet As DataSet ' Instantiate the DataSet variable dsDataSet = New DataSet() ' Add the new DataTable to the DataSet dsDataSet.Tables.Add(dtDataTable)

Finding Specific Records

The way you find records in a data set is different in Visual Basic .NET than in classic ADO. We first define a string that specifies the records we want to find and then pass the string to the Select method of our table. The Select method returns an array of DataRow objects for each of the rows that contain the string we're looking for. The objects are in order of primary key (or lacking a primary key, in order of addition). After we have the records we are looking for, the values can be edited.

Dim sFind, sMessage As String Dim iCounter As Integer Dim foundRows() As DataRow 'an array of DataRows sFind = "Language = 'VB .NET'" foundRows = dtDataTable.Select(sFind) For iCounter = 0 To foundRows.GetUpperBound(0) sMessage = "Item: " & foundRows(iCounter).Item(1) foundRows(iCounter).Item(1) = "Visual Basic .NET" sMessage += " is now " & foundRows(iCounter).Item(1) Next MessageBox.Show(sMessage, "Finding Records", _ MessageBoxButtons.OK, MessageBoxIcon.Information)

In this code, we searched our table for a field that contains the string "VB .NET". In this case, only a single DataRow entry was placed in the foundRows array. We then grabbed the original value of "VB .NET" and changed it to "Visual Basic .NET". You can see the results in Figure 12-6.

Figure 12-6

Values of "VB .NET" are changed to "Visual Basic .NET".

We now want to find all records that contain the string "C++" and delete them. We again use the Select method and then use the Delete method on the DataRow objects we want to extinguish. After successfully deleting the record, we do another search to ensure that the record has been deleted.

sFind = "Language = 'C++'" foundRows = dtDataTable.Select(sFind) For iCounter = 0 To foundRows.GetUpperBound(0) sMessage = "Item: " & foundRows(iCounter).Item(1) & _ " found." & ctrlchrs.CrLf foundRows(iCounter).Delete() sMessage += "C++ was successfuly deleted" & ctrlchrs.CrLf Next foundRows = dtDataTable.Select(sFind) If foundRows.Length < 1 Then sMessage += "C++ not found" & ctrlchrs.CrLf End If MessageBox.Show(sMessage, "Finding Records", _ MessageBoxButtons.OK, MessageBoxIcon.Information)

Notice how the record was deleted from the array containing the specific DataRow object returned from the Select method. Our successful results are confirmed in Figure 12-7.

Figure 12-7

We can successfully find and delete records.