Using ADO.NET's DataReader and DataAdapter
For me, the most enjoyable part of designing a software application is designing the database and the application’s data access layer. ADO.NET is the best tool I’ve found for the job. It excels at accessing data from almost any source, manipulating that data, and then sending it to just about any platform in XML form. Better yet, since ADO.NET is built on top of the Microsoft .NET framework, it’s robust and scalable right out of the box.
Most developers in the Microsoft arena are familiar with the legacy set of data access objects known as ADO. The ADO.NET object model was designed to be as similar as possible to ADO without sacrificing functionality. Therefore, in writing "Sams Teach Yourself ADO.NET in 24 Hours," I tried to point out the many similarities and differences between ADO and ADO.NET in order to make the transition for Visual Basic 6 developers as easy as possible. For this same reason the examples in this book were all created in Visual Basic .NET (with several of them provided in C#, as well). However, this book is also perfect for developers with some Microsoft .NET experience and no knowledge of legacy Microsoft development platforms whatsoever.
The four chapters you’ll be reading here showcase some of the new features of ADO.NET. Chapters 8 and 9 show you how to access data and bind them to a Windows form using Visual Studio .NET. Chapter 11 demonstrates how to use the same data access code to display data on Web forms using ASP.NET’s built-in List controls. Lastly, chapter 18 shows how to create and manage database transactions using ADO.NET.
— Jason Lefebvre
This chapter is from the book
This chapter is from the book
This chapter is from the book
In Hour 6, "Retrieving Data from the Data Source," you saw how to use ADO.NET to connect to a data source, fetch some records, place them into a DataSet using the DataAdapter, and display them on a Web form. In this hour, you'll see an alternative—and in many instances more efficient—method of retrieving data. Specifically, in this hour, you'll learn how to
-
Bind the DataReader object to Web controls
-
Step through the results of a DataReader object
-
Determine when to use a DataAdapter versus a DataReader
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>
NOTE
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.
){kind=link}
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 DataSets 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.
Figure 8.1 The appearance of a Web form with bound Web controls.
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.