本文档由李欣蔚(nirvana_li)翻译自http://www.csharp-station.com/,转载请注名出处!
更新日期2006-2-14
Lesson 04: Reading Data with the SqlDataReader
使用SqlDataReader读取数据
This lesson explains how to read data with a SqlDataReader object. Here are the objectives of this lesson:
本课解释了如何使用SqlDataReader对象读取数据。以下是本课的目标：
Learn what a SqlDataReader is used for.
Know how to read data using a SqlDataReader.
Understand the need to close a SqlDataReader.
学习SqlDataReader的用法
了解如何使用SqlDataReader读取数据
了解如何关闭SqlDataReader的必要性
Introduction
介绍
A SqlDataReader is a type that is good for reading data in the most efficient manner possible. You can *not* use it for writing data. SqlDataReaders are often described as fast-forward firehose-like streams of data.
SqlDataReader是对于大多数有效的情况下读取数据的好的方式。你不能使用它来写入数据。SqlDataReaders通常作为快速的只向前读的数据流。
You can read from SqlDataReader objects in a forward-only sequential manner. Once you've read some data, you must save it because you will not be able to go back and read it again.
你能够以只向前的顺序方式从SqlDataReader对象中进行读取。只要你已经读取了某些数据，你必须保存它们，因为你将不能够返回并再一次读取它。
The forward only design of the SqlDataReader is what enables it to be fast. It doesn't have overhead associated with traversing the data or writing it back to the data source. Therefore, if your only requirement for a group of data is for reading one time and you want the fastest method possible, the SqlDataReader is the best choice. Also, if the amount of data you need to read is larger than what you would prefer to hold in memory beyond a single call, then the streaming behavior of the SqlDataReader would be a good choice.
SqlDataReader的只向前读的设计使它很迅速。它并没有遍历数据或者将数据重新写回给数据源的负担。因此，如果你一次只需要读一组数据，并且希望最快速的方法，SqlDataReader则是最好的选择。同样，如果一个单独调用所需要读取的数据量大于内存的存放能力，SqlDataReader的数据流形式应该是一个好的选择。
Note: Observe that I used the term "one time" in the previous paragraph when discussing the reasons why you would use a SqlDataReader. As with anything, there are exceptions. In many cases, it is more efficient to use a cached DataSet. While caching is outside the scope of this tutorial, we will discuss using DataSet objects in the next lesson.
注意：当讨论为什么应该使用SalDataReader的时候，我在上一段中使用的术语“一次”。任何事情，都有异常发生。在一些情况下，更有效的是使用缓存DataSet。因为缓存超出了本指南的范畴，我们将在下一节课“使用DataSet对象”中讨论它。
Creating a SqlDataReader Object
创建SqlDataReader对象
Getting an instance of a SqlDataReader is a little different than the way you instantiate other ADO.NET objects. You must call ExecuteReader on a command object, like this:
得到SqlDataReader对象于实例化其它ADO.NET对象稍微有些不同。你必须对一个command对象调用ExecuteReaer方法，比如这样：
SqlDataReader rdr = cmd.ExecuteReader();
The ExecuteReader method of the SqlCommand object, cmd , returns a SqlDataReader instance. Creating a SqlDataReader with the new operator doesn't do anything for you. As you learned in previous lessons, the SqlCommand object references the connection and the SQL statement necessary for the SqlDataReader to obtain data.
SqlCommand对象cmd的ExecuteReader方法返回一个SqlDataReader实例。使用new关键字创建一个SqlDataReader并不做任何事情。前面的课程已经学到，SqlCommand对象引用connection和SQL语句对于SqlDataReader读取数据是必需的。
Reading Data
读取数据
Previous lessons contained code that used a SqlDataReader, but the discussion was delayed so we could focus on the specific subject of that particular lesson. This lesson builds from what you've seen and explains how to use the SqlDataReader.
前面的课程包含了使用SqlDataReader的代码，但是关于前面课程中的细节的讨论我们推迟了。这节课建立自你所见到的并解释如何使用SqlDataReader。
As explained earlier, the SqlDataReader returns data via a sequential stream. To read this data, you must pull data from a table row-by-row. Once a row has been read, the previous row is no longer available. To read that row again, you would have to create a new instance of the SqlDataReader and read through the data stream again.
前面已经解释了，SqlDataReader通过顺序数据流返回数据。为了读取这些数据，你必须从一个表中一行一行的取出数据。只要一行被读取，之前的数据就不再有效。为了再次读取那行，你应该创建一个新的SqlDataReader实例并且再次从数据流中读取它。
The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with a while loop. The following code shows how to accomplish this:
从SqlDataReader中读取返回的数据流的典型方法是通过while循环迭代没一行。下面的代码显示了如何完成：
while (rdr.Read())
{
// get the results of each column
string contact = (string)rdr["ContactName"];
string company = (string)rdr["CompanyName"];
string city = (string)rdr["City"];
// print out the results
Console.Write("{0,-25}", contact);
Console.Write("{0,-20}", city);
Console.Write("{0,-25}", company);
Console.WriteLine();
}
Notice the call to Read on the SqlDataReader, rdr, in the while loop condition in the code above. The return value of Read is type bool and returns true as long as there are more records to read. After the last record in the data stream has been read, Read returns false.
注意在上面代码中的while循环对SqlDataReader对象rdr调用的Read方法。Read方法的返回值为bool，并且只要有记录读取就返回真。在数据流中所有的最后一条记录被读取了，Read方法就返回false。
In previous lessons, we extracted the first column from the row by using the SqlDataReader indexer, i.e. rdr[0]. You can extract each column of the row with a numeric indexer like this, but it isn't very readable. The example above uses a string indexer, where the string is the column name from the SQL query (the table column name if you used an asterisk, *. String indexers are much more readable, making the code easier to maintain.
在前面的课程中，我们使用SqlDataReader的索引器，比如rdr[0]，提取行中的第一列。你能够使用诸如这样的数值索引器提取行中的列，但是它并不具有很好的可读性。上面的例子使用了字符串索引器，这里的字符串是从SQL查询语句中得到的列名（表的列名如果你使用一个星号，*.字符串下标具有更好的可读性，使得代码能够更好的维护。
Regardless of the type of the indexer parameter, a SqlDataReader indexer will return type object. This is why the example above casts results to a string. Once the values are extracted, you can do whatever you want with them, such as printing them to output with Console type methods.
无论索引器参数是什么类型，一个SqlDataReader索引器将返回object类型。这就是为什么上面要将结果转换为string的原因。只要值被提取，你能够对它们为所欲为，比如使用Console类型的方法将它们打印到输出。
Finishing Up
完结
Always remember to close your SqlDataReader, just like you need to close the SqlConnection. Wrap the data access code in a try block and put the close operation in the finally block, like this:
一定要记住关闭SqlDataReader，就像关闭SqlConnection一样。将数据存取代码用try语句块包围起来，并把关闭操作放到finally语句块中，就像这样：
try
{
// data access code
}
finally
{
// 3. close the reader
if (rdr != null)
{
rdr.Close();
}
// close the connection too
}
The code above checks the SqlDataReader to make sure it isn't null. After the code knows that a good instance of the SqlDataReader exists, it can close it. Listing 1 shows the code for the previous sections in its entirety.
上面的代码检测SqlDataReader，确保它不为空。在代码知道SqlDataReader的一个完好的实例存在，它就能够关闭它。Listing1完整的显示了前面各节的代码。
Listing 1: Using the SqlDataReader
using System;
using System.Data;
using System.Data.SqlClient;
namespace Lesson04
{
class ReaderDemo
{
static void Main()
{
ReaderDemo rd = new ReaderDemo();
rd.SimpleRead();
}
public void SimpleRead()
{
// declare the SqlDataReader, which is used in
// both the try block and the finally block
SqlDataReader rdr = null;
// create a connection object
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
// create a command object
SqlCommand cmd = new SqlCommand(
"select * from Customers", conn);
try
{
// open the connection
conn.Open();
// 1. get an instance of the SqlDataReader
rdr = cmd.ExecuteReader();
// print a set of column headers
Console.WriteLine(
"Contact Name City Company Name");
Console.WriteLine(
"------------ ------------ ------------");
// 2. print necessary columns of each record
while (rdr.Read())
{
// get the results of each column
string contact = (string)rdr["ContactName"];
string company = (string)rdr["CompanyName"];
string city = (string)rdr["City"];
// print out the results
Console.Write("{0,-25}", contact);
Console.Write("{0,-20}", city);
Console.Write("{0,-25}", company);
Console.WriteLine();
}
}
finally
{
// 3. close the reader
if (rdr != null)
{
rdr.Close();
}
// close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}
Summary
总结
SqlDataReader objects allow you to read data in a fast forward-only manner. You obtain data by reading each row from the data stream. Call the Close method of the SqlDataReader to ensure there are not any resource leaks.
SqlDataReader对象允许你以一种快速的只向前的方式读取数据。你从数据流中读取每一行来读取数据。调用SqlDataReader的Close方法保证资源泄漏不会发生。
I hope you enjoyed this lesson. The next one in this series is Lesson 05: Working with Disconnected Data - The DataSet and SqlDataAdapter.
For further questions, you are welcome to participate in the C# Station Discussion Forums.
Your feedback is very important and I appreciate any constructive contributions you have. Please feel free to contact me for feedback or comments you may have about this lesson.
