AddThis Social Bookmark Button

Print

ADO.NET, Part 2
Pages: 1, 2, 3, 4

The DataSet Class

The DataSet class encapsulates a set of tables and the relations between those tables. Figure 8-2 shows a class model diagram containing the DataSet and related classes. The DataSet is always completely disconnected from any data source. In fact, the DataSet has no knowledge of the source of its tables and relations. They may be dynamically created using methods on the DataSet, or they may be loaded from a data source. In the case of the SQL Server managed provider, a DataSet can be loaded from a SQL Server database using an SqlDataAdapter object. This is what was done in Example 8-1.

Figure 8-2. A class model diagram for the DataSet and related classes
Figure 2

After a DataSet is loaded, its data can be changed, added to, or deleted, all without affecting the data source. Indeed, a database connection does not need to be maintained during these updates. When ready, the updates can be written back to the database by establishing a new connection and calling the SqlDataAdapter object's Update method. Examples of writing updates to a database are shown later in this chapter.Navigating the DataSet



In this section you'll learn how to find specific data in a DataSet object, how to make changes to that data, and how to write those changes back to a database.

Finding Tables

The DataSet object's Tables property holds a TablesCollection object that contains the DataTable objects in the DataSet. The following code loops through all the tables in the DataSet and displays their names:

' Iterate through the tables in the DataSet ds.
Dim dt As DataTable
For Each dt In ds.Tables
   Console.WriteLine(dt.TableName)
Next

This code does the same thing, using a numeric index on the TablesCollection object:

' Iterate through the tables in the DataSet ds.
Dim n As Integer
For n = 0 To ds.Tables.Count - 1
   Console.WriteLine(ds.Tables(n).TableName)
Next

The TablesCollection object can also be indexed by table name. For example, if the DataSet ds contains a table named "Categories", this code gets a reference to it:

Dim dt As DataTable = ds.Tables("Categories")

Finding Rows

The DataTable object's Rows property holds a DataRowCollection object that in turn holds the table's DataRow objects. Each DataRow object holds the data for that particular row. The following code loops through all the rows in the DataTable and displays the value of the first column (column 0) in the row:

' Iterate through the rows.
Dim row As DataRow
For Each row In dt.Rows
   Console.WriteLine(row(0))
Next

This code does the same thing, using a numeric index on the RowsCollection object:

' Iterate through the rows.
Dim n As Integer
For n = 0 To dt.Rows.Count - 1
   Console.WriteLine(dt.Rows(n)(0))
Next

To assist with locating specific rows within a table, the DataTable class provides a method called Select. The Select method returns an array containing all the rows in the table that match the given criteria. The syntax of the Select method is:

Public Overloads Function Select( _
   ByVal filterExpression As String, _
   ByVal sort As String, _
   ByVal recordStates As System.Data.DataViewRowState _
) As System.Data.DataRow(  )

The parameters of the Select method are:

filterExpression
This parameter gives the criteria for selecting rows. It is a string that is in the same format as the WHERE clause in an SQL statement.

sort
This parameter specifies how the returned rows are to be sorted. It is a string that is in the same format as the ORDER BY clause in an SQL statement.

recordStates
This parameter specifies the versions of the records that are to be retrieved. Record versions are discussed in the later section "Changing, Adding, and Deleting Rows." The value passed in this parameter must be one of the values given by the System.Data.DataViewRowState enumeration. Its values are:

CurrentRows
Returns the current version of each row, regardless of whether it is unchanged, new, or modified.

Deleted
Returns only rows that have been deleted.

ModifiedCurrent
Returns only rows that have been modified. The values in the returned rows are the current values of the rows.

ModifiedOriginal
Returns only rows that have been modified. The values in the returned rows are the original values of the rows.

New
Returns only new rows.

None
Returns no rows.

OriginalRows
Returns only rows that were in the table prior to any modifications. The values in the returned rows are the original values.

Unchanged
Returns only unchanged rows.

These values can be combined using the And operator to achieve combined results. For example, to retrieve both modified and new rows, pass this value:

DataViewRowState.ModifiedCurrent And DataViewRowState.New

The return value of the Select method is an array of DataRow objects.

The Select method is overloaded. It has a two-parameter version that is the same as the full version, except that it does not take a recordStates parameter:

Public Overloads Function Select( _
   ByVal filterExpression As String, _
   ByVal sort As String _
) As System.Data.DataRow(  )

Calling this version of the Select method is the same as calling the full version with a recordStates value of DataViewRowState.CurrentRows.

Similarly, there is a one-parameter version that takes only a filterExpression:

Public Overloads Function Select( _
   ByVal filterExpression As String _
   ) As System.Data.DataRow(  )

This is the same as calling the three-parameter version with sort equal to "" (the empty string) and recordStates equal to DataViewRowState.CurrentRows.

Lastly, there is the parameterless version of Select:

Public Overloads Function Select(  ) As System.Data.DataRow(  )

This is the same as calling the three-parameter version with filterExpression and sort equal to "" (the empty string) and recordStates equal to DataViewRowState.CurrentRows.

As an example of using the Select method, this line of code returns all rows whose Country column contains the value "Mexico":

Dim rows(  ) As DataRow = dt.Select("Country = 'Mexico'")

Because the sort and recordStates parameters were not specified, they default to "" (the empty string) and DataViewRowState.CurrentRows, respectively.

The Select method versus the SQL SELECT statement

If an application is communicating with a database over a fast, persistent connection, it is more efficient to issue SQL SELECT statements that load the DataSet with only the desired records, rather than to load the DataSet with a large amount of data and then pare it down with the DataTable's Select method. The Select method is useful for distributed applications that might not have a fast connection to the database. Such an application might load a large amount of data from the database into a DataSet object, then use several calls to the DataTable's Select method to locally view and process the data in a variety of ways. This is more efficient in this case because the data is moved across the slow connection only once, rather than once for each query.

Pages: 1, 2, 3, 4

Next Pagearrow