Browse DevX
Sign up for e-mail newsletters from DevX


ADO.NET Best Practices, Part II : Page 2

In ADO, inner joins are accomplished using proper SQL statements within a single batch. With ADO.NET, you can obtain the same data organized in independent but related tables which greatly simplifies updates. What's faster? That depends on the application.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Conducting Multiple Queries
In light of the ADO.NET capabilities, you can implement multiple queries that span across multiple tables in at least two ways. The old-fashioned way, so to speak, is typical of ADO and any former data-access technology. It is based on the old faithful SQL JOIN command and returns a single, monolithic data structure that is quite easy to filter, but hard to update. In addition, the final result set is populated with redundant data.

A data adapter that is about to update a row during a batch update process fires the RowUpdating and RowUpdated events, respectively, before and after the update.
The alternative approach, touted by the ADO.NET disconnected model, exploits the capabilities of the DataSet object—that is, a multi-table cache of data. The idea is that you split the original query into two or more distinct queries and gather the results in separate tables held in memory within the same DataSet object. The queries can be run individually or grouped in a stored procedure or in a batch. Using split queries results in a much more compact data set that can be easily managed via the programming interface of the DataSet and DataRelation objects.

Once the result set is stored into a DataSet, it supports cascading changes and batch updates. Furthermore, no synchronization code is required to retrieve the children of a parent row because automatic relationships can be declared. On the downside, just relationships make applying filters to the records not particularly easy.

Suppose you need to obtain all orders issued to a given country. The query can be written like this:

SELECT c.customerid, c.companyname, c.city, o.orderid, o.orderdate, od.productid FROM customers c INNER JOIN orders o ON c.customerid=o.customerid INNER JOIN [order details] od ON o.orderid=od.orderid WHERE c.country = 'USA' ORDER BY o.customerid

The query spans three Northwind tables—Orders, Order Details, and Customers. It retrieves all orders issued to a customer resident in the US. Viewed through the Microsoft SQL Server Query Analyzer, the result set looks like Figure 1.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date