vb.net - 如何关闭所有打开的Sqlite连接？

Question

   If Trim(tbHdName.Text) = "" Then
        Label2.Text = "Enter Valid HardDisk Name!"
        Label2.Visible = True
        btnAddHD.Visible = False
        btnAddHDD.Visible = False
        Table2.Visible = True
        Exit Sub
    End If

    Dim cd1 As String = "select hddName from T_HDD"
    Dim conn1 As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmd1 As New SqliteCommand(cd1, conn1)
    conn1.Open()
    Dim rdr As SqliteDataReader
    rdr = cmd1.ExecuteReader
    While rdr.Read
        If tbHdName.Text.ToUpper = rdr.Item(0).ToUpper Then
            Label2.Text = "HardDisk Already Added!"
            Label2.Visible = True
            Table2.Visible = True
            btnAddHD.Visible = False
            tbHdName.Text = ""
            conn1.Close()
            Exit Sub
        End If
    End While
    conn1.Close()

    Dim osId As Integer
    Dim cdr As String = "select id from T_OS where osName=@osName and osVersion=@osVersion and osBit=@osBit and osLanguage=@osLanguage "
    Dim connr As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmdr As New SqliteCommand(cdr, connr)
    cmdr.Parameters.AddWithValue("@osName", DropDownList7.SelectedValue)
    cmdr.Parameters.AddWithValue("@osVersion", DropDownList8.SelectedValue)
    cmdr.Parameters.AddWithValue("@osBit", DropDownList9.SelectedValue)
    cmdr.Parameters.AddWithValue("@osLanguage", DropDownList10.SelectedValue)
    connr.Open()
    Dim rdrr As SqliteDataReader
    rdrr = cmdr.ExecuteReader
    If rdrr.Read Then
        osId = rdrr.Item(0)
    Else
        Label2.Text = "Check OS details!"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        tbHdName.Text = ""
        connr.Close()
        Exit Sub
    End If
    connr.Close()

    Dim id As Integer
    Dim cd11 As String = "select cbtId from T_Cbt where cbtName=@cbtName"
    Dim conn11 As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmd11 As New SqliteCommand(cd11, conn11)
    cmd11.Parameters.AddWithValue("@cbtName", ddlCbtList.SelectedValue.ToString)
    conn11.Open()
    Dim rdr11 As SqliteDataReader
    rdr11 = cmd11.ExecuteReader
    If rdr11.Read Then
        id = rdr11.Item(0)
    End If
    conn11.Close()

    If TextBox10.Text = "" Then
        Label2.Text = "Invalid Computer Name"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        tbHdName.Text = ""
        Exit Sub
    End If

    Dim cdc As String = "select compName from T_HDD where compName=@compName"
    Dim connc As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmdc As New SqliteCommand(cdc, connc)
    cmdc.Parameters.AddWithValue("@compName", TextBox10.Text)
    connc.Open()
    Dim rdrc As SqliteDataReader
    rdrc = cmdc.ExecuteReader
    If rdrc.Read Then
        Label2.Text = "Computer Name is Already Exists"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        tbHdName.Text = ""
        connc.Close()
        Exit Sub
    End If
    connc.Close()

    If TextBox11.Text = "" Then
        Label2.Text = "Invalid IP Address"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        tbHdName.Text = ""
        Exit Sub
    End If

    Dim cdi As String = "select ip from T_HDD where ip=@ip"
    Dim conni As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmdi As New SqliteCommand(cdi, conni)
    cmdi.Parameters.AddWithValue("@ip", TextBox11.Text)
    conni.Open()
    Dim rdri As SqliteDataReader
    rdri = cmdi.ExecuteReader
    If rdri.Read Then
        Label2.Text = "IP Address is Already Exists"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        conni.Close()
        Exit Sub
    End If
    conni.Close()

    Dim divid As Integer
    Dim cdd As String = "select divId from T_Division where divName = @divname"
    Dim connd As New SqliteConnection(SqlDataSource1.ConnectionString)
    Dim cmdd As New SqliteCommand(cdd, connd)
    cmdd.Parameters.AddWithValue("@divname", DropDownList11.SelectedValue)
    connd.Open()
    Dim rdrd As SqliteDataReader
    rdrd = cmdd.ExecuteReader
    If rdrd.Read Then
        divid = rdrd.Item(0)
        connd.Close()
    Else
        Label2.Text = "Check Division details"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        connd.Close()
        Exit Sub
    End If

    If txtPdate2.Text.Length <> 10 Then
        Label2.Text = "Purchasing Date is Invalid"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        Exit Sub
    End If

    If Trim(tbHdName.Text) = "" Or Trim(TextBox5.Text) = "" Or Trim(TextBox6.Text) = "" Or _
        Trim(TextBox10.Text) = "" Or Trim(TextBox11.Text) = "" Then
        Label2.Text = "Fields Not to be empty"
        Label2.Visible = True
        Table2.Visible = True
        btnAddHD.Visible = False
        Exit Sub
    End If

    Dim cdsr As String = "Insert Into T_HDD(cbtId,hddName,isPrimary,size,manufacture, " &
           "id,ip,compName,osId,division,pdate) values " & 
           "(@cbtid,@hddname,@isprimary,@size,@manufacture,@id,@ip,@compName," & 
           "@osId,@division,@pdate)"

    Dim connsr As New SQLiteConnection(SqlDataSource1.ConnectionString)
    Dim cmdsr As New SQLiteCommand(cdsr, connsr)
    cmdsr.Parameters.AddWithValue("@cbtid", id)
    cmdsr.Parameters.AddWithValue("@hddname", Trim(tbHdName.Text))
    cmdsr.Parameters.AddWithValue("@isprimary", ddlIsPmry.SelectedValue.ToString)
    cmdsr.Parameters.AddWithValue("@size", Trim(TextBox5.Text))
    cmdsr.Parameters.AddWithValue("@manufacture", Trim(TextBox6.Text))
    cmdsr.Parameters.AddWithValue("@id", Trim(TextBox7.Text))
    cmdsr.Parameters.AddWithValue("@ip", Trim(TextBox11.Text))
    cmdsr.Parameters.AddWithValue("@compName", Trim(TextBox10.Text))
    cmdsr.Parameters.AddWithValue("@osId", osId)
    cmdsr.Parameters.AddWithValue("@division", divid)
    cmdsr.Parameters.AddWithValue("@pdate", txtPdate2.Text)
    added = True

    connsr.Open()

    cmdsr.ExecuteNonQuery()


    connsr.Close()

这是我按下“提交”按钮时的代码。它显示数据库锁定异常..

但是我已经关闭了所有打开的连接...

Answer 1

我将展示如何编写您的一个 SELECT 命令作为示例，因为上面的代码中有太多重复模式

Dim cd1 As String = "select hddName from T_HDD"
Using conn1 = New SqliteConnection(SqlDataSource1.ConnectionString)
    Using  cmd1 = New SqliteCommand(cd1, conn1)
        conn1.Open()
        Using rdr = cmd1.ExecuteReader
            While rdr.Read
                If tbHdName.Text.ToUpper = rdr.Item(0).ToUpper Then
                    Label2.Text = "HardDisk Already Added!"
                    Label2.Visible = True
                    Table2.Visible = True
                    btnAddHD.Visible = False
                    tbHdName.Text = ""
                    Exit Sub
                End If
            End While
       End Using
   End Using
End Using

Using 语句确保当您退出Using block 时，Using 捕获的每个一次性对象都被正确关闭并处置，以防出现异常或通过简单的 Exit Sub 进行多次操作