Main Page

  Previous Section Next Section

7.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. 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 Examples

For 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 name—including 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

The MSDE installation included with Version 1.0 of the .NET Framework SDK is vulnerable to the SQL Slammer worm described in the Microsoft Knowledge Base article Q813440 (http://support.microsoft.com/?kbid=813440) and in article Q813850 (http://support.microsoft.com/?kbid=813850). If you have installed the NetSDK MSDE instance from the .NET Framework SDK Version 1.0, you should download and install the patch immediately to protect your systems.

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" %>
   <title>SqlDataReader Example</title>
      <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
   <h1>SqlDataReader Example</h1>
   <asp:label id="Output" runat="server"/>
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. 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" %>
   <title>DataSet Example</title>
      <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.
            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>";
   <h1>DataSet Example</h1>
   <asp:label id="Output" runat="server"/>

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.

If you open a connection associated with a SqlDataAdapter object (or other data adapter object) before calling Fill or Update, the data adapter will not close the connection automatically. If you open the connection explicitly, always be sure to close it, or you may find your scalability suffering.

A good practice is to open the connection in a Try block, and use a Finally block to ensure that the connection is closed, even if an exception is thrown. For more information on Try and Finally, see the discussion of error handling in Chapter 10.

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.

Another important point to observe about the code in Example 7-2 is that because we're not keeping a database connection open while looping through the data, we can take as much time as we'd like in displaying the data without affecting the ability of others to obtain connections to the database. We can also use the ASP.NET cache engine to cache the entire dataset for later use, if desired, so that we don't have to retrieve the data again. For data that is updated infrequently, this can result in a significant performance improvement, since it is far faster to retrieve a dataset from memory than to requery the data from the database.

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 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.

      Previous Section Next Section