Page 101 - DCAP312_WEB_TECHNOLOGIES_II

Unit 5: Database Access

Update method. No, it is not really a batch in the traditional sense, but that is what Microsoft Notes
chose to call it. What really happens is ADO.NET walks the set of rows passed to the Update
method and executes either the UpdateCommand, DeleteCommand or InsertCommand based
on the row’s RowState value. Each execution requires a round trip. It does not help that all too
many of the examples you will encounter suggest that you use the DataAdapter to fetch all of
the rows (and columns) from a database table and construct appropriate action queries to enable
the Update method. While this approach is valid for “toy” databases, it flies in the face of the
constraints of scalable, high-performance, real-world systems. If you are working with home
databases or those in your office with only a few hundred rows, and you are not concerned
with multi-user issues (and never plan to be), whether or not you fetch all of the rows from
the table would not make much difference. However, in most business applications, especially
those that have to scale up to support dozens to thousands of users and work with thousands
to millions of rows, how you fetch and update the data is critical to a successful application.
5.4.3 The Role of the CommandBuilder

One of the most respected produce managers at Microsoft quipped that the CommandBuilder
should be renamed the "CommandDonotUseBuilder". The CommandBuilder is a class used to
construct the action SQL that changes the data particular in the DataAdapter SelectCommand.
That is, the CommandBuilder uses the SelectCommand.CommandText to query the database
engine for additional schema information using the undocumented SET NO_BROWSETABLE
function (or other mechanisms as dictated by the data provider). If you choose to use the
CommandBuilder, you will discover the SQL it generates is pretty crude. In that the approach
it takes to concurrency management is very simplistic and inefficient and, more importantly, not
functional in a variety of common situations – not to mention the need for a costly additional
round trip to the server.
5.4.4 Fetching Data with the DataAdapter

There are several approaches to fetching data using the DataAdapter – only a few of the more
common techniques are discussed here. Each of these techniques lends itself to addressing
specific issues and data access challenges and opportunities for better performance. The following
discussion illuminates these techniques and balances their advantages and challenges.
• Return a rowset generated by an SQL SELECT that references a single-table, view or stored
procedure.
• Return two or more rowsets generated by a “batch” SQL query containing more than one
SELECT referencing one or more data tables or views.
• Return a rowset generated from a JOIN product that references several related tables.
• Return a rowset generated from a composite query. That is, the data is returned from
more than one data source.
On the surface, DataAdapter fetch mechanics are really quite simple. To retrieve the data, set
the SelectCommand property with a .NET data provider Command object describing either the
appropriate SELECT statement or stored procedure. Next, describe and specify the parameters
and values needed to focus the query’s WHERE clause and use the Fill method to execute the
query. For example:
da = New SqlDataAdapter( “SELECT pubid, pubname, _
city, state” & “ FROM publishers WHERE _
state = @StateWanted “ & “ SELECT pubid,isbn, title, _
price FROM titles where pubid in “ & _

LOVELY PROFESSIONAL UNIVERSITY 95