Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

If you need to return and manage a rowset returned from the ExecuteReader function (since that's the only way you can fetch a rowset), you have several choices. You can either spend the afternoon writing code to pick off and hard-cast each and every column that's returned and moving the data column by column to where ever you need it (like arrays or individual display controls), or you can take the fast and easy approachuse the DataTable or DataSet Load methods. I leave that discussion to sections later in this chapter that explain and show how to handle resultsets efficiently.

As you saw in Table 11.1, the ExecuteReader function returns a SqlDataReader. It should be more or less familiar to you now, as I've created SqlDataReader streams any number of times in earlier examples. Basically, the SqlDataReader is a "connected" data streamit cannot exist without an open connection to feed it data. It's also a "one-way" stream that exposes only a single row of data at a time. Yes, behind the scenes, the stream is buffered, and you can adjust that size using the PacketSize keyword in the ConnectionString. It's read-only, in that there is no mechanism to change the server-side data. While the SqlDataReader rows are being fetched, the Connection cannot be used for other workunless you enable MARS. The high(er)-level DataAdapter and TableAdapter Fill methods create SqlDataReader streams to fetch rows behind the scenes.

Frankly, I don't usually recommend use of ExecuteReader and the SqlDataReader for many applicationsat least, I didn't. That's because I usually need the features and flexibility, methods and events associated with updateable or simply scrollable and bindable DataTable and DataSet objects. A SqlDataReader data stream in itself is not particularly usefulespecially in Windows Forms applications. While you can bind a SqlDataReader to a bound control in an ASP application, that's not possible in Windows Forms. The SqlDataReader can't be sorted or filtered, and there are no "find" methods exposedbeyond the sort, filter, and find done by the initial query. Remember, the SqlDataReader is a "stream"like a flow of boxes on a high-speed conveyor belt. You can't reorder or filter out selected boxes after they're on the belt. Sure, you can choose the order in which the rows are placed in the stream and even filter in/out those specific rows you want to return, but all of these ordering and filtering operations are done on the servernot while the rows are being transported to your application.

Fetching Data from the Stream

The task of extracting the data returned by a SqlDataReader can be code-intensive and very brittle, as it can be easily broken when you (or someone you aren't speaking to since that incident in the cafeteria) make schema changes. While it's easier than wading hip-deep into freezing water to scoop out salmon from a fast-running mountain stream, it can be just as time-consuming to write the codeat least you don't have to compete with grizzly bears and your butt does not get as cold. Later in this chapter, I'll show you how to manage rowset population with far less code.

Testing the Stream for Data

The SqlDataReader is unlike anything you've used before (unless you're familiar with DB-Library). To start with, you should ask ADO.NET if any rows were returned by the query. Since ADO.NET 1.1, this is accomplished quite easily by using the SqlDataReader HasRows function. If HasRows returns true, there is a populated rowset available on the SqlDataReader. If the query succeeds but returns no rows, HasRows returns false. Since the SqlDataReader steps over resultsets generated by action commands (like UPDATE), you'll get only a RecordsAffected setting (one for each resultset). Figure 11.6 illustrates use of several ExecuteReader functions to execute and process a T-SQL batch, including HasRows to determine whether there is a rowset to process, Read to fetch each row, GetValue to fetch the column data values, and NextResult to step from one T-SQL command to the next.

Unlike ADO classic, ADO.NET handles manual rowset processing very differently. Since the SqlDataReader is a stream, there's no need for any "move" methods like MoveFirst, MoveNext, or MoveLast. These ADO classic Recordset methods made more sense for the ADO classic "cursor"-based approach.

In contrast, the SqlDataReader uses a new way to fetch the data from the data streamwell, it's new to ADO classic developerswe used this approach for years with ISAM data engines and DBLib. In this case, you first test for an available rowset using the HasRows functionif it's true, you can proceed to the Read function, as shown in Figure 11.7. Read fetches the row from the cache and exposes it to the Get methods. Read returns false when there are no more rows to fetch in the current resultset. That does not mean there isn't more data in the stream. To see if there are more resultsets, you need to execute NextResult to position to the next resultset. If NextResult returns true, you need to loop back, test for another rowset using HasRows, and proceed through the Read phase again. Once NextResult returns false, there are no more resultsetsbut there's still more data to fetch if you executed a stored procedure (especially one with OUTPUT parameters). Once the DataReader is closed (using Close or Load or by binding to a complex control), you can fetch the OUTPUT parameters. Now, there's no more data to fetchexcept the RecordsAffected property. Having fun yet? Of course, the Load and Fill functions do all of this for you with a single line of code.

You'll also see the Read method reappear if you start working with the SqlCeResultSet cursor, which exposes a scrollable cursor that uses Read, ReadNext, ReadPrevious and ReadLast to position the current row pointer. Be sure to read my new EBook Hitchhiker's Guide to SQL Server Everywhere^[6]it explains the SqlCeResultSet as well as a myriad of other SSEv details.

^[6] Visit the Hitchhiker's Guide support site for more details. www.hitchhikerguides.net

Fetching Individual Rows

Since the SqlDataReader is just a stream of rows being returned by the interface, it does not have an "in-memory" representation. Until the 2.0 Framework, this means that you have to (manually) move the rows and columns (one by one) into variables, an array, or a control of some kind to be able to use them. As I've said before, the new DataTable Load method virtually eliminates the need to do this yourselfat least, for simple (single) resultsets. ADO.NET 2.0 permits you to create a SqlDataReader stream and use the Load method to read the first rowset into a new DataTable. Assuming you can work with a DataTable and can afford the slight overhead associated with a DataTable, this approach makes a lot of sense. While the DataTable is a bit more expensive than a simple array, it exposes a wealth of features that you're likely to use. There are a number of other benefits to the Load method. For instance, you don't have to create a DataAdapter, a separate SelectCommand, and use the Fill method when you simply want to populate a DataTable from a query. You also don't have to hard-code the DataTable definition or generate it with some clever code that constructs it based on the SqlDataReader schema.

Yes, there are times when you're going to need to extract the data from a SqlDataReader manually. Let's walk through these techniquesif for no other reason than to show you how much trouble you'll be faced with when you don't use the Fill or Load methods to extract SqlDataReader data. After you've executed Read, you can proceed to fetching the data values from the stream. Don't forget this step. If you do, you'll throw a "Data not available" exception.

In the first version of ADO.NET (1.0), there was no GetRows function, so developers had to execute the Read method to determine if one or more rows were returned by the query. Unfortunately, Read also moves the "current row" point ahead, so if you use Read to test for rows and then bound the SqlDataReader to a complex bound control, the first row would be lost. That's because bound controls automatically execute a Read function before extracting data from the SqlDataReader.

Tip

Don't forget to call Read before using the Get functions.

Let's start with an example (as shown in Table 11.1) that calls the same MultipleResultsetOUTPUTTitlesPriceByState stored procedure as I called in the previous chapter. In this case, I don't (can't) use the DataTable Load method because it closes the SqlDataReader as soon as it processes the first SqlDataReader. This prevents one from capturing the rows from the second or any subsequent resultsets.

Instead of using the DataTable Load method, I use my own custom class GetTable, as shown in Figure 11.9. While not particularly efficient, it illustrates the code needed to access specific column values. This routine first uses the SqlDataReader GetSchemaTable method to return the SqlDataReader schema and builds a new DataTable Columns collection from this DDL definition. Once the DataTable is configured to match the schema of the inbound SqlDataReader, the data is extracted in the example row by row and column by column using the SqlDataReader GetValue method in a couple of loops. This means that if the schema changes (as it often does as your database matures), this code will have to be recoded and redeployed. I'll discuss the other "Get" methods next and more efficient methods introduced in ADO.NET 2.0 later in this chapter.

Using the SqlDataReader Get Functions

There are a wealth of "Get" functions associated with the SqlDataReader used to retrieve the schema and metadata, as well as fetch data from selected columns in the data streambasically, there is a Get function for each of the supported datatypes, plus a couple to return object values. Remember that ADO.NET returns data as an Object, so you'll want to cast the values into type-specific variables as it's imported into your application. This is what the Visual Studiogenerated strongly typed TableAdapter and DataSet classes do.

IMHO

Managers appreciate it when developers write fewer lines of code. These apps are cheaper to develop and easier to support.

As I'll show you, you can use the GetValue function to extract data without specifying the datatype, but this is (marginally) more expensive than fetching data with type-specific Get functions such as GetInt16. Table 11.4 lists and explains the basic characteristics of these methods.

Don't get me wrong. I think that there are precious few situations where you should use these functions. First, they are code-intensive. If you take this approach, you'll write and have to support a dozen lines of code (or perhaps far more) to simulate part of the functionality exposed in the Load or Fill methods. If your schema changes, you'll be back recoding and redeploying your application. Yes, there are situations where you want to fetch only one or a few columns from a SqlDataReader stream, and perhaps (just perhaps) this approach makes sense.

There is another entire set of these Get functions that is used to return SqlTypes. I won't list these here, as they're pretty intuitive. For example, GetSqlString returns a SqlTypes.SqlString. These are useful when targeting SqlType variables.

Fetching All Columns

There is another approach to fetch the data columns that I should mention: GetValues and GetProviderSpecificValues. These functions move all of the columns in the SqlDataReader row into an array of type System.Object. Okay, now that the row is loaded into an array, what can you do with it? I discussed binding to arrays in Chapter 8, "Getting Started with ADO.NET." Again, I don't see the real utility herewhy not just create a DataTable and populate it with Fill or Load?

Fetching Metadata from the SqlDataReader Stream

Table 11.5 lists the SqlDataReader metadata functions used to return detailed information about the SqlDataReader and the data it's exposing.

Implementing these Get functions (as illustrated in Figure 11.10) is straightforward. All of them can be called prior to Read except for IsDBNull. These functions are critical to any code trying to manually decode the SqlDataReader stream.

As I'll illustrate when I start discussing the new TableAdapter class and the "old fashioned" SqlDataAdapter, there are other ways to execute queries that return rowsets like the Fill function. I'll discuss these later in this chapter.

Tip

Keep in mind that low-level DataReader steams are used to return rows from a host of data sourcesnot just SQL Server. If you need to import, query, or correlate data from flat files, Oracle, DB2, Excel, delimited files, or any other data source, you can use other .NET, OleDb, Odbc, or other providers to return a data stream that your application can consume. As I discussed in Chapter 8, you can also pass these DataReader streams to the ADO.NET 2.0 SqlBulkCopy function so the data can be imported into SQL Server.

SqlDataReader Best Practice Designs

Yes, it's possible to read and process each row and column from a SqlDataReader stream one at a time. If you use this approach, you'll also have to hard-code references to each individual column you wish to fetch. Of course, all of this code can be replaced with a single DataTable Load method call that populates a DataTable from the SqlDataReader stream. The downside to using the Load method is that you can't charge as much for your applicationassuming you're charging by the line. Is it faster to fetch rows and columns one by one? Yes, it can be if you don't plan to perform all of the work done by the Load (or Fill) methods. If your program is sifting through the rows one by one and performing logic on the data before you fetch the next row, you might also benefit from this approachassuming that it could not have been done on the server without transporting it to the client in the first place. But what happens when the schema changes? In this case, you'll have to recode the routines that fetch the individual columns, recompile, retest, and redeploy. Next, I discuss how to use the SqlDataReader functions and methods to fetch and process resultsetsif you're still interested.