UnitySqlite数据库操作
在Unity中使⽤sqlite的教程有很多。 包括：
看完这两篇呢，基本上对sqlite数据库的使⽤都没有问题了，但是想在Unity中更⽅便的使⽤，我有对两者进⾏了改进和升级。
1.SqliteManager类
核⼼功能，使⽤C#函数，代替复杂的sql语句
using UnityEngine;
using System.Collections.Generic;
using Mono.Data.Sqlite;
public class SqlManager : MonoSingleton{
private const string dbName = "DBNAME";
//链接数据库
private SqliteConnection connection;
//数据库命令
private SqliteCommand command;
//数据库阅读器
private SqliteDataReader reader;
//打开数据库
public void OpenDB(string dbName)
{
try
{ //链接数据库操作
string dbPath = Application.streamingAssetsPath + "/" + dbName + ".db";
/
/固定sqlite格式data source
connection = new SqliteConnection(@"Data Source = " + dbPath);
connection.Open();
Debug.Log("DataBase Connect");
}
catch (System.Exception e)
{
Debug.LogError(e.ToString());
}
}
//关闭数据库
public void CloseDB()
{
/*
* IDispose接⼝可以通过Using(){}关键字实现使⽤后⽴刻销毁
* Close （）⽅法回滚任何挂起的事务。然后，它将连接释放到连接池，或者在连接池被禁⽤的情况下关闭连接，
* 应⽤程序可以多次调⽤ Close。不会⽣成任何异常。
* Dispose（）⽅法实际是和close（）做的同⼀件事，唯⼀的区别是Dispose⽅法清空了connectionString，即设置为了null.
*/
if (reader != null)
reader.Close();
if (command != null)
command.Dispose();
if (connection != null)
connection.Close();
Debug.Log("DataBase Close");
Debug.Log("DataBase Close");
}
//创建数据库表
public void CreateTabel(string _tableName,string[] col,string[] colType)
{
//string createSql = "CREATE TABLE player(ID int,name text,damage int)";
if (col.Length != colType.Length)
{
Debug.LogError("Colum's Length != ColumType's Length");
return;
}
string sql = "CREATE TABLE "+_tableName+"(";
for(int i=0;i<col.Length;i++)
{
sql += col[i] + " " + colType[i] + ",";
}
sql = sql.TrimEnd(',');
sql += ")";
ExcuteSql(sql);
}
//插⼊数据
public void Insert(string _tableName,object[] values)
{
//string createSql = "INSERT INTO player(ID,name,damage) VALUES (102,'ak47',120)"; if (values.Length == 0)
Debug.LogError("Values's length == 0");
string sql = "INSERT INTO "+_tableName + " VALUES(";
foreach(object value in values)
{
sql += "'" + value.ToString() + "'" +",";
}
sql = sql.TrimEnd(',');
sql += ")";
ExcuteSql(sql);
}
//删除数据 OR
public void DeleteOR(string _tabelName, params object[] colums)
{
//string createSql = "delete from player where rowid=1";
if (colums == null || colums.Length == 0)
Debug.LogError("colums == null || colums.Length == 0");
string sql = "DELETE FROM " + _tabelName + " WHERE ";
for (int i = 0; i < colums.Length; i += 2)
{
sql += colums[i] + " = '" + colums[i + 1].ToString() + "' OR ";
}
sql = sql.Remove(sql.Length - 3);
ExcuteSql(sql);
}
//删除数据 AND
public void DeleteAND(string _tabelName, params object[] colums)
{
if (colums == null || colums.Length == 0)
Debug.LogError("colums == null || colums.Length == 0");
string sql = "DELETE FROM " + _tabelName + " WHERE ";
for (int i=0;i<colums.Length;i+=2)
{
unity 教程sql += colums[i] + " = '" + colums[i+1].ToString() + "' AND ";
}
sql = sql.Remove(sql.Length - 4);
ExcuteSql(sql);
}
/
/更新数据 OR
public void UpdateOR(string _tableNmae,object[] colums, params object[] options) {
//string createSql = "Update player set name='M16' where id=102";
if (colums == null || colums.Length == 0 || colums.Length % 2 == 1)
Debug.LogError("colums Length has error!!!");
if (options == null || options.Length == 0 || options.Length % 2 == 1)
Debug.LogError("options Length has error!!!");
string sql = "UPDATE " + _tableNmae + " SET ";
for (int i = 0; i < colums.Length; i += 2)
{
sql += colums[i] + " = '" + colums[i + 1].ToString() + "' ";
}
sql += " WHERE ";
for (int i = 0; i < options.Length; i+=2)
{
sql += options[i] + "=" + options[i+1].ToString() + " OR ";
}
sql = sql.Remove(sql.Length - 3);
ExcuteSql(sql);
}
//更新数据 AND
public void UpdateAND(string _tableNmae, object[] colums, params object[] options) {
/
/string createSql = "Update player set name='M16' where id=102";
if (colums == null || colums.Length == 0 || colums.Length % 2 == 1)
Debug.LogError("colums Length has error!!!");
if (options == null || options.Length == 0 || options.Length % 2 == 1)
Debug.LogError("options Length has error!!!");
string sql = "UPDATE " + _tableNmae + " SET ";
for (int i = 0; i < colums.Length; i += 2)
{
sql += colums[i] + " = '" + colums[i + 1].ToString() + "' ";
}
sql += " WHERE ";
for (int i = 0; i < options.Length; i += 2)
{
sql += options[i] + "=" + options[i + 1].ToString() + " AND ";
}
sql = sql.Remove(sql.Length - 4);
ExcuteSql(sql);
}
//查询单个数据（ID是主键）
public Dictionary<string, object> SelectByID(string _tableName,int Id)
{
string sql = "SELECT * FROM " + _tableName +" WHERE Id ="+Id;
string sql = "SELECT * FROM " + _tableName +" WHERE Id ="+Id;
reader = ExcuteSql(sql);
Dictionary<string, object> dic = new Dictionary<string, object>();
//阅读电⼦书，翻页
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
dic.Add(reader.GetName(i), reader.GetValue(i));
}
reader.Close();
return dic;
}
//查所有
public List<Dictionary<string, object>> SelectAll(string _tableName)
{
string sql = "SELECT * FROM " + _tableName;
List<Dictionary<string, object>> dataArr = new List<Dictionary<string, object>>();
reader = ExcuteSql(sql);
while (reader.Read())
{
Dictionary<string, object> data = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
string key = reader.GetName(i);
object value = reader.GetValue(i);
data.Add(key, value);
}
dataArr.Add(data);
}
return dataArr;
}
//按照⾃定义条件查单条数据
public Dictionary<string,object> SelectWithCondition(string _tableName,params object[] options)
{
if (options == null || options.Length == 0 || options.Length % 2 == 1)
Debug.LogError("options Length has error!!!");
string sql = "SELECT * FROM " + _tableName + " WHERE ";
Dictionary<string, object> dic = new Dictionary<string, object>();
for(int i=0;i< options.Length;i+=2)
{
sql += options[i] + "= '" + options[i + 1]+"' AND ";
}
sql = sql.Remove(sql.Length-4);
reader = ExcuteSql(sql);
reader.Read();
for(int i=0;i<reader.FieldCount;i++)
{
string key = reader.GetName(i);
object value = reader.GetValue(i);
dic.Add(key, value);
}
return dic;
}
//按照⾃定义条件查整张表数据
public List<Dictionary<string, object>> SelectAllWithCondition(string _tableName, params object[] options) {
if (options == null || options.Length == 0 || options.Length % 2 == 1)
Debug.LogError("options Length has error!!!");
string sql = "SELECT * FROM " + _tableName + " WHERE ";
string sql = "SELECT * FROM " + _tableName + " WHERE ";
List<Dictionary<string, object>> dataArr = new List<Dictionary<string, object>>();
for (int i = 0; i < options.Length; i += 2)
{
sql += options[i] + "= '" + options[i + 1] + "' AND ";
}
sql = sql.Remove(sql.Length - 4);
reader = ExcuteSql(sql);
while (reader.Read())
{
Dictionary<string, object> data = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
string key = reader.GetName(i);
object value = reader.GetValue(i);
data.Add(key, value);
}
dataArr.Add(data);
}
return dataArr;
}
//执⾏数据库语句
SqliteDataReader ExcuteSql(string _sql)
{
Debug.Log("Excuted Sql :" + _sql);
//创建数据库连接命令（事务管理、命令管理：向数据库发送指令）
command = connection.CreateCommand();
//设置命令语句
command.CommandText = _sql;
//执⾏命令后返回阅读器信息
using (reader = command.ExecuteReader())
{
return reader;
}
}
}
2.实体类
数据库读出的数据，最终都是要转到实体类上的，⼀般两种⽅法：1.是⼿动转化，2.是通过反射⾃动赋值这⾥为了⽅便起见，暂时使⽤了⼿动转化的⽅式。
代码如下：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论