Populating and Manipulating the DataSet

Populating and Manipulating the DataSet

There are three ways to fill a DataTable in a DataSet with data:

1. Programmatically by directly defining metadata and inserting data

2. Using a DataAdapter to query a data source

3. Loading an XML document

In this chapter we present the first method, that of directly defining the schema and loading the data. In Chapter 6 we discuss the second method, that of using the DataAdapter. In Chapter 10 we cover the third method, that of filling a DataTable with data from an XML document. We begin in this section by focusing on populating the DataSet programmatically, including basic DataSet and DataTable access and functionality.

Note, however, that once the DataSet has been loaded with data, the method used doesn't matter. All subsequent operations involving this data and its manipulation are done identically.

Defining DataTable Schemas

Once again, we work with you to build a simple form to illustrate the concepts introduced. Begin by following these steps.

1. Launch Visual Studio.NET.

2. Create a new Visual Basic Windows Application project.

3. Name the project DataSetCode.

4. Specify a path for saving the project files.

5. Enlarge the size of Form1.

6. In the Properties window for Form1, set its Name property to frmDataSets and its Text property to DataSets.

7. In the upper-left corner of the form, add a button from the Windows Forms tab of the Toolbox.

8. In the Properties window, set the Name property of the button to btnCreateDS and set the Text property to Create DataSet.

9. From the Windows Forms tab of the Toolbox, add a listbox to frmDataSets and place it on the right side of the form.

10. In the Properties window, set the Name property of the listbox to lstOutput.

11. Enlarge the listbox so that it covers about 80 percent of the area of the form.

The first piece of code that you need to add to the top of the file is

 Imports System  Imports System.Data 

Then the following code goes within the body of the class definition for frmDataSets:

 Private dsEmployeeInfo As DataSet  Private Sub btnCreateDS_Click(ByVal sender As System.Object,   ByVal e As System.EventArgs) Handles btnCreateDS.Click      CreateDataSet()      AddData()      DisplayDataSet() End Sub 

The event handler subroutine btnCreateDS_Click will call three routines, one for each of the three phases of this application. The variable dsEmployeeInfo is a DataSet accessed by each of the subroutines called by btnCreateDS_Click.

Note

Although you normally use a DataSet to contain the DataTables that you are using, as here, you could alternatively just define and use DataTable (and subsidiary) objects on their own. You might do so with simple uses of a DataTable when you don't need to link multiple tables.

The first thing you need to do is to define the schema, or structure, for each table that you want to use. That consists of defining a DataColumn object and setting its properties for each of the columns in the table. The code for CreateDataSet is shown in Listing 5.1.

