Unity學習筆記：sqlite資料庫

基本操作

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

public class TestDB : MonoBehaviour {

	/// <summary>	/// 資料庫	/// </summary>
	private DbAccess db;
	/// <summary>	/// 資料庫路徑	/// </summary>
	private string appDBPath;

	private string name;
	private int age;
	private float exp;

	/// <summary>	/// 創建/打開資料庫	/// </summary>
	private void CreateDataBase()
	{
		#if UNITY_EDITOR || UNITY_STANDALONE_WIN
		appDBPath = Application.streamingAssetsPath + "/Test.db";
		#elif UNITY_ANDROID || UNITY_IPHONE
		appDBPath = Application.persistentDataPath + "/Test.db";
		if(!File.Exists(appDBPath)){
			StartCoroutine(CopyDataBase());
		}
		#endif
		db = new DbAccess ("URI=file:" + appDBPath);
	}

	/// <summary>	/// 拷貝資料庫	/// </summary>
	/// <returns>資料庫.</returns>
	private IEnumerator CopyDataBase()
	{
		WWW loadDB = new WWW (Application.streamingAssetsPath + "/Test.db");
		yield return loadDB;
		File.WriteAllBytes (appDBPath,loadDB.bytes);
	}

	/// <summary>	/// 創建表	/// </summary>
	private void CreateTable()
	{
		CreateDataBase ();
		db.CreateTable ("Role",new string[] {"id","name","age","lv","exp"},
			new string[] {"int","text","int","int","float"});
		db.CloseSqlConnection ();
	}

	/// <summary>	/// 插入資料	/// </summary>
	private void InsertData()
	{
		CreateDataBase ();
		db.InsertInto ("Role",new string[] {"1","'張三'","18","10","100"});
		db.InsertInto ("Role",new string[] {"2","'李四'","20","2","2.2"});
		db.InsertInto ("Role",new string[] {"3","'老王'","19","3","3.3"});
		db.CloseSqlConnection ();
	}

	/// <summary>	/// 更新資料	/// </summary>
	private void UpdateData()
	{
		CreateDataBase ();
		db.UpdateInto ("Role",new string[] {"name","lv","exp"},
			new string[] {"'test'","1","1.1"},"id","1");
		db.CloseSqlConnection ();
	}

	/// <summary>	/// 洗掉資料	/// </summary>
	private void DeleteData()
	{
		CreateDataBase ();
		db.Delete ("Role",new string[] {"id","id"},new string[] {"1","3"});
		db.CloseSqlConnection ();
	}

	/// <summary>	/// 洗掉表中全部資料	/// </summary>
	private void DeleteAllData()
	{
		CreateDataBase ();
		db.DeleteContents ("Role");
		db.CloseSqlConnection ();
	}

	/// <summary>	/// 查詢資料	/// </summary>
	private void FindData()
	{
		CreateDataBase ();
		SqliteDataReader sqReader = db.SelectWhere ("Role",new string[] {
			"name","age","exp"},new string[] {"id"},new string[] {"="},
			new string[] {"1"});
		sqReader.Read ();
		name = sqReader.GetString (sqReader.GetOrdinal("name"));
		age = sqReader.GetInt32 (sqReader.GetOrdinal("age"));
		exp = sqReader.GetFloat (sqReader.GetOrdinal("exp"));
		db.CloseSqlConnection ();
	}

	private void SelectData()
	{
		CreateDataBase ();
//		SqliteDataReader sqReader = db.ReadFullTable ("Role");
//		SqliteDataReader sqReader = db.SelectOrderASC ("Role","age");
		SqliteDataReader sqReader = db.SelectOrderDESC ("Role","age");
		while(sqReader.Read())
		{
			Debug.Log (sqReader.GetInt32(sqReader.GetOrdinal("id")) + " " +
				sqReader.GetString(sqReader.GetOrdinal("name")) + " " + 
				sqReader.GetInt32(sqReader.GetOrdinal("age")) + " " + 
				sqReader.GetInt32(sqReader.GetOrdinal("lv")) + " " + 
				sqReader.GetFloat(sqReader.GetOrdinal("exp")));
		}
		db.CloseSqlConnection ();
	}

