# Sqlite 助手

这篇文章介绍了如何在 Unity 中使用 Sqlite
Sqlite 助手是对 Sqlite 数据库语言的封装，使用者可以绕过数据库语言，用更简单的方法操作数据库
路径问题：
- 数据库文件（.db）应该放到 StreamingAssets 文件夹中
- 向 Sqlite 助手提供 StreamingAssets 中的路径，Sqlite 助手利用路径助手处理路径

# 代码

	using Mono.Data.Sqlite;
	using System;
	using System.Text;
	using UnityEngine;

	namespace Common
	{
	/// <summary>
	/// Sqlite 助手
	/// </summary>
	public class SqliteHelper
	{
	/// <summary>
	/// 数据库连接
	/// </summary>
	private SqliteConnection dbConnection;
	/// <summary>
	/// 数据库命令
	/// </summary>
	private SqliteCommand dbCommand;
	/// <summary>
	/// 数据读取器
	/// </summary>
	private SqliteDataReader reader;
	/// <summary>
	/// 字符串构建器
	/// </summary>
	private StringBuilder strBuilder;

	public SqliteHelper()
	{
	strBuilder = new StringBuilder();
	}

	/// <summary>
	/// 创建数据库并连接，如果存在数据库，则直接连接。 path=StreamingAssets 中的路径
	/// </summary>
	public SqliteHelper(string path)
	{
	strBuilder = new StringBuilder();
	OpenDB(path);
	}

	/// <summary>
	/// 打开数据库
	/// </summary>
	public void OpenDB(string path)
	{
	strBuilder.Clear();

	strBuilder.Append("URI=file:");
	strBuilder.Append(PathHelper.GetPath(path));

	try
	{
	dbConnection = new SqliteConnection(strBuilder.ToString());
	dbConnection.Open();
	}
	catch (Exception e)
	{
	Debug.LogError(e.ToString());
	}
	}

	/// <summary>
	/// 关闭数据库
	/// </summary>
	public void CloseSqlConnection()
	{
	dbCommand?.Dispose();
	dbCommand = null;

	reader?.Dispose();
	reader = null;

	dbConnection?.Close();
	dbConnection = null;
	}

	/// <summary>
	/// 执行命令
	/// </summary>
	private SqliteDataReader Execute(string sqlQuery)
	{
	dbCommand = dbConnection.CreateCommand();
	dbCommand.CommandText = sqlQuery;

	reader = dbCommand.ExecuteReader();

	return reader;
	}

	/// <summary>
	/// 批量提交命令
	/// </summary>
	public void BatchSubmitCommand(string sqlCommand)
	{
	dbCommand = dbConnection.CreateCommand();
	SqliteTransaction transaction = dbConnection.BeginTransaction();

	dbCommand.CommandText = sqlCommand;
	dbCommand.Transaction = transaction;
	dbCommand.ExecuteNonQuery();

	transaction.Commit();
	}

	/// <summary>
	/// 查询表中全部数据 param tableName = 表名
	/// </summary>
	public SqliteDataReader ReadFullTable(string tableName)
	{
	strBuilder.Clear();

	strBuilder.Append("SELECT * FROM ");
	strBuilder.Append(tableName);

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 插入数据 param tableName = 表名 values = 插入数据内容
	/// </summary>
	public SqliteDataReader InsertInto(string tableName, string[] values)
	{
	strBuilder.Clear();

	strBuilder.Append("INSERT INTO ");
	strBuilder.Append(tableName);
	strBuilder.Append(" VALUES (");
	strBuilder.Append(values[0]);

	for (int i = 1; i < values.Length; ++i)
	{
	strBuilder.Append(", ");
	strBuilder.Append(values[i]);
	}

	strBuilder.Append(")");

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 插入数据 param tableName = 表名 cols = 插入字段 value = 插入内容
	/// </summary>
	public SqliteDataReader InsertInto(string tableName, string[] cols, string[] values)
	{
	if (cols.Length != values.Length)
	{
	throw new SqliteException("columns.Length != values.Length");
	}

	strBuilder.Clear();

	strBuilder.Append("INSERT INTO ");
	strBuilder.Append(tableName);
	strBuilder.Append("(");
	strBuilder.Append(cols[0]);

	for (int i = 1; i < cols.Length; ++i)
	{
	strBuilder.Append(", ");
	strBuilder.Append(cols[i]);
	}

	strBuilder.Append(") VALUES (");
	strBuilder.Append(values[0]);

	for (int i = 1; i < values.Length; ++i)
	{
	strBuilder.Append(", ");
	strBuilder.Append(values[i]);
	}

	strBuilder.Append(")");

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 更新数据 param tableName = 表名 selectkey = 查找字段（主键) selectvalue = 查找内容 cols = 更新字段 colsvalues = 更新内容
	/// </summary>
	public SqliteDataReader UpdateInto(string tableName, string selectkey, string selectvalue, string[] cols, string[] colsvalues)
	{
	strBuilder.Clear();

	strBuilder.Append("UPDATE ");
	strBuilder.Append(tableName);
	strBuilder.Append(" SET ");
	strBuilder.Append(cols[0]);
	strBuilder.Append(" = ");
	strBuilder.Append(colsvalues[0]);

	for (int i = 1; i < colsvalues.Length; ++i)
	{
	strBuilder.Append(", ");
	strBuilder.Append(cols[i]);
	strBuilder.Append(" = ");
	strBuilder.Append(colsvalues[i]);
	}

	strBuilder.Append(" WHERE ");
	strBuilder.Append(selectkey);
	strBuilder.Append(" = ");
	strBuilder.Append(selectvalue);
	strBuilder.Append(" ");

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 删除数据 param tableName = 表名 cols = 字段 colsvalues = 内容
	/// </summary>
	public SqliteDataReader Delete(string tableName, string[] cols, string[] colsvalues)
	{
	strBuilder.Clear();

	strBuilder.Append("DELETE FROM ");
	strBuilder.Append(tableName);
	strBuilder.Append(" WHERE ");
	strBuilder.Append(cols[0]);
	strBuilder.Append(" = ");
	strBuilder.Append(colsvalues[0]);

	for (int i = 1; i < colsvalues.Length; ++i)
	{
	strBuilder.Append(" or ");
	strBuilder.Append(cols[i]);
	strBuilder.Append(" = ");
	strBuilder.Append(colsvalues[i]);
	}

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 删除表中全部数据
	/// </summary>
	public SqliteDataReader DeleteContents(string tableName)
	{
	strBuilder.Clear();

	strBuilder.Append("DELETE FROM ");
	strBuilder.Append(tableName);

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 创建表 param tableName = 表名 col = 字段名 colType = 字段类型 primaryKey_FirstField = 第一个字段是否是主键
	/// </summary>
	public SqliteDataReader CreateTable(string tableName, string[] col, string[] colType, bool primaryKey_FirstField)
	{
	if (col.Length != colType.Length)
	{
	throw new SqliteException("The number of fields is not the same as the number of field types");
	}

	strBuilder.Clear();

	strBuilder.Append("CREATE TABLE ");
	strBuilder.Append(tableName);
	strBuilder.Append(" (");
	strBuilder.Append(col[0]);
	strBuilder.Append(" ");
	strBuilder.Append(colType[0]);

	if (primaryKey_FirstField)
	{
	strBuilder.Append(" PRIMARY KEY NOT NULL");
	}

	for (int i = 1; i < col.Length; ++i)
	{
	strBuilder.Append(", ");
	strBuilder.Append(col[i]);
	strBuilder.Append(" ");
	strBuilder.Append(colType[i]);
	}

	strBuilder.Append(")");

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 按条件查询数据 param tableName = 表名 items = 查询字段 col = 查询依据 operation = 运算符 values = 查询值 isAnd=（true: And false: Or）
	/// </summary>
	public SqliteDataReader SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values, bool isAnd)
	{
	if (col.Length != operation.Length \|\| operation.Length != values.Length)
	{
	throw new SqliteException("col.Length != operation.Length != values.Length");
	}

	strBuilder.Clear();

	strBuilder.AppendLine("SELECT ");
	strBuilder.AppendLine(items[0]);

	for (int i = 1; i < items.Length; ++i)
	{
	strBuilder.Append(", ");
	strBuilder.Append(items[i]);
	}

	strBuilder.Append(" FROM ");
	strBuilder.Append(tableName);
	strBuilder.Append(" WHERE ");
	strBuilder.Append(col[0]);
	strBuilder.Append(operation[0]);
	strBuilder.Append("'");
	strBuilder.Append(values[0]);
	strBuilder.Append("' ");

	if (isAnd)
	{
	for (int i = 1; i < col.Length; ++i)
	{
	strBuilder.Append("AND ");
	strBuilder.Append(col[i]);
	strBuilder.Append(operation[i]);
	strBuilder.Append("'");
	strBuilder.Append(values[i]);
	strBuilder.Append("' ");
	}
	}
	else
	{
	for (int i = 1; i < col.Length; ++i)
	{
	strBuilder.Append("OR ");
	strBuilder.Append(col[i]);
	strBuilder.Append(operation[i]);
	strBuilder.Append("'");
	strBuilder.Append(values[i]);
	strBuilder.Append("' ");
	}
	}

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 查询表 tableName = 表名 col = 查询依据
	/// </summary>
	public SqliteDataReader Select(string tableName, string col)
	{
	strBuilder.Clear();

	strBuilder.Append("SELECT ");
	strBuilder.Append(col);
	strBuilder.Append(" FROM ");
	strBuilder.Append(tableName);

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 查询表 tableName = 表名 col = 查询依据 values = 查询值
	/// </summary>
	public SqliteDataReader Select(string tableName, string col, string values)
	{
	strBuilder.Clear();

	strBuilder.Append("SELECT * FROM ");
	strBuilder.Append(tableName);
	strBuilder.Append(" WHERE ");
	strBuilder.Append(col);
	strBuilder.Append(" = ");
	strBuilder.Append(values);

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 查询表 tableName = 表名 col = 查询依据 operation = 运算符 values = 查询值
	/// </summary>
	public SqliteDataReader Select(string tableName, string col, string operation, string values)
	{
	strBuilder.Clear();

	strBuilder.Append("SELECT * FROM ");
	strBuilder.Append(tableName);
	strBuilder.Append(" WHERE ");
	strBuilder.Append(col);
	strBuilder.Append(operation);
	strBuilder.Append(values);

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 升序查询 tableName = 表名 col = 查询依据
	/// </summary>
	public SqliteDataReader SelectOrderASC(string tableName, string col)
	{
	strBuilder.Clear();

	strBuilder.Append("SELECT * FROM ");
	strBuilder.Append(tableName);
	strBuilder.Append(" ORDER BY ");
	strBuilder.Append(col);
	strBuilder.Append(" ASC");

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 降序查询 tableName = 表名 col = 查询依据
	/// </summary>
	public SqliteDataReader SelectOrderDESC(string tableName, string col)
	{
	strBuilder.Clear();

	strBuilder.Append("SELECT * FROM ");
	strBuilder.Append(tableName);
	strBuilder.Append(" ORDER BY ");
	strBuilder.Append(col);
	strBuilder.Append(" DESC");

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 查询表行数 tableName = 表名
	/// </summary>
	public SqliteDataReader SelectCount(string tableName)
	{
	strBuilder.Clear();

	strBuilder.Append("SELECT COUNT(*) FROM ");
	strBuilder.Append(tableName);

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 查询所有表的名称
	/// </summary>
	public SqliteDataReader SelectTableName()
	{
	strBuilder.Clear();

	strBuilder.Append("SELECT name FROM sqlite_master WHERE type='table'");

	return Execute(strBuilder.ToString());
	}

	/// <summary>
	/// 查询表的信息 tableName = 表名
	/// </summary>
	public SqliteDataReader SelectTableInfo(string tableName)
	{
	strBuilder.Clear();

	strBuilder.Append("PRAGMA table_info(");
	strBuilder.Append(tableName);
	strBuilder.Append(")");

	return Execute(strBuilder.ToString());
	}
	}
	}

# 使用方法

注意：在插入或更新数据时，对于字符串类型的数据必须添加单引号（原因：数据库语言中的字符串使用单引号），例："' 数据_001'"

	using Common;
	using Mono.Data.Sqlite;
	using System.Data;
	using UnityEngine;

	namespace Default
	{
	/// <summary>
	/// SQLiteTest
	/// </summary>
	public class SQLiteTest : MonoBehaviour
	{
	/// <summary>
	/// 路径
	/// </summary>
	private static string Path = "/DB/Test.db";

	/// <summary>
	/// 数据库助手
	/// </summary>
	private SqliteHelper DB;

	/// <summary>
	/// 读取到的数据
	/// </summary>
	private string Data;

	private void OnGUI()
	{
	if (GUILayout.Button("CreateTable"))
	{
	CreateTable();
	}
	if (GUILayout.Button("InsertDate"))
	{
	InsertData();
	}
	if (GUILayout.Button("UpdateDB"))
	{
	UpdateData();
	}
	if (GUILayout.Button("DeleteDate"))
	{
	DeleteData();
	}
	if (GUILayout.Button("SelectData"))
	{
	SelectData();
	}

	GUILayout.Button(Data);
	}

	/// <summary>
	/// 创建数据库并连接。
	/// 如果存在数据库，则直接连接
	/// </summary>
	private void CreateDB()
	{
	DB = new SqliteHelper(Path);
	}

	/// <summary>
	/// 创建表
	/// </summary>
	private void CreateTable()
	{
	// 连接
	CreateDB();

	string name = "Role"; // 表名
	string[] col = new string[] { "id", "name", "lv" }; // 字段名
	string[] colType = new string[] { "int", "string", "int" }; // 字段类型
	DB.CreateTable(name, col, colType, true);

	// 关闭连接
	DB.CloseSqlConnection();
	}

	/// <summary>
	/// 插入数据
	/// </summary>
	private void InsertData()
	{
	CreateDB();

	// 插入一行数据
	DB.InsertInto("Role", new string[] { "0", "'Test_0'", "0" });
	DB.InsertInto("Role", new string[] { "1", "'Test_1'", "10" });
	DB.InsertInto("Role", new string[] { "2", "'Test_2'", "20" });
	DB.InsertInto("Role", new string[] { "3", "'Test_3'", "30" });
	DB.InsertInto("Role", new string[] { "4", "'Test_4'", "40" });

	Data = string.Empty;

	DB.CloseSqlConnection();
	}

	/// <summary>
	/// 更新数据
	/// </summary>
	private void UpdateData()
	{
	CreateDB();

	DB.UpdateInto("Role", "id", "0", new string[] { "name", "lv" }, new string[] { "'Test_00'", "100" });

	Data = string.Empty;

	DB.CloseSqlConnection();
	}

	/// <summary>
	/// 删除数据
	/// </summary>
	private void DeleteData()
	{
	CreateDB();

	// 删除某些数据
	//DB.Delete("Role", new string[] { "id" }, new string[] { "0" });

	// 删除表中全部数据
	DB.DeleteContents("Role");

	Data = string.Empty;

	DB.CloseSqlConnection();
	}

	/// <summary>
	/// 查询数据
	/// </summary>
	private void SelectData()
	{
	CreateDB();

	SqliteDataReader reader;

	// 查询指定的数据
	//reader = DB.SelectWhere(
	// "Role",
	// new string[] { "name", "lv" },
	// new string[] { "id", "id" },
	// new string[] { "=", "=" },
	// new string[] { "1", "2" },
	// false);

	// 查询一行
	//reader = DB.Select("Role", "id", "1");
	//reader = DB.Select("Role", "id", ">=", "1");

	// 查询全部数据
	reader = DB.ReadFullTable("Role");

	// 降序查询
	//reader = DB.SelectOrderDESC("Role", "id");

	//reader.Read () 会读取一行，反复调用可以读取多行
	while (reader.Read())
	{
	string name = reader.GetString("name");
	int lv = reader.GetInt32("lv");

	Data += "name: " + name + "\nlv: " + lv + "\n\n";
	}

	DB.CloseSqlConnection();
	}
	}
	}