Handling and Displaying Data in ADO.NET and ASP.NET

Chapter 4 - Data Readers, Command Objects, and Web Server Controls
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

Regardless of its size or complexity, an ASP.NET page that uses data via ADO.NET will have three basic sections of code:

• Code to create a connection to a data source

• Code to read from and write to the data source, and modify data

• Code to display data on the ASP.NET-generated page

In the last chapter, we focused on step one: the connection. In this chapter and the one that follows it, we'll go into much more detail about steps two and three.

The second step in particular covers a lot of bases - it can be very simple, or quite complex. Broadly speaking, those two extremes will be addressed in our ASP.NET pages by data reader and dataset objects respectively; which one you choose depends on a fairly simple decision-making process. In a nutshell, you'll use data reader objects when:

• You are only reading data

• The data retrieved from the store is ready to be displayed without further processing

And use DataSet objects (along with helper data adapter objects) when:

• You might modify (edit or add new) data in the data source

• You want to modify the data after reading, and prior to displaying it. For example, you may want to read data into several tables, or create relationships between the data in multiple tables. You may want to constrain columns, or create multiple tables of data from multiple connections.

Note 

A relationship in a database describes how information from one table links to information in one or more other tables. Careful use of relationships greatly reduces the storage space, inconsistencies and maintenance costs of a database. More information on relationships is available in any database text, including Beginning SQL Server 2000 Programming (ISBN 1-8610015-23-7), also from Wrox Press.

As stated above, this chapter will focus on data readers, leaving dataset objects until the next chapter.

Command Objects

In the last chapter, we used command objects without ever fully discussing them; it's now time for us to examine them in more detail. You'll recall from our diagrams that each .NET data provider provides its own implementation of a command object - we saw and used both SqlCommand and OleDbCommand. In terms of their behavior and their members, however, the different command objects are very similar. Unless we state otherwise, any features that we ascribe to one of them in this discussion will be present in all.

A command object holds a definition of what to read to or write from the data store. It sits in the middle of our flow of tasks, between connecting and displaying. The command object and the data reader object (which we'll discuss next) work together: the command object defines what to read, while the data reader defines how to use the connection object to perform the read. The most common definitions of what to read or write include:

• An SQL statement that reads some of the data of a table (some fields and/or some records)

• The name of a table in the data store

• An SQL statement (or stored procedure) that runs a stored procedure that returns data

• An SQL statement that writes to the data store

When the command object is instantiated, ADO.NET requires us to supply two arguments. The first is a string that holds the actual SQL statement (or the table name), and the second is the connection object. If we assume a connection object named objConnection, then we can write the following examples for SQL Server and OLE DB respectively:

    Dim strSQL As String = "SELECT MyColumn FROM MyTable"    Dim objCommand As New SqlCommand(strSQL, objConnection)    Dim  strSQL As String = "SELECT MyColumn FROM MyTable"    Dim objCommand As New OleDbCommand(strSQL, objConnection) 

The default use of the first argument to the command object's constructor, which will be assigned to its CommandText property, is as an SQL statement. If you want to use a table name, or a stored procedure name, then you must change the related CommandType property, as follows:

    ' Read an entire table    Dim strSQL_Suppliers As String = "MyTable"    Dim objConnection As New OleDbConnection(strConnection)    Dim objCommand As New OleDbCommand(strSQL_Suppliers, objConnection)    objCommand.CommandType = CommandType.TableDirect 

Note 

CommandType can only be set to CommandType.TableDirect when you're using the OLE DB.NET data provider. The option is not available with the SQL Server .NET data provider.

    ' Execute a stored procedure (or a query in Access)    Dim strSQL_Suppliers As String = "MyStoredProc"    Dim objConnection As New SqlConnection(strConnection)    Dim objCommand As New SqlCommand(strSQL_Suppliers, objConnection)    objCommand.CommandType = CommandType.StoredProcedure 

A command object's CommandType property can be set to one of three different values. CommandType.TableDirect and CommandType.StoredProcedure are used above; the third is the default value: CommandType.Text.

In this chapter, we'll just use SQL statements to read data. However, SQL can do more than just select data: it enables us to perform complicated sorting and selecting, to provide statistical information on data (such as maximums and averages), and to provide aggregate answers (such as, say, the ten records with the largest values for a particular field). To learn these kinds of syntax, consult a SQL text - for example, Beginning SQL Programming (ISBN 1-861001-80-0), by the same authors as this book.

Data Reader Objects

Data readers form one of the two kinds of object that define how we read data from a connection. Like the command object, they come in different versions for the different data providers, but in use they're so similar that we can consider their common functionality without fear of having to issue countless provisos. Data readers have three characteristics to keep in mind:

• Data readers can only read data. They don't provide for editing, creation, or deletion of the data in records.

• Data readers can only navigate forwards through data. Once you've moved to the 'next' record, there's no going back to the previous one, short of executing the SQL query all over again.

• Data readers do not hold data in the memory of IIS - the data goes straight to the display object (for example, the DataGrid).

By contrast, DataSet objects create a copy of the data in the web server's memory that can be written to, or navigated around. We'll discuss DataSet objects in detail in the .

The read-only constraint of data readers is both a blessing and a curse. The good part is that data readers are very fast and efficient in terms of the resources they consume. They're also very easy to use - the data you read goes straight to a display object such as a DataGrid. The downside, of course, is that if you need to edit, create, or delete data, you cannot use a data reader.

Although the fact that a data reader reads data forward-only sounds restrictive, in practice it's not too severe. The limitation only applies to the set of records that's defined by the command object's CommandText property (your table name or SQL statement). If your command object defines a whole table to read, then you'll get the records from first to last, as they appear in the table - but if you use a string such as "SELECT * FROM Employees ORDER BY LastName", then the data reader will go forward through these records alone, which will have already been sorted from A to Z when they were supplied by the database.

Similarly, if your CommandText is, say, "SELECT * FROM Employees ORDER BY LastName DESC", then your forward-only data reader will actually be reading out the names in reverse alphabetical order. And you can include SQL clauses like TOP or UNIQUE to get exactly the records you want. Once you understand SQL, the world is your oyster: you can supply the data to the data reader in any order, and thus overcome the constraint to a great degree. In the end, forward-only is just about navigation - you cannot move back and forth, or to a specific record, once you have read through the data.

There are two further strategies for data manipulation that can help to overcome the problems of the forward-only nature of data readers. First, you can write code to loop through the records as they are read, and apply modifications before they're displayed. Second, as you'll learn in the , there are ways to sort and select data after it has entered a DataGrid display control. Bear in mind, though, that neither of these strategies will have any effect on the data in the store - that's just not possible with a data reader.