	private void OnGUI()
	{
		if (GUILayout.Button ("創建表"))
			CreateTable ();
		if (GUILayout.Button ("插入資料"))
			InsertData ();
		if (GUILayout.Button ("更新資料"))
			UpdateData ();
		if (GUILayout.Button ("洗掉資料"))
			DeleteData ();
		if (GUILayout.Button ("洗掉表中全部資料"))
			DeleteAllData ();
		if (GUILayout.Button ("讀取資料"))
			FindData ();
		if (GUILayout.Button ("多種讀取資料"))
			SelectData ();
		#if HTC
		if (GUILayout.Button ("充值"))
			FindData ();
		#endif
		#if OC
		if (GUILayout.Button ("充值"))
			SelectData ();
		#endif
		GUILayout.Label ("name:" + name);
		GUILayout.Label ("age:" + age);
		GUILayout.Label ("exp:" + exp);
	
	}
}

工具類DbAccess

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


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;
		
	}
	
	/// <summary>
	/// 查詢表中全部資料 param tableName=表名 
	/// </summary>
	public SqliteDataReader ReadFullTable (string tableName)
		
	{
		
		string query = "SELECT * FROM " + tableName;
		
		return ExecuteQuery (query);
		
	}
	
	
	/// <summary>
	/// 插入資料 param tableName=表名 values=插入資料內容
	/// </summary>
	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);
		
	}
	
	/// <summary>
	/// 更新資料 param tableName=表名 cols=更新欄位 colsvalues=更新內容 selectkey=查找欄位（主鍵) selectvalue=查找內容
	/// </summary>
	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);
	}
	
	/// <summary>
	/// 洗掉資料 param tableName=表名 cols=欄位 colsvalues=內容
	/// </summary>
	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);
	}
	
	/// <summary>
	/// 插入資料 param tableName=表名 cols=插入欄位 value=插入內容
	/// </summary>
	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);
		
	}
	
	
	/// <summary>
	/// 洗掉表中全部資料
	/// </summary>
	public SqliteDataReader DeleteContents (string tableName)
		
	{
		
		string query = "DELETE FROM " + tableName;
		
		return ExecuteQuery (query);
		
	}
	
	/// <summary>
	/// 創建表 param name=表名 col=欄位名 colType=欄位型別
	/// </summary>
	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);
		
	}
	
	
	
	
	
	/// <summary>
	/// 按條件查詢資料 param tableName=表名 items=查詢欄位 col=查找欄位 operation=運算子 values=內容
	/// </summary>
	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);
	}

	/// <summary>
	/// 查詢表
	/// </summary>
	public SqliteDataReader Select(string tableName, string col, string values)
	{
		string query = "SELECT * FROM " + tableName  + " WHERE " + col + " = " + values;
		return ExecuteQuery (query);
	}

	public SqliteDataReader Select(string tableName, string col,string operation, string values)
	{
		string query = "SELECT * FROM " + tableName  + " WHERE " + col + operation + values;
		return ExecuteQuery (query);
	}

	/// <summary>
	/// 升序查詢
	/// </summary>
	public SqliteDataReader SelectOrderASC (string tableName,string col)
	{
		string query = "SELECT * FROM " + tableName  + " ORDER BY " + col + " ASC";
		return ExecuteQuery (query);
	}

	/// <summary>
	/// 降序查詢
	/// </summary>
	public SqliteDataReader SelectOrderDESC (string tableName,string col)
	{
		string query = "SELECT * FROM " + tableName  + " ORDER BY " + col + " DESC";
		return ExecuteQuery (query);
	}

	/// <summary>
	/// 查詢表行數
	/// </summary>
	public SqliteDataReader SelectCount(string tableName)
	{
		string query = "SELECT COUNT(*) FROM " + tableName;
		return ExecuteQuery (query);
	}
}