SQLite在unity下使用的工具类

/// <summary>
/// SQLite数据库简单操作的分装（Sqlite3）
/// 注意：SQLite 是不区分大小写
/// </summary>
public class DbAccess
{
    //SQLite链接库
	private SqliteConnection dbConnection;
	
	private SqliteCommand dbCommand;
	//数据库读取
	private SqliteDataReader reader;
	
	
	public DbAccess (string connectionString)
		
	{
		
		OpenDB (connectionString);
		
	}
	public DbAccess ()
	{
		
	}
	
    /// <summary>
    /// 数据库链接
    /// </summary>
    /// <param name="connectionString"></param>

	public void OpenDB (string connectionString)
		
	{
		try
		{
			dbConnection = new SqliteConnection (connectionString);
			
			dbConnection.Open ();
			
			//Debug.Log ("Connected to db,连接数据库成功！");
		}
		catch(Exception e)
		{
			string temp1 = e.ToString();
			Debug.Log(temp1);
		}
		
	}
	
	
	/// <summary>
	/// 关闭数据库
	/// </summary>
	public void CloseSqlConnection ()
		
	{
		
		if (dbCommand != null) {
			
			dbCommand.Dispose ();
			
		}
		
		dbCommand = null;
		
		if (reader != null) {
			
			reader.Dispose ();
			
		}
		
		reader = null;
		
		if (dbConnection != null) {
			
			dbConnection.Close ();
			
		}
		
		dbConnection = null;
		
		//Debug.Log ("Disconnected from db.关闭数据库！");
		
	}
	
	/// <summary>
	/// sql数据库命令
	/// </summary>
	/// <param name="sqlQuery"></param>
	/// <returns>执行结果</returns>
	public SqliteDataReader ExecuteQuery (string sqlQuery)
		
	{
		
		dbCommand = dbConnection.CreateCommand ();
		
		dbCommand.CommandText = sqlQuery;
	
		reader = dbCommand.ExecuteReader ();

		return reader;
		
	}
	
	/// <summary>
	/// 查询表中全部数据 param tableName=表名 
    ///   SELECT*FROM 表名
	/// </summary>
	public SqliteDataReader ReadFullTable (string tableName)
		
	{
		
		string query = "SELECT * FROM " + tableName;
		
		return ExecuteQuery (query);
		
	}
	
	
	/// <summary>
	/// 插入数据 param tableName=表名 values=插入数据内容
    /// INSERT INTO 表名 VALUES（aa，bb，cc...）
	/// </summary>
	public SqliteDataReader InsertInto (string tableName, string[] values)
		
	{
		
		string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
		
		for (int i = 1; i < values.Length; ++i) {
			
			query += ", " + values[i];
			
		}
		
		query += ")";
		
		return ExecuteQuery (query);
		
	}
	
	/// <summary>
	/// 更新数据 param tableName=表名 cols=更新字段 colsvalues=更新内容 selectkey=查找字段（主键) selectvalue=查找内容
    /// UPDATE  表名 SET 字段1 = xxx，字段2 = xxx  WHERE 主键 = yyy 
	/// </summary>
	public SqliteDataReader UpdateInto (string tableName, string []cols,string []colsvalues,string selectkey,string selectvalue)
	{
		
		
		string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0];
		
		for (int i = 1; i < colsvalues.Length; ++i) {
			
			query += ", " +cols[i]+" ="+ colsvalues[i];
		}
		
		query += " WHERE "+selectkey+" = "+selectvalue+" ";
		
