在Unity中对SQLite的增删改查的简单封装

using System;
using Mono.Data.SqliteClient;
using System.Data;
using UnityEngine;

class DbAccessHelper
{
    private IDbConnection dbConn;
    private IDbCommand dbCommand;
    private IDataReader dbReader;
    public DbAccessHelper(string path)
    {
        OpenDb(path);
    }
    /// <summary>
    /// 打开数据库
    /// </summary>
    /// <param name="path">路径</param>
    private void OpenDb(string path)
    {
        dbConn = new SqliteConnection("URI=file:" + path);
        dbConn.Open();
        Debug.Log("连接数据库成功!");
    }
    /// <summary>
    /// 创建表
    /// </summary>
    /// <param name="name">表明</param>
    /// <param name="col">数据</param>
    /// <param name="colType">数据类型</param>
    /// <returns></returns>
    public bool CreateTable(string name, string[] col, string[] colType)
    {
        string commPath = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0];
        for (int i = 1; i < col.Length; i++)
        {
            commPath += "," + col[i] + " " + colType[i];
        }
        commPath += ")";

        ExecuteCommand(commPath);
        Debug.Log("创建表成功!");
        return true;
    }
    /// <summary>
    /// 插入多条数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="values">数据</param>
    /// <returns></returns>
    public bool InsertInto(string tableName, string[] values)
    {
        string commPath = "INSERT INTO " + tableName + " VALUES (" + values[0];
        for (int i = 1; i < values.Length; i++)
        {
            commPath += "," + values[i];
        }
        commPath += ")";

        ExecuteCommand(commPath);
        Debug.Log("插入数据成功!");
        return true;
    }
    /// <summary>
    /// 插入单条数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="colName">列名</param>
    /// <param name="value">数据</param>
    public bool InsertIntoSingle(string tableName, string colName, string value)
    {
        string commPath = "INSERT INTO " + tableName + "(" + colName + ") " + "VALUES (" + value + ")";

        ExecuteCommand(commPath);
        Debug.Log("插入单条数据成功!");
        return true;
    }
    /// <summary>
    /// 根据列插入数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="colnames">列名</param>
    /// <param name="values">数据</param>
    /// <returns></returns>
    public bool InsertIntoSpecific(string tableName, string[] colnames, string[] values)
    {
        string commPath = "INSERT INTO " + tableName + "(" + colnames[0];
        for (int i = 1; i < colnames.Length; i++)
        {
            commPath += "," + colnames[i];
        }
        commPath += ") VALUES (" + values[0];
        for (int i = 1; i < values.Length; i++)
        {
            commPath += "," + values[i];
        }

        ExecuteCommand(commPath);
        return true;
    }
    /// <summary>
    /// 更新表中数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="colNames">列名</param>
    /// <param name="colValues">更新的数据</param>
    /// <param name="selectKey">主键</param>
    /// <param name="selectValue">主键值</param>
    public bool UpdateInto(string tableName, string[] colNames, string[] colValues, string selectKey, string selectValue)
    {
        string commPath = "UPDATE " + tableName + " SET " + colNames[0] + "=" + colValues[0];
        for (int i = 1; i < colNames.Length; i++)
        {
            commPath += "," + colNames[i] + "=" + colValues[i];
        }
        commPath += " WHERE " + selectKey + "=" + selectValue;

        ExecuteCommand(commPath);
        Debug.Log("更新数据成功!");
        return true;
    }
    /// <summary>
    /// 执行删除数据语句
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="colNames">列名</param>
    /// <param name="values">某列对应的数据</param>
    /// <returns></returns>
    public void Delete(string tableName, string[] colNames, string[] values)
    {
        string commPath = "DELETE FROM " + tableName + " WHERE " + colNames[0] + "=" + values[0];
        for (int i = 1; i < colNames.Length; i++)
        {
            commPath += " OR " + colNames[i] + "=" + values[i];
        }
        ExecuteCommand(commPath);
        Debug.Log("删除成功!");
    }

    public IDataReader SelectWhere(string tableName, string[] item, string[] colNames, string[] operations, string[] values)
    {
        string commPath = "SELECT " + item[0];
        for (int i = 1; i < item.Length; i++)
        {
            commPath += "," + item[i];
        }
        commPath += " FROM " + tableName + " WHERE " + colNames[0] + operations[0] + values[0];
        for (int i = 1; i < colNames.Length; i++)
        {
            commPath += " OR " + colNames[i] + operations[i] + values[i];
        }
        return ExecuteCommand(commPath);
    }
    /// <summary>
    /// 执行命令
    /// </summary>
    /// <param name="commPath"></param>
    /// <returns></returns>
    private IDataReader ExecuteCommand(string commPath)
    {
        try
        {
            dbCommand = dbConn.CreateCommand();
            dbCommand.CommandText = commPath;
            dbReader = dbCommand.ExecuteReader();
        }
        catch (Exception e)
        {
            Debug.LogError(e);
        }
        return dbReader;
    }

    /// <summary>
    /// 关闭数据库
    /// </summary>
    public void CloseDatabase()
    {
        dbReader.Close();
        dbReader = null;
        dbCommand.Dispose();
        dbCommand = null;
        dbConn.Close();
        dbConn = null;
        Debug.Log("关闭数据库!");
    }

}

