DataSet, DataTable, DataColumn, DataRow, UniqueConstraint, and ForeignKeyConstraint Object Reference

A Brief Overview of Relational Data Access

Obviously, ADO.NET did not pioneer relational data access. It was predated by other ways of processing data from related tables. Let's review the most common methods of working with data from related tables and quickly compare them to using the DataRelation object.

Join Queries

Join queries predate all Microsoft data access technologies. They're a simple, standard way to retrieve data from multiple tables in a single query. The following query retrieves data from the Customers, Orders, and Order Details tables in the Northwind database:

SELECT C.CustomerID, C.CompanyName, C.ContactName, C.Phone,         O.OrderID, O.EmployeeID, O.OrderDate,         D.ProductID, D.Quantity, D.UnitPrice     FROM Customers C, Orders O, [Order Details] D      WHERE C.CustomerID = O.CustomerID AND O.OrderID = D.OrderID

The benefits of join queries include:

  • They're a widely accepted standard Every database programmer knows how to use join queries.

  • They return results in a single structure

  • They're easy to filter If you want data only for customers from a particular country, you can simply add a filter to the query and the query will return data for only those customers.

The drawbacks include:

  • They can return redundant data If a customer has 100 orders, the same customer information will be returned for each and every order. (See Figure 7-2.)

    Figure 7-2

    Data returned by a join query

  • They're difficult to update It's hard for a data access model such as ADO.NET to know how to interpret the changes to the results of a join query. For example, if you delete a row, does that mean you want to delete just the corresponding row in the child table or do you want to delete the row in the parent tables as well? If you add a new row, does that mean you want to add a new row to just the child table or do you want to add a new row to the parent tables as well?

  • They're difficult to keep in sync If you modify a parent row—by changing the contact name for a customer, for example—you have to submit the change to the database and then reexecute the entire query to see that change in all related rows in the result set.

Separate Queries

Because join queries have always been notoriously difficult to update using data access technologies such as DAO and ADO, many developers use separate queries to retrieve data from each table into separate structures.

The benefits of using separate queries include:

  • They return less total data than join queries do

  • They're more suitable for updates Because you're modifying a structure, such as a Recordset, that corresponds to a single table, it's easy for a technology such as ADO to interpret that change and modify the data in your database accordingly.

  • They're suitable for multiple data sources You can use this approach if the related tables exist on different database systems.

The drawbacks include:

  • They require synchronization code To locate the orders for a particular customer, you must apply a filter to the child Recordset and write code to keep the Recordset objects in synch with each other.

  • They're difficult to filter Constructing queries against child tables that retrieve only the rows that relate to the rows retrieved from the parent table can be challenging. We'll examine this topic further later in the chapter.

Hierarchical ADO Recordset Objects

ADO 2.0 introduced the concept of a hierarchical Recordset. You can use a special provider and a special query syntax to combine the results of multiple queries into a single structure. The following code snippet retrieves the contents of the Customers, Orders, and Order Details tables into a hierarchical Recordset:

Dim rsCustomers As ADODB.Recordset, rsOrders As ADODB.Recorders Dim rsOrderDetails As ADODB.Recordset Dim strConn As String, strSQL As String strConn = "Provider=MSDataShape;Data Provider=SQLOLEDB;" & _           "Data Source=(local)\NetSDK;Initial Catalog=Northwind;" & _           "Trusted_Connection=Yes;" strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName, " & _          "ContactTitle FROM Customers} AS Customers APPEND " & _          "((SHAPE {SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _          "FROM Orders} AS Orders APPEND ({SELECT OrderID, ProductID, " & _          "UnitPrice, Quantity FROM [Order Details]} AS OrderDetails " & _          "RELATE 'OrderID' TO 'OrderID') AS OrderDetails) AS Orders " & _          "RELATE 'CustomerID' TO 'CustomerID') AS Orders" Set rsCustomers = New ADODB.Recordset rsCustomers.Open strSQL, strConn, adOpenStatic, adLockBatchOptimistic Set rsOrders = rsCustomers.Fields("Orders").Value Set rsOrderDetails = rsOrders.Fields("OrderDetails").Value

The Recordset has three object variables, but they all reference data that's maintained in a single structure. As you navigate through the top-level Recordset, only the related data will be visible in the child Recordset objects.

The benefits of using hierarchical Recordset objects include:

  • They return less total data than join queries do

  • They return data in a single structure

  • They don't require complex synchronization code

  • They're suitable for simple updates However, even though hierarchical Recordset objects handle simple updates well, they have limitations. Submitting pending changes against multiple tables can be problematic at best.

The drawbacks include:

  • The query syntax is hideous Look at that query! I like to consider myself an ADO expert, but I never bothered to learn the SHAPE syntax.

  • They offer limited control You have to define the relationship in your query.

  • You can query only a single data source

  • They're difficult to filter

ADO.NET DataRelation Objects

The ADO.NET DataRelation is very different in structure from hierarchical Recordset objects. DataRelation objects do not require an additional provider, and no abominable SHAPE query syntax is required. DataRelation objects are considered part of the DataSet object's schema.

Quite simply, the DataRelation combines the best features of the separate query and hierarchical Recordset approaches to managing data from related tables and eliminates nearly all of their drawbacks. The following lists of the pros and cons should whet your appetite for now.

The benefits of using DataRelation objects include:

  • They return less total data than join queries do

  • They simplify locating related data

  • They don't require complex synchronization code

  • They can handle advanced updating scenarios For example, you can submit new customers before new orders but also delete existing orders before deleting existing customers. Or, if you have a series of pending orders and order details, you can fetch server-generated autoincrement values for your new orders before submitting their new order details. I'll cover both scenarios in detail in Chapter 11.

  • They're dynamic You can create, modify, and delete DataRelation objects programmatically before or after you query the related database tables.

  • They support cascading changes You can control whether changes to a row cascade down to child rows by using properties of the foreign key constraint associated with the DataRelation.

  • They support creating hierarchies from different data sources Need to relate the results of a customer query against a SQL Server database and an order query against an Oracle database? No problem.

The drawbacks include:

  • They're difficult to filter Unfortunately, DataRelation objects do not simplify fetching only child rows that correspond to the desired parent rows. I'll discuss ways of handling such scenarios later in the chapter.



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net