c# - 极其简单的 SQLite 查询不返回预期

Question

下面我提供了我的数据库架构、当前插入的数据库数据(只有一条记录)以及我正在运行的代码。这是一个非常简单的设置，但它没有返回数据库中的一条记录，而是什么都不返回。任何人都知道为什么？我在这里束手无策......

表:分包商

列:以(名称类型)格式在下面列出。

ID guid, 
BusinessName varchar(50), 
Address varchar(200), 
City varchar(50), 
State varchar(50), 
ZipCode varchar(50), 
Contact varchar(50), 
Phone varchar(50), 
Fax varchar(50), 
Email varchar(200), 
GLOPolicy bit, 
GLOLimit bigint, 
GLOExpiration datetime, 
ALPolicy bit, 
ALLimit bigint, 
ALExpiration datetime, 
WCPolicy bit, 
WCLimit bigint, 
WCExpiration datetime, 
ULPolicy bit, 
ULLimit bigint, 
ULExpiration datetime, 
Notes varchar(15000)

=====

我的数据库中有一条记录，如下。

ID "7b143c19-ad66-46ad-b587-db0bee98cf1e"
BusinessName "1"
Address "1"
City "1"
State "1"
ZipCode "1"
Contact NULL
Phone NULL
Fax NULL 
Email NULL
GLOPolicy False (0) 
GLOLimit NULL 
GLOExpiration NULL
ALPolicy False (0)
ALLimit NULL
ALExpiration NULL
WCPolicy False (0)
WCLimit NULL
WCExpiration NULL
ULPolicy False (0) 
ULLimit NULL
ULExpiration NULL
Notes NULL

=====*我正在尝试以下查询，但它什么也没返回，而它显然应该返回唯一的记录，如上所示。*

String ID = "7b143c19-ad66-46ad-b587-db0bee98cf1e";
DataTable dt = sqliteQuery.selectFromDatabase("*", "WHERE ID = '" + ID + "'");

上述方法的代码是...

    public DataTable selectFromDatabase(String column, String filter)
    {
        string SQL = "SELECT " + column + " FROM SUBCONTRACTOR " + filter;
        SQLiteCommand cmd = new SQLiteCommand(SQL);
        cmd.Connection = connection;
        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
        DataSet ds = new DataSet();
        try
        {
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            return dt;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
            return null;
        }
        finally
        {
            cmd.Dispose();
            connection.Close();
        }
    }

Answer 1

看起来 SqLite 本身不支持 GUID 类型。它由包装器和插件支持，但我认为您在使用类型的方式之间遇到了一些阻抗。您最好将其更改为 TEXT 类型。然后您的谓词将起作用。

编辑

要创建一个新的 GUID 值以作为 pkey 插入到行中，只需执行以下操作:

Guid.NewGuid().ToString();

另外我发现the following info :

GUID is not(!) a native SQLite-datatype, but an addon provided by Robert's wrapper. In the connection string you can specify "BinaryGUID=Yes|No", with Yes as default. When "BinaryGUID=Yes" the GUID is stored taking 16 bytes of storage. Now it depends whether "SQLite Expert" recognises the datatype GUID. If so, you should look at its documentation how it is handled. If not, it is probably treated as text (likely with invalid character data). You will have this trouble again when you change your SQLite editor or wrapper.

When "BinaryGUID=No" the GUID is stored as text taking 32-38 bytes (I don't know whether the minus-signs and braces {} are stored, you have to test). When storage is not a large problem, I would recommend to use this form. You then should have no problems with whatsover wrapper or DB editor you use and save a lot of time and trouble.

类型本身似乎有问题。