DataReader Versus DataAdapter

for RuBoard

DataReader Versus DataAdapter

In Hour 6, you saw how to use the Command object in conjunction with the DataAdapter object to retrieve records from the database and place them into a DataSet . The DataSet was then bound to a Web control such as the DataGrid and displayed in a Web form. The code in Listing 8.1 is a review of the ADO.NET code required to perform these tasks .

Listing 8.1 Retrieving Records with the DataAdapter

 <script language="VB" runat="server" >    Sub Page_Load(Source as Object, E as EventArgs)          Dim conn as New SqlConnection("Initial " + _             Catalog=Northwind;Server=(local);UID=sa;PWD=;")       Dim cmd as New SqlCommand("SELECT * FROM Employees", conn)       Dim adapt as New SqlDataAdapter(cmd)       Dim dsEmployees as New DataSet()       conn.Open()       adapt.Fill(dsEmployees, "Employees")       conn.Close()       employees.DataSource = dsEmployees       employees.DataBind()    End Sub </script> 

If you are placing the code from Listing 8.1 into a Web form, do not forget to import the System.Data and System.Data.SqlClient namespaces at the top of your Web form.

To use the DataAdapter , you must create a DataSet , as shown in line 7 of Listing 8.1. The DataSet is then passed to the DataAdapter in line 10, where it is filled with records from the database. In lines 13 and 14, the DataSet is then bound to a Web control in order to display the data as shown in Figure 8.1.

Figure 8.1. The appearance of a Web form with bound Web controls.

There is one problem with this method of retrieving data: The DataSet object exists in memory and contains all rows returned by your query. Suppose that you are retrieving a large number of records from the data source. For the brief amount of time it takes to bind the data to your form and send it to the user , you have a potentially large amount of memory consumed by the DataSet .

If you are only retrieving small DataSet s on a low-traffic site, this probably won't be of much concern. However, as the number of concurrent users of your application increases , the more important this issue becomes.

Like the DataAdapter , the DataReader object is designed to retrieve records from the data source. However, unlike the DataAdapter , the DataReader never has more than a single database record in memory at any given time. It does this by opening a forward-only, read-only stream of data from your data source.

If you think of your data source as a water reservoir, you could envision the DataAdapter method of returning records as a man running back and forth between the source and destination with buckets (albeit very quickly). The DataReader is more like a firehose.

for RuBoard