Visual Studio 2022中SQLite查询返回空记录问题及解决

环境与数据库结构 #

• 使用Visual Studio Enterprise 2022，项目已安装System.Data.SQLite NuGet包
• 在DB Browser for SQLite中创建两个表，表结构如下：

documentTypes表 #

CREATE TABLE "documentTypes" (
    "docTypeID"   INTEGER NOT NULL UNIQUE,
    "docType"   TEXT NOT NULL,
    "active"    INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY("docTypeID" AUTOINCREMENT)
);

documentFields表 #

CREATE TABLE "documentFields" (
    "fieldID"   INTEGER NOT NULL UNIQUE,
    "docType"   TEXT NOT NULL,
    "fieldName" TEXT NOT NULL,
    "fieldDataType" TEXT NOT NULL,
    "active"    INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY("fieldID" AUTOINCREMENT)
);

已插入测试数据，在DB Browser for SQLite中查询可正常返回结果。

正常工作的查询代码 #

以下代码可正常查询documentTypes表并填充ListView：

Dim SQLiteconn As New SQLiteConnection(ConnectionString())
Dim sqlitecmd As New SQLiteCommand("SELECT docTypeID,doctype FROM documentTypes WHERE active=1;", SQLiteconn)
Dim dt As New DataTable
SQLiteconn.Open()

Dim dr As SQLiteDataReader = sqlitecmd.ExecuteReader()

If dr.HasRows Then
    dt.Load(dr)
    If dt.Rows.Count > 0 Then
       lstvDocTypes.DataSource = Nothing
       lstvDocTypes.DataSource = dt
       lstvDocTypes.ValueMember = dt.Columns(0).ToString
       lstvDocTypes.DisplayMember = dt.Columns(1).ToString
    End If
End If
SQLiteconn.Close()

出现问题的查询代码 #

选中ListView项后，执行以下代码查询documentFields表时返回空记录：

Dim SQLiteconn As New SQLiteConnection(ConnectionString())
Dim sqlitecmd As New SQLiteCommand($"SELECT * FROM documentFields WHERE docType = '{strDocType}';", SQLiteconn)
Dim dt As New DataTable
Try
    SQLiteconn.Open()

    Dim dr As SQLiteDataReader = sqlitecmd.ExecuteReader()

    If dr.HasRows Then
        dt.Load(dr)
        If dt.Rows.Count > 0 Then
            lstvDocTypeFields.DataSource = Nothing
            lstvDocTypeFields.DataSource = dt
            lstvDocTypeFields.ValueMember = dt.Columns(0).ToString
            lstvDocTypeFields.DisplayMember = dt.Columns(1).ToString
        End If

    End If

    SQLiteconn.Close()

注：该拼接的SQL语句在DB Browser for SQLite中可正常返回数据，但在代码中执行时DataReader无数据，调试时查看DataReader后数据会被清空。曾尝试重装、更新System.Data.SQLite包，改用Microsoft.Data.SQLite包，均未解决问题。

解决方法：参数化查询 #

使用参数化查询即可解决问题，注意不要给参数名添加单引号：

Dim sqlitecmd As New SQLiteCommand("SELECT * FROM documentFields WHERE docType = @DocType;", SQLiteconn)
sqlitecmd.Parameters.AddWithValue("@DocType", strDocType)

移除参数的单引号后，查询可正常返回数据并填充ListView。

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