VB.NET: 在运行 SQL 查询后执行 ExecuteReader(CommandBehavior.CloseConnection) 时挂起。

huangapple go评论66阅读模式
英文:

VB.NET: ExecuteReader(CommandBehavior.CloseConnection) hangs after running SQL Query

问题

以下是翻译好的部分:

我正在尝试执行一个SELECT SQL语句该语句会返回一个值以下是从SQL选择语句中获取值的代码

Private objSQLHelper As SQLHelper

Private Const SQL_Error_Query = "SELECT field1 FROM TESTTBL WHERE field1ID = @field1ID;"

Public Function GetField1(ByVal field1ID As Integer) As Boolean
    Dim sdr As SqlClient.SqlDataReader = Nothing
    Dim sqlParams As New ArrayList
    Dim resultBool As Boolean

    sqlParams.Add(New SqlClient.SqlParameter("field1ID", SqlDbType.Int))
    sqlParams(0).value = field1ID

    sdr = objSQLHelper.sdrExecuteReader(sqlConnectionString, CommandType.Text, SQL_Error_Query, sqlParams)

    While sdr.Read()
        resultBool = CBool(sdr("field1"))
    End While

    sqlParams.Clear()

    Return resultBool
End Function

在sdrExecuteReader函数中,代码总是在CommandBehavior.CloseConnection这一行卡住。对于上述的GetField1函数,这个简单的SELECT查询语句导致了这个问题。然而,属于其他文件的其他SELECT查询是有效的,它们也调用相同的sdrExecuteReader函数。因此,我不确定为什么在GetField1函数中,代码会在CommandBehavior.CloseConnection这一行卡住。以下是sdrExecuteReader函数的代码:

Public Function sdrExecuteReader(ByVal sqlConnectionString As String, ByVal commandType As CommandType, ByVal commandString As String, ByVal sqlParams As ArrayList) As SqlClient.SqlDataReader
    Dim sqlCommand As New SqlClient.SqlCommand
    Dim sqlConnection As SqlClient.SqlConnection = Nothing

    Try
        sqlConnection = New SqlClient.SqlConnection(sqlConnectionString)
        PrepareCommand(sqlCommand, sqlConnection, Nothing, commandType, commandString, sqlParams)

        ' 代码总是卡在这一行
        sdrExecuteReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Try
End Function

问题:

  1. 有谁知道为什么会发生这种情况吗?我尽量不想改变我的sdrExecuteReader函数,因为很多函数依赖于它。
  2. 在我的GetField1函数中,有什么可以写来进行调试,或者有什么可以包含来修复该函数内的代码吗?
  3. 如果问题1和2无法回答,那么导致这种情况发生的原因是什么?

注意:当我将SQL_Error_Query更改为"SELECT field1 = 1"(基本上是硬编码),它可以正常工作。可能是SQL连接出了问题,但其他SELECT查询却可以正常工作...

英文:

I am trying to execute a SELECT SQL statement which returns a value to me. The following is my code to Get the value from the SQL select statement.

Private objSQLHelper As SQLHelper

Private Const SQL_Error_Query = "SELECT field1 FROM TESTTBL WHERE field1ID = @field1ID;"

Public Function GetField1(ByVal field1ID As Integer) As Boolean
    Dim sdr As SqlClient.SqlDataReader = Nothing
    Dim sqlParams As New ArrayList
    Dim resultBool As Boolean

    sqlParams.Add(New SqlClient.SqlParameter("field1ID", SqlDbType.Int))
    sqlParams(0).value = field1ID

    sdr = objSQLHelper.sdrExecuteReader(sqlConnectionString, CommandType.Text, SQL_Error_Query, sqlParams)

    While sdr.Read()
        resultBool = CBool(sdr("field1"))
    End While

    sqlParams.Clear()

    Return resultBool
End Function

Inside the sdrExecuteReader function, the code always gets stuck at the line with CommandBehavior.CloseConnection. For the above GetField1 function, this simple SELECT query statement caused this. However, other SELECT queries that belong to other files is working and they also call the same sdrExecuteReader function. Thus, I am unsure why for the GetField1 function the code hangs at the line with CommandBehavior.CloseConnection. Below is how the sdrExecuteReader function looks like:

