Page 102 - DCAP312_WEB_TECHNOLOGIES_II

Web Technologies-II

Notes “ (SELECT pubid from publishers WHERE state = _@StateWanted)”, cn)
da.SelectCommand.Parameters.Add(_ “@StateWanted”, SqlDbType.VarChar, 2)
dsPublisherTitle = New DataSet() _
‘Create new Dataset
The Fill method makes sure the connection is open, executes the query, constructs any needed
DataTables, completes rowset population (fetches the rows into the DataTable Rows collection),
and closes the connection.

Using the SelectCommand to manage an SQL query and here is an example of the code:
da.SelectCommand.Parameters(_“@StateWanted”).Value = txtStateWanted.Text
da.Fill(dsPublisherTitle)
DataGrid1.DataSource = dsPublisherTitle.Tables(0)
DataGrid2.DataSource = dsPublisherTitle.Tables(1)
lblTables.Text = dsPublisherTitle.Tables.Count.ToString
lblRows.Text = _
dsPublisherTitle.Tables(0).Rows.Count.ToString & “ : “ & _
dsPublisherTitle.Tables(1).Rows.Count.ToString
Depending on how the DataAdapter is configured, the Fill method either creates a new DataTable
for each rowset returned, or cleverly “updates” the DataTable objects already constructed in a
selected DataSet. But before we get to that most powerful achievement, let us step through some
of the interesting fetch scenarios. The scenario that makes the most sense for you depends on
where the data is located and whether or not you need to use the Update method to perform
updates on a selected database table.
5.4.5 The Single-Table Fetch Approach

Most of the examples illustrates the use of the DataAdapter by coding the SelectCommand
CommandText as “SELECT * FROM Authors”. This simple query masks several important
issues. First, it does not limit the number of rows or columns returned from the data table. This
approach is fine for home databases, but cripples an application dealing with serious amounts of
data. This means you will need an example of fetching selected columns focused (and limited)
by a WHERE clause for your production application that extracts rows from that 100,000 (or
1,000) row table (assuming you want to build a scalable application). This simplistic approach
also assumes that you can return the information you want without benefit of data from other
tables. Sure, there are lots of cases where a simple query will do. But SELECT * is evil. It blindly
returns all columns from a product whether you need them or not and assumes that changes to
the database table post-deployment would not affect your application. This is called optimistic
programming, or programming by wishful thinking. Ironically, this approach is the one most
likely to work with the DataAdapter’s CommandBuilder used to generate the action queries to
update the fetched data.

5.4.6 The Multiple Resultset Approach
Assuming the single-table query approach would not work for your application, you should
examine the outcome of a “multiple resultset” query. Suppose you avoid the lure of the
DataAdapter configuration wizard and create a DataAdapter using the code in our first listing,
above.
This query returns two independent rowsets which might be logically related. In this case,
ADO.NET constructs two DataTables (or it will if the Fill method is executed) one containing
selected Publisher names and the other associated Titles. It is up to my own client-side code to
relate these two tables if, let ADO.NET manage and bind controls to show these relationships.

96 LOVELY PROFESSIONAL UNIVERSITY