Lesson 04: Reading Data with the SqlDataReader(使用SqlDataReader读取数据)

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.