英文:
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
问题:
- 有谁知道为什么会发生这种情况吗?我尽量不想改变我的sdrExecuteReader函数,因为很多函数依赖于它。
- 在我的GetField1函数中,有什么可以写来进行调试,或者有什么可以包含来修复该函数内的代码吗?
- 如果问题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:
- 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.
- Is there anything I can write in my
GetField1
function to debug or anything I can include to fix the code inside that function? - 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论