DataReader 过期连接对象 [英] DataReader outlive connection object

问题描述

请看下面的代码:

Private objCommand As SQLCommand 

Public Overrides Function ExecuteDataReader(ByVal strCommandType As String, ByVal sqlCommandString As String) As DbDataReader
    Dim objDR As SqlDataReader

    Try
        _objCon = getConnection()
        _objCon.Open()
        Using _objCon
            Using _objCommand
                _objCommand.Connection = _objCon
                _objCommand.CommandText = sqlCommandString
                _objCommand.CommandType = strCommandType
                objDR = _objCommand.ExecuteReader
                ExecuteDataReader = objDR
            End Using
        End Using
    Catch ex As Exception
        Throw
    Finally
        _objCon = Nothing
        _objCommand.Dispose()
        _objCommand = Nothing
        objDR = Nothing
    End Try
End Function

DataReader 返回关闭，因为它在连接对象关闭时关闭.DataReader 如何比连接对象存活时间更长?

The DataReader is returned closed because it is closed when the connection object is closed. How can the DataReader outlive the connection object?

我寻找过类似的问题，我找到了这个:DataReader连接关闭时未关闭，后果如何?.但是，它没有回答我的具体问题.

I have looked for similar questions and I found this one: DataReader not closed when Connection is closed, consequences?. However, it does not answer my specific question.

推荐答案

你的连接提前关闭的原因是你在退出 Using 块之后才从函数返回.离开 Using 块的行为将强制您的连接立即关闭.将 datareader 设置为返回的对象是不够的.即使使用显式的 Return 语句也不够……离开函数仍然意味着离开 Using 块，因此在您使用数据读取器之前，您的连接仍然关闭.

The reason your connection is closed pre-maturely is that you don't return from the function until after you exit the Using block. The act of leaving the Using block will force your connection to close immediately. Setting the datareader as your returned object is not enough. Even using an explicit Return statement would not be enough... leaving the function would still mean leaving the Using block, and so your connection is still closed before you ever get to use the datareader.

为了解决所有这些问题，我使用了如下所示的模式:

To get around all that, I use a pattern that looks like this:

Public Iterator Function ExecuteDataReader(Of T)(ByVal sql As String, ByVal addParams as Action(Of SqlParameterCollection), ByVal castRow As Funnction(Of IDataRecord, T)) As IEnumerable(Of T)

    Using cn As SqlConnection = getConnection(), _
          cmd As New SqlCommand(sql, cn)

        addParams(cmd.Parameters)
        cn.Open()

        Using rdr As SqlDataReader = cmd.ExecuteReader()
            While rdr.Read()
                Yield castRow(rdr)
            End While
        End Using
    End Using
End Function

然后我会像这样调用该函数:

I would then call that function like this:

Dim results As IEnumerable(Of Customer) = ExecuteDataReader( _
           "SELECT * FROM Customer WHERE Sales> @MinSales", _
      Sub(p) p.Add("@MinSales", SqlDbType.Double).Value = 10000.0, _
      Function(r) New Customer() With {Name=r("Name"), Address=r("Address"), Sales=r("Sales") })

For Each c As Customer in results
   '...
Next

让我们稍微回顾一下那个模式，因为有些事情可能会令人困惑......也就是说，我想涵盖委托参数.

Let's go over that pattern a bit, because there are some things that can confuse... namely I want to cover the delegate arguments.

首先是 addParameter 参数.您需要了解的是，您的原始模式已被严重破坏，因为它迫使您创建充满 Sql 注入漏洞的代码，因为没有其他方法可以发送参数信息.这是一个巨大的问题.幸运的是，它很容易解决.这就是 addParameter 参数的用途.这不是做到这一点的唯一方法 —也就是说，你可以做一些简单的事情，比如传递一个键/值/类型数组 —但我喜欢它，因为它避免了通过数组的重复工作或重复存储两次参数数据的内存.

First up is the addParameter argument. You need to understand is that your original pattern is horribly broken, because it forces you to create code riddled with Sql injection vulnerabilities, as there is no other way to send parameter information. That is a huge problem. Thankfully, it's easily solved. This is what the addParameter argument is for. It's not the only way to do this — ie, you could do something as simple as pass an array of key/values/type as well — but I like it because it avoids duplicate work going through the array or duplicate memory storing the parameter data twice.

接下来是 castRow 参数.这是必要的，因为没有它，您会遇到与您的示例类似的问题.在这里，代码仍会运行，但因为您在其他地方不断产生相同的目标代码，最终将全部使用结果中的最终记录.通过这种方式，您可以获得正确的预期结果，并且以强类型方式获得它们.

Next is the castRow argument. This is necessary because without it you run into a similar problem you see with your example. Here, the code would still run, but because you keep yielding the same object code elsewhere would end up all working with the final record in the results. This way, you get the correct expected results, and you get them in a strongly-typed manner.

继续前进，希望您已经熟悉 Iterator 和 Yield 关键字，但由于它们对 VB.Net 来说相对较新，所以如果您不熟悉也没关系.只需知道它们会导致编译器将此代码转换为可以迭代您的数据读取器对象的内容.

Moving on, hopefully you're already familiar with the Iterator and Yield keywords, but as they're relatively new to VB.Net it's okay if you're not. Just know that they cause the compiler to transform this code into something that will let iterate over your datareader object.

这篇关于DataReader 过期连接对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！