Listing 5.1 Code to create DataSet and DataTable schemas

 Private Sub CreateDataSet()   'Create a "EmployeeInfo" DataSet   dsEmployeeInfo = New DataSet()   'Create an "Employees" Table   Dim dtEmployees As DataTable = New _     DataTable("Employees")   dtEmployees.CaseSensitive = False   dtEmployees.Columns.Add("FirstName", _     Type.GetType("System.String"))   dtEmployees.Columns.Add("LastName", _     Type.GetType("System.String"))   dtEmployees.Columns.Add("DepartmentID", _     Type.GetType("System.Int32"))   'Add Employees table to EmployeeInfo DataSet   dsEmployeeInfo.Tables.Add(dtEmployees)   ' Create an "Departments" Table   ' We'll do this one with different function overrides   ' This approach is more lengthy for standard columns,   ' but allows setting other column properties (e.g.   ' ReadOnly & Unique) before adding the DataColumn to   ' the Columns collection   Dim dtDepartments As DataTable   dtDepartments = New DataTable()   dtDepartments.TableName = "Departments"   dtDepartments.MinimumCapacity = 5   dtDepartments.CaseSensitive = False   Dim NewColumn As New DataColumn()   With NewColumn     .ColumnName = "ID"     .DataType = Type.GetType("System.Int32")     .ReadOnly = True     .Unique = True     .AutoIncrement = True   End With   dtDepartments.Columns.Add(NewColumn)   NewColumn = New DataColumn()   With NewColumn     .ColumnName = "DepartmentName"     .DataType = Type.GetType("System.String")     .Unique = True     .AllowDBNull = False   End With   dtDepartments.Columns.Add(NewColumn)   'Add Departments table to EmployeeInfo DataSet   dsEmployeeInfo.Tables.Add(dtDepartments) End Sub 

After an instance of the DataSet dsEmployeeInfo is created, we create the Employees table by using one of the overloaded constructors of the DataTable and passing in the table name. We then set the DataTable's CaseSensitive property. This property determines whether the sorting, searching, filtering, and other operations of the DataTable are performed in a case-sensitive manner. By default, this value is set to the parent DataSet object's CaseSensitive property, or to False if the DataTable was created in de pen dently of a DataSet.

Note

The CaseSensitive property applies only to the data in the DataTable object. It doesn't affect the case-sensitivity rules applied to DataTable objects themselves. A DataSet may contain two or more tables (or relations) that have the same name but that differ in case, such as mytable and Mytable. When that occurs, references to the tables must match exactly (case-sensitive search). However, if there is only one such table, any reference to that name will succeed (case-insensitive search).

We then add three column definitions to the dtEmployees DataTable by supplying the Columns Add method with the column name and data type. Note that the data type specified is the .NET data type and not some database data type. If we don't supply a data type, the column's type defaults to a string. The Employees table is then added to the dsEmployeeInfo DataSet.

We repeat the process for the Departments table, only this time we use different constructor and function overloads. They achieve the same goal in a different way. You can choose an approach based on personal taste, company standards, or the specifics of the task.

The MinimumCapacity property of the dtDepartments DataTable is set to 5, specifying that the DataTable instance should start by internally creating 5 rows. Setting this to a value other than the default of 25 allows you to influence how the required resources are allocated and may optimize performance in critical situations. Of course, these rows don't actually exist insofar as the user of the DataTable is concerned until rows are actually added to the DataTable.

For these columns, we also set various column properties before adding them to schema defined by the Columns collection. Properties such as ReadOnly, Unique, AllowDBNull, and Auto-Increment should be familiar to you if you've had any experience building database applications.

• ReadOnly = True indicates that the column's value cannot be modified.

• Unique = True indicates that each of the values of this column in all the rows in the table must be unique. This property is implemented by having a UniqueConstraint automatically created for the column. We discuss this method in the Table Constraints section later in this chapter.

• AllowDBNull = True indicates that null values are allowed for this column.

• AutoIncrement = True indicates that the column's value is incremented each time a new row is added to the table. You can specify the starting value and increment step with the AutoIncrementSeed and AutoIncrementStep properties, respectively.

Note

The DataTable will accept a row with a value assigned for a column with AutoIncrement set to True and will use the AutoIncrement value only if the column's value is different from its default value.

Other common DataColumn properties include MaxLength (for columns of type String), DefaultValue, and Table, which returns a reference to the DataTable to which the column belongs.

A column can also be defined to have an expression that is used to calculate the column's value, create an aggregate column, or filter rows. This expression can consist of column values from the current or other rows, constants, operators, wildcard characters, aggregate functions, or other expression functions. For more information and examples of column expressions, see the help topic for the DataColumn Expression property.

Adding Data to a DataTable

Once you have defined a DataTable and its schema, you can start adding rows of data. The code in Listing 5.2, which you should add to frmDataSets, shows how to add data rows programmatically to a DataTable. The subroutine AddData adds four rows of data to the Departments tables and three rows of data to the Employees table in a three-step process.

1. Create a new instance of a DataRow for the desired table by calling the NewRow method of that DataTable.

2. Assign values to columns of that row.

3. Add the row to the Rows collection of the table by passing the DataRow object to the Add method of the table's Rows property.

Listing 5.2 Code to add data programmatically to a DataTable

 Private Sub AddData()   Dim dtDepartments As DataTable = _     dsEmployeeInfo.Tables("Departments")   Dim dtEmployees As DataTable = _     dsEmployeeInfo.Tables("Employees")   'Add 4 records to Departments table   Dim rowDept As DataRow   rowDept = dtDepartments.NewRow   rowDept("DepartmentName") = "Administration"   dtDepartments.Rows.Add(rowDept)   rowDept = dtDepartments.NewRow   rowDept("DepartmentName") = "Engineering"   dtDepartments.Rows.Add(rowDept)   rowDept = dtDepartments.NewRow   rowDept("DepartmentName") = "Sales"   dtDepartments.Rows.Add(rowDept)   rowDept = dtDepartments.NewRow   rowDept("DepartmentName") = "Marketing"   dtDepartments.Rows.Add(rowDept)   'Add 3 records to the Employees table   Dim rowEmployee As DataRow   rowEmployee = dtEmployees.NewRow   rowEmployee("FirstName") = "Jackie"   rowEmployee("LastName") = "Goldstein"   rowEmployee("DepartmentID") = 2   dtEmployees.Rows.Add(rowEmployee)   rowEmployee = dtEmployees.NewRow   rowEmployee("FirstName") = "Jeffrey"   rowEmployee("LastName") = "McManus"   rowEmployee("DepartmentID") = 3   dtEmployees.Rows.Add(rowEmployee)   rowEmployee = dtEmployees.NewRow   rowEmployee("FirstName") = "Sam"   rowEmployee("LastName") = "Johnson"   rowEmployee("DepartmentID") = 3   dtEmployees.Rows.Add(rowEmployee) End Sub 

Note

You can also add a new row of data to a table by passing the Add method an array (of Objects) containing the data in the order of the columns in the table definition. This approach to add the last employee in Listing 5.2 would then look like:

 Dim empData(2) As Object  empData(0) = "Sam" empData(1) = "Johnson" empData(2) = 3 dtEmployees.Rows.Add(empData) 

Updating the DataSet

To update an individual row in a table, simply access the desired row and assign a new value to one of its columns. If you wanted to change the department to which Sam Johnson belongs, you would write

   dtEmployees.Rows(2) ("DepartmentID") = 2 

Note

In this line of code the row specifier (2) is hard-coded, taking advantage of the fact that the order of the rows in the table is known. This practice isn't a particularly good one, and we show a much better way to find a specific row (or rows) in the section Accessing Data from a DataTable.

You can make as many changes as you want, but they all are pending until the AcceptChanges method is called and commits the changes. There is also a RejectChanges method than cancels and rolls back any changes made since the data was loaded or AcceptChanges was last called.

Note

The AcceptChanges (and RejectChanges) method is available at several different levels. The DataTable, DataSet, and DataRow classes all support this method. A call to the DataSet's AcceptChanges will cause AcceptChanges to be called on each table in the DataSet. A call to the AcceptChanges of a DataTable will cause AcceptChanges to be called on each of the rows in that table. Thus you can commit any changes row by row by calling AcceptChanges for each row individually, or in one fell swoop with a single call to AcceptChanges on the DataSet containing the data. The same holds for the RejectChanges method.

Of course, entire rows may be added or deleted. We have already shown you how to add rows. One way to delete a row is to call the Remove method of the DataRowCollection object (that is, the Rows property of the DataTable object). This method actually removes the row from the collection. Another way is to call the Delete method of a specific DataRow object. This method marks the row for deletion, which actually takes place when AcceptChanges is subsequently called.

Once the Remove method has been called, all data for that row is lost. Even if RejectChanges is subsequently called, the removed row won't be returned.

Row States and Versions

Every DataRow object has a property RowState that indicates the current state, or status, of the row. In addition, each row maintains up to four different versions of the values for that row. As different editing operations are performed on the row, its state and/or value versions will change. The RowState enumeration is summarized in Table 5.1, and the DataRowVersion enumeration is summarized in Table 5.2.

Table 5.1. The RowState Enumeration

Enumeration Member Name	Description
Unchanged	No changes have been made since the last call to AcceptChanges or since the row was originally filled by a DataAdapter.
Added	The row has been added to a DataRowCollection (that is, the Rows property of DataTable), but AcceptChanges hasn't been called.
Deleted	The Delete method has been called to delete the row, but AcceptChanges hasn't been called.
Modified	The row has been modified, but AcceptChanges hasn't been called.
Detached	The row has been created but hasn't been added to a DataRowCollection or the Remove method has been called to remove the row from a DataRowCollection or the Delete method has been called to delete the row and AcceptChanges has been called.

Table 5.2. The DataRowVersion Enumeration

Enumeration Member Name	Description
Original	The original values for the row. This version doesn't exist for a row whose RowState is Added.
Current	The current (possibly modified) values for the row. This version doesn't exist for a row whose RowState is Deleted.
Default	The default row version for the row, which depends on the rows current RowState. If the RowState is Deleted, the default row version is Original. If the RowState is Detached, the default row version is Proposed. Otherwise, the default row version is Current.
Proposed	The proposed values for the row. This version exists only during an edit operation (begun by calling BeginEdit and ended by calling either EndEdit or CancelEdit) or for a row that hasn't been added to a DataRowCollection.

If a row's RowState is Deleted when AcceptChanges method is called, the row is removed from the DataRowCollection. Otherwise, the Original row version is updated with the Current row version and the RowState becomes Unchanged.

Conversely, if a row's RowState is Added when RejectChanges is called, the row is removed from the DataRowCollection. Otherwise, the Current row version is updated with the Original row version and the RowState becomes Unchanged.

Note

As not all four row versions are available in all situations, you can call the HasVersion method of the DataRow to check on whether a specific version is available in the current state. It is passed one of the four members of the DataRowVersion enumeration and returns a Boolean value, indicating whether the specified version currently exists.

A few comments are in order regarding the Proposed version of a DataRow. When you call the BeginEdit method of a DataRow, the normal actions and events are suspended, allowing the user to make multiple changes to the row without causing the execution of validation rules. While in this mode, changes made are not reflected in the Current version of the row. Instead, they are reflected in the Proposed version of the row. Once the EndEdit method has been called, the Proposed values are transferred to the Current values. Any changes can be canceled by calling CancelEdit before EndEdit. Note that none of the changes are permanently committed until AcceptChanges is called.

Note

You can always access a specific version of a DataRow column (assuming that it exists) by specifying the desired version as a second parameter to the DataRow Item method. That's true whether you call the Item method explicitly or implicitly, as in

 dtEmployees.Rows(2).Item ("lastname", DataRowVersion.Proposed)  

or

 dtEmployees.Rows(2)("lastname", DataRowVersion.Original)  

Row and Column Errors

ADO.NET provides a flexible mechanism for defining and handling user-defined errors for the rows and columns of a DataTable. This mechanism permits application-defined validation of data. It allows the flagging of errors when they are detected but postpones resolution of these errors until a later point in the application's workflow. (Don't confuse these errors with the regular system-defined run-time errors (exceptions) handled by the .NET Frameworks standard exception handling mechanism, Try-Catch-Finally.)

