首先感谢下秦元培大大的文章，通过大大的文章学习了SQLite的一些基本使用方法。
本文主要是对在项目中使用SQLite的一个扩展，具体可以先参考下秦元培大大的文章。
猴~现在开始本文：
因为上一个项目是在Android平台的游戏，所以还进行了一个数据库文件的移动的操作，移动到可持久化目录（各种平台的目录可以参考该文章），然后针对一些二进制文件的读取也封装了一些操作。
先上代码：
using UnityEngine;
using System.Collections;
using Mono.Data.Sqlite;
using System;
using System.IO;
using System.Data;
using System.Text;
using System.Collections.Generic;
public class SQLiteHelper : Singleton<SQLiteHelper>
{
public enum SQLiteDataType
{
DT_NULL,
DT_INTEGER,
DT_REAL,
DT_TEXT,
DT_BLOB,
}
#region Static 私有字段
/// <summary>
/// 所有数据库文件名的数组
/// </summary>
private string[] m_DatabaseFiles = new string[] { DatabaseFileName.GameDb, DatabaseFileName.TestDb,DatabaseFileName.PlayerPrefsDB };
// <summary>
/// 数据库连接定义
/// </summary>
private IDbConnection m_Connection;
public IDbConnection Connection
{
get { return m_Connection; }
}
/// <summary>
/// SQL命令定义
/// </summary>
private IDbCommand m_Command;
/// <summary>
/// 数据读取定义
/// </summary>
private IDataReader m_Reader;
/// <summary>
/// 保留字段，暂时无用
/// </summary>
private bool m_ReleasedFromStreamingAssets = false;
/// <summary>
/// 存储上次出连接的数据库的文件名字符串
/// </summary>
private string m_DatabaseFileNameChache = string.Empty;
#endregion
/// <summary>
/// 空构造函数
/// </summary>
private SQLiteHelper() { }
/// <summary>
/// 异常信息格式化
/// </summary>
/// <param name="msg">抛出的异常字符串</param>
/// <returns>格式化之后的字符串</returns>
public static string MakeDatabaseExceptionMessage(string msg)
{
return string.Format("<color=magenta>SQLite Exception : {0}</color>", msg);
}
#region Static 数据库相关操作
///// <summary>
///// 加密数据库
///// </summary>
///// <param name="password"></param>
//public void EncryptDatabase(string password)
//{
// SqliteConnection con = m_Connection as SqliteConnection;
// con.ChangePassword(password);
//}
/// <summary>
/// 设置当前要使用的数据库
/// </summary>
/// <param name="databaseFileName"></param>
/// <returns></returns>
public bool SetCurrentDatabaseConnection(string databaseFileName)
{
m_Connection = GetOrCreateConnect(databaseFileName);
if (m_Connection != null)
return true;
else
return false;
}
Dictionary<string, IDbConnection> connectDic = new Dictionary<string, IDbConnection>(); // 存储数据连接类型的字典
private IDbConnection GetOrCreateConnect(string databaseFileName)
{
foreach (KeyValuePair<string, IDbConnection> dc in connectDic)
{
if (string.Compare(dc.Key, databaseFileName) == 0)
{
return dc.Value;
}
}
string connectionString = this.GetConnectionString(databaseFileName);
IDbConnection curConnection = null; // 创建连接对象
try
{
// 构造数据库链接
curConnection = (new SqliteConnection(connectionString)) as IDbConnection;
// 打开数据库
curConnection.Open();
connectDic.Add(databaseFileName, curConnection);
return curConnection;
}
catch (Exception e)
{
Debug.LogError("connection command " + connectionString);
Debug.LogError(SQLiteHelper.MakeDatabaseExceptionMessage(e.Message));
if (curConnection != null && curConnection.State != System.Data.ConnectionState.Closed)
{
curConnection.Close();
curConnection = null;
}
return null;
}
}
/// <summary>
/// 关闭某个数据库连接
/// </summary>
public void CloseConnection(string databaseFileName)
{
foreach(KeyValuePair<string, IDbConnection> kv in connectDic)
{
if (string.Compare(kv.Key, databaseFileName) == 0)
{
kv.Value.Close();
}
}
connectDic.Remove(databaseFileName);
// 销毁Command
if (m_Command != null)
{
m_Command.Cancel();
m_Command = null;
}
// 销毁Reader
if (m_Reader != null)
{
m_Reader.Close();
m_Reader = null;
}
// Connection置空
if (m_Connection != null)
{
m_Connection = null;
}
}
/// <summary>
/// 关闭所有数据库连接
/// </summary>
public void CloseConnection()
{
foreach (KeyValuePair<string, IDbConnection> kv in connectDic)
{
kv.Value.Close();
}
connectDic.Clear();
// 销毁Command
if (m_Command != null)
{
m_Command.Cancel();
m_Command = null;
}
// 销毁Reader
if (m_Reader != null)
{
m_Reader.Close();
m_Reader = null;
}
// Connection置空
if (m_Connection != null)
{
m_Connection = null;
}
}
/// <summary>
/// 清理SQLite的引用关系
/// </summary>
public void CleanSQLiteReference()
{
// 销毁Command
if (m_Command != null)
{
m_Command.Dispose();
m_Command = null;
}
// 销毁Reader
if (m_Reader != null)
{
m_Reader.Close();
m_Reader = null;
}
}
/// <summary>
/// 执行SQL命令
/// </summary>
/// <returns>The query.</returns>
/// <param name="queryString">SQL命令字符串</param>
public IDataReader ExecuteQuery(string queryString)
{
try
{
m_Command = m_Connection.CreateCommand();
if (m_Reader != null && !m_Reader.IsClosed)
m_Reader.Close();
m_Command.CommandText = queryString;
m_Reader = m_Command.ExecuteReader();
}
catch (Exception e)
{
Debug.LogError(SQLiteHelper.MakeDatabaseExceptionMessage(e.ToString()));
this.CleanSQLiteReference();
}
return m_Reader;
}
/// <summary>
/// 执行SQL命令
/// </summary>
/// <param name="queryString"></param>
/// <param name="para"></param>
/// <returns></returns>
public IDataReader ExecuteQuery(string queryString, IDataParameter para)
{
try
{
m_Command = m_Connection.CreateCommand();
if (m_Reader != null && !m_Reader.IsClosed)
m_Reader.Close();
m_Command.CommandText = queryString;
m_Command.Parameters.Add(para);
m_Reader = m_Command.ExecuteReader();
}
catch (Exception e)
{
Debug.LogError(SQLiteHelper.MakeDatabaseExceptionMessage(e.ToString()));
this.CleanSQLiteReference();
}
return m_Reader;
}
/// <summary>
/// 获取数据库链接字符窜
/// </summary>
/// <returns>链接字符窜</returns>
private string GetConnectionString(string databaseFileName)
{
string connectionString = string.Empty;
if (RuntimePlatform.WindowsEditor == Application.platform ||
RuntimePlatform.WindowsPlayer == Application.platform ||
RuntimePlatform.OSXEditor == Application.platform ||
RuntimePlatform.OSXPlayer == Application.platform)
{
//connectionString = string.Format("data source=:{0}/{1}", Application.streamingAssetsPath, databaseFileName);
connectionString = string.Format("URI=file:{0}/{1}", Application.streamingAssetsPath, databaseFileName);
}
else if (RuntimePlatform.Android == Application.platform) // 在android真机上运行
{
connectionString = string.Format("URI=file:{0}/{1}", Application.persistentDataPath, databaseFileName);
}
return connectionString;
}
/// <summary>
/// 创建数据表
/// </summary> +
/// <returns>The table.</returns>
/// <param name="tableName">数据表名</param>
/// <param name="colNames">字段名</param>
/// <param name="colTypes">字段名类型</param>
public IDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)
{
StringBuilder queryString = new StringBuilder();
queryString.AppendFormat("CREATE TABLE {0}( {1} {2}", tableName, colNames[0], colTypes[0]);
for (int i = 1; i < colNames.Length; i++)
{
queryString.AppendFormat(", {0} {1}", colNames[i], colTypes[i]);
}
queryString.Append(" )");
return ExecuteQuery(queryString.ToString());
}
/// <summary>
/// 读取整张数据表
/// </summary>
/// <returns>The full table.</returns>
/// <param name="tableName">数据表名称</param>
public IDataReader ReadFullTable(string tableName)
{
string queryString = "SELECT * FROM " + tableName;
return ExecuteQuery(queryString);
}
/// <summary>
/// 向指定数据表中插入数据
/// </summary>
/// <param name="tableName">数据表名称</param>
/// <param name="values">插入的数组</param>
/// <returns></returns>
public IDataReader InsertValues(string tableName, string[] values)
{
//获取数据表中字段数目
int fieldCount = ReadFullTable(tableName).FieldCount;
//当插入的数据长度不等于字段数目时引发异常
if (values.Length != fieldCount)
{
throw new SqliteException("values.Length != fieldCount");
}
StringBuilder queryString = new StringBuilder();
queryString.AppendFormat("INSERT INTO {0} VALUES ({1}", tableName, values[0]);
for (int i = 1; i < values.Length; i++)
{
queryString.AppendFormat(", {0}", values[i]);
}
queryString.Append(" )");
return ExecuteQuery(queryString.ToString());
}
/// <summary>
/// 插入二进制数据
/// </summary>
/// <param name="tableName"></param>
/// <param name="column"></param>
/// <param name="content"></param>
/// <returns></returns>
public IDataReader InsertBlob(string tableName, string column, Byte[] content)
{
string queryString = "INSERT INTO " + tableName + " VALUES(" + column + ", @data)";
IDataParameter dPara = new SqliteParameter("@data", DbType.Binary);
dPara.Value = content;
return ExecuteQuery(queryString, dPara);
}
/// <summary>
/// 修改某个Blob字段的值
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columnName">待查询的列名</param>
/// <param name="content">要设置的值</param>
/// <param name="key">判定条件用的字段</param>
/// <param name="operation">判定条件符号，如 = AND OR</param>
/// <param name="value">判定条件用的字段对应的值</param>
/// <returns></returns>
public IDataReader UpdateBlob(string tableName, string columnName, Byte[] content, string key, string operation, string value)
{
string queryString = string.Format("UPDATE {0} SET {1}=@data WHERE {2}{3}{4}", tableName, columnName, key, operation, value);
IDataParameter dPara = new SqliteParameter("@data", DbType.Binary);
dPara.Value = content;
return ExecuteQuery(queryString, dPara);
}
/// <summary>
/// 更新指定数据表内的数据
/// </summary>
/// <param name="tableName">数据表名称</param>
/// <param name="colNames">要更新的字段名</param>
/// <param name="colValues">所要改变的数值</param>
/// <param name="key">关键字</param>
/// <param name="operation">条件符号</param>
/// <param name="value">关键字要符合的数值</param>
/// <returns></returns>
public IDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string operation, string value)
{
//当字段名称和字段数值不对应时引发异常
if (colNames.Length != colValues.Length)
{
throw new SqliteException("colNames.Length != colValues.Length");
}
StringBuilder queryString = new StringBuilder();
queryString.AppendFormat("UPDATE {0} SET {1}={2}", tableName, colNames[0], colValues[0]);
for (int i = 1; i < colValues.Length; i++)
{
queryString.AppendFormat(" , {0}={1}", colNames[i], colValues[i]);
}
queryString.AppendFormat(" WHERE {0}{1}{2}", key, operation, value);
return ExecuteQuery(queryString.ToString());
}
/// <summary>
/// 删除指定数据表内的数据（条件为“且（and）”）
/// </summary>
/// <param name="tableName">数据表名称</param>
/// <param name="colNames">判定条件字段名</param>
/// <param name="operations">条件符号</param>
/// <param name="colValues">字段名对应的数据</param>
///
public IDataReader DeleteValuesOR(string tableName, string[] colNames, string[] operations, string[] colValues)
{
//当字段名称和字段数值不对应时引发异常
if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
{
throw new SqliteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
}
StringBuilder queryString = new StringBuilder();
queryString.AppendFormat("DELETE FROM {0} WHERE {1}{2}{3}", tableName, colNames[0], operations[0], colValues[0]);
for (int i = 1; i < colValues.Length; i++)
{
queryString.AppendFormat(" OR {0}{1}{2}", colNames[i], operations[i], colValues[i]);
}
return ExecuteQuery(queryString.ToString());
}
/// <summary>
/// 删除指定数据表内的数据（条件为“或（or）”）
/// </summary>
/// <param name="tableName">数据表名称</param>
/// <param name="colNames">判定条件字段名</param>
/// <param name="operations">条件符号</param>
/// <param name="colValues">字段名对应的数据</param>
/// <returns></returns>
public IDataReader DeleteValuesAND(string tableName, string[] colNames, string[] operations, string[] colValues)
{
//当字段名称和字段数值不对应时引发异常
if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
{
throw new SqliteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
}
StringBuilder queryString = new StringBuilder();
queryString.AppendFormat("DELETE FROM {0} WHERE {1}{2}{3}", tableName, colNames[0], operations[0], colValues[0]);
for (int i = 1; i < colValues.Length; i++)
{
queryString.AppendFormat(" AND {0}{1}{2}", colNames[i], operations[i], colValues[i]);
}
return ExecuteQuery(queryString.ToString());
}
/// <summary>
/// 读取表格中的数据(条件为OR相连)
/// </summary>
/// <returns>IDataReader</returns>
/// <param name="tableNames">所要读取的表</param>
/// <param name="items">所需字段的名称</param>
/// <param name="colNames">需要满足条件的字段</param>
/// <param name="operations">具体条件符号</param>
/// <param name="colValues">具体条件数值</param>
public IDataReader ReadTableOR(string[] tableNames, string[] items, string[] colNames, string[] operations, string[] colValues)
{
//当字段名称和字段数值不对应时引发异常
if (colNames.Length != operations.Length || colNames.Length != colValues.Length || operations.Length != colValues.Length)
{
throw new SqliteException("colNames.Length != operations.Length || colNames.Length != colValues.Length || operations.Length != colValues.Length");
}
StringBuilder queryString = new StringBuilder();
queryString.AppendFormat("SELECT {0}", items[0]);
for(int i = 1 ; i<items.Length ; i++)
{
queryString.AppendFormat(", {0}", items[i]);
}
queryString.AppendFormat(" FROM {0}", tableNames[0]);
for (int i = 1; i < tableNames.Length; i++)
{
queryString.AppendFormat(", {0}", tableNames[i]);
}
queryString.AppendFormat(" WHERE {0} {1} {2}", colNames[0], operations[0], colValues[0]);
for (int i = 1; i < colNames.Length; i++)
{
queryString.AppendFormat(" OR {0} {1} {2} ", colNames[i], operations[i], colValues[i]);
}
return ExecuteQuery(queryString.ToString());
}
/// <summary>
/// 读取表格中的数据(条件为AND相连)
/// </summary>
/// <returns>IDataReader</returns>
/// <param name="tableNames">所要读取的表</param>
/// <param name="items">所需字段的名称</param>
/// <param name="colNames">需要满足条件的字段</param>
/// <param name="operations">具体条件符号</param>
/// <param name="colValues">具体条件数值</param>
public IDataReader ReadTableAND(string[] tableNames, string[] items, string[] colNames, string[] operations, string[] colValues)
{
//当字段名称和字段数值不对应时引发异常
if (colNames.Length != operations.Length || colNames.Length != colValues.Length || operations.Length != colValues.Length)
{
throw new SqliteException("colNames.Length != operations.Length || colNames.Length != colValues.Length || operations.Length != colValues.Length");
}
StringBuilder queryString = new StringBuilder();
queryString.AppendFormat("SELECT {0}", items[0]);
for (int i = 1; i < items.Length; i++)
{
queryString.AppendFormat(", {0}", items[i]);
}
queryString.AppendFormat(" FROM {0}", tableNames[0]);
for (int i = 1; i < tableNames.Length; i++)
{
queryString.AppendFormat(", {0}", tableNames[i]);
}
queryString.AppendFormat(" WHERE {0} {1} {2}", colNames[0], operations[0], colValues[0]);
for (int i = 1; i < colNames.Length; i++)
{
queryString.AppendFormat(" AND {0} {1} {2} ", colNames[i], operations[i], colValues[i]);
}
return ExecuteQuery(queryString.ToString());
}
/// <summary>
/// 查询字段自否存在
/// </summary>
/// <param name="tableName">要查的表名</param>
/// <param name="columnName">要查的字段名</param>
/// <returns></returns>
public bool ExamineTableColumn(string tableName, string columnName)
{
string queryString = string.Format("SELECT {0} FROM {1}", columnName, tableName);
IDataReader reader = ExecuteQuery(queryString);
if (reader == null)
return false;
else
return true;
}
/// <summary>
/// 增加数据表字段
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columnName">字段名</param>
/// <param name="columnType">类型</param>
public void AddTableColumn(string tableName, string columnName, SQLiteDataType columnType)
{
string dy = string.Empty;
switch(columnType)
{
case SQLiteDataType.DT_NULL:
dy = "NULL";
break;
case SQLiteDataType.DT_INTEGER:
dy = "INTEGER";
break;
case SQLiteDataType.DT_REAL:
dy = "REAL";
break;
case SQLiteDataType.DT_TEXT:
dy = "TEXT";
break;
case SQLiteDataType.DT_BLOB:
dy = "BLOB";
break;
}
string queryString = string.Format("ALTER TABLE {0} ADD COLUMN {1} {2};", tableName, columnName, dy);
ExecuteQuery(queryString);
}
#endregion
#region Static 文件转移操作
/// <summary>
/// 从StreamAssets目录下导出sqlite database文件到Persistent目录，然后
/// android平台特有的机制，在lua开发阶段使用,
/// </summary>
public IEnumerator ReleaseDatabaseFromStreamingAssetsToPersistent()
{
if (RuntimePlatform.Android == Application.platform)
{
// 首先读取到StreamingAssets目录下llist.txt的目录和文件名，然后依次拷贝和生成
string dbFilePath = string.Empty;
string dbFileInPersistent = string.Empty;
// 遍历所要移动的文件
for (int i = 0; i < m_DatabaseFiles.Length; i++)
{
dbFilePath = string.Format("{0}/{1}", Application.streamingAssetsPath, m_DatabaseFiles[i]);
dbFileInPersistent = string.Format("{0}/{1}", Application.persistentDataPath, m_DatabaseFiles[i]);
// 如果player数据存盘文件已经在持久化目录中存在了，就不拷贝了
if (m_DatabaseFiles[i].Equals(DatabaseFileName.PlayerPrefsDB) && File.Exists(dbFileInPersistent))
continue;
Debug.LogFormat("Read database file {0} Begin", dbFilePath);
FileStream dbFS = null;
WWW dbListGetter = new WWW(dbFilePath);
yield return dbListGetter;
if (!string.IsNullOrEmpty(dbListGetter.error)) // 能取出文件的话才执行操作
{
Debug.LogError(SQLiteHelper.MakeDatabaseExceptionMessage(dbListGetter.error));
yield break;
}
while (dbListGetter.isDone == false) // 等待下载完成再继续
yield return null;
// 取出原始文件字节流，保存到 persistent 目录下
dbFS = null;
try
{
// 删旧建新
if (File.Exists(dbFileInPersistent))
File.Delete(dbFileInPersistent);
dbFS = File.Create(dbFileInPersistent);
}
catch (System.Exception ex)
{
Debug.LogErrorFormat("SQLite Exception : Load datebase file {0} from streaming assets directory error\n,Reason is:\n{1}",
dbFilePath, ex.ToString());
yield break; // 读取文件异常了就不处理，直接报错，游戏逻辑上不允许不成功！！！
}
Byte[] fileContent = dbListGetter.bytes;
dbFS.Write(fileContent, 0, fileContent.Length);
dbFS.Close();
Debug.LogFormat("SQLiteHelper Read database file {0} success", dbFilePath);
}
yield return null;
}
else if (RuntimePlatform.WindowsEditor == Application.platform || RuntimePlatform.WindowsPlayer == Application.platform)
{
string playerPrefsFilePath = string.Format("{0}/{1}", Application.streamingAssetsPath, DatabaseFileName.PlayerPrefsDB);
string playerPrefsFileInPersistent = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseFileName.PlayerPrefsDB);
// 如果player数据存盘文件已经在持久化目录中存在了，并且不删除那个旧的文件的话，就不拷贝了
bool fileExists = File.Exists(playerPrefsFileInPersistent);
if ( fileExists && Global.Instance().m_DeletePlayerPrefs == false)
yield break;
if (fileExists)
File.Delete(playerPrefsFileInPersistent);
File.Copy(playerPrefsFilePath, playerPrefsFileInPersistent);
yield return null;
}
else
{
m_ReleasedFromStreamingAssets = true;
yield return null;
}
}
#endregion
}其实代码已经很明了了（DatabaseFileName类是保存数据库名的字符串，就不上传代码了）：
1、SetCurrentDatabaseConnection：该方法是便于在游戏时进行相关的数据库链接变更，因为在整个游戏过程中，是有可能需要多个数据库的，此处就是在使用相关数据库时改变下连接；
2、ReleaseDatabaseFromStreamingAssetsToPersistent：批量地进行数据库文件的移动到响应的平台的可持久化目录，方便读写；
3、实现了包括Blob（二进制文件）在内的所有增删查改操作。
4、关于Singleton，请看此处
下面再给出一个测试类，方便有需要的人进行理解：
using UnityEngine;
using System.Collections;
using Mono.Data.Sqlite;
using System.Data;
using System.IO;
using System;
public class DatabaseTest : MonoBehaviour
{
// Use this for initialization
IEnumerator Start()
{
yield return null;
yield return StartCoroutine(SQLiteHelper.Instance.ReleaseDatabaseFromStreamingAssetsToPersistent());
//TestSqlCommand();
}
// Update is called once per frame
void Update()
{
//if (Input.GetKeyDown(KeyCode.R)) this.ReadTextAndWrite();
//if (Input.GetKeyDown(KeyCode.S)) this.ReadTextAndStoreSqlite();
//if (Input.GetKeyDown(KeyCode.T)) this.ShowData();
//if (Input.GetKeyDown(KeyCode.Q)) this.ReadPictureAndStore();
//if (Input.GetKeyDown(KeyCode.W)) this.ReadBinaryToPic();
//if (Input.GetKeyDown(KeyCode.C)) this.CreateDatabase();
//if (Input.GetKeyDown(KeyCode.C)) this.TestSqlCommand();
//if (Input.GetKeyDown(KeyCode.O)) this.ConnectDatabase();
//if (Input.GetKeyDown(KeyCode.J)) this.ContainString();
}
private Rect showDataRect = new Rect(0.0f, 0.0f, 100.0f, 100.0f);
private Rect labelRect = new Rect(0.0f, 110.0f, 250.0f, 100.0f);
private Rect btnPantsRect = new Rect(0.0f, 220.0f, 100.0f, 100.0f);
private Rect btnShoesRect = new Rect(0.0f, 330.0f, 100.0f, 100.0f);
private Rect btnTopRect = new Rect(0.0f, 440.0f, 100.0f, 100.0f);
private Rect btnFaceRect = new Rect(0.0f, 550.0f, 100.0f, 100.0f);
private string m_ShowData = "nothing";
void OnGUI()
{
if (GUI.Button(showDataRect, "ShowText"))
{
m_ShowData = this.ShowData();
}
GUI.Label(labelRect, m_ShowData);
}
private void ContainString()
{
if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.TestDb)) return;
if (SQLiteHelper.Instance.ExamineTableColumn("table1", "Nams"))
Debug.Log("Yes!");
else
Debug.Log("No!");
}
private void ConnectDatabase()
{
if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.TestDb)) return;
if (SQLiteHelper.Instance.ExamineTableColumn("table1", "Names"))
Debug.Log("true");
else
{
Debug.Log("false");
SQLiteHelper.Instance.AddTableColumn("table1", "Names", SQLiteHelper.SQLiteDataType.DT_TEXT);
}
//SQLiteHelper.Instance.InsertValues("table1", new string[] { "111111", "'哈哈1'", "25", "'Wang5@163.com'", "sda" });
//SQLiteHelper.Instance.UpdateValues("table1", new string[] { "Name" }, new string[] { "'哈哈1'" }, "Name", "=", "'张三'");
//if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.Test2Db)) return;
//SQLiteHelper.Instance.InsertValues("table2", new string[] { "222222", "'哈哈2'", "25", "'Wang5@163.com'" });
//if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.TestDb)) return;
//SQLiteHelper.Instance.InsertValues("table1", new string[] { "111111", "'哈哈12'", "25", "'Wang5@163.com'" });
//SQLiteHelper.Instance.CloseConnection(DatabaseFileName.TestDb);
//if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.Test2Db)) return;
//SQLiteHelper.Instance.InsertValues("table2", new string[] { "222222", "'哈哈2'", "25", "'Wang5@163.com'" });
#region Static 创建多个数据库连接
//string connectionString1 = string.Format("URI=file:{0}/{1}", Application.streamingAssetsPath, DatabaseFileName.TestDb);
//IDbConnection con1 = (new SqliteConnection(connectionString1)) as IDbConnection;
//con1.Open();
//string connectionString2 = string.Format("URI=file:{0}/{1}", Application.streamingAssetsPath, DatabaseFileName.Test2Db);
//IDbConnection con2 = (new SqliteConnection(connectionString2)) as IDbConnection;
//con2.Open();
//string queryString1 = "INSERT INTO table1 VALUES('111', '哈哈1', '25', 'Wang5@163.com')";
//string queryString2 = "INSERT INTO table2 VALUES('222', '哈哈2', '25', 'Wang5@163.com')";
//IDbCommand cmd1 = con1.CreateCommand();
//IDbCommand cmd2 = con2.CreateCommand();
//cmd1.CommandText = queryString1;
//cmd2.CommandText = queryString2;
//cmd1.ExecuteNonQuery();
//cmd2.ExecuteNonQuery();
#endregion
Debug.Log("Successful!");
}
/// <summary>
/// 加密数据库
/// </summary>
private void CreateDatabase()
{
Debug.Log("EncryptDatabase");
string connectionString = string.Format("URI=file:{0}/{1}", Application.streamingAssetsPath, DatabaseFileName.Test2Db);
string fileString = string.Format("{0}/{1}", Application.streamingAssetsPath, DatabaseFileName.Test2Db);
SqliteConnection.CreateFile(fileString);
SqliteConnection cnn = new SqliteConnection(connectionString);
cnn.Open();
cnn.ChangePassword("123456");
SQLiteHelper.Instance.CloseConnection();
}
/// <summary>
/// 读取数据库中的二进制流并存储到本地
/// </summary>
private void ReadBinaryToPic()
{
Debug.Log("ReadBinaryToPic");
if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.GameDb)) return;
IDataReader reader = SQLiteHelper.Instance.ReadTableOR(
new string[] { "ByteTable" },
new string[] { "File", "Byte" },
new string[] { "File" },
new string[] { "=" },
new string[] { "'qinnv'" });
//Byte[] picBytes ;
while (reader.Read())
{
//reader.GetBytes(0, 0, picBytes , 0, int.MaxValue);
//MemoryStream stream = new MemoryStream(reader["Byte"] as byte[]);
//stream.Read()
Byte[] binaryByte = reader["Byte"] as byte[];
//Debug.Log(stream.ReadByte());
Debug.Log(binaryByte[0]);
}
SQLiteHelper.Instance.CloseConnection();
}
/// <summary>
/// 读取数据库中的数据
/// </summary>
private string ShowData()
{
if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.GameDb)) return "can't open database!";
//IDataReader reader = SQLiteHelper.Instance.ReadTable(
// new string[] { "TextFile" },
// new string[] { "FileName", "Content" },
// new string[] { "FileName" },
// new string[] { "=" },
// new string[] { "'TestText'" });
IDataReader reader = SQLiteHelper.Instance.ReadTableOR(
new string[] { "characters_parts" },
new string[] { "id", "name" },
new string[] { "id", "id" },
new string[] { "=", "=" },
new string[] { "10010001", "10010002" });
string st = string.Empty;
while(reader.Read())
{
//Debug.Log(reader.GetString(reader.GetOrdinal("FileName")));
//Debug.Log(reader.GetString(reader.GetOrdinal("Content")));
//return string.Format("{0},{1},{2},{3}", reader.GetInt32(reader.GetOrdinal("id")), reader.GetString(reader.GetOrdinal("name")), reader.GetInt32(reader.GetOrdinal("id")), reader.GetString(reader.GetOrdinal("name")));
st += reader[0];
st += reader[1];
//st += reader[0].GetType().ToString();
//st += reader[1].GetType().ToString();
}
return st;
}
/// <summary>
/// 存储文本文件数据到数据库中
/// </summary>
/// <param name="tableName">数据表的名字</param>
/// <param name="fileName">要存储的文件的名字</param>
/// <param name="content">要存储的数据流</param>
private void StoreTextData(string tableName, string fileName, string content)
{
if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.TestDb)) return;
SQLiteHelper.Instance.InsertValues(tableName, new string[] { fileName, content });
SQLiteHelper.Instance.CloseConnection();
}
/// <summary>
/// 存储二进制数据到数据库中
/// </summary>
/// <param name="tableName">数据表的名字</param>
/// <param name="fileName">要存储的文件的名字</param>
/// <param name="content">要存储的数据流</param>
private void StoreByteData(string tableName, string fileName, Byte[] content)
{
if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.TestDb)) return;
//string sqlCmd = "INSERT INTO " + tableName + " VALUES('qinnv', @data)";
//SqliteParameter para = new Mono.Data.Sqlite.SqliteParameter("@data", DbType.Binary);
//IDbCommand cmd = SQLiteHelper.Instance.Connection.CreateCommand();
//cmd.CommandText = sqlCmd;
//para.Value = content;
//cmd.Parameters.Add(para);
//cmd.ExecuteNonQuery();
//IDataReader reader = SQLiteHelper.Instance.InsertBlob(tableName, "'test2'", content);
SQLiteHelper.Instance.UpdateBlob(tableName, "Byte", content, "File", "=", "'test'");
SQLiteHelper.Instance.CloseConnection();
}
/// <summary>
/// 开启读取二进制文件的协程并在读取完毕后存储到响应的数据库表中
/// </summary>
/// <param name="url">本地文件的读取连接</param>
/// <param name="tableName">所要存储到数据库中的表的名字</param>
/// <param name="fileName">存储到该表中的FileName字段的名字</param>
/// <returns></returns>
private IEnumerator LoadBinaryDataAndStoreSqlite(string url, string tableName, string fileName)
{
WWW w = new WWW(url);
yield return w;
// 能取出文件的话才执行操作
if (!string.IsNullOrEmpty(w.error))
{
Debug.LogError(w.error);
yield break;
}
if (!w.isDone)
yield return null;
//string storeText = System.Text.Encoding.UTF8.GetString(w.bytes);
//storeText = string.Format("'{0}'", storeText);
StoreByteData(tableName, fileName, w.bytes);
}
//===============================================================================================================
#region Static 读取本地图片并且存储到数据库中
/// <summary>
/// 读取本地图片并且存储到数据库中
/// </summary>
private void ReadPictureAndStore()
{
Debug.Log("ReadPictureAndStore");
string urlPath = string.Empty;
#if UNITY_EDITOR
urlPath = "file://" + Application.streamingAssetsPath + "/qinnv.jpg";
#elif UNITY_IPHONE
urlPath = Application.dataPath +"/Raw"+"/qinnv.jpg";
#elif UNITY_ANDROID
urlPath = "jar:file://" + Application.dataPath + "!/assets/" + "/qinnv.jpg";
#endif
Global.BeginCoroutine(LoadBinaryDataAndStoreSqlite(urlPath, "ByteTable", "qinnvvvv"));
}
#endregion
#region 读取文本文件并且将其存储到数据库文件中
/// <summary>
/// 读取文本文件内容并且存储到数据库
/// </summary>
private void ReadTextAndStoreSqlite()
{
Debug.Log("ReadTextAndStoreSqlite");
string urlPath = string.Empty;
#if UNITY_EDITOR
urlPath = "file://" + Application.streamingAssetsPath + "/TestText.txt";
#elif UNITY_IPHONE
urlPath = Application.dataPath +"/Raw"+"/TestText.txt";
#elif UNITY_ANDROID
urlPath = "jar:file://" + Application.dataPath + "!/assets/" + "/TestText.txt";
#endif
Global.BeginCoroutine(LoadTextAndStoreSqlite(urlPath));
}
private IEnumerator LoadTextAndStoreSqlite(string url)
{
WWW w = new WWW(url);
yield return w;
// 能取出文件的话才执行操作
if (!string.IsNullOrEmpty(w.error))
{
Debug.LogError(w.error);
yield break;
}
if (!w.isDone)
yield return null;
string storeText = System.Text.Encoding.UTF8.GetString(w.bytes);
storeText = string.Format("'{0}'", storeText);
StoreTextData("TextFile", "'TestText'", storeText);
}
#endregion
#region Static 读写文本到本地
/// <summary>
/// 读取文本文件并且取出内容存到一个新的文本文件中
/// </summary>
private void ReadTextAndWrite()
{
Debug.Log("ReadText");
string urlpath = string.Empty;
#if UNITY_EDITOR
urlpath = "file://" + Application.streamingAssetsPath + "/TestText.txt";
#elif UNITY_IPHONE
urlpath = Application.dataPath +"/Raw"+"/TestText.txt";
#elif UNITY_ANDROID
urlpath = "jar:file://" + Application.dataPath + "!/assets/" + "/TestText.txt";
#endif
Global.BeginCoroutine(LoadTextAndWrite(urlpath));
}
private IEnumerator LoadTextAndWrite(string url)
{
WWW w = new WWW(url);
yield return w;
// 能取出文件的话才执行操作
if (!string.IsNullOrEmpty(w.error))
{
Debug.LogError(w.error);
yield break;
}
if (!w.isDone)
yield return null;
Byte[] b = w.bytes;
WriteText(b, "This is Load By TestText.txt:\r\n");
}
private void WriteText(byte[] bStream, string testBegin = "This Text Begin.. end.\r\n这是第二行a ！、、、、end。")
{
Debug.Log("WriteText");
string filepath = string.Empty;
#if UNITY_EDITOR
filepath = Application.streamingAssetsPath + "/WriteText.txt";
#elif UNITY_IPHONE
filepath = Application.dataPath +"/Raw"+"/WriteText.txt";
#elif UNITY_ANDROID
filepath = "jar:file://" + Application.dataPath + "!/assets/" + "/WriteText.txt";
#endif
FileStream sw;
FileInfo fl = new FileInfo(filepath); // 注意此处打开的是文件路径名而不是file链接
if (fl.Exists) fl.Delete();
sw = fl.Create();
sw.Write(bStream, 0, bStream.Length);
sw.Close();
sw.Dispose();
}
#endregion
#region Static 数据库相关操作示例代码
/// <summary>
/// SQLiteHelper类的使用示例
/// </summary>
private void TestSqlCommand()
{
Debug.Log("TestSqlCommand");
// 创建连接
if (!SQLiteHelper.Instance.SetCurrentDatabaseConnection(DatabaseFileName.TestDb)) return;
//================================= 内置操作 示例 begin =================================================================================================
//SQLiteHelper.Instance.CreateTable("table2", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" });
//SQLiteHelper.Instance.InsertValues("table2", new string[] { "'1'", "'张三'", "'22'", "'Zhang3@163.com'" });
//SQLiteHelper.Instance.InsertValues("table2", new string[] { "'2'", "'李四'", "'25'", "'Li4@163.com'" });
//SQLiteHelper.Instance.UpdateValues("table2", new string[] { "Name" }, new string[] { "'Zhang3'" }, "Name", "=", "'张三'");
//SQLiteHelper.Instance.InsertValues("table2", new string[] { "3", "'王五'", "25", "'Wang5@163.com'" });
//SQLiteHelper.Instance.InsertValues("table2", new string[] { "4", "'王五'", "26", "'Wang5@163.com'" });
//SQLiteHelper.Instance.InsertValues("table2", new string[] { "5", "'王五'", "27", "'Wang5@163.com'" });
//SQLiteHelper.Instance.DeleteValuesAND("table2", new string[] { "Name", "Age" }, new string[] { "=", ">" }, new string[] { "'王五'", "25" });
//SQLiteHelper.Instance.ExecuteQuery("DELETE FRO table1 WHERE NAME = '王五'");
//IDataReader reader = SQLiteHelper.Instance.ReadFullTable("table1");
//while (reader.Read())
//{
// //读取ID
// Debug.Log(reader.GetInt32(reader.GetOrdinal("ID")));
// //读取Name
// Debug.Log(reader.GetString(reader.GetOrdinal("Name")));
// //读取Age
// Debug.Log(reader.GetInt32(reader.GetOrdinal("Age")));
// //读取Email
// Debug.Log(reader.GetString(reader.GetOrdinal("Email")));
//}
//IDataReader reader = SQLiteHelper.Instance.ReadTable(new string[] { "table1" }, new string[] { "ID", "Name" }, new string[] { "Age" }, new string[] { ">=" }, new string[] { "'25'" });
//while (reader.Read())
//{
// //读取ID
// Debug.Log(reader.GetInt32(reader.GetOrdinal("ID")));
// //读取Name
// Debug.Log(reader.GetString(reader.GetOrdinal("Name")));
//}
// =============================== 内置操作 end =====================================================================================================
// 关闭连接
//SQLiteHelper.Instance.CloseConnection();
}
#endregion
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
