Understanding .NET (2nd Edition)

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.

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.

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.