基本操作
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);
}
}