C# SqlDataAdapter 与 SqlDataReader

SqlDataAdapter vs SqlDataReader

提问by stewsha

What are the differences between using SqlDataAdapter vs SqlDataReader for getting data from a DB?

使用 SqlDataAdapter 与 SqlDataReader 从数据库获取数据有什么区别？

I am specifically looking into their Pros and Cons as well as their speed and memory performances.

我特别关注它们的优缺点以及它们的速度和内存性能。

Thanks

谢谢

采纳答案by Joel Coehoorn

SqlDataReader:

SqlDataReader:

• Holds the connection open until you are finished (don't forget to close it!).
• Can typically only be iterated over once
• Is not as useful for updating back to the database

• 保持连接打开直到完成（不要忘记关闭它！）。
• 通常只能迭代一次
• 对更新回数据库没有用

On the other hand, it:

另一方面，它：

• Only has one record in memory at a time rather than an entire result set (this can be HUGE)
• Is about as fast as you can get for that one iteration
• Allows you start processing results sooner (once the first record is available). For some query types this can also be a very big deal.

• 一次只有一条记录在内存中，而不是整个结果集（这可能是巨大的）
• 与一次迭代一样快
• 允许您更快地开始处理结果（一旦第一条记录可用）。对于某些查询类型，这也可能是一件大事。

SqlDataAdapter/DataSet

SqlDataAdapter/数据集

• Lets you close the connection as soon it's done loading data, and may even close it for you automatically
• All of the results are available in memory
• You can iterate over it as many times as you need, or even look up a specific record by index
• Has some built-in faculties for updating back to the database

• 让您在完成加载数据后立即关闭连接，甚至可以自动为您关闭它
• 所有结果都在内存中可用
• 您可以根据需要对其进行多次迭代，甚至可以通过索引查找特定记录
• 有一些内置的功能可以更新回数据库

At the cost of:

代价是：

• Muchhigher memory use
• You wait until all the data is loaded before using any of it

• 很多更高的内存使用
• 在使用任何数据之前，您要等到所有数据都加载完毕

So really it depends on what you're doing, but I tend to prefer a DataReader until I need something that's only supported by a dataset. SqlDataReader is perfect for the common data access case of binding to a read-only grid.

所以实际上这取决于你在做什么，但我倾向于更喜欢 DataReader，直到我需要只由数据集支持的东西。SqlDataReader 非常适合绑定到只读网格的常见数据访问情况。

For more info, see the official Microsoft documentation.

有关详细信息，请参阅Microsoft 官方文档。

回答by Wim Hollebrandse

A SqlDataAdapter is typically used to fill a DataSet or DataTable and so you will have access to the data after your connection has been closed (disconnected access).

SqlDataAdapter 通常用于填充 DataSet 或 DataTable，因此您可以在连接关闭（断开访问）后访问数据。

The SqlDataReader is a fast forward-only and connected cursor which tends to be generally quicker than filling a DataSet/DataTable.

SqlDataReader 是一个快速只进和连接的游标，它通常比填充数据集/数据表更快。

Furthermore, with a SqlDataReader, you deal with your data one record at a time, and don't hold any data in memory. Obviously with a DataTable or DataSet, you do have a memory allocation overhead.

此外，使用 SqlDataReader，您可以一次处理一条数据，并且不会在内存中保存任何数据。显然，对于 DataTable 或 DataSet，您确实有内存分配开销。

If you don't need to keep your data in memory, so for rendering stuff only, go for the SqlDataReader. If you want to deal with your data in a disconnected fashion choose the DataAdapter to fill either a DataSet or DataTable.

如果您不需要将数据保存在内存中，那么仅用于渲染内容，请使用 SqlDataReader。如果要以断开连接的方式处理数据，请选择 DataAdapter 来填充 DataSet 或 DataTable。

回答by AdaTheDev

Use an SqlDataAdapter when wanting to populate an in-memory DataSet/DataTable from the database. You then have the flexibility to close/dispose off the connection, pass the datatable/set around in memory. You could then manipulate the data and persist it back into the DB using the data adapter, in conjunction with InsertCommand/UpdateCommand.

当想要从数据库填充内存中的 DataSet/DataTable 时，请使用 SqlDataAdapter。然后，您可以灵活地关闭/处理连接，在内存中传递数据表/设置。然后，您可以使用数据适配器结合 InsertCommand/UpdateCommand 操作数据并将其保存回数据库。

Use an SqlDataReader when wanting fast, low-memory footprint data access without the need for flexibility for e.g. passing the data around your business logic. This is more optimal for quick, low-memory usage retrieval of large data volumes as it doesn't load all the data into memory all in one go - with the SqlDataAdapter approach, the DataSet/DataTable would be filled with all the data so if there's a lot of rows & columns, that will require a lot of memory to hold.

当需要快速、低内存占用的数据访问而不需要灵活性（例如，围绕业务逻辑传递数据）时，请使用 SqlDataReader。这对于大数据量的快速、低内存使用情况检索更理想，因为它不会一次性将所有数据加载到内存中 - 使用 SqlDataAdapter 方法，DataSet/DataTable 将填充所有数据，因此如果有很多行和列，这将需要大量内存来保存。

回答by jaywon

The answer to that can be quite broad.

答案可能非常广泛。

Essentially, the major difference for me that usually influences my decisions on which to use is that with a SQLDataReader, you are "streaming" data from the database. With a SQLDataAdapter, you are extracting the data from the database into an object that can itself be queried further, as well as performing CRUD operations on.

从本质上讲，对我来说，通常会影响我决定使用哪个的主要区别在于，使用 SQLDataReader，您是从数据库“流式传输”数据。使用 SQLDataAdapter，您可以将数据库中的数据提取到一个对象中，该对象本身可以进一步查询，并对其执行 CRUD 操作。

Obviously with a stream of data SQLDataReader is MUCH faster, but you can only process one record at a time. With a SQLDataAdapter, you have a complete collection of the matching rows to your query from the database to work with/pass through your code.

显然，使用数据流 SQLDataReader 快得多，但一次只能处理一条记录。使用 SQLDataAdapter，您可以从数据库中收集与查询匹配的行的完整集合，以处理/传递您的代码。

WARNING:If you are using a SQLDataReader, ALWAYS, ALWAYS, ALWAYS make sure that you write proper code to close the connection since you are keeping the connection open with the SQLDataReader. Failure to do this, or proper error handling to close the connection in case of an error in processing the results will CRIPPLEyour application with connection leaks.

警告：如果您使用的是 SQLDataReader，请始终确保编写正确的代码来关闭连接，因为您使用 SQLDataReader 保持连接打开。如果不这样做，或者在处理结果出现错误的情况下关闭连接的正确错误处理将使您的应用程序因连接泄漏而瘫痪。

Pardon my VB, but this is the minimum amount of code you should have when using a SqlDataReader:

请原谅我的 VB，但这是使用 SqlDataReader 时应该拥有的最少代码量：

Using cn As New SqlConnection("..."), _
      cmd As New SqlCommand("...", cn)

    cn.Open()
    Using rdr As SqlDataReader = cmd.ExecuteReader()
        While rdr.Read()
            ''# ...
        End While
    End Using
End Using     

equivalent C#:

等效的 C#：

using (var cn = new SqlConnection("..."))
using (var cmd = new SqlCommand("..."))
{
    cn.Open();
    using(var rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
            //...
        }
    }
}

回答by billpg

The Fill function uses a DataReader internally. If your consideration is "Which one is more efficient?", then using a DataReader in a tight loop that populates a collection record-by-record, is likely to be the same load on the system as using DataAdapter.Fill.

Fill 函数在内部使用 DataReader。如果您的考虑是“哪个更有效？”，那么在一个紧密循环中使用 DataReader 来填充一个记录一个记录的集合，可能与使用 DataAdapter.Fill 对系统产生相同的负载。

(System.Data.dll, System.Data.Common.DbDataAdapter, FillInternal.)

（System.Data.dll、System.Data.Common.DbDataAdapter、FillInternal。）