unity3d sqlite資料庫的讀寫方法

標籤：

　　首先，我們要從unity的安裝路徑中複製mono.data.sqlite.dll和sqlite3.dll兩個動態連結程式庫到untiy的plugins目錄下，如所示：

使用navicat for sqlite建立一個sqlite資料庫檔案，放到Resources檔案夾目錄中，如所示：

建立一個DbAccess腳步，添加以下腳步：

using UnityEngine;

using System;

using System.Collections;

using Mono.Data.Sqlite;

using System.IO;

 

public class DbAccess  {

    private SqliteConnection dbConnection;//sql串連

    private SqliteCommand dbCommand=null;//sql命令

    private SqliteDataReader dbReader;//sql讀取器

 

    public DbAccess(string connectionString)

    {

        string appDBPath = "";

 

        if(Application.platform == RuntimePlatform.WindowsEditor)//如果是windows編輯器中

        {

             appDBPath = Application.dataPath+"//"+connectionString;

        }

        else if(Application.platform == RuntimePlatform.Android)--如果是安卓平台

        {

            appDBPath = Application.persistentDataPath +"/" + connectionString;

            if (!File.Exists(appDBPath))

            {

                WWW loader = new WWW("jar:file://" + Application.dataPath + "/" + connectionString);//把資料庫複寫到安卓可寫路徑中，註：sqlite不能在安裝包中讀取資料

                File.WriteAllBytes(appDBPath,loader.bytes);

            }

        }

        OpenDB("Data Source="+appDBPath);

    }

 

    private void OpenDB(string connectionString)

    {

        try

        {

            dbConnection = new SqliteConnection(connectionString);

            dbConnection.Open();

            Debug.Log("connect to db");

        }

        catch (System.Exception ex)

        {

            Debug.Log(ex.Message);

        }

    }

 

    public void CloseSqlConnection()//關閉資料庫連接

    {

        if (dbCommand!=null)

            dbCommand.Dispose();

        dbCommand = null;

 

        if (dbReader!=null)

            dbReader.Dispose();

        dbReader = null;

 

        if (dbConnection!=null)

            dbConnection.Close();

        dbConnection = null;

    }

 

    public SqliteDataReader ExecuteQuery(string sqlQuery)//執行查詢

    {

        dbCommand = dbConnection.CreateCommand();

        dbCommand.CommandText = sqlQuery;

        dbReader = dbCommand.ExecuteReader();

        return dbReader;

    }

 

    public SqliteDataReader ReadFullTable(string tableName)//讀取整個表

    {

        string query = "SELECT * FROM " + tableName+";";

        return ExecuteQuery(query);

    }

 

    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);

    }

 

    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);

    }

 

    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);

    }

 

    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);

 

    }

 

    public SqliteDataReader DeleteContents (string tableName)//刪除表

    {

        string query = "DELETE FROM " + tableName;

 

        return ExecuteQuery (query);

 

    }

 

    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);

 

    }

 

    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);

 

    }

 

}

 

使用案例：

資料庫的Dialog表中有一下資料：

 

        DbAccess db = new DbAccess("/Resources/DazzleParkour.sqlite");

 

        using (SqliteDataReader reader = db.SelectWhere("Dialog", new string[] { "id,Name,FileName,Scale" }, new string[] { "Scale" }, new string[] { "=" }, new string[] { "1" }))//讀取出Scale等於1的資料

 

        {

 

            while (reader.Read())// 迴圈遍曆資料

 

            {

 

                int name = reader.GetInt32(reader.GetOrdinal("id"));

 

                Debug.Log(name);

 

            }

 

            reader.Close();

 

            db.CloseSqlConnection();

 

        }

 

最後列印的資料如下

 

在打包時，注意要把playersetting裡的api解析等級改為.Net 2.0

unity3d sqlite資料庫的讀寫方法