Working with DataSets
| for RuBoard |
Figure 9-5 depicts the hierarchy of classes that exist within the DataSet class. It will be helpful to glance at this diagram over the next few sections that discuss these classes.
Figure 9-5. 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 HotelBroker object has the following SelectCommand property:
string cmd = "select CustomerId, HotelName, City, ArrivalDate, DepartureDate, ReservationId from Reservations, Hotels where Reservations.HotelId = Hotels.HotelId"; adapter.SelectCommand = new SqlCommand(cmd, conn); dataset = new DataSet(); adapter.Fill(dataset, "Reservations");
The DataSet will only have one DataTable 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(dataset, SchemaType.Source, "Order Details"); adapter.Fill(dataset, "Order Details"); adapter.SelectCommand = new SqlCommand("select * from Shippers", conn); adapter.FillSchema(dataset, SchemaType.Source, "Shippers"); adapter.Fill(dataset, "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 .
foreach(DataTable t in dataset.Tables) { Console.WriteLine(t.TableName); DataColumn[] dc = t.PrimaryKey; for (int i = 0; i < dc.Length; i++) { Console.WriteLine("\tPrimary Key Field {0} = {1}", i, dc[i].ColumnName); } Console.Write("\t"); foreach(DataColumn c in t.Columns) Console.Write("{0, -20}", c.ColumnName); Console.WriteLine(); foreach(DataRow r in t.Rows) { Console.Write("\t"); foreach(DataColumn c in t.Columns) Console.Write("{0, -20}", r[c].ToString().Trim()); Console.WriteLine(); } } 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
One 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 data set 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.
DataSet ds = new DataSet(); ds.EnforceConstraints = true; DataTable categories = ds.Tables.Add("Categories"); DataTable bookcategories = ds.Tables.Add("BookCategories"); DataTable authors = ds.Tables.Add("Authors"); DataTable books = 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.
Type stringType = System.Type.GetType("System.String"); Type intType = System.Type.GetType("System.Int32"); DataColumn categoryname = categories.Columns.Add("Category",stringType); DataColumn cn = bookcategories.Columns.Add ("CategoryName", stringType); DataColumn loc = bookcategories.Columns.Add( "LibraryofCongressNumber", stringType); DataColumn auid = authors.Columns.Add("AuthorId", intType); authors.Columns.Add("AuthorLastName", stringType); authors.Columns.Add("AuthorFirstName", stringType); DataColumn ISBN = books.Columns.Add("ISBN", stringType); DataColumn booksauid = books.Columns.Add("AuthorId", intType); books.Columns.Add("Title", stringType); DataColumn bloc = 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:
DataColumn[] bookcategoriesPK = new DataColumn[2]; bookcategoriesPK[0] = cn; bookcategoriesPK[1] = loc; bookcategories.PrimaryKey = bookcategoriesPK; DataColumn[] authorsPK = new DataColumn[1]; authorsPK[0] = auid; authors.PrimaryKey = authorsPK; DataColumn[] booksPK = new DataColumn[1]; booksprimarykey[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 ).
DataColumn[] bookauthorFK = new DataColumn[1]; bookauthorFK[0] = booksauid; ForeignKeyConstraint fk = 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. [18]
[18] 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.
foreach(DataTable t in ds.Tables) { Console.WriteLine(t.TableName); Console.WriteLine("\tPrimary Key:"); for (int i = 0; i < t.PrimaryKey.Length; i++) { DataColumn c = t.PrimaryKey[i]; Console.WriteLine("\t\t{0}", c.ColumnName); } Console.WriteLine("\tConstraints:"); foreach(Constraint c in t.Constraints) { string constraintName; if (c is ForeignKeyConstraint) constraintName = "Foreign Key:" + c.ConstraintName; else if (c is UniqueConstraint) { UniqueConstraint u = (UniqueConstraint)c; if (u.IsPrimaryKey) constraintName = "Primary Key"; else constraintName = u.ConstraintName; } else constraintName = "Unknown Name"; Console.WriteLine("\t\t{0, -40}", constraintName); } } This produces the following output. Note how the relations defined as a DataRelation appear in the table's constraint collection as a ForeignKeyConstraint instance. PrimaryKeys 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.
foreach(DataRelation dr in ds.Relations) { DataTable parentTable = dr.ParentTable; DataTable childTable = dr.ChildTable; Console.WriteLine(" Relation: {0} ", dr.RelationName); Console.WriteLine(" ParentTable: {0, -10}", parentTable); Console.Write(" Columns: "); for(int j = 0; j < dr.ParentColumns.Length; j++) Console.Write(" {0, -10}", dr.ParentColumns[j].ColumnName); Console.WriteLine(); Console.WriteLine(" ChildTable: {0, -10}", childTable); Console.Write(" Columns: "); for(int j = 0; j < dr.ChildColumns.Length; j++) Console.Write(" {0, -10}", dr.ChildColumns[j].ColumnName); Console.WriteLine(); } 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 SqlConnection 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.
books.RowChanged+=new DataRowChangeEventHandler(Row_Changed); private static void Row_Changed(object sender, System.Data.DataRowChangeEventArgs e) { DataTable table = (DataTable)sender; DataColumn[] primaryKey = table.PrimaryKey; string keyName = primaryKey[0].ColumnName; Console.WriteLine("Table " + table.TableName + " " + e.Action.ToString() + "Row with Primary Key " + e.Row[keyName]); return; } So when the code adds some rows, including some to the Books table:
DataRow row 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 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 GetParentRows 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.
foreach (DataRelation dr in ds.Relations) { Console.WriteLine(dr.RelationName); DataTable parentTable = dr.ParentTable; foreach(DataRow parentRow in parentTable.Rows) { Console.Write(" Parent Row: "); foreach(DataColumn pc in parentTable.Columns) Console.Write(" {0} ", parentRow[pc]); Console.WriteLine(); DataRow[] childRows = parentRow.GetChildRows(dr); for(int k = 0; k < childRows.Length; k++) { Console.Write(" Child Row: "); foreach(DataColumn cc in childTable.Columns) Console.Write(" {0} ", childRows[k][cc]); Console.WriteLine(); } } Console.WriteLine(); foreach(DataRow childRow in childTable.Rows) { Console.Write(" Child Row: "); for(int m = 0; m < childTable.Columns.Count; m++) Console.Write(" {0} ", childRow[childTable. Columns[m].ColumnName].ToString().Trim()); Console.WriteLine(); foreach(DataRow pRow in childRow.GetParentRows(dr)) { Console.Write(" Parent Row: "); for(int p = 0; p < parentTable.Columns.Count; p++) Console.Write(" {0} ", pRow[parentTable. Columns[p].ColumnName].ToString().Trim()); Console.WriteLine(); } } Console.WriteLine(); } 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.
DataRow rowToEdit = books.Rows[0]; rowToEdit.BeginEdit(); try { rowToEdit["AuthorId"] = 21; ... } catch(Exception e) { Console.WriteLine("\n" + e.Message + " while editing a row."); Console.WriteLine(); } However, when we invoke the EndEdit method on the row, the exception is raised.
try { rowToEdit.EndEdit(); } catch(Exception e) { Console.WriteLine(); Console.WriteLine("\n" + e.Message + " on EndEdit"); Console.WriteLine(); } 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 [19] of the row can take a DataRowVersion to specify which value you want. The version field can be Original , Default , Current , or Proposed .
[19] 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("Current Value {0}", rowToEdit["AuthorId", DataRowVersion.Current]); Console.WriteLine("Default Value {0}", rowToEdit["AuthorId", DataRowVersion.Default]); 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 , Modified , 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 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. 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 void CancelReservation(int id) { DataTable t = null; try { t = dataset.Tables["Reservations"]; DataRow[] rc = t.Select("ReservationId = " + id + " "); for (int i = 0; i < rc.Length; i++) rc[i].Delete(); int NumberRows = adapter.Update(dataset, "Reservations"); if (NumberRows > 0) t.AcceptChanges(); else t.RejectChanges(); } catch(Exception e) { t.RejectChanges(); throw e; } return; } 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 GetColunmsInError methods.
| for RuBoard |
EAN: 2147483647
Pages: 158