C# sqlite-net使用（unity、WPF、Winform使用sqlite）

引用包

搜索sqlite-net包并引用进项目，unity直接复制SQLite、SQLiteAsync到项目中即可

添加管理类

public static class DatabaseHandler
{
    private static string connstr = UnityEngine.Application.streamingAssetsPath + "/data.db"; //没有数据库会创建数据库
    public static SQLiteConnection db;
    public static SQLiteAsyncConnection dbAsync;
    static DatabaseHandler()
    {
        db = new SQLiteConnection(connstr, false);
        dbAsync = new SQLiteAsyncConnection(connstr, false);

        db.CreateTable<User>();
    }

    public static int Add(object model)
    {
        return db.Insert(model);
    }
    public static async Task<int> AddAsync(object model)
    {
        return await dbAsync.InsertAsync(model);
    }

    public static int Update(object model)
    {
        return db.Update(model);
    }
    public static async Task<int> UpdateAsync(object model)
    {
        return await dbAsync.UpdateAsync(model);
    }

    public static int Delete(object model)
    {
        return db.Delete(model);
    }
    public static async Task<int> DeleteAsync(object model)
    {
        return await dbAsync.DeleteAsync(model);
    }
    public static List<T> Query<T>(string sql) where T : new()
    {
        return db.Query<T>(sql);
    }
    public static async Task<List<T>> QueryAsync<T>(string sql) where T : new()
    {
        return await dbAsync.QueryAsync<T>(sql);
    }
    public static int Execute(string sql)
    {
        return db.Execute(sql);
    }
    public static async Task<int> ExecuteAsync(string sql)
    {
        return await dbAsync.ExecuteAsync(sql);
    }

    public static TableQuery<T> Table<T>() where T : new()
    {
        return db.Table<T>();
    }
    public static AsyncTableQuery<T> TableAsync<T>() where T : new()
    {
        return dbAsync.Table<T>();
    }
}

[Table("user")]
public class User
{
    [PrimaryKey, AutoIncrement]
    [Column("id")]
    public int Id { get; set; }
    [Column("name")]
    public string Name { get; set; }
}

使用

 var users = await DatabaseHandler.TableAsync<User>().Where(x => x.Name == "测试")
 .ToListAsync();

使用QueryAsync中文无法搜索的问题

在SQLite.cs中添加

[DllImport("sqlite3", EntryPoint = "sqlite3_prepare16_v2", CallingConvention = CallingConvention.Cdecl)]
public static extern Result Prepare16_2(IntPtr db, [MarshalAs(UnmanagedType.LPWStr)] string sql, int numBytes, out IntPtr stmt, IntPtr pzTail);

然后将Prepare2方法中的

var r = Prepare2 (db, query, System.Text.UTF8Encoding.UTF8.GetByteCount (query), out stmt, IntPtr.Zero);

替换为

var r = Prepare16_2(db, query, System.Text.Encoding.Unicode.GetByteCount(query), out stmt, IntPtr.Zero);