Reading Data with the SqlDataReader

来源：互联网发布：gitlab ci php 编辑：程序博客网时间：2024/06/18 11:35

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