When an application detects a validation error, it flags the error by setting an error description for a row or an individual column. Setting a DataRow's RowError property indicates that the particular row contains an error, such as:

 myDataRow.RowError = "Something wrong  here"  

Calling the SetColumnError method of a DataRow indicates an error in a specific column, such as:

 myDataRow.SetColumnError (2, "Bad data in this column")  

You can retrieve the error strings for a row or column by accessing the RowError property or by calling the GetColumnError, respectively. You can clear these errors by setting the respective error strings to an empty string (""). Or you can do so by calling a DataRow's ClearErrors method, which clears both the RowError property and all errors that were set by calling SetColumnError.

The DataRow also has a property HasErrors that is True if the row currently has any errors (either at the row or column level). This property value is reflected up to the table and DataSet levels if HasErrors is True for any row in a table, the HasErrors property of the table is also True. Similarly, if the HasErrors property of any table in a DataSet is true, the HasErrors property of the DataSet is also True. The DataTable's GetErrors method returns an array of DataRow objects that have errors. It provides a simple mechanism to determine quickly whether any validation errors exist and, if so, where as shown in Listing 5.3.

Listing 5.3 Locating errors in all the tables of a DataSet

 Private Sub ResolveErrors (myDataSet as DataSet)   Dim rowsWithErrors() As DataRow   Dim myTable As DataTable   Dim myCol As DataColumn   Dim currRow As Integer   For Each myTable In myDataSet.Tables     If myTable.HasErrors Then       ' Get all rows that have errors.       rowsWithErrors = myTable.GetErrors()       For currRow = 0 To rowsWithErrors.GetUpperBound(0)         For Each myCol In myTable.Columns           ' Find columns with errors and decide           ' how to deal with it.           ' A column's error is retrieved with:           ' rowsWithErrors (currRow).GetColumnError(myCol)         Next         ' Clear the row's errors         rowsWithErrors (currRow).ClearErrors       Next currRow     End If   Next End Sub 

