| [ Team LiB ] |
|
7.2 Reading DataThere 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 DatabaseThe 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 readerThe 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.
Figure 7-1. Output of Add_ASPNET.bat
Example 7-1 shows the implementation of a 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 adapterFor 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.
Once the dataset has been filled, we 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.
The output of Example 7-2 should look much like Figure 7-2 (with the exception of the heading, which will read "DataSet Example"). 7.2.2 Reading from XMLOne 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.  |
| [ Team LiB ] |
|
