Populating and Manipulating the DataSet
Populating and Manipulating the DataSetThere are three ways to fill a DataTable in a DataSet with data:
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 SchemasOnce again, we work with you to build a simple form to illustrate the concepts introduced. Begin by following these steps.
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.
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 DataTableOnce 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.
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 DataSetTo 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 VersionsEvery 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.
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 ErrorsADO.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 DataSetPrivate 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 DataTableBecause 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 RowsAt 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 RelationsBecause 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.
Continue with the form frmDataSets that you prepared earlier.
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.
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 ConstraintsConstraints 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.
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.
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. |
EAN: 2147483647
Pages: 97