Finding Column Values
The DataRow class has an Item property that provides access to the value in each column of a row. For example, this code iterates through all the columns of a row, displaying the value from each column (assume that
row holds a reference to a DataRow object):
' Iterate through the column values.
Dim n As Integer
For n = 0 To row.Table.Columns.Count - 1
Note the expression used to find the number of columns:
row.Table.Columns.Count. The DataRow object's Table property holds a reference to the DataTable object of which the row is a part. As will be discussed shortly, the Table object's Columns property maintains a collection of column definitions for the table. The Count property of this collection gives the number of columns in the table and therefore in each row.
The DataRow object's Item property is overloaded to allow a specific column value to be accessed by column name. The following code assumes that the DataRow
row contains a column named "Description". The code displays the value of this column in this row:
Finding Column Definitions
The DataTable object's Columns property holds a ColumnsCollection object that in turn holds the definitions for the columns in the table. The following code iterates through the columns in the table and displays their names:
' Iterate through the columns.
Dim column As DataColumn
For Each column In dt.Columns
This code does the same thing, using a numeric index on the ColumnsCollection object:
' Iterate through the columns.
Dim n As Integer
For n = 0 To dt.Columns.Count - 1
The ColumnsCollection object can also be indexed by column name. For example, if DataTable
dt contains a column named "Description", this code gets a reference to the associated DataColumn object:
Dim column As DataColumn = dt.Columns("Description")
To change data in a DataSet, first navigate to a row of interest and then assign new values to one or more of its columns. For example, the following line of code assumes that
row is a DataRow object that contains a column named "Description". The code sets the value of the column in this row to be "Milk and cheese":
row("Description") = "Milk and cheese"
Adding a new row to a table in a DataSet is a three-step process:
- Use the DataTable class's NewRow method to create a new DataRow. The method takes no parameters.
- Set the values of the columns in the row.
- Add the new row to the table.
For example, assuming that
dt is a DataTable object, and that the table has columns named "CategoryName" and "Description", this code adds a new row to the table:
' Add a row.
Dim row As DataRow = dt.NewRow( )
row("CategoryName") = "Software"
row("Description") = "Fine code and binaries"
The DataRow object referenced by
row in this code can be indexed by the names "CategoryName" and "Description" because the DataRow object was created by the DataTable object's NewRow method and so has the same schema as the table. Note that the NewRow method does not add the row to the table. Adding the new row to the table must be done explicitly by calling the DataRowCollection class's Add method through the DataTable class's Rows property.
Deleting a row from a table is a one-liner. Assuming that
row is a reference to a DataRow, this line deletes the row from its table:
When changes are made to a row, the DataRow object keeps track of more than just the new column values. It also keeps track of the row's original column values and the fact that the row has been changed. The Item property of the DataRow object is overloaded to allow you to specify the desired version of the data that you wish to retrieve. The syntax of this overload is:
Public Overloads ReadOnly Property Item( _
ByVal columnName As String, _
ByVal version As System.Data.DataRowVersion _
) As Object
The parameters are:
- The name of the column whose value is to be retrieved.
- The version of the data to retrieve. This value must be a member of the System.Data.DataRowVersion enumeration. Its values are:
- Retrieve the current (changed) version.
- Retrieve the current version if the data has been changed, the original version if not.
- Retrieve the original (unchanged) version.
- Retrieve the proposed change. Proposed changes are changes that are made after a call to a DataRow object's BeginEdit method but before a call to its EndEdit or CancelEdit methods.
For example, after making some changes in DataRow
row, the following line displays the original version of the row's Description column:
The current value of the row would be displayed using any of the following lines:
Calling the DataSet object's AcceptChanges method commits outstanding changes. Calling the DataSet object's RejectChanges method rolls records back to their original versions.
TIP: The code shown in this section affects only the DataSet object, not the data source. To propagate these changes, additions, and deletions back to the data source, use the Update method of the SqlDataAdapter class, as described in the next section, "Writing Updates Back to the Data Source."
If there are relations defined between the DataTables in the DataSet, it may be necessary to call the DataRow object's BeginEdit method before making changes.