Using a Data Reader
|
In the last chapter, we breezed over the actual use of data readers by employing them in the simplest way we could - we just executed the command object's ExecuteReader() method, and assigned the result straight to the DataSource property of a DataGrid control, leaving the latter to do the rest. In fact, however, this is an entirely typical way of using a data reader - we don't call its constructor, and we tend to leave the job of 'doing the reading' to another object. The purpose of this section is to spell out how data reader objects work, and to detail their close relationship with command objects. Let's look again at the code we were using in the last chapter to place information from the Northwind database in our web pages:
objConnection.Open() dgNameList.DataSource = objCommand.ExecuteReader() dgNameList.DataBind() objConnection.Close() End Sub This is actually an illustration of the simplest possible case: we've got just one web server control (the DataGrid object), one source of data, and one SQL query to run on that data. The data reader object is created in the second line, and destroyed when the DataGrid object goes out of scope at the end of the event handler. This neatly conceals the fact that data readers have 'open' and 'close' semantics (just like connection objects), as a slightly more involved example will demonstrate. When you're reading data, a common requirement is to find out the number of records returned by an SQL query, for use later when we come to present the data on the page. An easy way to achieve this is to execute two SQL queries against the same data, the first being a COUNT, and the second actually doing the SELECT. The following listing assumes that we have two DataGrids named dgACount and dgAData. Notice that after the first read, we close the data reader; and after the second, we close the connection object.
Dim strConnection As String = "..." Dim objConnection As New SqlConnection(strConnection) Dim strSQLcount As String strSQLcount = "SELECT COUNT(CategoryID) FROM Categories" Dim objCommand As New SqlCommand(strSQLcount, objConnection) objConnection.Open() ' SQL to count Dim objReader As SqlDataReader = objCommand.ExecuteReader() dgCount.DataSource = objReader dgCount.DataBind() objReader.Close() ' SQL to read data Dim strSQLdata As String strSQLdata = "SELECT * FROM Categories" objCommand.CommandText = strSQLdata objReader = objCommand.ExecuteReader() dgData.DataSource = objReader dgData.DataBind() objConnection.Close() Executing two queries allows us to reuse both the command object and the data reader object, but we have to remember to call the reader's Close() method in between the calls to ExecuteReader() - if we don't, we'll get an error that says, "There is already an open data reader associated with this connection." (Re)opening the data reader object is dealt with by the ExecuteReader() method itself. Try It Out - Advanced use of Data Readers
We're going to use this example to pull together a few of the things that we've discussed already in this chapter. Let's imagine that we've been asked to create a page that shows a list of all the products sold by Northwind in 1997, and a count of all the records in the report. This is not quite as difficult as it sounds, since the SQL Server version of the Northwind database has a view that will retrieve precisely this information; it's called "Product Sales for 1997".
How It WorksIn the HTML section, we simply create two DataGrids. The first of these will hold the count, and the second will hold the data that we'll report. It would be a little more efficient to hold the dgCount data in a label, but since we haven't looked at labels yet (you'll have to wait until the next chapter!), we just format our DataGrid to show neither borders nor a header.
<body> <h1>Data Reader</h1> Total Products listed = <asp:DataGrid runat="server" borderwidth=0 showheader=False /> <br/> <asp:DataGrid runat="server" /> </body> Looking at the script, there are two sections of code: the first gets the count, and the second the actual records. In the former, SQL Server does the counting, as it processes the SQL query. Note that in SQL, the COUNT keyword requires you to specify a field - we can't simply count the number of records (although you can also use the syntax COUNT(*)).
' Get and display count Dim strSQLcount As String strSQLcount = "SELECT COUNT(ProductName) FROM [Product Sales for 1997]" Normally, we don't explicitly declare a data reader object, since one is created implicitly by the ExecuteReader() method. The downside of this, however, is that they're destroyed immediately after use. In this example, we intend to use the data reader object twice, which means that we must explicitly declare it so that it persists after the first use.
Dim objCommand As New SqlCommand(strSQLcount, objConnection) Dim objReader As SqlDataReader = objCommand.ExecuteReader() The single datum we retrieve is then put into the dgCount DataGrid:
dgCount.DataSource = objReader dgCount.DataBind() As we observed above, it's important, when using the same data reader object twice, to close it after the first use. Otherwise, when you use it the second time, you'll get an error stating that it's still open from the previous use.
objReader.Close() Next, we begin work on the second task, which is to place the actual data into the second grid. We start by setting up the CommandText property, and notice that using view from a SQL Server database involves exactly the same syntax as using a table:
' Get and display output of procedure Dim strProcName As String strProcName = "SELECT * FROM [Product Sales for 1997]" objCommand.CommandText = strProcName We can then fill the data reader object, using our standard ExecuteReader() method. Note that there is no need to Open() it first.
objReader = objCommand.ExecuteReader() Filling the second grid involves the same syntax as for the first grid:
dgData.DataSource = objReader dgData.DataBind() After the first use of the data reader object, we closed it. After the last use of the data reader, we can close the connection, safe in the knowledge that the data reader will automatically be cleaned up too.
objConnection.Close() By no means does this example demonstrate the limits of data reader objects' capabilities. For example, and as mentioned above, it's possible to read the records returned from a query one by one, applying modifications to each before sending them to be displayed - the Read() method lies at the heart of this ability, as you'll soon see if you take a look at the Microsoft documentation. Our specialization in this book, however, is on ASP.NET's facilities for dealing with data, and it's with that in mind that we're going to move on. | |||||||||||||||||||||||||||||||||||
EAN: 2147483647
Pages: 263