لطفا منتظر باشید ...
Publisher7.2 Reading Data
There are two basic techniques for reading data in ADO.NET: using data readers and
datasets. Additionally, data may be read from either a backend DBMS,
such as SQL Server, or from a simple (or complex) XML file. In the
next several sections, we''ll discuss these
techniques and data sources.
7.2.1 Reading from a Database
The following sections describe the use of data readers and datasets,
provide an example, and discuss why one would use one technique over
the other.
7.2.1.1 Using a data reader
The data reader technique consists of using
an instance of either the SqlDataReader,
OleDbDataReader, or
other data reader class to retrieve the data in a similar fashion to
a forward-only, read-only database cursor. Data readers provide
lightweight access to data that is recommended when retrieving data
for display in a Web Forms page or for other circumstances in which
the overhead of a dataset is not desirable.
About the ExamplesFor the sake of simplicity and consistency, all examples in this chapter that access data from a DBMS use the Pubs sample database in the NetSDK named instance of the Microsoft Data Engine (MSDE). MSDE is a slimmed-down version of SQL Server that fills a role that is similar to Microsoft Access for desktop applications. The NetSDK instance may be installed along with the .NET Framework SDK samples, after installing either the .NET Framework SDK or Visual Studio .NET.All examples in this chapter use trusted connections rather than pass a user ID and password as part of the connection string. As explained later in the chapter, this requires either adding the ASPNET account under which ASP.NET is run to the desired database or enabling Windows authentication and impersonation in the web.config file for the application. The examples in this chapter use the former technique.Among the sample files included with the book is a batch file named Add_ASPNET.bat that adds the ASPNET account to the NetSDK MSDE instance and assigns it the required permissions in the Pubs sample database. This batch file uses the Add_ASPNET.sql file for its commands. Before running Add_ASPNET.bat , you will need to open Add_ASPNET.sql in a text editor and change all instances of <machine or domain> to the name of the machine or domain containing the ASPNET account. If you modify the machine.config file to have the ASP.NET worker process run under a different account than ASPNET, you should modify Add_ASPNET.sql to use that account nameincluding the machine or domain name of the account.Add_ASPNET.bat itself uses a trusted connection to access MSDE, so you must run this batch file while logged in using an account that has administrative access to the NetSDK instance of MSDE (by default, this will include any members of the Administrators group on the machine on which MSDE is installed). Running Add_ASPNET.bat should result in output that looks like that shown in Figure 7-1.Once you''ve run Add_ASPNET.bat , you''re ready to run the samples included with this chapter, which are downloadable from the O''Reilly web site at http://examples.oreilly.com/aspnut2/. |
Figure 7-1. Output of Add_ASPNET.bat
|
SqlDataReader object, which retrieves two
columns from the Titles table of the Pubs sample database from the
NetSDK instance of MSDE. The output from Example 7-1
should look similar to Figure 7-2.
Example 7-1. ReadTitles.aspx
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<title>SqlDataReader Example</title>
<head>
<script runat="server">
Sub Page_Load( )
Dim ConnStr As String = "Data Source=(local)\NetSDK;" & _
"Initial Catalog=Pubs;Trusted_Connection=True;"
Dim SQL As String = "SELECT title, price FROM titles " & _
"WHERE PRICE IS NOT NULL"
Dim PubsConn As New SqlConnection(ConnStr)
Dim TitlesCmd As New SqlCommand(SQL, PubsConn)
Dim Titles As SqlDataReader
PubsConn.Open( )
Titles = TitlesCmd.ExecuteReader( )
Output.Text = "<table>"
While Titles.Read( )
Output.Text &= "<tr>"
Output.Text &= "<td>" & Titles.GetString(0) & "</td>"
Output.Text &= "<td>$" & _
Format(Titles.GetDecimal(1), "##0.00") & "</td>"
Output.Text &= "</tr>"
End While
Output.Text &= "</table>"
Titles.Close( )
PubsConn.Close( )
End Sub
</script>
</head>
<body>
<h1>SqlDataReader Example</h1>
<asp:label id="Output" runat="server"/>
</body>
</html>
Figure 7-2. Output of ReadTitles.aspx
Example 7-1 begins by adding an
@
Import directive to import the
System.Data.SqlClient namespace. The example uses an ASP.NET Label
control called Output for the display of the retrieved data. To get
the data, we create string variables for the connection string and
the desired SQL statement and then create a new SqlConnection
instance, passing the variable containing the desired connection
string to its constructor, which initializes the appropriate
properties on the connection. We then create a new SqlCommand
instance, passing the SQL string and the new connection object to its
constructor. Then we create a SqlDataReader object variable, open the
connection to the database, and set the SqlDataReader object variable
to the instance returned by the ExecuteReader method of the
SqlCommand class.To display the data, we begin by sending an HTML
<table> tag to the Text property of the
Label control and then loop through the contents of the data reader,
adding a row with two cells for each row in the data reader. The
SqlDataReaders'' Read method advances the reader to
the next available row and returns a Boolean indicating whether there
is more data to read. This makes it ideal for looping through data.
Note that the example uses the Visual Basic .NET
Format function to format the price data with
trailing zeros.Finally, once we''ve read through all the rows in the
data reader, we append a closing </table>
tag to the Text property of the label and close both the data reader
and the connection. It is very important that you close both when
using a data reader, since failing to close either object can
negatively impact the scalability of your application by interfering
with the built-in connection pooling mechanism provided by ADO.NET.
7.2.1.2 Dataset and data adapter
For
circumstances when simply reading through a set of rows once is not
sufficient, or if you plan to modify data that
you''ve retrieved for later updating on the backend
data store, the data reader will not be sufficient to meet your
needs. For these occasions, the DataSet class
(part of the System.Data namespace) and the
SqlDataAdapter provide more functionality and
flexibility than the SqlDataReader, albeit at the
cost of additional overhead.Example 7-2 retrieves the same data as Example 7-1, but uses a SqlDataAdapter
and a DataSet instead of the
SqlDataReader. This example is written in C#, to
demonstrate that the basic syntax of calling the ADO.NET classes is
very similar in both VB.NET and C#, with the major difference being
the variable declaration syntax.
Example 7-2. ReadTitles_DataSet.aspx
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<title>DataSet Example</title>
<head>
<script runat="server">
void Page_Load( )
{
String ConnStr = "Data Source=(local)\\NetSDK;"
+ "Initial Catalog=Pubs;Trusted_Connection=True;";
String SQL = "SELECT title, price FROM titles "
+ "WHERE PRICE IS NOT NULL";
SqlDataAdapter TitlesAdpt = new SqlDataAdapter(SQL, ConnStr);
DataSet Titles = new DataSet( );
// No need to open or close the connection
// since the SqlDataAdapter will do this automatically.
TitlesAdpt.Fill(Titles);
Output.Text = "<table>";
foreach (DataRow Title in Titles.Tables[0].Rows)
{
Output.Text += "<tr>";
Output.Text += "<td>" + Title[0] + "</td>";
Output.Text += "<td>" + String.Format("{0:c}", Title[1])
+ "</td>";
Output.Text += "</tr>";
}
Output.Text += "</table>";
}
</script>
</head>
<body>
<h1>DataSet Example</h1>
<asp:label id="Output" runat="server"/>
</body>
</html>
In addition to the @ Import
statement for the System.Data.SqlClient
namespace, we add another @
Import statement to import the System.Data
namespace, which allows us to call the DataSet and
DataRow classes without fully qualifying their
namespace name.As in Example 7-1, we begin by creating a connection
string and a SQL statement, but unlike Example 7-1,
we do not need to create instances of the SqlConnection and
SqlCommand objects; by passing the SQL statement and connection
string to the constructor of the SqlDataAdapter
class, the data adapter instance creates the connection and command
objects internally.Now, instead of creating a SqlDataReader, we
create a new SqlDataAdapter, passing
in the SQL statement and connection string created earlier, and then
create a new dataset. We then call the
SqlDataAdapter''s Fill method to
retrieve the data and store it in the dataset. When the Fill method
is called, the SqlDataAdapter creates a connection
based on the provided connection string, opens it, executes the
query, and then closes the connection. This feature results in
simpler and cleaner code and reduces the likelihood of forgetting to
close a connection.
|
loop through the rows in the first (and only) table of the dataset by
using the C# foreach statement, sending output to
the Text property of the Label control, as in Example 7-1. Note that the example actually declares the
DataRow instance Title within the foreach
statement. In Visual Basic .NET, you would declare the instance
outside of the loop and then refer to it by name in the
For Each statement.Also note that in C#, when referring by index to items such as
the tables in the DataSet object or the items in a DataRow object,
you must use square brackets (rather than the parentheses you would
use in Visual Basic .NET). This is consistently one of the biggest
gotchas in moving from VB.NET to C# and vice-versa. One final
difference in the looping code between Examples Example 7-1 and Example 7-2 is that since
the VB.NET Format function is not available for formatting the price
data, we use the static Format method exposed by the
String class instead; it formats the data as
currency and includes the appropriate regional currency symbol for
the current system.
|
which
will read "DataSet Example").
7.2.2 Reading from XML
One of
the neat things about the
DataSet class is that
it doesn''t require a data adapter or a backend DBMS.
Instead, you can populate a dataset from an XML file or stream by
using the DataSet''s
ReadXml
method. The ReadXml method is overloaded and can read from a Stream,
a TextReader, an XmlReader, or from a
file by passing the filename as a string.
This last technique is illustrated in the custom control examples in
Chapter 6, both of which use the ReadXml method
to populate a dataset with data from an XML file.