Public Function sdrExecuteReader(ByVal sqlConnectionString As String, ByVal commandType As CommandType, ByVal commandString As String, ByVal sqlParams As ArrayList) As SqlClient.SqlDataReader
    Dim sqlCommand As New SqlClient.SqlCommand
    Dim sqlConnection As SqlClient.SqlConnection = Nothing

    Try
        openingConnection = New SqlClient.SqlConnection(sqlConnectionString)
        PrepareCommand(sqlCommand, sqlConnection, Nothing, commandType, commandString, sqlParams)

        ' Code always gets stuck or hangs at this line
        sdrExecuteReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Try
End Function

Questions:

  1. Does anyone know why this happens? I do not wish to change my sdrExecuteReader function as much as possible because many functions rely on it.
  2. Is there anything I can write in my GetField1 function to debug or anything I can include to fix the code inside that function?
  3. If qns 1 and 2 cannot be answered, what are the reasons that this is happening?

Note: When I change SQL_Error_Query to "SELECT field1 = 1" (basically hard coded), it works. Possibly the sql connection is not working? but other select queries are working though...

答案1

得分: 1

你可以使用try/catch包围代码行,并使用调试器。查看PrepareCommand:问题可能在SQL连接、查询或参数中。由于我不了解SQLHelper包装器,我使用标准SQLclient测试您的查询。这是测试通过的代码,也许可以帮助您进行调查。

Imports System.Data.SqlClient

Public ConnSQL As New SqlConnection("your connection string;")
Const SQL_Error_Query = "SELECT field1 FROM TESTTBL WHERE field1ID = @field1ID;"

Public Function GetField1(ByVal field1ID As Integer) As Boolean

    Dim resultBool As Boolean

    Dim sqlParams As New SqlParameter
    sqlParams.ParameterName = "field1ID"
    sqlParams.SqlDbType = SqlDbType.Int
    sqlParams.Value = field1ID

    ConnSQL.Open()
    Try
        Dim cmdSQL As New SqlCommand(SQL_Error_Query, ConnSQL)
        cmdSQL.Parameters.Add(sqlParams)
        Dim sdr As SqlClient.SqlDataReader = cmdSQL.ExecuteReader()
        While sdr.Read()
            resultBool = CBool(sdr(0))
        End While
    Catch ex As Exception
    End Try

    ConnSQL.Close()

    Return resultBool
End Function
英文:

you can enclose lines with try/catch and use debugger. have a look to PrepareCommand : problem can be in SQL connexion, in Query or in parameter.
as I don't know SQLHelper wrapper, I test your query by using standard SQLclient. this is the code (tested OK). maybe help you to investigate.

Imports System.Data.SqlClient

    Public ConnSQL As New SqlConnection("your connexion string;")
    Const SQL_Error_Query = "SELECT field1 FROM TESTTBL WHERE field1ID = @field1ID;"

    Public Function GetField1(ByVal field1ID As Integer) As Boolean

        Dim resultBool As Boolean

        Dim sqlParams As New SqlParameter
        sqlParams.ParameterName = "field1ID"
        sqlParams.SqlDbType = SqlDbType.Int
        sqlParams.Value = field1ID

        ConnSQL.Open()
        Try
            Dim cmdSQL As New SqlCommand(SQL_Error_Query, ConnSQL)
            cmdSQL.Parameters.Add(sqlParams)
            Dim sdr As SqlClient.SqlDataReader = cmdSQL.ExecuteReader()
            While sdr.Read()
                resultBool = CBool(sdr(0))
            End While
        Catch ex As Exception
        End Try

        ConnSQL.Close()

        Return resultBool
    End Function

答案2

得分: 0

在 sdrExecuteReader 中,不要直接将 sqldatareader 返回给调用者,而是创建一个包含数据的数据集,并返回另一个引用该数据集的数据阅读器。尝试这样做,看看是否解决了你的问题。

英文:

In sdrExecuteReader instead of returning the sqldatareader directly to the caller, create a dataset that holds the data and return another datareader that references this dataset. Try this and see if it resolves your issue.

huangapple
  • 本文由 发表于 2023年3月1日 15:01:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/75600455.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定