在服务器 Web 应用程序中填充数据表时出现随机错误 asp.net/ado.net/sql

Question

My public class (dbLibrary.vb)
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Security.Cryptography
Public Class dbLibrary
Public dbCon As SqlConnection
Public Constring As String
Public dbCmd As SqlCommand
Public dbAdp As SqlDataAdapter
Public dbDTable As DataTable
Dim PmtName As String, PmtValue
Dim Counter As Integer
Dim MySqlTransact As SqlTransaction
Public Sub New(Optional ByVal OpenConnection As Boolean = False)
    Constring = ConfigurationManager.ConnectionStrings("dbconnection").ConnectionString
    dbCon = New SqlConnection(Constring)
    If OpenConnection = True Then
        dbCon.Open()
    End If
End Sub
Public Function FetchRecordsDataTable(ByVal CmdType As Integer, ByVal CmdString As String, Optional ByVal PmtValue As Integer = -100, Optional ByVal PmtName As String = "-") As DataTable
    'CmdType = 1 if Stored Procedure, CmdType = 2 if Text, CmdType = 3 if Table Direct
    dbCmd = New SqlCommand
    If CmdType = 1 Then dbCmd.CommandType = CommandType.StoredProcedure
    If CmdType = 2 Then dbCmd.CommandType = CommandType.Text
    'If CmdType = 3 Then Cmd.CommandType = CommandType.TableDirect 'Not Supported in SQL
    If PmtValue <> -100 Then
        dbCmd.Parameters.AddWithValue(PmtName, PmtValue)
    End If
    dbCmd.CommandText = CmdString
    dbCmd.Connection = dbCon
    dbAdp = New SqlDataAdapter
    dbAdp.SelectCommand = dbCmd
    dbDTable = New DataTable
    dbAdp.Fill(dbDTable)
    Return dbDTable
End Function
 Public Function FetchRecordsDataTableByPmtArray(ByVal StoredProcedureName As String, ByVal ParameterArrayList As ArrayList, Optional ByVal CloseConnection As Boolean = False) As DataTable
    Dim Ctr As Integer
    dbCmd = New SqlCommand
    dbCmd.Connection = dbCon
    dbCmd.CommandType = CommandType.StoredProcedure
    dbCmd.CommandText = StoredProcedureName
    dbCmd.CommandTimeout = 300
    For Ctr = 0 To ParameterArrayList.Count - 1 Step 2
        PmtName = ParameterArrayList(Ctr)
        PmtValue = ParameterArrayList(Ctr + 1)
        dbCmd.Parameters.AddWithValue(PmtName, PmtValue)
    Next
    dbAdp = New SqlDataAdapter
    dbAdp.SelectCommand = dbCmd
    dbDTable = New DataTable
    dbAdp.Fill(dbDTable)
    If CloseConnection = True Then
        dbCon.Close()
    End If
    Return dbDTable
End Function
Public Sub CloseConnection()
    If dbCon.State <> ConnectionState.Closed Then
        dbCon.Close()
    End If
    dbCmd = Nothing
    dbAdp = Nothing
    dbDTable = Nothing
    dbCon = Nothing
End Sub
End Class
My Module (Module1.vb)
Imports dbLibrary
Module Module1
Dim dbobj As dbLibrary
Function getStates() As DataTable
    Dim dt As DataTable
    dbobj = New dbLibrary
    dt = dbobj.FetchRecordsDataTable(1, "spGetStates")
    dbobj.CloseConnection()
    dbobj = Nothing
    Return dt
End Function
Function getDistricts(ByVal arrList As ArrayList) As DataTable
    Dim dt As DataTable
    dbobj = New dbLibrary
    dt = dbobj.FetchRecordsDataTableByPmtArray("spGetDistricts", arrList)
    dbobj.CloseConnection()
    dbobj = Nothing
    Return dt
End Function
End Module

我在网页中创建了dbLibrary对象来访问数据库。 CloseConnection()方法始终在代码中调用。现在，每当调用返回数据表的函数时，错误就会随机发生

错误是：

"Column 'XXXXX' does not belong to table" "'System.Data.DataRowView' does not contain a property with the name 'x'"

也许数据表正在覆盖 2 个并发用户。

Answer 1

看起来您正在单独的函数中打开和关闭连接。您应该打开和关闭连接到 SQL 的每个函数中的每个连接。

SqlConnection 类中的 Close（） 方法用于关闭 数据库连接。Close 方法回滚任何挂起的内容 事务，并从 SQL Server 数据库释放连接。

从

其他函数中删除"打开"和"关闭"，并将它们放入从 SQL 检索数据的函数中。

获取记录数据表示例：

Public Function FetchRecordsDataTable(ByVal CmdType As Integer, ByVal CmdString As String, Optional ByVal PmtValue As Integer = -100, Optional ByVal PmtName As String = "-") As DataTable
    'CmdType = 1 if Stored Procedure, CmdType = 2 if Text, CmdType = 3 if Table Direct
    dbCmd = New SqlCommand
    If CmdType = 1 Then dbCmd.CommandType = CommandType.StoredProcedure
    If CmdType = 2 Then dbCmd.CommandType = CommandType.Text
    'If CmdType = 3 Then Cmd.CommandType = CommandType.TableDirect 'Not Supported in SQL
    If PmtValue <> -100 Then
        dbCmd.Parameters.AddWithValue(PmtName, PmtValue)
    End If
    dbCon.Open()
    dbCmd.CommandText = CmdString
    dbCmd.Connection = dbCon
    dbAdp = New SqlDataAdapter
    dbAdp.SelectCommand = dbCmd
    dbDTable = New DataTable
    dbAdp.Fill(dbDTable)
    dbCon.Close()
    Return dbDTable
End Function

FetchRecordsDataTableByPmtArray 示例：

 Public Function FetchRecordsDataTableByPmtArray(ByVal StoredProcedureName As String, ByVal ParameterArrayList As ArrayList, Optional ByVal CloseConnection As Boolean = False) As DataTable
    Dim Ctr As Integer
    dbCmd = New SqlCommand
    dbCon.Open()
    dbCmd.Connection = dbCon
    dbCmd.CommandType = CommandType.StoredProcedure
    dbCmd.CommandText = StoredProcedureName
    dbCmd.CommandTimeout = 300
    For Ctr = 0 To ParameterArrayList.Count - 1 Step 2
        PmtName = ParameterArrayList(Ctr)
        PmtValue = ParameterArrayList(Ctr + 1)
        dbCmd.Parameters.AddWithValue(PmtName, PmtValue)
    Next
    dbAdp = New SqlDataAdapter
    dbAdp.SelectCommand = dbCmd
    dbDTable = New DataTable
    dbAdp.Fill(dbDTable)
    dbCon.Close()
    Return dbDTable
End Function

作为最佳实践。将上面的代码包装在"最后尝试捕获"块中。如果将代码包装在"最后尝试捕获"中，即使出现错误，也可以关闭连接。

Try
    dbCon.Open()
    'Do database work
    .
    .
    .
Catch ex As Exception
    'Handle Exception
    .
    .
    .
Finally
    dbCon.Close()
End Try

Microsoft还提供了另一组很好的示例来使用 ADO.Net。