Accessing Data from a DataTable

Because a DataSet and the DataTables that it contains are always fully populated and not connected to a data source, the method of accessing data records is very different from that of ADO and other previous data access models (such as ODBC, DAO, and RDO). As all the data is available simultaneously, there is no concept of a current record. That in turn implies that there are no properties or methods to move from one record to another. Each DataTable has a Rows property, which is a collection of DataRow objects. Individual DataRow objects are accessed with an index or the For Each statement. Thus the ADO.NET objects offer a simpler, easier, and more efficient array-like approach to navigating and accessing data records.

Listing 5.4 shows the subroutine DisplayDataSet, which displays the contents of the tables that you previously defined and loaded with data. It uses the preferred approach of looping through all elements of a collection in this case the Rows and Columns collections for displaying the Employees table. It uses the alternative method of accessing the row and column elements with a numeric index to display the Department tables.

Listing 5.4 Code to display the data in DataTables

 Private Sub DisplayDataSet()   Dim dr As DataRow   Dim dc As DataColumn   Me.lstOutput.Items.Add("DISPLAY DATASET")   Me.lstOutput.Items.Add("===============")   ' Display data in Employees table   For Each dr In dsEmployeeInfo.Tables("Employees").Rows     For Each dc In _       dsEmployeeInfo.Tables("Employees").Columns       Me.lstOutput.Items.Add( _         dc.ColumnName & " : " & dr(dc))     Next     Me.lstOutput.Items.Add(" ")   Next   Me.lstOutput.Items.Add("")   ' Display data in Departments table   ' Show how to use index, instead of For Each   Dim row As Integer   Dim col As Integer   For row = 0 To _     dsEmployeeInfo.Tables("Departments").Rows.Count 1     For col = 0 To _     dsEmployeeInfo. Tables("Departments").Columns.Count 1       Me.lstOutput.Items.Add( _       dsEmployeeInfo.Tables("Departments").         Columns(col).ColumnName _         & " : " & _         dsEmployeeInfo.Tables("Departments").       Rows(row)(col))       Next col     Me.lstOutput.Items.Add(" -")   Next row End Sub 

You can write the entire subroutine more generically by using generic loops not only for the rows and columns, but also for the tables in the DataSet. Listing 5.5 shows this approach.

