Content components encapsulate data. In previous ADO versions, the Recordset object represented such a component. The data contained by the recordset component is in the form of a table, consisting of columns and rows. In ADO.NET, the data encapsulated by the DataSet component is in the form of a relational database, consisting of tables and relationships. This is a major improvement in data-access technology. In this section, we provide a high-level survey of the core classes that make up the content components, including DataSet, DataTable, DataColumn, DataRow, DataView, and DataRelation.[35]
If you are familiar with ADO, you know that data transfers between components are a recordset . The recordset contains data in a tabular form. Whether the recordset includes information from one or many tables in the database, the data is still returned in the form of rows and columns as if they were from a single table. ADO.NET allows for more than just a recordset to be shared between application components. This is one of the most important features of ADO.NET: we will be transferring a DataSet instead of a recordset.
The DataSet can be viewed as an in-memory view of the database. It can contain multiple DataTable and DataRelation objects. With previous versions of ADO, the closest you could get to this functionality was to exchange data with a chain of Recordset objects. When the client application receives this chained recordset, it can get to each of the recordsets through NextRecordset( ); however, there is no way to describe the relationship between each of the recordsets in the chain. With ADO.NET, developers can navigate and manipulate the collection of tables and their relationships.
As mentioned earlier, ADO.NET involves disconnected datasets because it is geared toward a distributed architecture. Since a DataSet is disconnected, it must provide a way to track changes to itself. The DataSet object provides a number of methods so that all data manipulation done to the DataSet can be easily reconciled with the actual database (or other data source) at a later time. They include: HasChanges( ), HasErrors, GetChanges( ), AcceptChanges( ), and RejectChanges( ). You can employ these methods to check for changes that have happened to the DataSet, obtain the modifications in the form of a changed DataSet, inspect the changes for errors, and then accept or reject the changes. If you want to communicate the changes to the data store back end (which is usually the case), just ask the DataSet for an update.
The DataSet is intended to benefit enterprise web applications, which are disconnected by nature. You don’t know that the data at the back end has changed until you have updated records you were editing or performed any other tasks that required data reconciliation with the database.
As depicted in Figure 5-2, a DataSet contains two important collections. The first is the Tables (of type DataTableCollection), which holds a collection for all the tables belonging to a given DataSet. The second collection contains all the relationships between the tables, and it is appropriately named the Relations (of type DataRelationCollection).
All the tables and relations inside the DataSet are exposed through the DataSet’s Tables and Relations properties, respectively. The following block of C# code demonstrates how to create a DataSet dynamically that consists of two tables, Orders and OrderDetails, and a relationship between the two tables:
using System;
using System.Data;
// Class and method declarations omitted for brevity...
// Construct the DataSet object
DataSet m_ds = new DataSet("DynamicDS");
// Add a new table named "Order" to m_ds's collection tables
m_ds.Tables.Add ("Order");
// Add new columns to table "Order"
m_ds.Tables["Order"].Columns.Add("OrderID",
Type.GetType("System.Int32"));
m_ds.Tables["Order"].Columns.Add("CustomerFirstName",
Type.GetType("System.String"));
m_ds.Tables["Order"].Columns.Add("CustomerLastName",
Type.GetType("System.String"));
m_ds.Tables["Order"].Columns.Add("Date",
Type.GetType("System.DateTime"));
// Register the column "OrderID" as the primary key of table "Order"
DataColumn[] keys = new DataColumn[1];
keys[0] = m_ds.Tables["Order"].Columns["OrderID"];
m_ds.Tables["Order"].PrimaryKey = keys;
// Add a new table named "OrderDetail" to m_ds's collection of tables
m_ds.Tables.Add ("OrderDetail");
// Add new columns to table "OrderDetail"
m_ds.Tables["OrderDetail"].Columns.Add("fk_OrderID",
Type.GetType("System.Int32"));
m_ds.Tables["OrderDetail"].Columns.Add("ProductCode",
Type.GetType("System.String"));
m_ds.Tables["OrderDetail"].Columns.Add("Quantity",
Type.GetType("System.Int32"));
m_ds.Tables["OrderDetail"].Columns.Add("Price",
Type.GetType("System.Currency"));
// Get the DataColumn objects from two DataTable objects in a DataSet.
DataColumn parentCol = m_ds.Tables["Order"].Columns["OrderID"];
DataColumn childCol = m_ds.Tables["OrderDetail"].Columns["fk_OrderID"];
// Create and add the relation to the DataSet.
m_ds.Relations.Add(new DataRelation("Order_OrderDetail",
parentCol,
childCol));Let’s highlight some important points in this block of code.
After instantiating the DataSet object with the new operator, we add
some tables with the Add method of the Tables object. We go through a
similar process to add columns to each Table’s Columns
collection. Each of the added tables or columns can later be
referenced by name. In order to assign the primary key for the Order
table, we have to create the DataColumn array to hold one or more
fields representing a key or a composite key. In this case, we have
only a single key field,
OrderID
. We set the PrimaryKey property of the
table to this array of key columns. For the relationship between the
two tables, we first create the DataRelation called
Order_OrderDetail with the two linking columns
from the two tables, and then we add this DataRelation to the
collection of relations of the DataSet.
The following block of C# code shows how to insert data into each of the two tables:
DataRow newRow; newRow = m_ds.Tables["Order"].NewRow( ); newRow["OrderID"] = 101; newRow["CustomerFirstName"] = "John"; newRow["CustomerLastName"] = "Doe"; newRow["Date"] = new DateTime(2001, 5, 1);; m_ds.Tables["Order"].Rows.Add(newRow); newRow = m_ds.Tables["Order"].NewRow( ); newRow["OrderID"] = 102; newRow["CustomerFirstName"] = "Jane"; newRow["CustomerLastName"] = "Doe"; newRow["Date"] = new DateTime(2001, 4, 29); m_ds.Tables["Order"].Rows.Add(newRow); newRow = m_ds.Tables["OrderDetail"].NewRow( ); newRow["fk_OrderID"] = 101; newRow["ProductCode"] = "Item-100"; newRow["Quantity"] = 7; newRow["Price"] = "59.95"; m_ds.Tables["OrderDetail"].Rows.Add(newRow); newRow = m_ds.Tables["OrderDetail"].NewRow( ); newRow["fk_OrderID"] = 101; newRow["ProductCode"] = "Item-200"; newRow["Quantity"] = 1; newRow["Price"] = "9.25"; m_ds.Tables["OrderDetail"].Rows.Add(newRow); newRow = m_ds.Tables["OrderDetail"].NewRow( ); newRow["fk_OrderID"] = 102; newRow["ProductCode"] = "Item-200"; newRow["Quantity"] = 3; newRow["Price"] = "9.25"; m_ds.Tables["OrderDetail"].Rows.Add(newRow);
Tables and Relations are important properties of DataSet. Not only do they describe the structure of the in-memory database, but the DataTables inside the collection also hold the content of the DataSet.
Three methods integrate DataSet tightly with XML, thus making it universally interoperable. These methods are WriteXml( ), WriteXmlSchema( ), and ReadXml( ).
WriteXmlSchema( ) dumps only the schema of the tables, including all tables and relationships between tables. WriteXml( ) can dump both the schema and table data as an XML encoded string. Both WriteXmlSchema( ) and WriteXml( ) accept a Stream, TextWriter, XmlWriter, or String representing a filename. WriteXml( ) accepts an XmlWriteMode as the second argument.
With XmlWriteMode.WriteSchema, the WriteXml( ) method dumps the schema and data. You can retrieve only the data portion of the XML by using the XmlWriteMode.IgnoreSchema property.
The DataSet object also provides methods to reconstruct itself from an XML document. Use ReadXmlData( ) for reading XML data documents, and ReadXmlSchema( ) for reading XML schema documents.
The following code creates an XML document from the previously created dataset:
// Dump the previously shown DataSet to
// the console (and also to an XML file)
m_ds.WriteXml(Console.Out, XmlWriteMode.WriteSchema);
m_ds.WriteXml("DS_Orders.xml", XmlWriteMode.WriteSchema);
// Constructing a new DataSet object
DataSet ds2 = new DataSet("RestoredDS");
ds2.ReadXml("DS_Orders.xml");Let’s examine the resulting XML file and its representation of the dataset:
<?xml version="1.0" standalone="yes"?>
<DynamicDS>
<xsd:schema id="DynamicDS"
targetNamespace=""
xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:element name="DynamicDS" msdata:IsDataSet="true">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="Order">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OrderID"
type="xsd:int"
msdata:Ordinal="0" />
<xsd:element name="CustomerFirstName"
type="xsd:string"
minOccurs="0"
msdata:Ordinal="1" />
<xsd:element name="CustomerLastName"
type="xsd:string"
minOccurs="0"
msdata:Ordinal="2" />
<xsd:element name="Date"
type="xsd:dateTime"
minOccurs="0"
msdata:Ordinal="3" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="OrderDetail">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="fk_OrderID"
type="xsd:int"
minOccurs="0"
msdata:Ordinal="0" />
<xsd:element name="ProductCode"
type="xsd:string"
minOccurs="0"
msdata:Ordinal="1" />
<xsd:element name="Quantity"
type="xsd:int"
minOccurs="0"
msdata:Ordinal="2" />
<xsd:element name="Price"
msdata:DataType="System.Currency"
type="xsd:string"
minOccurs="0"
msdata:Ordinal="3" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
<xsd:key name="Constraint1" msdata:PrimaryKey="true">
<xsd:selector xpath=".//Order" />
<xsd:field xpath="OrderID" />
</xsd:key>
<xsd:keyref name="Order_OrderDetail" refer="Constraint1">
<xsd:selector xpath=".//OrderDetail" />
<xsd:field xpath="fk_OrderID" />
</xsd:keyref>
</xsd:element>
</xsd:schema>
< ... data portion ... />
</DynamicDS>The root element is named DynamicDS because that
is the name of the dataset we created earlier. The
xsd:schema
tag contains all table and
relationship definitions in this DynamicDS dataset. Each table is
represented as an xsd:element where the name
attribute contains the table’s name. Each table’s
xsd:element contains
xsd:element
tags corresponding to each
column.
After the table definitions, the document holds definitions for
various key types. The xsd:key element is used for
keys, as shown in the xsd:key named
Constraint1. The
msdata:PrimaryKey
attribute makes this a primary
key, which has the added effect of enforcing uniqueness (every
OrderID in the Order table must be unique).
The xsd:keyref element is used for foreign keys,
as shown in the Order_OrderDetail key that refers
to the Constraint1 key. This links the OrderDetail
and Order tables where OrderDetail.fk_OrderID =
Order.OrderID.
Let us now look at the data portion of the XML file:
<Order> <OrderID>101</OrderID> <CustomerFirstName>John</CustomerFirstName> <CustomerLastName>Doe</CustomerLastName> <Date>2001-05-01T00:00:00.0000</Date> </Order> <Order> <OrderID>102</OrderID> <CustomerFirstName>Jane</CustomerFirstName> <CustomerLastName>Doe</CustomerLastName> <Date>2001-04-29T00:00:00.0000</Date> </Order> <OrderDetail> <fk_OrderID>101</fk_OrderID> <ProductCode>Item-100</ProductCode> <Quantity>7</Quantity> <Price>59.95</Price> </OrderDetail> <OrderDetail> <fk_OrderID>101</fk_OrderID> <ProductCode>Item-200</ProductCode> <Quantity>1</Quantity> <Price>9.25</Price> </OrderDetail> <OrderDetail> <fk_OrderID>102</fk_OrderID> <ProductCode>Item-200</ProductCode> <Quantity>3</Quantity> <Price>9.25</Price> </OrderDetail>
This part of the XML document is fairly self-explanatory. For each row of data in the Order table, we end up with one record of type Order. This is the same for the OrderDetail table.
Because the dataset is inherently disconnected from its source, changes to the data inside the dataset have to be tracked by the dataset itself. This is done through the following methods: HasChanges( ), GetChanges( ), and Merge( ). The application can check the changes to the dataset and then ask the DataAdapter object to reconcile the changes with the data source through the DataAdapter Update method.
The following block of code demonstrates how to the track and manage changes to a DataSet:
/* Make a change to the data set. */
m_ds.Tables["OrderDetail"].Rows[0]["Quantity"] = 12;
if(m_ds.HasChanges( )){
/* Get a copy of the data set containing the changes. */
DataSet changeDS = m_ds.GetChanges( );
/* Dump the changed rows. */
changeDS.WriteXml("ChangedDS.xml");
/* Commit all changes. */
m_ds.AcceptChanges( );
}We first change the quantity of one of the OrderDetail rows. Then we
ask the dataset for all the changes and dump it into a new dataset
called changeDS. This dataset results in the
following XML dump. Notice that because OrderDetail is a child of
Order, the change also includes the parent row.
<?xml version="1.0" standalone="yes"?>
<DynamicDS>
<Order>
<OrderID>101</OrderID>
<CustomerFirstName>John</CustomerFirstName>
<CustomerLastName>Doe</CustomerLastName>
<Date>2001-05-01T00:00:00.0000</Date>
</Order>
<OrderDetail>
<fk_OrderID>101</fk_OrderID>
<ProductCode>Item-100</ProductCode><Quantity>12</Quantity>
<Price>59.95</Price>
</OrderDetail>
</DynamicDS>We would like to emphasize that the DataSet object is the most important construct in ADO.NET. Because DataSet does not tie to an underlying representation such as SQL Server or Microsoft Access, it is extremely portable. Its data format is self-described in its schema, and its data is in pure XML. A DataSet is self-contained regardless of how it was created, perhaps by reading data from a SQL Server, from Microsoft Access, from an external XML file, or even by being dynamically generated as we have seen in an earlier example. This portable XML-based entity—without a doubt—should be the new standard for data exchange.
Enough said about DataSet. Let’s drill down from DataSet to DataTable.
DataTable represents a table of data and thus contains a collection of DataColumns as a Columns property and a collection of DataRows as a Rows property. The Columns property provides the structure of the table, while the Rows property provides access to actual row data. Fields in the table are represented as DataColumn objects, and table records are represented as DataRow objects. Here is some sample code that dumps the name of each column as a row of headers, followed by each row of data:
/* Walk the DataTable and display all column headers
* along with all data rows.
*/
DataTable myTable = m_ds.Tables["OrderDetail"];
/* Display all column names. */
foreach(DataColumn c in myTable.Columns) {
Console.Write(c.ColumnName + " ");
}
Console.WriteLine(""); // newline
/* Process each row. */
foreach(DataRow r in myTable.Rows) {
/* Display each column. */
foreach(DataColumn c in myTable.Columns) {
Console.Write(r[c] + " ");
}
Console.WriteLine(""); // newline
}Here is the output of that code:
fk_OrderID ProductCode Quantity Price 101 Item-100 12 59.95 101 Item-200 1 9.25 102 Item-200 3 9.25
Typically, a DataTable has one or more fields serving as a primary key. This functionality is exposed as the PrimaryKey property. Because the primary key might contain more than one field, this property is an array of DataColumn objects. We revisit this excerpt of code here to put things in context. Note that in this example, the primary key consists of only one field, hence the array of size one.
// Register the column "OrderID" as the primary key of table "Order" DataColumn[] keys = new DataColumn[1]; keys[0] = m_ds.Tables["Order"].Columns["OrderID"]; m_ds.Tables["Order"].PrimaryKey = keys;
Relations define how tables in a database relate to each other. The DataSet globally stores the collection of relations between tables in the Relations property; however, each of the tables participating in the relation also has to know about the relationship. ChildRelations and ParentRelations, two properties of the DataTable object, take care of this. ChildRelations enumerates all relations that this table participates in as a master table. ParentRelations, on the other hand, lists the relations in which this table acts as a slave table. We provide more information on the topic of relations when we explain the DataRelation object in an upcoming section of this chapter.
While we are on the topic of tables and relationships, it is
important to understand how to set up constraint enforcements. There
are two types of constraints that we can set up and enforce,
UniqueConstraint
and ForeignKeyConstraint. UniqueConstraint enforces the uniqueness of
a field value for a table. ForeignKeyConstraint enforces rules on
table relationships. For ForeignKeyConstraint, we can set up
UpdateRule and DeleteRule to dictate how the application should
behave upon performing updateor delete on a row of data in the
parent table.
Table 5-1 shows the constraint settings and behavior of ForeignKeyConstraint rules.
Table 5-1. Constraint types and behaviors
|
Setting |
Behavior |
|---|---|
|
None |
Nothing. |
|
Dependent rows (identified by foreign key) are deleted/updated when parent row is deleted/updated. | |
|
Foreign keys in dependent rows are set to the default value when parent row is deleted. | |
|
Foreign keys in dependent rows are set to null value when parent row is deleted. |
Constraints are activated only when the EnforceConstraint property of
the DataSet object is set to true.
The DataView object is similar to a view in conventional database programming. We can create different customized views of a DataTable, each having different sorting and filtering criteria. Through these different views, we can traverse, search, and edit individual records. This ADO.NET concept is the closest to the old ADO recordset. In ADO.NET, DataView serves another important role—data binding to Windows Forms and Web Forms. We show the usage of DataView when we discuss data binding on Windows Forms and Web Forms in Chapter 7 and Chapter 8.
A DataSet object as a collection of DataTable objects alone is not useful enough. A collection of DataTable objects returned by a server component provides little improvement upon the chained recordset in previous versions of ADO. In order for your client application to make the most of the returned tables, you also need to return the relations between these DataTables. This is where the DataRelation object comes into play.
With DataRelation, you can define relationships between the DataTable objects. Client components can inspect an individual table or navigate the hierarchy of tables through these relationships. For example, you can find a particular row in a parent table and then traverse all dependent rows in a child table.
The DataRelation contains the parent table name, the child table name, the parent table column (primary key), and the child table column (foreign key).
By having multiple DataTables and DataRelations within the DataSet, ADO.NET allows for a much more flexible environment where consumers of the data can choose to use the data in whichever way they wish.
One example might be the need to display all information about a particular parent table and all of its dependent rows in a child table. You have ten rows in the parent table. Each of the rows in the parent table has ten dependent rows in the child table. Let’s consider two approaches to getting this data to the data consumer. First, we will just use a join in the query string:
Select
Order.CustomerFirstName, Order.CustomerLastName, Order.OrderDate,
OrderDetail.ProductCode, OrderDetail.Quantity, OrderDetail.Price
from
Order, OrderDetail
where Order.OrderID = OrderDetail.fk_OrderIDThe result set contains 100 rows, in which each group of ten rows contains duplicate information about the parent row.
A second approach is to retrieve the list of rows from the parent table first, which would be ten rows:
Select Order.OrderID, Order.CustomerFirstName, Order.CustomerLastName, Order.OrderDate from Order
Then for each of the ten rows in the parent table, you would retrieve the dependent rows from the child table:
Select OrderDetail.ProductCode, OrderDetail.Quantity, OrderDetail.Price from OrderDetail where fk_OrderID = thisOrderID
This second approach is less of a resource hog since there is no redundant data; however, you end up making 11 round-trips (one time for the parent table, and 10 times for each parent of the child table).
It’s better to get the parent table, the child table, and the relation between them using one round-trip, without all the redundant data. This is one of the biggest benefits that DataSet brings. The following block of code demonstrates the power of having tables and relationships:
/*
* Given an order id, display a single order.
*/
public static void DisplaySingleOrder(DataSet m_ds, int iOrderID) {
Decimal runningTotal = 0;
Decimal lineTotal = 0;
Decimal dPrice = 0;
int iQty = 0;
DataTable oTable = m_ds.Tables["Order"];
// Find an order from the Order table.
DataRow oRow = oTable.Rows.Find(iOrderID);
/* Navigate to the OrderDetail table
* through the Order_Details relationship.
*/
DataRow[] arrRows = oRow.GetChildRows("Order_OrderDetail");
/* Display the order information. */
Console.WriteLine ("Order: {0}", iOrderID);
Console.WriteLine ("Name: {0} {1}",
oRow["CustomerFirstName"].ToString( ),
oRow["CustomerLastName"].ToString( ));
Console.WriteLine ("Date: {0}", oRow["Date"].ToString( ));
/*
* Display and calculate line total for each item.
*/
for(int i = 0; i < arrRows.Length; i++) {
foreach(DataColumn myColumn in m_ds.Tables["OrderDetail"].Columns)
{
Console.Write(arrRows[i][myColumn] + " ");
}
iQty = System.Int32.Parse(arrRows[i]["Quantity"].ToString( ));
dPrice = System.Decimal.Parse(arrRows[i]["Price"].ToString( ));
lineTotal = iQty * dPrice;
Console.WriteLine("{0}", lineTotal);
/* Keep a running total. */
runningTotal += lineTotal;
}
/* Display the total of the order. */
Console.WriteLine("Total: {0}", runningTotal);
}DisplaySingleOrder finds a single row in the Order table with a given
order ID. Once this row is found, we ask the row for an array of
dependent rows from the OrderDetail table according to the
Order_OrderDetail relationship. With the returned
array of DataRows, we then proceed to display all fields in the row.
We also calculate the lineTotal value based on the
quantity ordered and the price of the item, as well as keeping a
runningTotalfor the whole
order. The following shows the output from
the
DisplaySingleOrder
function:
Order: 101 Name: John Doe Date: 5/1/2001 12:00:00 AM --------------------------- 101 Item-100 12 59.95 719.4 101 Item-200 1 9.25 9.25 Total: 728.65