Unity数据库的学习笔记

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System.IO;
using Mono.Data.Sqlite;

public class DataBaseTool : Singleton<DataBaseTool> {

    public string DataBaseName="LOLData.sqlite";

    private SqliteConnection conn;
    private SqliteCommand command;
    private SqliteDataReader reader;
    private string datapath="";

    public DataBaseTool(){
        if(conn==null){
        datapath= "Data Source=" + Application.streamingAssetsPath + "/" + DataBaseName;
            #if UNITY_ANDRIOD
            datapath=AndriodPaltformSet();
            #endif
            try{
                conn = new SqliteConnection (datapath);
            }catch(System.Exception e){
                Debug.Log ("创建数据库连接失败...");
                Debug.LogError (e.Message);
            }
        }
    }

    //安卓平台数据库设置
    public string AndriodPaltformSet(){
        string path = "URI = file:" + Application.persistentDataPath + "/" + DataBaseName;

        //Android APK中数据库文件的路径
        string androidPath = "jar:file://" + Application.dataPath + "!/assets/" + DataBaseName;
        //Android沙盒路径
        string androidFilePath = Application.persistentDataPath + "/" + DataBaseName;
        //如果Android项目源文件中不存在数据库文件，说明没有加载过，需要加载
        if(!File.Exists(androidFilePath))
        {
            //从APK路径拿到Sqlite数据库文件，下载
            WWW www = new WWW(androidPath);
            //下载未完成时，保持等待
            while(!www.isDone){}
            //下载完成，IO流写入到沙盒路径
            File.WriteAllBytes(androidFilePath, www.bytes);
        }
        return path;
    }

    /// <summary>
    /// The .打开数据库连接
    /// </summary>
    private void OpenConnection(){

        if(conn!=null){
            try{
            conn.Open ();
            }catch(System.Exception e){
                Debug.Log ("数据库打开失败...");
                Debug.LogError (e.Message);
            }
        }
    }

    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    private void CloseConnection(){
        if(conn!=null){
            try {
                conn.Close();
            } catch (System.Exception ex) {
                Debug.Log ("数据库连接关闭失败...");
                Debug.LogError (ex.Message);
            }
        }
    }

    /// <summary>
    /// 获取一行数据的方法
    /// </summary>
    /// <returns>The row data.</returns>
    #region 对外接口方法
    public Dictionary<string ,object> GetRowData(string sql){
        OpenConnection ();
        command = conn.CreateCommand ();
        command.CommandText = sql;
        SqliteDataReader reader= command.ExecuteReader ();
        Dictionary <string,object> data = new Dictionary<string, object> ();
        if(reader.Read()){
            for (int i = 0; i < reader.FieldCount; i++) {
                data.Add( reader.GetName (i),reader.GetValue(i)) ;
            }
        }
        reader.Close ();
        reader = null;
        command.Dispose ();
        command = null;
        CloseConnection ();
        return data;
    }
    #endregion

    /// <summary>
    /// 获取一张表的方法
    /// </summary>
    /// <returns>The all row data.</returns>
    /// <param name="sql">Sql.</param>
    public List<Dictionary<string,object>> GetAllRowData(string sql){
        OpenConnection ();
        command = conn.CreateCommand ();
        command.CommandText = sql;
        SqliteDataReader reader = command.ExecuteReader ();

        List<Dictionary<string,object>> allData = new List<Dictionary<string, object>> ();
        while (reader.Read()) {
            Dictionary<string,object> rowData = new Dictionary<string, object> ();
            for (int i = 0; i < reader.FieldCount; i++) {
                rowData.Add (reader.GetName(i),reader.GetValue(i));
            }
            allData.Add (rowData);
        }
        reader.Close ();
        reader = null;
        command.Dispose ();
        command = null;
        CloseConnection ();
        return allData;
    }
}