Listing 5.5 A generic implementation of DisplayDataSet

 Private Sub DisplayDataSet(ByVal ds As DataSet)      ' Generic routine to display the contents of a DataSet      ' DataSet to be displayed is passed as parameter      Dim dt As DataTable      Dim dr As DataRow      Dim dc As DataColumn      Me.lstOutput.Items.Add("DISPLAY DATASET")      Me.lstOutput.Items.Add("===============")      ' For Each dt In ds.Tables      Me.lstOutput.Items.Add("")      Me.lstOutput.Items.Add("TABLE: " & dt.TableName)      Me.lstOutput.Items.Add(" ")      For Each dr In dt.Rows        For Each dc In dt.Columns          Me.lstOutput.Items.Add( _            dc.ColumnName & " : " & dr(dc))        Next        Me.lstOutput.Items.Add(" -")     Next   Next dt End Sub 

Note the overloading of the DisplayDataSet subroutine where we have an identically named routine with a different signature. Our generic version accepts as a parameter the DataSet to be displayed.

With all the pieces in place, you can now run the DataSetCode project. When you click on the Create DataSet button, the DataSet and tables will be created, filled, and displayed. The resulting output is shown in Figure 5.2.

Figure 5.2. Results of creating, filling, and displaying the Employees and Departments tables

Note

To test the generic version of DisplayDataSet from btnCreateDS_Click, add the ds parameter to the invocation of DisplayDataSet in btnCreateDS_Click:

 DisplayDataSet(dsEmployeeInfo)  

Finding, Filtering, and Sorting Rows

At times you won't be interested in all the available rows of a DataTable. You might be interested in one specific row or in a particular subset of the available rows. Two mechanisms let you be selective: the Find and the Select methods.

The Find method is a method of the Rows property of the DataTable object that is, of the DataRowCollection object. This method is used to find and return a single row specified by a value of the primary key of the table.

Before you can use Find to locate a specific row in the Departments table defined in Listing 5.1, you must define a primary key for the table. You do so by assigning one or more table columns to the table's PrimaryKey property. (Even if the primary key is only a single column, the Primary-Key property is an array of DataColumn objects.)

The following lines of code, which you can add to the end of the CreateDataSet procedure of Listing 5.1, define the DepartmentName column as the primary key for the Departments table:

 Dim pk(0) As DataColumn  pk(0) = dtDepartments.Columns("DepartmentName") dtDepartments.PrimaryKey = pk 

Note

When a single column defines the PrimaryKey for a DataTable, the AllowDBNull property of the column is automatically set to False and the Unique property of the column is automatically set to True. If the PrimaryKey comprises several columns, only the AllowDBNull property of the columns is set to False.

Once you've defined a primary key, using the Find method is straightforward:

 Dim desiredRow As DataRow  desiredRow = dtDepartments.Rows.Find("sales") 

The variable desiredRow will be set to the DataRow with the corresponding primary key value, or it will be set to Nothing if no such row is found.

If a table's primary key comprises more than one column, the desired values for each of the primary key columns are passed as elements of an array (typed as Object) to the Find method:

 ' Set Primary Key  Dim pk(0) As DataColumn pk(0) = dtEmployees.Columns("FirstName") pk(1) = dtEmployees.Columns("LastName") dtEmployees.PrimaryKey = pk ' Try to Find desired data Dim desiredRow as DataRow Dim desiredValues (1) as object desiredValues(0) ="Sam" desiredValues(1) ="Johnson" desiredRow = dtEmployees.Rows.Find (desiredValues) 

The Select method of the DataTable returns an array of DataRow objects. The rows returned may match a filter criterion, sort order and/or a state specification (DataViewRowState).

The following lines of code return and display the first names of all of the employees whose last name is "Johnson."

 Dim selectedRows() As DataRow  selectedRows = dtEmployees.Select("LastName = 'Johnson'") Dim i As integer For i = 0 To selectedRows.GetUpperBound(0)   MessageBox.Show(selectedRows(i)("FirstName")) Next 

If you also wanted to have the returned rows sorted by first name is descending order, you could modify the call to the Select method:

 selectedRows = dtEmployees.Select( _    "LastName = 'Johnson'", "FirstName DESC") 

Finally, specifying a state as an argument to the Select method allows you to retrieve specific versions of rows from the table when you're in the midst of editing. For example, retrieving all the original values of rows, despite the fact that many changes have been made (but Accept-Changes hasn't yet been called), is done by specifying the OriginalRows row state in the Select method:

 selectedRows = dtEmployees.Select(Nothing, Nothing, _       DataViewRowState.OriginalRows) 

To select the newly added rows that have a last name of "Johnson", use

 selectedRows = dtEmployees.Select("LastName = 'Johnson'", _         Nothing, DataViewRowState.Added) 

If you also want to sort them by first name, as before, use

 selectedRows = dtEmployees.Select("LastName = 'Johnson'", _    "FirstName DESC", DataViewRowState.Added) 

The options that can be specified for the row state are shown in Table 5.3, which describes the members of the DataViewRowState enumeration. References to modifications are to changes made since the table was last loaded or AcceptChanges was called.

