Accessing Data with DataReaders

Straightforward read-only access to data relies on DataReader objects. DataReaders are fast, and they don't use much memory, because only one row of data at a time is made accessible (although more may be cached). An application using a DataReader can read a query's results only one row at a time, and can move forward only through those resultsno random access is allowed. This is very simple, but it's the right solution for a significant set of applications. Like everything else, data access should be as simple as possible. There's enough inherent complexity in software development without unnecessarily adding more.

A DataReader object provides fast access to data

To read a row, the application calls a DataReader's Read method, which makes the next row from the result of the executed query accessible. (This method returns FALSE when there are no more rows to be read from the result.) Once this has been done, the contents of that row can be accessed in various ways. If you know the types of the columns in the result (which is the usual case since you probably wrote the query), the values from the current row can be read by calling the appropriately typed Get methods (officially called typed accessor methods) provided by the DataReader. For example, if a SQL query asks for a list of all employee names and ages, each row in that query's result will contain a string and an integer. To read these, a client application could use the DataReader's GetString and GetInt32 methods, respectively. DataReaders also provide many more Get methods, each capable of reading a particular type of data in a row. It's also possible to access each column of the current row by the column name or position, but using the Get methods is more efficient.

A client reads data from a DataReader one row at a time

Here's a C# class that illustrates opening a connection, creating a command, and reading the results using a DataReader:

using System.Data.SqlClient; class DataReaderExample {     public static void Main()     {     SqlConnection Cn = new SqlConnection(       "Data Source=localhost;" +       "Integrated Security=SSPI;" +       "Initial Catalog=example");     SqlCommand Cmd = Cn.CreateCommand();     Cmd.CommandText =       "SELECT Name, Age FROM Employees";     Cn.Open();     SqlDataReader Rdr = Cmd.ExecuteReader();     while (Rdr.Read())     {       System.Console.WriteLine(         "Name: {0}, Age: {1}",         Rdr.GetString(0),         Rdr.GetInt32(1));     }     Rdr.Close();     Cn.Close();     } }

This example uses the .NET Framework data provider for SQL Server, so it begins with the appropriate using statement for this set of classes. Following this is a single class, DataReaderExample, containing the single method Main. This method begins by creating a new Connection object, passing in a very simple connection string. The example then creates a Command object and sets its CommandText property to contain a simple SQL query. Next, the Connection object's Open method is used to open a connection to the database, the command is executed by a call to ExecuteReader, and a DataReader object is returned. The result of the query is read using a simple while loop. Each iteration reads an employee name and age from the current row in the result using the appropriate Get method. When there are no more results, first the DataReader and then the Connection object are closed. And although it's not shown in this simple example, using a try/ catch block to handle any exceptions that occur is a common thing to do.

Accessing relational data in a DBMS using a DataReader object is simple and fast. For applications that need nothing more than sequential access to data or straightforward updates, this approach is perfect. Not all applications can get by with this simple mechanism, however. For those that need more, ADO.NET provides the much more flexible (and much more complicated) alternative of a DataSet, described next.

DataReaders are useful, but they're not always the best approach

ADO.NET and SQL Server 2005

The release of SQL Server 2005 was a big step for Windows-based data management. The first new version of SQL Server in five years, this product added a wide range of new features. One of the most interesting, and perhaps most important, was incorporating the Common Language Runtime (CLR) directly into the DBMS. It's now possible to create stored procedures in C# or Visual Basic (VB) as well as in SQL Server's native T-SQL language. While developers should use this option with careT-SQL is still a better choice in many casesit offers a useful alternative in some situations.

SQL Server 2005 includes many other changes, some of which are more directly relevant to ADO.NET. Because version 2.0 of the .NET Framework was designed to work with this new product, ADO.NET's .NET Framework data provider for SQL Server has a number of additions that allow developers to use these new features. Among the most important are the following:

• Support for new SQL Server 2005 data types. This includes large value types that allow creating data values as big as 2³² bytes, user-defined types for storing CLR objects and other information, and a new XML data type that allows working with data using the System.Xml types described in Chapter 4.

• The ability for an application to use SQL Server 2005's new database mirroring capability. This allows maintaining a current copy of a database on another machine that can be used if the primary system fails.

• Access to runtime statistics about SQL Server 2005. The information available to an ADO.NET client application includes how many bytes have been sent to and from the DBMS, how many SQL SELECT statements have been executed, how many transactions have been started, and much more.

• Query notifications that let an application be notified when data is changed. For example, an application that caches data retrieved from SQL Server 2005 might use this to know when to refresh its cache.

• Support for SQL Server 2005's notion of snapshot isolation, a new mechanism for reducing the number of locks placed on shared data.

• The ability to change a user's password without administrator intervention, a new option in SQL Server 2005.

• Integration with System.Transactions. As described in Chapter 4, the types in this namespace allow an application to create a transaction that spans multiple resource managers, including SQL Server and others. SQL Server 2005 can work intelligently with System.Transactions, taking control of a transaction when necessary. It can also determine when a transaction must be handed off to the Distributed Transaction Coordinator (DTC), a standard Windows component that handles distributed transactions.