在UWP中使用SQLite数据库，支持加密

SQLite-net 项目地址： https://github.com/praeclarum/sqlite-net

这个库支持Xamarin.*（iOS，Android等）、 .NET， .NET Core、Mono、UWP等主流系统和程序。支持对数据库文件的加密，其sqlcipher加密版为sqlite-net-sqlcipher，可以从Nuget获取。

如果在开发iOS / macOS应用中使用的也是基于sqlcipher实现的sqlite加密的库，如SQLite.swift，那么这二种程序生成的数据文件应该是可以互相兼容的。

安装

使用VS新建一个UWP项目，右键解决方案资源管理器中项目节点下的的引用节点，选择管理Nuget程序包，打开NuGet包管理器。切换到浏览标签，搜索sqlite-net-sqlcipher，选择第一项，点击右侧的安装，这个是sqlite-net带sqlcipher加密功能的版本。

uwp_sqlite_01.png

SQL方式操作SQLite

创建和连接数据库

var connection = new SQLite.SQLiteConnection("Test.db", key: "Mandarava");
...
connection.Dispose();

直接实例一个SQLiteConnection对象，并提供数据库的保存路径和加密密码（如果不需要可以提供null）来连接数据库，如果数据库不存在，则会自动创建数据文件。这里我只提供了文件名称Test.db，密码为Mandarava。最后，可以使用connection.Dispose方法来关闭数据库，当然，因为SQLiteConnection派生自IDisposable，所以使用using语句会更简洁一下。

那么Test.db这个数据文件到底保存在了哪里呢？可以用下面的代码来取得它的实际目录：

var currentLocalFolder = Windows.Storage.ApplicationData.Current.LocalFolder.Path;

所以最终的保存位置大概是：C:\Users\[用户名]\AppData\Local\Packages\xxxx\LocalState目录下。

创建表

var sql = @"CREATE TABLE [User] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
[Name] VARCHAR(50) NOT NULL,
[Sex] VARCHAR(10) NOT NULL)";
var command = connection.CreateCommand(sql);
command.ExecuteNonQuery();

使用connection.CreateCommand(sql)来获得一个SQLiteCommand实例，使用command.ExecuteNonQuery来执行建表操作。这里创建一个User表用于存放User用户信息。这个表它有三个字段，分别是Id字段（Integer类型，主键），Name字段（字符串类型），Sex字段（字符串类型）。然后，根据这个表，可以创建一个对应的User实体类。

public class User
{
    [SQLite.PrimaryKey, SQLite.AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Sex { get; set; }
}

插入记录

sql = "INSERT INTO [User] VALUES(?, ?, ?)";
command = connection.CreateCommand(sql, null, "Manda", "M");
command.ExecuteNonQuery();

sql中的?表示需要接收参数的占位符，然后在connection.CreateCommand方法中提供它们需要接收的参数值即可。

查询记录

sql = "SELECT * FROM [User]";
command = connection.CreateCommand(sql);
var userList = command.ExecuteQuery<User>();
foreach (var user in userList)
{
    UWPConsole.Console.WriteLine($"{user.Id}\t{user.Name}\t{user.Sex}");
}

这里使用command.ExecuteQuery方法来进行查询，查询时需要提供一个结果所对应的实体类型，这里就是User类。

注：UWPConsole.Console.WriteLine是一个UWP控制台工具提供的方法，你可以在NuGet中查找并安装UWPConsole后来使用。

问题：command.ExecuteQuery<User>()必须提供查询结果对应的实体类型，这里就是User类，但如果我的sql是个join查询，这就出现问题了，没有对应的类可用。尝试提供Dictionary<obect, object>，List<object>都是接收到不到任何数据的。看了一下ExecuteQuery的实现，似乎确实没办法，而且ORM方式也似乎不支持join操作。

运行结果

到这里的全部代码如下：

//连接数据库，如果数据库不存在则自动创建
using (var connection = new SQLite.SQLiteConnection("Test.db", key: "Mandarava"))
{
    //新建表
    var sql = @"CREATE TABLE IF NOT EXISTS [User] (
[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
[name] VARCHAR(50) NOT NULL,
[sex] VARCHAR(10) NOT NULL)";
    var command = connection.CreateCommand(sql);
    command.ExecuteNonQuery();

    //插入二条记录
    sql = "INSERT INTO [User] VALUES(?, ?, ?)";
    command = connection.CreateCommand(sql, null, "Manda", "M");
    command.ExecuteNonQuery();
    command = connection.CreateCommand(sql, null, "Flower", "F");
    command.ExecuteNonQuery();

    //查询记录
    sql = "SELECT * FROM [User]";
    command = connection.CreateCommand(sql);
    var userList = command.ExecuteQuery<User>();
    foreach (var user in userList)
    {
        UWPConsole.Console.WriteLine($"{user.Id}\t{user.Name}\t{user.Sex}");
    }
}

运行结果如下，创建的二条记录成功被查询到了（控制台窗口由UWPConsole呈现）：

uwp_sqlite_02.png

删除记录

sql = "DELETE FROM [User] WHERE ID = 1";
command = connection.CreateCommand(sql);
command.ExecuteNonQuery();

这个只需要提供Delete语句即可。

ORM方式操作SQLite

这种方式就比较简洁了，避开了繁杂的SQL语句。

这种方式下就要创建表的实体类，本例只有一个表User表，其对应的实体类就是User类，在前文中已经创建过了。

public class User
{
    [SQLite.PrimaryKey, SQLite.AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Sex { get; set; }
}

在这个类中，用[SQLite.PrimaryKey, SQLite.AutoIncrement]将Id属性声明为数据库自动递增的主键字段。更多ORM属性可以查阅官方文档 ORM Attributes（注意MaxLength属性）。

创建表

connection.CreateTable<User>();

创建表时提供表的实体类型即可，这里就是User类，所创建的表的名称就是类的名称。

提示：这种ORM方式有一个问题，如果你想对app代码进行混淆加密，混淆工具通常都会提供将类名、字段名混淆的功能，每次混淆后，类名、字段名很大可能都不同于上一次混淆结果（不过，有些工具似乎可以设置为相同），所以对于这种情况，就要禁止对相关类进行混淆了，否则你的app这次发布时运行正常，下次发布时可能就找不到原来的数据表了（因为相关的类名、字段名可能变了）。

插入记录

connection.Insert(new User() { Name = "钢蛋", Sex = "男" });
connection.Insert(new User() { Name = "约汉", Sex = "女" });
connection.Insert(new User() { Name = "铁锤", Sex = "男" });

插入很简单，就是提供一个User类的实例即可。

查询记录

var users = connection.Table<User>().Where(u => u.Sex == "男");
foreach (var user in users)
{
    UWPConsole.Console.WriteLine($"{user.Id}\t{user.Name}\t{user.Sex}");
}

查询也很简单，先用connection.Table<User>()取得要查询的表，再给Where方法一个查询条件即可。

uwp_sqlite_03.png

删除记录

connection.Delete<User>(1);

删除记录时，只需要提供一个主键值即可。比如这里要删除第1条钢蛋的记录，因为它的主键是Id，钢蛋的Id是1，就提供其Id值即1即可。

其它

其它功能，比如异步处理、事务处理等可以参考官方文档。