[基础常用] Unity[C#]中使用SqLite需要注意的问题

请注意博客发布时间及相关内容版本。

前些天奉命研究SqLite，但说实话，以我现在对数据库知识还停留在仅能看懂SQL语句的这种程度，关于怎么连接数据库，以及里面的工作原理是一窍不通的。

所以我只能借助在网络上查找这类知识，比较幸运的是这类内容还是比较多的，雨松之前也写过相关的教程。

但当我按照教程进行实际操作的时候却出现了问题，那些教程多数已经过于久远，虽然大体方向依旧不变，但在细节上的不同是更让人头疼的。

首先要导入相应的SqLite所需DLL，这里我导入了三个DLL和一个DEF文件，分别是

System.Data.dll            836 KB

sqlite3.dll                     1713 KB

Mono.Data.Sqlite.dll    166 KB

sqlite3.def                    6 KB

这里就要注意了，敲黑板，划重点，这几个DLL有多种版本，我们必须使用可以配套兼容的才可以正常操作SqLite。

我并不知道自己使用的是什么版本，但实测可用，所以我将他们，打包在一起上传上来了，

百度网盘链接

我之前按照网上几种给的老版的DLL，在连接数据库方法调用后始终拿不到对象，相当郁闷，后来在Unity文件夹下一搜才发现有好多大小或不相同的同名文件，这说明有很多版本的DLL，但网上通常不会说到这个问题，希望能分享一下我在这里卡坑的经验。

将上面的DLL放入Unity的Assets下后，就可以调用SqLite了，调用封装我这里就直接引用雨松的代码，感谢雨松~

(这个对象不继承MonoBehaviour)

using UnityEngine;
using System;
using System.Collections;

using Mono.Data.Sqlite;

public class DBAccess
{

    private SqliteConnection m_dbConnection;

    private SqliteCommand m_dbCommand;

    private SqliteDataReader m_dbReader;

    /// <summary>
    /// 构造
    /// </summary>
    /// <param name="dbFilePath">db文件位置</param>
    public DBAccess(string dbFilePath)
    {
        try
        {
            string t_strConnectionPath = "URI=file:" + Application.dataPath + "/" + dbFilePath;
            m_dbConnection = new SqliteConnection(t_strConnectionPath);
            m_dbConnection.Open();
            Debug.Log("Connected to db");
        }
        catch (Exception ex)
        {
            string t_strError = ex.ToString();
            Debug.Log(t_strError);
        }
    }

    /// <summary>
    /// 关闭连接
    /// </summary>
    public void CloseSqlConnection()
    {
        if (m_dbCommand != null) { m_dbCommand.Dispose(); }
        m_dbCommand = null;
        if (m_dbReader != null) { m_dbReader.Close(); }
        m_dbReader = null;
        if (m_dbConnection != null) { m_dbConnection.Close(); }
        m_dbConnection = null;
        Debug.Log("Disconnected from DB.");
    }

    public SqliteDataReader ExecuteQuery(string sqlQuery)
    {
        m_dbCommand = m_dbConnection.CreateCommand();
        m_dbCommand.CommandText = sqlQuery;
        m_dbReader = m_dbCommand.ExecuteReader();
        return m_dbReader;
    }

    /// <summary>
    /// 读取全内容[查]
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <returns>结果Reader</returns>
    public SqliteDataReader ReadFullTable(string tableName)
    {
        string t_strQuery = "SELECT * FROM " + tableName;
        Debug.Log("Select Query : " + t_strQuery);
        SqliteDataReader reader = ExecuteQuery(t_strQuery);
        while (reader.Read())
        {
            string id = reader["_id"].ToString();
            string book = reader["book"].ToString();
            Debug.Log("get:" + id + " " + book);
        }
        //return ExecuteQuery(t_strQuery);
        return reader;
    }

    /// <summary>
    /// 插入内容[增]
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="values">要插入都值</param>
    /// <returns>结果Reader</returns>
    public SqliteDataReader Insert(string tableName, string[] values)
    {
        string t_strQuery = "INSERT INTO " + tableName + " VALUES ( " + values[0];
        for (int i = 1; i < values.Length; ++i) { t_strQuery += ", " + values[i]; }
        t_strQuery += ")";
        Debug.Log("Insert Query : " + t_strQuery);
        return ExecuteQuery(t_strQuery);
    }

    /// <summary>
    /// 插入内容[增]
    /// 这个和上一个不一样都是要输入字段名一一对应，可以防止插错
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="columns">字段名</param>
    /// <param name="columnValues">字段值</param>
    /// <returns>结果Reader</returns>
    public SqliteDataReader InsertIntoSpecific(string tableName, string[] columns, string columnValues)
    {
        if (columns.Length != columnValues.Length) { throw new SqliteException("columns.Length != columnValues.Length"); }
        string t_strQuery = "INSERT INTO " + tableName + "(" + columns[0];
        for (int i = 1; i < columns.Length; ++i) { t_strQuery += ", " + columns[i]; }
        t_strQuery += ") VALUES ( " + columnValues[0];
        for (int i = 1; i < columns.Length; ++i) { t_strQuery += ", " + columnValues[i]; }
        t_strQuery += ")";
        return ExecuteQuery(t_strQuery);
    }

    /// <summary>
    /// 根据条件修改指定字段值[改]
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="columns">字段名</param>
    /// <param name="columnValues">修改都值</param>
    /// <param name="selectKey">条件字段</param>
    /// <param name="selectValue">条件字段值</param>
    /// <returns>结果Reader</returns>
    public SqliteDataReader Update(string tableName, string[] columns, string[] columnValues, string selectKey = null, string selectValue = null)
    {
        if (columns.Length != columnValues.Length) { throw new SqliteException("columns.Length != values.Length"); }
        string t_strQuery = "UPDATE " + tableName + " SET " + columns[0] + " = " + columnValues[0];
        for (int i = 1; i < columns.Length && i < columnValues.Length; ++i) { t_strQuery += ", " + columns[i] + " = " + columnValues[i]; }
        if (selectKey != null && selectValue != null)
            t_strQuery += " WHERE " + selectKey + " = " + selectValue + " ";
        Debug.Log("Update Query : " + t_strQuery);
        return ExecuteQuery(t_strQuery);
    }

    /// <summary>
    /// 删除指定字段值[删]
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="columns">字段名</param>
    /// <param name="columnValues">字段值</param>
    /// <returns>结果Reader</returns>
    public SqliteDataReader Delete(string tableName, string[] columns, string columnValues)
    {
        string t_strQuery = "DELETE FROM " + tableName + " WHERE " + columns[0] + " = " + columnValues[0];
        for (int i = 1; i < columns.Length && i < columnValues.Length; ++i) { t_strQuery += "or " + columns[i] + " = " + columnValues[i]; }
        Debug.Log("Delete Query : " + t_strQuery);
        return ExecuteQuery(t_strQuery);
    }

    /// <summary>
    /// 清空表[删]
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <returns>结果Reader</returns>
    public SqliteDataReader ClearTable(string tableName)
    {
        string t_strQuery = "DELETE FROM " + tableName;
        return ExecuteQuery(t_strQuery);
    }

    /// <summary>
    /// 创建表[Create]
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="columns">字段名</param>
    /// <param name="columnTypes">字段类型</param>
    /// <returns>结果Reader</returns>
    public SqliteDataReader CreateTable(string tableName, string[] columns, string[] columnTypes)
    {
        if (columns.Length != columnTypes.Length) { throw new SqliteException("Columns.Length != ColumnTypes.Length."); }
        string t_strQuery = "CREATE TABLE " + tableName + " (" + columns[0] + " " + columnTypes[0];
        for (int i = 1; i < columns.Length; ++i) { t_strQuery += ", " + columns[i] + " " + columnTypes[i]; }
        t_strQuery += ")";
        return ExecuteQuery(t_strQuery);
    }

    /// <summary>
    /// 根据内容查找[查]
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="items">字段名</param>
    /// <param name="columns">条件字段</param>
    /// <param name="operation">条件运算符</param>
    /// <param name="columnValues">条件值</param>
    /// <returns>结果Reader</returns>
    public SqliteDataReader SelectWhere(string tableName, string[] items, string[] columns, string[] operation, string[] columnValues)
    {
        if (columns.Length != columnValues.Length) { throw new SqliteException("Columns.Length != columnValues.Length."); }
        string t_strQuery = "SELECT " + items[0];
        for (int i = 1; i < items.Length; ++i) { t_strQuery += ", " + items[i]; }
        t_strQuery = "FROM " + tableName + " WHERE " + columns[0] + operation[0] + "'" + columnValues[0] + "'";
        for (int i = 1; i < columns.Length; ++i) { t_strQuery = " AND " + columns[i] + operation[0] + "'" + columnValues[0] + "'"; }
        return ExecuteQuery(t_strQuery);
    }
}