Database Reference
In-Depth Information
Select *
From Production.Productionwhere Color= 'Black'
order by ProductNumber asc
This would be ideal in terms of performance, but it'd be feasible only if the data you needed was
limited to these specific rows in this particular sequence. However, if you were building a more elaborate
system, it might be better to pull all the data once from the database (as you do here) and then filter and
sort it in different ways. ADO.NET's rich suite of methods for manipulating data sets and their
components gives you a broad range of techniques for meeting specific needs in an optimal way.
Tip
In general, try to exploit SQL, rather than code C# procedures, to get the data you need from the database.
Database servers are optimized to perform selections and sorts, as well as other things. Queries can be far more
sophisticated and powerful than the ones you've been playing with in this topic. By carefully (and creatively)
coding queries to return
exactly
what you need, you not only minimize resource demands (on memory, network
bandwidth, and so on) but also reduce the code you must write to manipulate and format result set data.
The loop through the second data table is interesting mainly for its first line, which uses an ordinal
index:
foreach (DataRow row in dtc[l].Rows)
You don't rename the second data table (you could do so with its
TableName
property), it is better to
use the index rather than the name Location), since a change to the name in the
Fill()
call would
require you to change it here, an unlikely thing to remember to do, if the case ever arises.
Comparing FilterSort to PopDataSet
In the first example,
PopDataSet
(Listing 15-1), you saw how simple it is to get data into a data set. The
second example,
FilterSort
(Listing 15-2), was just a variation, demonstrating how multiple result sets
are handled and how to filter and sort data tables. However, the two programs have one major
difference. Did you notice it?
FilterSort
doesn't explicitly open a connection! In fact, it's the first (but won't be the last) program
you've written that doesn't. Why doesn't it?
The answer is simple but
very
important. The
Fill
method
automatically
opens a connection if it's
not open when
Fill()
is called. It then closes the connection after filling the data set. However, if a
connection is open when
Fill()
is called, it uses that connection and
doesn't
close it afterward.
So, although data sets are completely independent of databases (and connections), just because
you're using a data set doesn't mean you're running disconnected from a database. If you want to run
disconnected, use data sets, but don't open connections before filling them (or, if a connection is open,
close it first). In other words, data sets are inherently disconnected from the database. However, that
does not mean the application using DataSet is disconnected.
You leave the standard
conn.Close();
in the
finally
block. Since
Close()
can be called without
error on a closed connection, it presents no problems if called unnecessarily, but it definitely guarantees
that the connection will be closed, whatever may happen in the
try
block.