Table 5.3. The DataViewRowState Enumeration

Enumeration Member Name	Description
Added	New rows that have been added
CurrentRows	All current rows (including new, modified, and unchanged rows)
Deleted	All rows that have been marked as deleted
ModifiedCurrent	The current versions of rows that have been modified
ModifiedOriginal	The original versions of rows that have been modified
None	None
OriginalRows	All original rows (including unchanged and deleted rows, but not new rows)
Unchanged	All rows that have not been modified

Table Relations

Because a DataSet can contain multiple tables, it is only natural to expect (at least if you've had some exposure to relational databases) that you can create links, or relations, between those tables. In ADO.NET, the DataRelation object provides this functionality.

A DataRelation link relates columns in two tables that have a Parent-Child or primary key-foreign key relationship. The classic example of such a relationship is customers and orders, whereby a customer record is related to one or more order records. The customer record is the parent, and the order(s) is(are) the child(ren). We pursue this topic by using the example we started with earlier the Department (parent) and Employees (child) tables defined in our DataSet.

The DataRelation object supports two different functions.

• It allows navigation between the related tables by making available the records that are related to a record you're working with. If you're working with a parent record, the DataRelation provides the child records. If you're working with a child record, it provides its parent record.

• It can enforce referential integrity rules, such as cascading changes to related tables, when performing operations on records in either table.

Continue with the form frmDataSets that you prepared earlier.

1. Add a button immediately below the btnCreateDS button from the Windows Forms tab of the Toolbox.

2. In the Properties window, set the Name property of the button to btnCreateRelations and set the Text property to Create Relations.

3. Add the code shown in Listing 5.6.

Listing 5.6 Code to create and display table relations

 Private Sub btnCreateRelations_Click( _      ByVal sender As System.Object, _      ByVal e As System.EventArgs) _      Handles btnCreateRelations.Click    Dim rel As DataRelation    CreateDataSet()    ' Create the relation between the Departments and    ' Employees tables    rel = dsEmployeeInfo.Relations.Add( _      "relDepartmentEmployees", _        dsEmployeeInfo.Tables("Departments"). Columns("ID"), _        dsEmployeeInfo.Tables("Employees").Columns("DepartmentID"))        DisplayRelations(dsEmployeeInfo)   End Sub   Private Sub DisplayRelations(ByVal ds As DataSet)     Dim rel As DataRelation     ' Print the names of each column in each table through     ' the Relations.     Me.lstOutput.Items.Add("")     Me.lstOutput.Items.Add("DISPLAY RELATIONS")     For Each rel In ds.Relations       ' Display Relation Name       Me.lstOutput.Items.Add("NAME: " & rel.RelationName)       ' Show Parent table & field       Me.lstOutput.Items.Add("PARENT: " & _         rel.ParentTable.ToString & " " & _         rel.ParentColumns(0).ColumnName)       ' Show Child table & field       Me.lstOutput.Items.Add("CHILD: " & _         rel.ChildTable.ToString & " " & _         rel.ChildColumns(0).ColumnName)   Next   Me.lstOutput.Items.Add("") End Sub 

The first thing you need to do is to create the appropriate DataRelation object. Every DataSet has a collection of Relations that is exposed as its Relations property. This property is of type DataRelationCollection and supports several overloaded forms of the Add method. The form used in Listing 5.6 takes three arguments a name for the relation, a reference to a DataColumn in the parent table, and a reference to a DataColumn in the child table. If the relation between the tables comprised more than one column, a different form of the Add method could be called with arguments that were arrays of DataColumn objects.

The DisplayRelations procedure simply loops across the relations in the Relations property of the DataSet that it receives as an argument. For each relation that exists, the name of the relation, the name of the parent table and column, and the name of the child table and column are displayed.

Note

To make DisplayRelations more generic, you could add code to loop across all the columns in the ParentColumns and ChildColumns array properties, rather than just displaying the first element as you've done here.

When you run the DataSetCode project and click on the Create Relations button, the listbox should display the specifics of the relation created between the Employees and Departments tables.

In addition to the Relations collection of the DataSet, which contains all the relations defined between tables in that DataSet, each DataTable has two collections of relations (properties): ParentRelations and ChildRelations, which contain the relations between the DataTable and related parent and child tables, respectively.

Now that you can access the relation definitions between tables, you can also navigate the tables and actually retrieve the related data. Begin by adding another button and code to the form frmDataSets we prepared earlier.

1. Add a button immediately below the btnCreateRelations button from the Windows Forms tab of the Toolbox.

2. In the Properties window, set the Name property of the button to btnChildRows and set the Text property to Child Rows.

3. Add the code shown in Listing 5.7.

Listing 5.7 Code to display parent and child data from related tables

 Private Sub btnChildRows_Click (ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnChildRows.Click     Dim rel As DataRelation     CreateDataSet ()     AddData ()     'Create the relation between the Departments and     'Employees tables     rel = dsEmployeeInfo.Relations.Add( _       ("relDepartmentEmployees", _       dsEmployeeInfo.Tables("Departments").Columns("ID"), _       dsEmployeeInfo.Tables("Employees").Columns("DepartmentID"))   DisplayChildRows(dsEmployeeInfo.Tables("Departments")) End Sub Private Sub DisplayChildRows(ByVal dt As DataTable)   Dim rel As DataRelation   Dim relatedRows () As DataRow   Dim row As DataRow   Dim col As DataColumn   Dim i As Integer   Dim rowData As String   Me.lstOutput.Items.Add ("")   Me.lstOutput.Items.Add ("CHILD ROWS")   For Each row In dt.Rows     For Each rel In dt.ChildRelations       Me.lstOutput.Items.Add( _         dt.TableName & ":" & _         rel.ParentColumns(0).ColumnName & _         "= " & row(rel.ParentColumns(0).ToString))       relatedRows = row.GetChildRows(rel)       'Print values of rows.       For i = 0 To relatedRows.GetUpperBound(0)         rowData = "****" & _           rel.ChildTable.TableName & ":"         For Each col In rel.ChildTable.Columns           rowData = rowData & "" & _             relatedRows(i)(col.ToString)         Next col         Me.lstOutput.Items.Add(rowData)       Next i     Next rel   Next row End Sub 

The button click handler, btnChildRows_Click, first creates the DataSet and DataTables by calling CreateDataSet (shown previously in Listing 5.1) and then calls AddData (shown previously in Listing 5.2) to fill the tables with data. It then creates the relation between the Employees and Departments tables, using the line of code from the btnCreateRelations_Click procedure, shown previously in Listing 5.6. Finally, DisplayChildRows is called, passing the Departments table as the parent table.

DisplayChildRows implements a triple-nested loop to display all the columns of data in each related table (in this case only one) for each row in the parent table. For each row in the parent table passed in as an argument, it goes through all the relations defined in the table's ChildRelations property, displays the table's name, the column name in the parent table, and the value of that column in the current row. The row's GetChildRows method is called, with the current relation as an argument, and an array of DataRows is returned with the appropriate child rows. For each of these rows, all the column values are displayed, prefixed by asterisks and the child table name.

Note

Some versions of GetChildRows accept an additional argument defining which version of the rows to return (as defined in the DataRowVersion enumeration shown in Table 5.2). Equivalent methods exist for getting the parent row or rows of a given child row.

You may question why the preceding statement refers to parent rows (plural). How can a child have more than a single parent? The answer is that, although a relation normally will define a single parent for each child row (unique parent column values), it also allows for defining nonunique parent columns and therefore a set of methods for retrieving multiple parent rows (GetParentRows), rather than a single parent row (GetParentRow).

When you run the DataSetCode project and click on the Child Rows button, the child rows in the Employees table for each of the parent rows in the Department tables are displayed in the listbox, as shown in Figure 5.3.

Figure 5.3. Results of displaying parent and related child rows of the Departments and Employees tables

Table Constraints

Constraints are rules used to enforce certain restrictions on one or more columns of a table. The purpose of these rules is to ensure the integrity of the data in the table. ADO.NET supports two types of constraints: UniqueConstraint and ForeignKeyConstraint. A UniqueConstraint ensures that all values for the specified column(s) are unique within the table. A ForeignKeyConstraint defines a primary key-foreign key relationship between columns in two tables and allows the specification of actions to be performed when parent (primary key) rows are added, deleted, or modified. An attempted violation of the constraint results in a run-time error.

Note that constraints are enforced only when the EnforceConstraints property of the DataSet containing the table is set to True. The default value of this property is True.

Although constraints can be created directly, they will most often be created indirectly. In fact, you have already created several constraints in the previous code examples. A UniqueConstraint object is automatically created and added to the Constraints collection of a DataTable whenever you set the Unique property of a DataColumn to True and whenever you create a primary key for a DataTable. In addition, both a UniqueConstraint and a ForeignKeyConstraint are automatically created whenever you create a DataRelation between two tables. The UniqueConstraint is created on the related column(s) in the parent table and the ForeignKeyConstraint is created on the related column(s) in the child table.

Note

You can create a DataRelation that relates two tables without actually creating the two constraints just mentioned. The usefulness of this approach is questionable, but it is available.

Let's add some code to the form frmDataSets to display the constraints of the tables in a DataSet.

1. Add a button immediately below the btnChildRows button from the Windows Forms tab of the Toolbox.

2. In the Properties window, set the Name property of the button to btnConstraints and set the Text property to Constraints.

3. Add the code shown in Listing 5.8.

Listing 5.8 Code to display both unique and foreign key constraints

 Private Sub btnConstraints_Click(ByVal sender As _      System.Object, ByVal e As System.EventArgs) _      Handles btnConstraints.Click   Dim dt As DataTable   Dim rel As DataRelation   CreateDataSet ()   ' Create the relation between the Departments and   ' Employees tables   rel = dsEmployeeInfo.Relations.Add( _     "relDepartmentEmployees", _     dsEmployeeInfo.Tables("Departments").Columns("ID"), _     dsEmployeeInfo.Tables("Employees"). Columns("DepartmentID"))   For Each dt In dsEmployeeInfo.Tables     DisplayConstraints(dt)   Next dt End Sub Private Sub DisplayConstraints(ByVal dt As DataTable)   Dim i As Integer   Dim cs As Constraint   Dim uCS As UniqueConstraint   Dim fkCS As ForeignKeyConstraint   Dim columns() As DataColumn   Me.lstOutput.Items.Add("")   Me.lstOutput.Items.Add( _     "CONSTRAINTS FOR TABLE: " & dt.TableName)     Me.lstOutput.Items.Add( _       "====================================")   For Each cs In dt.Constraints     Me.lstOutput.Items.Add( _       "Constraint Name: " & cs.ConstraintName)     Me.lstOutput.Items.Add( _       "Type: " & cs.GetType().ToString())     If TypeOf cs Is UniqueConstraint Then       uCS = CType(cs, UniqueConstraint)       ' Get the Columns as an array.       columns = uCS.Columns       ' Print each column's name.       For i = 0 To columns.Length 1         Me.lstOutput.Items.Add( _         "Column Name: " & _         columns(i).ColumnName)       Next i   ElseIf TypeOf cs Is ForeignKeyConstraint Then     fkCS = CType(cs, ForeignKeyConstraint)     ' Get the child Columns and display them     columns = fkCS.Columns     For i = 0 To columns.Length 1       Me.lstOutput.Items.Add( _       "Column Name: " & _       columns(i).ColumnName)     Next i     ' Display the related (parent) table name.     Me.lstOutput.Items.Add( _       "Related Table Name: " & _       fkCS.RelatedTable.TableName)     ' Get the related (parent) columns and     ' display them.     columns = fkCS.RelatedColumns     For i = 0 To columns.Length 1       Me.lstOutput.Items.Add( _         "Related Column Name: " & _columns(i).ColumnName)     Next i   End If   Me.lstOutput.Items.Add(" ")   Next cs End Sub 

The purpose of the btnConstraints_Click procedure is to respond to the button click; set up the DataSet, DataTables, and DataRelation (using code written in previous listings); and then call DisplayConstraints, which does all the interesting work.

DisplayConstraints is a generic routine that accepts a DataTable as a parameter and displays information about the constraints defined for that table. It loops across all the constraints in the Constraints property of the passed table. For each constraint, you need to test whether it is a UniqueConstraint or a ForeignKeyConstraint. Both of these classes are derived from the abstract Constraint class, so they can coexist within the same typed collection. However, each has a different set of properties, so you need to identify the Constraint and then convert the object to the appropriate specific type. For a UniqueConstraint, just display the names of all the (one or more) columns defined in the constraint. For a ForeignKeyConstraint, also display the name of the related (parent) table along with the names of related columns in that table.

The results of running the DataSetCode project and clicking on the Constraints button are shown in Figure 5.4. Remember that, although three constraints are shown (one on the Employees table and two on the Departments table), none were explicitly created. They were automatically created when you set the DataColumn unique property to True and when you created the relation between the tables.

Figure 5.4. Results of displaying the constraints of the Departments and Employees tables

The ForeignKeyConstraint object has three Rule properties that govern the actions taken during the editing of related tables. The UpdateRule and DeleteRule properties define the action to be taken when a row in a parent table is either updated or deleted. The options for these rules are defined in the Rule enumeration, shown in Table 5.4.

Table 5.4. The Rule Enumeration

Enumeration Member Name	Description
Cascade	The deletion or update made to the parent row is also made to the related child row(s). It is the default value.
None	The deletion or update made to the parent row isn't made to the related child row(s). This condition could create child rows having reference to invalid parent rows.
SetDefault	The deletion or update made to the parent row isn't made to the related child row(s). Instead, the related column (foreign key) in the related child rows is set to the default value defined for that column.
SetNull	The deletion or update made to the parent row isn't made to the related child row(s). Instead, the related column is set to DBNull. This condition could create orphaned child rows that have no relationship to parent rows.

The third Rule property is the AcceptRejectRule. This rule, whose value can be either Cascade or None, defines whether invoking the AcceptChanges or RejectChanges method on a parent row causes AcceptChanges or RejectChanges to be invoked automatically on the related child rows. The default is Cascade, which means that if AcceptChanges or RejectChanges is called on a parent row, the corresponding method will be automatically called on the related child rows. If the value is set to None, calling one of these two methods on parent row doesn't affect the editing of the related child rows.