VB.NET如何在单个子程序中整合多个带Using语句的SQLite搜索查询

你只需要先根据搜索类型动态生成对应的SQL语句，再传入Using结构中的SQLiteCommand即可，无需为每种查询单独写重复的逻辑。

修改步骤 #

1. 把原来5个独立的查询子程序删除，替换为1个接收搜索类型参数的统一子程序
2. 在子程序中首先根据传入的搜索类型生成对应SQL语句
3. 再将生成的SQL传入Using结构的SQLiteCommand实例中，后续数据读取、绑定DataGridView的逻辑完全复用

修改后的窗体加载事件代码 #

Private Sub frmViewTX_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    StyleDGV()
    ' 直接传入搜索类型调用统一子程序
    LoadTxData(gvTEST)
End Sub

整合后的统一查询子程序 #

Private Sub LoadTxData(searchType As String)
    Dim intID As Integer
    Dim strDate As String
    Dim strTxType As String
    Dim strAmt As Decimal
    Dim strCKNum As String
    Dim strDesc As String
    Dim strBal As Decimal
    Dim rowCount As Integer
    Dim maxRowCount As Integer
    Dim emptyStr As String = "  "
    Dim sql As String = String.Empty

    ' 先根据搜索类型生成对应的SQL语句
    Select Case searchType
        Case "All"
            sql = "SELECT * FROM TxData"
        Case "MoYr"
            sql = $"SELECT * FROM TxData WHERE txSearchMonth = '{gvFromMonth}' AND txYear = '{gvYear}'"
        Case "TxMoYr"
            sql = $"SELECT * FROM TxData WHERE txType = '{gvTxType}' AND txSearchMonth = '{gvFromMonth}' AND txYear = '{gvYear}'"
        Case "Year"
            sql = $"SELECT * FROM TxData WHERE txYear = '{gvYear}'"
        Case "MoRangeYr"
            ' 注意：原代码这里逻辑有误，两个txSearchMonth等值判断永远无法匹配范围，改为区间判断
            sql = $"SELECT * FROM TxData WHERE txSearchMonth BETWEEN '{gvFromMonth}' AND '{gvToMonth}' AND txYear = '{gvYear}'"
    End Select

    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
        conn.Open()
        ' 直接传入动态生成的SQL创建Command对象
        Using cmd As SQLiteCommand = New SQLiteCommand(sql, conn)
            Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
                dgvTX.Rows.Clear() ' 每次查询前先清空原有数据
                rowCount = 0 ' 重置计数
                While rdr.Read()
                    intID = CInt((rdr("TID")))
                    strDate = rdr("txSortDate").ToString
                    strTxType = rdr("txType").ToString
                    strAmt = CDec(rdr("txAmount"))
                    strCKNum = rdr("txCKNum").ToString
                    strDesc = rdr("txDesc").ToString
                    strBal = CDec(rdr("txBalance"))
                    dgvTX.Columns(3).DefaultCellStyle.Format = "N"
                    dgvTX.Columns(6).DefaultCellStyle.Format = "N"
                    dgvTX.Rows.Add(intID, strDate, strTxType, strAmt, strCKNum, strDesc, strBal, emptyStr)
                    Dim dateToday = Date.Today
                    Dim lblDate As String = dateToday.ToString("MMM d yyyy")
                    lblAmt.Text = "Current Balance  " & lblDate
                    tbBal.Text = "$ " & String.Format("{0:n}", strBal)
                    rowCount += 1
                End While

                dgvTX.Sort(dgvTX.Columns(0), ListSortDirection.Descending)
                If rowCount <= 25 Then
                    maxRowCount = 25 - rowCount
                    For iA = 1 To maxRowCount
                        dgvTX.Rows.Add(" ")
                    Next
                End If
                rdr.Close()
            End Using
        End Using
        conn.Close()
    End Using
    tbBal.Focus()
End Sub

• 原MoRangeYr类型的查询逻辑存在错误，原语句用txSearchMonth = '{gvFromMonth}' AND txSearchMonth = '{gvToMonth}'，只有当起始月和结束月完全相同时才能查到数据，已改为BETWEEN区间判断，若你的业务逻辑有特殊要求可自行调整。
• 目前代码用字符串拼接SQL存在SQL注入风险，建议后续改为参数化查询，示例如下：

' 参数化写法示例
cmd.CommandText = "SELECT * FROM TxData WHERE txSearchMonth = @Month AND txYear = @Year"
cmd.Parameters.AddWithValue("@Month", gvFromMonth)
cmd.Parameters.AddWithValue("@Year", gvYear)

内容的提问来源于stack exchange，提问作者Vector