unity3d，C#使用sqlite作為資料庫解決方案思路

標籤：unity3d   資料庫   sqlite   android平台   ios   

1，編輯器建立好資料庫結構，產生sqlite資料庫檔案，可以用navicat圖形介面編輯器來操作。
2，建立好的資料庫，尾碼名變為.txt格式（方便unity3d載入），放檔案放到Assest/Resources目錄下（建立目錄）。
放在Resources目錄下的檔案，在Pc/ios/android端均可以不作區分的用Resource來載入，假設資料庫檔案名位：data.txt,語句如下：
TextAsset txt = Resources.Load ("data", typeof(TextAsset))as TextAsset;
3, 將讀取到的TextAsset檔案寫入對應平台的沙箱路徑下, 代碼為：
   databaseFilePath = Application.persistentDataPath+"//"+data.db;（沙箱，各平台路徑不同，均為可讀寫）
      File.WriteAllBytes(databaseFilePath,txt.bytes);
4，載入沙箱路徑下的資料庫檔案進行讀寫操作。DbAccess封裝了資料庫操作。其中需要兩個dll檔案，一個so檔案（android平台需要libsq

<p><span style="font-family: Arial, Helvetica, sans-serif;">using UnityEngine;</span></p>

using System.Collections;using Mono.Data.Sqlite;using System;using System.IO;public class DbAccess{private SqliteConnection dbConnection;private SqliteCommand dbCommand;private SqliteDataReader reader;public DbAccess (string connectionString){OpenDB (connectionString);}public DbAccess (){}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);}}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.");}public SqliteDataReader ExecuteQuery (string sqlQuery){dbCommand = dbConnection.CreateCommand ();dbCommand.CommandText = sqlQuery;reader = dbCommand.ExecuteReader ();return reader;}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];}Debug.Log(query);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];}if (col.Length == 0) {query += " FROM " + tableName;} else {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);}}public class DataCenter{private string databaseFilename = "data.db";private string databaseFilePath;private DbAccess dbaccess;public DataCenter(){databaseFilePath = Application.persistentDataPath+"//"+databaseFilename;if (!File.Exists (databaseFilePath)) {TextAsset txt = Resources.Load ("data", typeof(TextAsset))as TextAsset;File.WriteAllBytes(databaseFilePath,txt.bytes);//copy data file to sandbox}dbaccess = new DbAccess (@"Data Source=" + databaseFilePath);}}

unity3d，C#使用sqlite作為資料庫解決方案思路