Unity使用SQLite

一、概述

参考：https://github.com/praeclarum/sqlite-net

参考：https://github.com/kdw9502/UnitySQLiteAsync

二、同步API

1、命名空间

using SQLite;

2、创建表格

//创建表格的实例对象
class Person
{
    [PrimaryKey, AutoIncrement]
    public int id { set; get; }
    [Column("name")]
    public string name { set; get; }
    [Column("address")]
    public string address { set; get; }
}

//创建表格
//dbPath为数据库的位置
SQLiteConnection connection = new SQLiteConnection(dbPath);
connection.CreateTable<Person>();

3、插入数据

SQLiteConnection connection = new SQLiteConnection(dbPath);

//使用LINQ插入数据
Person person1 = new Person { id = 1, name = "p1", address = "a1" };
connection.Insert(person1);

//使用sql语句插入数据
string insertSQL = string.Format("INSERT INTO {0} ({1},{2},{3}) VALUES ({4},'{5}','{6}');", "person", "id", "name", "address", 2, "name2", "address2");
connection.Query<Person>(insertSQL);

4、更新数据

//LINQ更改数据，根据主键来匹配
Person person1 = new Person { id = 1, name = "np1", address = ",a1" };
connection.Update(person1);

//使用SQL语句更新数据
string updateSQL = string.Format("UPDATE {0} SET name = '{1}',address = '{2}' WHERE id = {3};", "person", "newname", "new address", 3);
connection.Query<Person>(updateSQL);

5、删除数据

//使用LINQ删除数据
int id = 2;
connection.Delete<Person>(id);
connection.Close();

//使用sql语句删除数据
string deleteSQL = string.Format("DELETE FROM {0} WHERE id = {1};", "person", 4);
connection.Query<Person>(deleteSQL);

6、查询数据

//使用LINQ来进行查询
var result = connection.Table<Person>().Where((p) => p.name == "p1");
foreach (var item in result)
 {
  print(string.Format("id:{0},name:{1},address:{2}", item.id, item.name, item.address));
}

//使用SQL语句进行查询
string selectSQL = string.Format("SELECT * FROM {0} WHERE name = '{1}';", "person", "p1");
var result = connection.Query<Person>(selectSQL);
foreach (var item in result)
 {
  print(string.Format("id:{0},name:{1},address:{2}", item.id, item.name, item.address));
}

三、异步API

1、连接关闭数据库

SQLiteAsyncConnection connection = new SQLiteAsyncConnection(dbPath);
connection.CloseAsync();

2、创建数据库

        SQLiteAsyncConnection connection = new SQLiteAsyncConnection(dbPath);
        Func<Task> taskFunc = async () =>
        {
            await connection.CreateTableAsync<Person>();
        };
        Task task = taskFunc();
        task.ContinueWith(async (ta) =>
        {
            print("创建表格完成");
            await connection.CloseAsync();
        });

3、其他操作

• connection.InsertAsync
• connection.UpdateAsync
• connection.DeleteAsync
• connection.FindAsync
• connection.ExecuteAsync
• connection.QueryAsync

四、特性

• PrimaryKey该属性是表的主键。仅支持单列主键。
• AutoIncrement该属性由数据库在插入时自动生成。propertytype应该是一个整数，并且还应该用PrimaryKey属性来标记。
• Indexed应为该属性创建一个索引。
• MaxLength如果此属性是 String，则MaxLength用于指定varchar最大大小。默认最大长度为 140。
• Ignore该属性不会出现在表中。
• Table指定表格的名字
• Column指定列的名字

异步的SQLite工具

同步的SQLite工具