时间戳

首页 > 解决方案 > 如何正确关闭 VB.net Web 应用程序上的 sql 连接？

vb.net - 如何正确关闭 VB.net Web 应用程序上的 sql 连接？

问题描述

我想知道我当前打开和关闭数据库连接的方式是否正确。

在 execQuery 函数中，我在执行后添加了 DB 连接。我在 web 表单中调用这个函数 executeButton 所以我假设它关闭了。

如果在 executeButton 中触发 try and catch 异常，它会跳过数据库关闭吗？还是先关闭数据库连接，然后触发 try and catch 异常？

SQL 控制

Public Function ExecQuery(query As String) As DataTable

    Dim DBDT = New DataTable
    Using DBCon As New SqlConnection(ConStr),
            DBCmd As New SqlCommand(query, DBCon)
        Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
        Params.Clear()
        DBCon.Open()
        DBDT.Load(DBCmd.ExecuteReader)
        DBCon.Close()
        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Using
    Return DBDT
End Function

网页表单代码

Protected Sub ExecuteButton_Click(sender As Object, e As EventArgs) Handles ExecuteButton.Click
    Dim StoreID As Integer
    Dim TransID As Integer
    Dim RgstID As Integer
    Dim dt As DataTable

    If Not Integer.TryParse(StoreIDTextbox.Text, StoreID) Then
            MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
        Exit Sub

    End If

    If Not Integer.TryParse(TransactionIDTextbox.Text, TransID) Then
        MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
        Exit Sub
    End If

    SQL.AddParam("@Str_ID", StoreID)
    SQL.AddParam("@Tran_ID", TransID)

    'Rgst_ID Validation
    If RegisterIDTextbox.Text.Length = 0 Then
        SQL.AddParam("@Rgst_ID", "")
    ElseIf RegisterIDTextbox.Text.Length > 0 Then
        RgstID = Integer.Parse(RegisterIDTextbox.Text)
        SQL.AddParam("@Rgst_ID", RgstID)
    End If

    Try
        dt = SQL.ExecQuery("Select H.Emp_ID, H.Cust_ID, H.Rgst_ID, D.TRAN_LN_NUM, D.DISC_CD, D.AUTH_EMP_ID, D.ORIG_PRC, D.DISC_AMT, D.DISC_PCT, D.GL_ACCT_ID
                             From Transaction_Header H 
                             INNER Join LN_Detail L On (H.Str_ID = L.Str_ID And H.Rgst_ID = L.Rgst_ID And H.Tran_ID = L.Tran_ID)
                             INNER Join LN_Discount D ON (L.Str_ID = D.Str_ID And L.Rgst_ID = D.Rgst_ID And L.Tran_ID = D.Tran_ID And L.Tran_LN_Num = D.Tran_LN_Num)  
                             WHERE(H.Str_ID = @Str_ID)
                                And (H.Tran_ID = @Tran_ID)
                                And ((H.Rgst_ID = @Rgst_ID) Or (@Rgst_ID Is NULL Or @Rgst_ID = ''))")

        GridView1.DataSource = dt
        GridView1.DataBind()

    Catch ex As Exception

        MsgBox(ex.Message)
        Exit Sub

    End Try

标签： vb.net

解决方案

事件的顺序是：

  1. 发生错误
  2. 抛出异常ExecuteReader
  3. 异常未在using块内捕获ExecQuery，因此块退出
  4. 因为块退出，连接被释放，连接被“关闭”（返回连接池）
  5. 异常继续冒出ExecQuery
  6. 除了陷入ExecuteButton_Click

推荐阅读