| Figure 13-6 depicts the hierarchy of classes that exist within the DataSet class. The shaded boxes represent collection classes. It will be helpful to glance at this diagram over the next few sections that discuss these classes. Figure 13-6. DataSet class hierarchy.  Multiple Tables in a DataSet Each DataSet has a collection of one or more DataTable objects. Each DataTable object represents one table. With a SelectCommand that contains a join, you can place data from multiple database tables into one DataTable . If you want to update the multiple tables, you will have to specify the update commands, because all relationship of the data to the original tables is lost. The SqlDataAdapter for the HotelBookings object has the following SelectCommand property (see the file HotelBookings.vb in the CaseStudy folder):  adapter = New SqlDataAdapter() Dim cmd As String = "select CustomerId, HotelName, " & _ "City, ArrivalDate, DepartureDate, ReservationId " & _ "from Reservations, Hotels where " & _ "Reservations.HotelId = Hotels.HotelId" adapter.SelectCommand = New SqlCommand(cmd, conn) ds = New DataSet() adapter.Fill(ds, "Reservations") The DataSet will have only one DataTab le , called Reservations. The fact that some of the data came from the Hotels table is lost. You can also load more than one table into a dataset. The DataSchema example does just this: adapter.SelectCommand = New SqlCommand("select * from " _ & "[Order Details] where ProductId = 1", conn) adapter.FillSchema(ds, SchemaType.Source, "Order Details") adapter.Fill(ds, "Order Details") adapter.SelectCommand = New SqlCommand(_ "select * from Shippers", conn) adapter.FillSchema(ds, SchemaType.Source, "Shippers") adapter.Fill(ds, "Shippers") There will be two tables, OrderDetails and Shippers, in the DataSet . The method SqlDataAdapter.FillSchema fills the DataSet with the primary key information associated with the tables. The code can now iterate through the tables and print out both the data and the primary keys of the tables. The Columns collection on the DataTable enables you to find the DataColumns for the DataTable . Dim t As DataTable For Each t In ds.Tables Console.WriteLine(t.TableName) Dim dc() As DataColumn = t.PrimaryKey Dim i As Integer For i = 0 To dc.Length - 1 Console.WriteLine(_ " Primary Key Field {0} = {1}", _ i, dc(i).ColumnName) Next i Console.Write(" ") Dim c As DataColumn For Each c In t.Columns Console.Write("{0, -15}", c.ColumnName) Next c Console.WriteLine() Dim r As DataRow For Each r In t.Rows Console.Write(" ") For Each c In t.Columns Console.Write("{0, -15}", r(c).ToString().Trim()) Next c Console.WriteLine() Next r Next t The example output shows the tables, primary keys, columns, and data: Order Details Primary Key Field 0 = OrderID Primary Key Field 1 = ProductID OrderID ProductID UnitPrice Quantity Discount 10285 1 14.4 45 0.2 10294 1 14.4 18 0 ... Shippers Primary Key Field 0 = ShipperID ShipperID CompanyName Phone 1 Speedy Express (503) 555-9831 2 United Package (503) 555-3199 3 Federal Shipping (503) 555-9931 Table Creation without a Data Source We can use a DataSet as a memory-resident relational database not based on any database. In fact, we will explore various features of the DataSet in the DataEditing example by adding the data and relationships directly to the dataset without extracting them from a database. First, we create a new DataSet and turn on constraint checking. We then add four DataTables to the DataSet: Books, Categories, Authors, and BookCategories. Even though it is set in the example code for pedagogical reasons, EnforceConstraints by default is true. Dim ds As New DataSet() ds.EnforceConstraints = True ' Add tables to Dataset Dim categories As DataTable = ds.Tables.Add("Categories") Dim bookcategories As DataTable = ds.Tables.Add("BookCategories") Dim authors As DataTable = ds.Tables.Add("Authors") Dim books As DataTable = ds.Tables.Add("Books") Each DataTable object has a collection of DataColumn objects. Each object represents one column of the table. We then add columns to the table definition. ' define types for column definitions Dim stringType As Type = System.Type.GetType("System.String") Dim intType As Type = System.Type.GetType("System.Int32") ' Define columns for tables ' Add column to Category table Dim categoryname As DataColumn = _ categories.Columns.Add("Category", stringType) ' Add columns for BookCategories table Dim cn As DataColumn = bookcategories.Columns.Add(_ "CategoryName", stringType) Dim loc As DataColumn = bookcategories.Columns.Add(_ "LibraryofCongressNumber", stringType) ' Add columns for Authors table Dim auid As DataColumn = authors.Columns.Add(_ "AuthorId", intType) authors.Columns.Add("AuthorLastName", stringType) authors.Columns.Add("AuthorFirstName", stringType) ' Add columns for Books table Dim ISBN As DataColumn = books.Columns.Add(_ "ISBN", stringType) Dim booksauid As DataColumn = books.Columns.Add(_ "AuthorId", intType) books.Columns.Add("Title", stringType) Dim bloc As DataColumn = books.Columns.Add(_ "LibraryofCongressNumber", stringType) Constraints and Relations Each DataTable object has a collection of DataRow objects. Each object represents one row of the table. When you add a DataRow , it is subject to the constraints on the DataTable objects ( assuming the DataSet's EnforceConstraints property has been set to true). Primary Keys There are several constraints on a table. The primary key constraint is the unique identifier for the table. Other unique constraints force the values in various column(s) to which they are applied to be unique. A foreign key constraint forces the values in the column(s) to which it applies to be a primary key in another table in the DataSet . The DataTable 's primary key is a property: ' Define PK for BookCategories table Dim bookcategoriesPK() As DataColumn = New DataColumn(1) {} bookcategoriesPK(0) = cn bookcategoriesPK(1) = loc bookcategories.PrimaryKey = bookcategoriesPK ' Define PK for Authors table Dim authorsPK() As DataColumn = New DataColumn(0) {} authorsPK(0) = auid authors.PrimaryKey = authorsPK ' Define PK for Books table Dim booksPK() As DataColumn = New DataColumn(0) {} booksPK(0) = ISBN books.PrimaryKey = booksPK Constraints The other constraints on the Table are represented by the abstract base class Constraint and its derived classes: UniqueConstraint and ForeignKeyConstraint . The base class enables the constraints to be placed in the table's constraint collection. Primary keys also appear in the table's constraint collection as a unique constraint with a system-generated name . The UniqueConstraint.IsPrimaryKey property can be used to detect primary keys. We constrain the Category column in the Categories table to be unique. Since the last argument to the Add method is false, this is not a primary key of the table. We do not define a primary key for this table, only a unique constraint. In fact, we do not even have to define any constraint on the table. Although that would violate the rules of relational integrity, you are not forced to use the DataSet in a relational manner. If you wish, you can add a name to the constraint. categories.Constraints.Add(_ "Unique CategoryName Constraint", categoryname, False) Foreign keys can specify what action should be taken when the primary key on which it is based is changed. Your choices are the standard database choices: None , Cascade , SetNull . You can also use SetDefault to set the new value to the DataColumn's DefaultValue property. These operations can be specified for both update and delete conditions. In this example, a foreign key constraint is set so that all author ids in the Books table have to be found in the Authors table. In other words, when a new book row is inserted, it must have an author. We give this constraint a name: Authors->Books. If the author ID is changed, the update rule forces the DataSet to change all the author ids in the related rows to the new author id. If the author ID is deleted, the DataSet will set the deleted author ids in the Book rows to null. If we had set the DeleteRule to Cascade , a cascading delete would be applied to all those rows in the Books table. The AcceptRejectRule applies to editing of the DataSet , which we will cover in a future section. This rule dictates what happens when the AcceptChanges method is invoked on a DataSet , DataRow , or DataTable . In this case all changes are cascaded. The alternative rule would be to take no action ( None ). ' Define FK for Books table ' (AuthorId must be in Authors table) Dim bookauthorFK() As DataColumn = New DataColumn(0) {} bookauthorFK(0) = booksauid Dim fk As New ForeignKeyConstraint(_ "Authors->Books", authorsPK, bookauthorFK) fk.AcceptRejectRule = AcceptRejectRule.Cascade fk.DeleteRule = Rule.SetNull fk.UpdateRule = Rule.Cascade books.Constraints.Add(fk) Data Relations Besides constraints, you can add a relation to the DataSet's DataRelation collection. A relation connects two tables so that you can navigate between the parent and the child or the child and the parent. When you add the relation, you can optionally create and add the equivalent unique and foreign key constraints to the parent and child tables' constraint collections. [13] [13] Use the optional boolean createConstraints argument when you add a relation to indicate whether the associated constraint should be added. If this argument is not specified, the default is to add the associated constraint. The Categories table is made the parent of the BookCategories table through the Categories and CategoryName columns. In a relation both columns have to be of the same type (string). You can use this relation to navigate by finding all the rows in the child table that have the same value as in the parent table or finding the row in the parent table that is the parent of a row in the child table. Similarly, the Library of Congress number associated with a book has to be found in the Library of Congress field in the BookCategory's Library of Congress field. ds.Relations.Add(_ "Category->BookCategories Relation", categoryname, cn) ds.Relations.Add(_ "Book Category LOC->Book LOC Relation", Loc, bloc) Examining the Schema Information about a DataTable You can examine the information about a DataTable. Here is how to examine the constraint and key information. A previous example has already shown you how to find the DataColumns for a DataTable. Note the use of the IsPrimaryKey property on the UniqueConstraint to detect a primary key. Dim t As DataTable For Each t In ds.Tables Console.WriteLine(" " + t.TableName) Console.WriteLine(" Primary Key:") Dim i As Integer For i = 0 To t.PrimaryKey.Length - 1 Dim dc As DataColumn = t.PrimaryKey(i) Console.WriteLine(" {0}", dc.ColumnName) Next i Console.WriteLine(" Constraints:") Dim c As Constraint For Each c In t.Constraints Dim constraintName As String If TypeOf c Is ForeignKeyConstraint Then constraintName = "Foreign Key:" & c.ConstraintName ElseIf (TypeOf c Is UniqueConstraint) Then Dim u As UniqueConstraint = _ CType(c, UniqueConstraint) If (u.IsPrimaryKey) Then constraintName = "Primary Key" Else constraintName = u.ConstraintName End If Else constraintName = "Unknown Name" End If Console.WriteLine(" {0, -40}", constraintName) Next c Next t This produces the following output. Note how the relations defined as a DataRelation appear in the table's constraint collection as a ForeignKeyConstraint instance. Primary keys appear in the constraint collection as a UniqueConstraint instance. Constraints defined as unique constraints or foreign keys appear as you would expect in the collection. Categories Primary Key: Constraints: Unique CategoryName Constraint BookCategories Primary Key: CategoryName LibraryofCongressNumber Constraints: Primary Key Foreign Key:Category->BookCategories Relation Constraint2 Authors Primary Key: AuthorId Constraints: Primary Key Books Primary Key: ISBN Constraints: Primary Key Foreign Key:Authors->Books Foreign Key:Book Category LOC->Book LOC Relation Note the BookCategories constraint with the system-generated name. If you examine the code carefully , you will see we never added this constraint. Where did it come from? If you were to look at the columns in that constraint, you would find the Library of Congress field. The system realized that since the CategoryName is a foreign key in another table, the Library of Congress field should be unique. You can also examine the relations collection on the DataSet . You can examine the parent table and the columns in the parent table involved in the relationship. You can also examine the child table in the relationship and its columns. Dim dr As DataRelation For Each dr In ds.Relations Dim parentTable As DataTable = dr.ParentTable Dim childTable As DataTable = dr.ChildTable Console.WriteLine(" Relation: {0} ", dr.RelationName) Console.WriteLine(" ParentTable: {0, -10}", _ parentTable) Console.Write(" Columns: ") Dim j As Integer For j = 0 To dr.ParentColumns.Length - 1 Console.Write(" {0, -10}", _ dr.ParentColumns(j).ColumnName) Console.WriteLine() Console.WriteLine(" ChildTable: {0, -10}", _ childTable) Console.Write(" Columns: ") Next j For j = 0 To dr.ChildColumns.Length - 1 Console.Write(" {0, -10}", _ dr.ChildColumns(j).ColumnName) Console.WriteLine() Next j Next dr Here is the resulting output: Output Relations between tables in the DataSet... Relation: Category->BookCategories Relation ParentTable: Categories Columns: Category ChildTable: BookCategories Columns: CategoryName Relation: Book Category LOC->Book LOC Relation ParentTable: BookCategories Columns: LibraryofCongressNumber ChildTable: Books Columns: LibraryofCongressNumber Database Events Several ADO.NET classes generate events. The Sq lConnection class generates the StateChange and InfoMessage events. The SqlDataAdapter generates the RowUpdated and RowUpdating events. The DataTable class generates the ColumnChanging , ColumnChanged , RowChanged , RowChanging , RowDeleted , and RowDeleting events. For example, the RowChanged event occurs after an action has been performed on a row. Continuing with our DataEditing example, it defines a handler for the RowChanged event in the Books table. Every time a row changes in the Books table, the event handler will run. The event handler is set up by defining a handler procedure Row_Changed and hooking it to the RowChanged event by AddHandler . Sub Row_Changed(ByVal sender As Object, _ ByVal e As System.Data.DataRowChangeEventArgs) Dim table As DataTable = CType(sender, DataTable) Dim primaryKey() As DataColumn = table.PrimaryKey Dim keyName As String = primaryKey(0).ColumnName Console.WriteLine("Rowchanged:Table " & _ table.TableName & " " & e.Action.ToString() & _ "Row with Primary Key " & e.Row(keyName)) End Sub ... AddHandler books.RowChanged, _ New DataRowChangeEventHandler(AddressOf Row_Changed) So, when the code adds some rows, including some to the Books table, Dim row As DataRow row = categories.NewRow() row("Category") = "UnitedStates:PoliticalHistory" categories.Rows.Add(row) ... row = authors.NewRow() row("AuthorId") = 1 row("AuthorLastName") = "Burns" row("AuthorFirstName") = "James M." authors.Rows.Add(row) ... row = books.NewRow() row("ISBN") = "0-201-62000-0" row("Title") = "The Deadlock of Democracy" row("AuthorId") = 1 row("LibraryofCongressNumber") = "E183.1" books.Rows.Add(row) row = books.NewRow() row("ISBN") = "0-201-62000-3" row("Title") = "Freedom and Order" row("AuthorId") = 2 row("LibraryofCongressNumber") = "E183.1" books.Rows.Add(row) we get one output line for each book added, printed by the event handler: Table Books AddRow with Primary Key 0-201-62000-0 Table Books AddRow with Primary Key 0-201-62000-3 If we were to change the ISBN numbers of the two books that were added to the same value, a ConstraintException would be thrown. If we changed the DataSet.EnforceConstraints property to false, however, no exception would be thrown. Navigating Relationships Using the schema information, we can navigate from parent table to child table and print out the results. This cannot be done with relationships defined as ForeignKeyConstraint , only with those defined as a DataRelation in the relations collection of the DataSet . We previously printed out the schema information associated with the relationships. Now we use this information to print out the parent and child rows in the relationships. By using relationships appropriately, you can walk through the data without using relational queries. This can be quite useful for finding all the books in a certain category or all order items in an order. Note the use of the DataRow methods GetChildRows and GetParent-Rows to do the navigation. For a given relation, first we navigate from parent to children, then from the children to their parent. We also show how you can use different constructs to access the items in the various collections. For Each dr In ds.Relations Console.WriteLine(dr.RelationName) Dim parentTable As DataTable = dr.ParentTable Dim childTable As DataTable = dr.ChildTable Dim parentRow As DataRow For Each parentRow In parentTable.Rows Console.Write(" Parent Row: ") Dim pc As DataColumn For Each pc In parentTable.Columns Console.Write(" {0} ", parentRow(pc)) Next pc Console.WriteLine() Dim childRows() As DataRow = _ parentRow.GetChildRows(dr) Dim k As Integer For k = 0 To childRows.Length - 1 Console.Write(" Child Row: ") Dim cc As DataColumn For Each cc In childTable.Columns Console.Write(" {0} ", childRows(k)(cc)) Next cc Console.WriteLine() Next k Next parentRow Console.WriteLine() Dim childRow As DataRow For Each childRow In childTable.Rows Console.Write(" Child Row: ") Dim m As Integer For m = 0 To childTable.Columns.Count - 1 Dim strg As String = _ childRow(childTable.Columns(m).ColumnName).ToString() Console.Write(" {0} ", strg.Trim()) Next Console.WriteLine() Dim pRow As DataRow For Each pRow In childRow.GetParentRows(dr) Console.Write(" Parent Row: ") Dim p As Integer For p = 0 To parentTable.Columns.Count - 1 Dim strg As String = _ pRow(parentTable.Columns(p).ColumnName).ToString() Console.Write(" {0} ", strg.Trim()) Next p Console.WriteLine() Next pRow Next childRow Console.WriteLine() Next dr Next, let us look at the output that this code produces. Note how we loop through each relation. For each relation, we first loop through the parent table and output each row of the parent table with its corresponding child rows. We then loop through the child table and output each row of the child table with its corresponding parent rows. ... Category->BookCategories Relation Parent Row:UnitedStates:PoliticalHistory Child Row:UnitedStates:PoliticalHistory E183 Parent Row:UnitedStates:PoliticalHistory:Opinion Child Row:UnitedStates:PoliticalHistory:Opinion E183.1 Child Row:UnitedStates:PoliticalHistory:Opinion E183.2 Parent Row:UnitedStates:PoliticalHistory:Predictions Child Row:UnitedStates:PoliticalHistory:Predictions E183.3 Child Row:UnitedStates:PoliticalHistory E183 Parent Row:UnitedStates:PoliticalHistory Child Row:UnitedStates:PoliticalHistory:Opinion E183.1 Parent Row:UnitedStates:PoliticalHistory:Opinion Child Row:UnitedStates:PoliticalHistory:Opinion E183.2 Parent Row:UnitedStates:PoliticalHistory:Opinion Child Row:UnitedStates:PoliticalHistory:Predictions E183.3 Parent Row:UnitedStates:PoliticalHistory:Predictions Book Category LOC->Book LOC Relation Parent Row:UnitedStates:PoliticalHistory E183 Parent Row:UnitedStates:PoliticalHistory:Opinion E183.1 Child Row:0-201-62000-0 1 The Deadlock of Democracy E183.1 Child Row:0-201-62000-3 2 Freedom and Order E183.1 Parent Row:UnitedStates:PoliticalHistory:Opinion E183.2 Parent Row:UnitedStates:PoliticalHistory:Predictions E183.3 Child Row:0-201-62000-0 1 The Deadlock of Democracy E183.1 Parent Row:UnitedStates:PoliticalHistory:Opinion E183.1 Child Row:0-201-62000-3 2 Freedom and Order E183.1 Parent Row:UnitedStates:PoliticalHistory:Opinion E183.1 DataRow Editing BeginEdit, EndEdit, CancelEdit If you want to make multiple edits to a DataSet and postpone the checking of constraints and events, you can enter a dataset editing mode. You enter this mode by invoking the BeginEdit method on the row. You leave it by invoking the EndEdit or CancelEdit row methods. In the DataEditing example, we violate the foreign-key constraint by adding a row with a nonexistent author id. The foreign-key constraint exception will not be raised until the EndEdit method is called. Since we have called BeginEdit in the following code fragment, there is no exception caught. Dim rowToEdit As DataRow = books.Rows(0) rowToEdit.BeginEdit() Try rowToEdit("AuthorId") = 21 Console.WriteLine(_ "Book Author Id Field Current Value {0}", _ rowToEdit("AuthorId", DataRowVersion.Current)) Console.WriteLine(_ "Book Author Id Field Proposed Value {0}", _ rowToEdit("AuthorId", DataRowVersion.Proposed)) Console.WriteLine(_ "Book Author Id Field Default Value {0}", _ rowToEdit("AuthorId", DataRowVersion.Default)) Catch e As Exception Console.WriteLine(vbNewLine & e.Message & _ " while editing a row.") Console.WriteLine() End Try However, when we invoke the EndEdit method on the row, the exception is raised. Try rowToEdit.EndEdit() Catch e As Exception Console.WriteLine() Console.WriteLine(e.Message & " on EndEdit") Console.WriteLine() End Try The following message is printed out because the illegal value was still present when the editing session was finished. ForeignKeyConstraint Authors->Books requires the child key values (21) to exist in the parent table. on EndEdit DataRow Versions Before the row changes have been accepted, both the original and the changed row data are available. The item property [14] of the row can take a DataRowVersion to specify which value you want. The version field can be Original , Default , Current , or Proposed . [14] The item property of the DataRow is the indexer for the class. Console.WriteLine("BeginEdit called for Book AuthorId.") rowToEdit.BeginEdit() rowToEdit("AuthorId") = 2 Console.WriteLine("Current Value {0}", _ rowToEdit("AuthorId", DataRowVersion.Current)) Console.WriteLine("Proposed Value {0}", _ rowToEdit("AuthorId", DataRowVersion.Proposed)) Console.WriteLine("Default Value {0}", _ rowToEdit("AuthorId", DataRowVersion.Default)) rowToEdit.EndEdit() Console.WriteLine("EndEdit called.") ... This code caused the following output to be printed out: BeginEdit called for Book AuthorId. Current Value 1 Proposed Value 2 Default Value 2 ... EndEdit called. Current Value 2 Default Value 2 During editing, the Current and Proposed item values are available. After CancelEdit , the Proposed value is no longer available. After EndEdit , the Proposed value becomes the Current value, and the Proposed value is no longer available. DataRow RowState Property In addition to the Current and Proposed values of a field, the DataRow itself has a property that indicates the state of the particular row. The values can be Added , Deleted , Detached , Modfied , or Unchanged . A row is in the Detached state when it has been created, but has not been added to any DataRow collection, or it has been removed from a collection. The Default DataRowVersion of a field returns the appropriate row version depending on the RowState property. Accepting and Rejecting Changes Calling EndEdit on a DataRow does not cause the changes to be made to the row. Calling the AcceptChanges or RejectChanges method on the DataSet , DataTable , or DataRow ends editing on all the contained rows of the appropriate scope. If EndEdit or CancelEdit has not been called, these methods do it implicitly for all rows within its scope. After the AcceptChanges method, the Current value becomes the Original value. If EndEdit has not been called, the Proposed value becomes the new Current and Original values. If the RowState was Added , Modified , or Deleted it becomes Unchanged and the changes are accepted. After the RejectChanges method, the Proposed value is deleted. If the RowState was Deleted or Modified , the values revert to their previous values and the RowState becomes Unchanged . If the RowState was Added , the row is removed from the Rows collection. Since the RowState after AcceptChanges is Unchanged , calling the DataAdapter's Update method at this point will not cause any changes to be made on the data source. Therefore, you should call the Update method on the DataAdapter to update changes to the data source before calling AcceptChanges on any row, table, or DataSet . Here is the code from the case study's HotelBroker object's CancelReservation method. The code example is in the HotelBookings.vb file in the CaseStudy directory. Note how AcceptChanges on the DataSet is called if the SqlDataAdapter.Update method succeeds. If an exception is thrown or the update fails, RejectChanges is called. Public Sub CancelReservation(ByVal id As Integer) _ Implements IHotelReservation.CancelReservation Dim t As DataTable Try t = ds.Tables("Reservations") Dim rc() As DataRow = t.Select(_ "ReservationId = " & id & " ") Dim i As Integer For i = 0 To rc.Length - 1 rc(i).Delete() Dim NumberRows As Integer = _ adapter.Update(ds, "Reservations") If (NumberRows > 0) Then t.AcceptChanges() Else t.RejectChanges() End If Next Catch e As Exception t.RejectChanges() Throw e End Try End Sub If you do not reject the changes on failure, the rows will still be in the DataSet . The next time an update is requested , the update will be rejected again, because the rows are still waiting to be updated. Since the DataSet is independent of a database, the fact that an update occurs on the database has nothing to do with accepting or rejecting the changed rows in the DataSet . DataRow Errors If there have been any data editing errors on a row, the HasErrors property on the DataSet , DataTable , or DataRow will be set to true. To get the error, use the DataRow's GetColumnError or the GetColumnsInError methods.  | 
