unity3d，C#使用sqlite作為數據庫解決方案思路
來源：程序員人生 發布時間：2015-04-23 07:48:04 閱讀次數：4167次
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文件，1個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 db
access;
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
}
db
access = new DbAccess (@"Data Source=" + databaseFilePath);
}
}
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