		return ExecuteQuery (query);
	}
	
	/// <summary>
	/// 删除数据 param tableName=表名 cols=字段 colsvalues=内容
    /// DELETE FROM  表名 WHERE 字段1 = xxx or 字段2 = yyy 
	/// </summary>
	public SqliteDataReader Delete(string tableName,string []cols,string []colsvalues)
	{
		string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0];
		
		for (int i = 1; i < colsvalues.Length; ++i) {
			
			query += " or " +cols[i]+" = "+ colsvalues[i];
		}
		return ExecuteQuery (query);
	}
	
	/// <summary>
	/// 插入数据 param tableName=表名 cols=插入字段 value=插入内容
    /// INSERT INTO 表名 （字段1，字段2...） VALUES （xxx，yyy...）
	/// </summary>
	public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values)
		
	{
		
		if (cols.Length != values.Length) {
			
			throw new SqliteException ("columns.Length != values.Length");
			
		}
		
		string query = "INSERT INTO " + tableName + "(" + cols[0];
		
		for (int i = 1; i < cols.Length; ++i) {
			
			query += ", " + cols[i];
			
		}
		
		query += ") VALUES (" + values[0];
		
		for (int i = 1; i < values.Length; ++i) {
			
			query += ", " + values[i];
			
		}
		
		query += ")";
		
		return ExecuteQuery (query);
		
	}
	
	
	/// <summary>
	/// 删除表中全部数据
    ///  DELETE FROM 表名
	/// </summary>
	public SqliteDataReader DeleteContents (string tableName)
		
	{
		
		string query = "DELETE FROM " + tableName;
		
		return ExecuteQuery (query);
		
	}
	
	/// <summary>
	/// 创建表 param name=表名 col=字段名 colType=字段类型
    /// CREATE TABLE 表名（字段1  字段类型1，字段2  字段类型2，...）
    /// 不设置主键，让数据库自己设置隐藏主键
	/// </summary>
	public SqliteDataReader CreateTable (string name, string[] col, string[] colType)
		
	{
		
		if (col.Length != colType.Length) {
			
			throw new SqliteException ("columns.Length != colType.Length");
			
		}
		
		string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
		
		for (int i = 1; i < col.Length; ++i) {
			
			query += ", " + col[i] + " " + colType[i];
			
		}
		
		query += ")";
		
		
		return ExecuteQuery (query);
		
	}
	
	
	
	
	
	/// <summary>
	/// 按条件查询数据 param tableName=表名 items=查询字段 col=查找字段 operation=运算符 values=内容
    /// SELECT 字段1，字段2，... FROM 表名 WHERE  字段x=<> 'xxx' AND 字段y=<> 'yyy' ...
	/// </summary>
	public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)	
	{	
		if (col.Length != operation.Length || operation.Length != values.Length) {	
			throw new SqliteException ("col.Length != operation.Length != values.Length");	
		}
		string query = "SELECT " + items[0];
		for (int i = 1; i < items.Length; ++i) {	
			query += ", " + items[i];
		}
		query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
		for (int i = 1; i < col.Length; ++i) {
			query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
		}
		return ExecuteQuery (query);
	}

	/// <summary>
	/// 查询表
    /// SELECT * FROM 表名 WHERE 字段 = xxx
	/// </summary>
	public SqliteDataReader Select(string tableName, string col, string values)
	{
		string query = "SELECT * FROM " + tableName  + " WHERE " + col + " = " + values;
		return ExecuteQuery (query);
	}

    /// <summary>
    /// 查询表 
    /// SELECT * FROM 表名 WHERE 字段=<> xxx
    /// </summary>

	public SqliteDataReader Select(string tableName, string col,string operation, string values)
	{
		string query = "SELECT * FROM " + tableName  + " WHERE " + col + operation + values;
		return ExecuteQuery (query);
	}

	/// <summary>
	/// 升序查询
    ///  SELECT * FROM 表名 ORDER BY 字段 ASC 
	/// </summary>
	public SqliteDataReader SelectOrderASC (string tableName,string col)
	{
		string query = "SELECT * FROM " + tableName  + " ORDER BY " + col + " ASC";
		return ExecuteQuery (query);
	}

	/// <summary>
	/// 降序查询
    /// SELECT * FROM 表名 ORDER BY 字段 DESC
	/// </summary>
	public SqliteDataReader SelectOrderDESC (string tableName,string col)
	{
		string query = "SELECT * FROM " + tableName  + " ORDER BY " + col + " DESC";
		return ExecuteQuery (query);
	}

	/// <summary>
	/// 查询表行数
    ///  SELECT COUNT(*) FROM 表名
	/// </summary>
	public SqliteDataReader SelectCount(string tableName)
	{
		string query = "SELECT COUNT(*) FROM " + tableName;
		return ExecuteQuery (query);
	}
}