Page 103 - DCAP312_WEB_TECHNOLOGIES_II

Unit 5: Database Access

The code shown in our second listing does just that – it executes the query and returns the two Notes
DataTables which are bound to separate DataGrid controls.

This approach assumes that you do not expect to update the source tables, you expect to use the
DataAdapter Update method to update only one of the source tables, or you plan to provide
your own update routines and not use the Update method. In this case the CommandBuilder
will be able to construct the action commands for you – but only for the first data table specified
by the initial SELECT.
5.4.7 The JOIN Product Approach

Of course, you can still use the server’s ability to JOIN two or more database tables or views
together to produce a composite SQL product and the DataAdapter can execute this query
and construct a DataTable from the product’s rowset. No, ADO.NET does not treat this rowset
differently from any other – one DataTable is created for each unique rowset returned by the
server. This means that you can return a rowset from a JOIN product and another from a single-
table query or another JOIN product and use these to generate multiple DataTables as necessary.
Unlike the previous approach, the CommandBuilder would not be able to construct the action
Commands so you will have to fall back on your own ad hoc action commands or stored
procedures to make changes to the base tables. For many developers, this is what they have
had to do for years anyway.
5.4.8 The Composite Query Approach

One of the more interesting (and powerful) approaches you can use is construction of “composite”
queries that return rowsets from more than one data source. The trick here is to use more than one
DataAdapter to specify different data sources drawing rowsets from the same or different servers,
from different providers or from non-database data sources. Once the data is downloaded into
DataTable objects, it is a simple matter to construct relationships between them. Updating is also
straightforward in this case, and if the SelectCommand is simple enough the CommandBuilder
can be used to generate the action commands. Ideally, you could use stored procedures on each
data source system to perform updates to the data table associated with each DataAdapter.
This approach is bound to become one of the most popular as it deals with so many issues quite
neatly. Regardless of the SQL product’s source, you can define a suitable DataAdapter to focus
the update operation on a specific member. That is, you define a DataAdapter UpdateCommand,
InsertCommand and DeleteCommand to change a specific row in a specific data table – you
define a separate DataAdapter for each database table you wish to update. Just because a DataSet
contains DataTables drawn from disparate data sources that is no reason you cannot update
any or all of the base database tables as needed.

Write a program to INSERT, UPDATE and DELETE data from a table.

5.5 ADO.NET DataSet

The ADO.NET DataSet (See Figure 5.2) contains DataTableCollection and their
DataRelationCollection. It represents a collection of data retrieve from the Data Source. We
can use Dataset in combination with DataAdapter class. The DataSet object offers disconnected
data source architecture. The Dataset can work with the data it contains, without knowing the
source of the data coming from. That is, the Dataset can work with a disconnected mode from
its DataSource. It gives a better advantage over DataReader , because the DataReader is working
only with the connection oriented Data Sources.

LOVELY PROFESSIONAL UNIVERSITY 97