上面这个类就是封装了SQLite的打开数据库、关闭数据库、增、删、改、查等相应功能，如果有其他需求可以自己增加功能

下面我们可以测试一下功能是否正常使用。

using UnityEngine;
using System.Collections;
using System.Data;
using System.IO;

public class TestSqlite : MonoBehaviour
{

    private string dbPath;

    private DbAccessHelper dbAccessHelper;

    private string[] name = new string[3];
    private int[] age= new int[3];
    private float[] exp =new float[3];
    private int i = 0;
    private int size = 0;
    void OnGUI()
    {
        if (GUILayout.Button("连接数据库"))
        {
            CreateDataBase();
        }
        else if (GUILayout.Button("创建数据表"))
        {
            CreateTable();
        }
        else if (GUILayout.Button("插入数据"))
        {
            InsertData();
        }
        else if (GUILayout.Button("插入单条数据"))
        {
            InsertSigleData();
        }
        else if (GUILayout.Button("根据列插入多条数据"))
        {
            InsertSpecificData();
        }
        else if (GUILayout.Button("更新数据"))
        {
            UpdateData();
        }
        else if (GUILayout.Button("删除数据"))
        {
            DeleteData();
        }
        else if (GUILayout.Button("查找数据"))
        {
            FindData();
        }
        GUILayout.Label("name: " + name[0] + " age: " + age[0] + " exp: " + exp[0]);
        GUILayout.Label("name: " + name[1] + " age: " + age[1] + " exp: " + exp[1]);
        GUILayout.Label(dbPath);
        GUILayout.Label(size.ToString());
    }
    /// <summary>
    /// 创建数据库
    /// </summary>
    void CreateDataBase()
    {
#if UNITY_EDITOR
        dbPath = Application.dataPath + "/TestDb.db";
#elif UNITY_STANDALONE_WIN
        dbPath = Application.dataPath + "/TestDb.db";
#elif UNITY_ANDROID
        dbPath = Application.persistentDataPath + "/TestDb.db";
        if (!File.Exists(dbPath))
        {
            //拷贝数据库
            StartCoroutine(CopyDataBase());
        }
#elif UNITY_IPHONE
        dbPath = Application.persistentDataPath + "/TestDb.db";
        if (!File.Exists(dbPath))
        {
            //拷贝数据库
            StartCoroutine(CopyDataBase());
        }
#endif
        dbAccessHelper = new DbAccessHelper(dbPath);
    }

    IEnumerator CopyDataBase()
    {
#if UNITY_ANDROID
        WWW www = new WWW("jar:file://" + Application.streamingAssetsPath + "/TestDb.db");
#elif UNITY_IPHONE
        WWW www = new WWW(Application.streamingAssetsPath + "/TestDb.db");
#elif UNITY_EDITOR || UNITY_STANDALONE_WIN
        WWW www = new WWW(Application.dataPath + "/TestDb.db");
#endif
        yield return www;
        size = www.size;
        File.WriteAllBytes(dbPath, www.bytes);
    }

    void CreateTable()
    {
        //建立数据库连接
        CreateDataBase();
        //创建数据表
        dbAccessHelper.CreateTable("Role", new string[] {"id", "name", "age", "lv", "exp"},
            new string[] {"int", "text", "int", "int", "float"});
        //关闭数据库
        dbAccessHelper.CloseDatabase();
    }

    void InsertData()
    {
        CreateDataBase();
        dbAccessHelper.InsertInto("Role", new string[] {"2", "'张三'", "18", "12", "2.2"});
        dbAccessHelper.CloseDatabase();
    }
    
    void InsertSigleData()
    {
        CreateDataBase();
        dbAccessHelper.InsertIntoSingle("Role", "id", "3");
        dbAccessHelper.CloseDatabase();
    }

    void InsertSpecificData()
    {
        CreateDataBase();
        dbAccessHelper.InsertIntoSpecific("Role", new string[] {"id", "name"}, new string[] {"4", "12"});
        dbAccessHelper.CloseDatabase();
    }

    void UpdateData()
    {
        CreateDataBase();
        dbAccessHelper.UpdateInto("Role", new string[] {"lv", "exp"}, new string[] {"10", "100"}, "id", "1");
        dbAccessHelper.CloseDatabase();
    }

    void DeleteData()
    {
        CreateDataBase();
        dbAccessHelper.Delete("Role", new string[] { "id", "id" }, new string[] { "2", "3" });
        dbAccessHelper.CloseDatabase();
    }

    void FindData()
    {
        CreateDataBase();
        IDataReader reader = dbAccessHelper.SelectWhere("Role", new string[] {"name", "age", "exp"}, new string[] {"id", "name"},
            new string[] {"=", "="}, new string[] {"1", "'12'"});
        while (reader.Read())
        {
            name[i] = reader.GetString(reader.GetOrdinal("name"));
            age[i] = reader.GetInt32(reader.GetOrdinal("age"));
            exp[i] = reader.GetFloat(reader.GetOrdinal("exp"));
            i++;
        }

        dbAccessHelper.CloseDatabase();
    }
}

拷贝上述测试代码到unity中运行即可

在Unity中使用SQLite必须下载相应的SQLite库文件，如何不知道如何下载请留言我会发链接