文章介绍了如何在Unity中使用SQLite进行数据库的读写操作，包括打开、关闭数据库连接，执行SQL查询、插入、更新和删除数据。同时，展示了将读取到的数据展示在Unity的TMPro文本组件上，实现了游戏内数据交互的功能。
首先是先布局好sqllite的环境，网上很多教程。
然后现有代码SqliteDbHelper（网上也很多）如下：
using UnityEngine;
using System.Collections;
//导入sqlite数据集，也就是Plugins文件夹下的那个dll文件
using Mono.Data.Sqlite;
using System;
//数据集 是formwork2.0 用vs开发要自己引用框架中的System.Data
using System.Data;
using System.IO;//引用此命名空间是用于数据的写入与读取
using System.Text; //引用这个命名空间是用于接下来用可变的字符串的
public class SqliteDbHelper
{
/// <summary>
/// 声明一个连接对象
/// </summary>
private SqliteConnection dbConnection;
/// <summary>
/// 声明一个操作数据库命令
/// </summary>
private SqliteCommand dbCommand;
/// <summary>
/// 声明一个读取结果集的一个或多个结果流
/// </summary>
private SqliteDataReader reader;
public object Tables { get; internal set; }
/// <summary>
/// 数据库的连接字符串，用于建立与特定数据源的连接
/// </summary>
/// <param name="connectionString">数据库的连接字符串，用于建立与特定数据源的连接</param>
public SqliteDbHelper (string connectionString)
{
//OpenDB (connectionString);
//Debug.Log(connectionString);
}
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);
var sw = new StreamWriter("D:\\errolog.txt");
sw.WriteLine(connectionString);
sw.WriteLine(temp1);
sw.Close();
}
}
/// <summary>
/// 关闭连接
/// </summary>
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.");
}
//
/// <summary>
/// 执行查询sqlite语句操作
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public SqliteDataReader ExecuteQuery (string sqlQuery)
{
//try
// {
dbCommand = dbConnection.CreateCommand ();
dbCommand.CommandText = sqlQuery;
reader = dbCommand.ExecuteReader ();
/*}
catch(Exception e)
{
string temp1 = e.ToString();
}*/
return reader;
}
/// <summary>
/// 查询该表所有数据
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public SqliteDataReader ReadFullTable (string tableName)
{
string query = "SELECT * FROM " + tableName;
return ExecuteQuery (query);
}
/// <summary>
/// 动态添加表字段到指定表
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="values">字段集合</param>
/// <returns></returns>
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;
}
query += ")";
return ExecuteQuery (query);
}
/// <summary>
/// 动态更新表结构
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="cols">字段集</param>
/// <param name="colsvalues">对于集合值</param>
/// <param name="selectkey">要查询的字段</param>
/// <param name="selectvalue">要查询的字段值</param>
/// <returns></returns>
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+" ="+ colsvalues;
}
query += " WHERE "+selectkey+" = "+selectvalue+" ";
return ExecuteQuery (query);
}
/// <summary>
/// 动态删除指定表字段数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="cols">字段</param>
/// <param name="colsvalues">字段值</param>
/// <returns></returns>
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+" = "+ colsvalues;
}
Debug.Log(query);
return ExecuteQuery (query);
}
/// <summary>
/// 动态添加数据到指定表
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="cols">字段</param>
/// <param name="values">值</param>
/// <returns></returns>
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;
}
query += ") VALUES (" + values[0];
for (int i = 1; i < values.Length; ++i)
{
query += ", " + values;
}
query += ")";
return ExecuteQuery (query);
}
/// <summary>
/// 动态删除表
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public SqliteDataReader DeleteContents (string tableName)
{
string query = "DELETE FROM " + tableName;
return ExecuteQuery (query);
}
/// <summary>
/// 动态创建表
/// </summary>
/// <param name="name">表名</param>
/// <param name="col">字段</param>
/// <param name="colType">类型</param>
/// <returns></returns>
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 + " " + colType;
}
query += ")";
Debug.Log(query);
return ExecuteQuery (query);
}
/// <summary>
/// 根据查询条件 动态查询数据信息
/// </summary>
/// <param name="tableName">表</param>
/// <param name="items">查询数据集合</param>
/// <param name="col">字段</param>
/// <param name="operation">操作</param>
/// <param name="values">值</param>
/// <returns></returns>
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;
}
query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
for (int i = 1; i < col.Length; ++i)
{
query += " AND " + col + operation + "'" + values[0] + "' ";
}
return ExecuteQuery (query);
}
}
之后我根据我的项目现有的读取数据并且传递到unity的TMPtext中去
using System.Collections;
using UnityEngine;
using Mono.Data.Sqlite;
using TMPro;
public class PC端IC封装独立机器分数统计数据库读写DP : MonoBehaviour
{
//public PlayMakerFSM myFSM;
public string 模块;
public TMP_Text[] 分数组;
public TMP_Text[] 数据组;
string sql;
private SqliteDbHelper db = new SqliteDbHelper("");
public void 读取数据库()
{
db.OpenDB("data source = E:/**/**/**.db");///这里的星号是你的数据库db文件的位置，我这里是绝对位置，后期导出后可以改为相对位置
db.ExecuteQuery("PRAGMA synchronous = OFF;"); ///关闭数据库写协同
string selStr = "SELECT * FROM ***";///***这3个星是指你的数据库 表名称
SqliteDataReader dbReader = db.ExecuteQuery(selStr);
///这里的while{}代码可以输出数据库上述表名称里的第三列的所有数据，然后保存到数组里
ArrayList stuscore = new ArrayList()
while (dbReader.Read())
{
string stu = dbReader.GetString(3);///数字3是指数据库里的第三列
stuscore.Add(stu);
}
///下面代码是可以把上述数组内容放入unity的TMPtext中（我这里代码跳过第六行)
for (int j = 0; j <= stuscore.Count; j++){
if (j <= 5)
{
数据组[j].GetComponent<TMP_Text>().text = (string)stuscore[j + 2];
}
if (j>6&&j<=9)
{
数据组[j - 3].GetComponent<TMP_Text>().text = (string)stuscore[j];
}
}
db.CloseSqlConnection();
return;
}
///下面是写入到数据库中
public void 写入数据库()
{
db.OpenDB("data source = GxqSqlDate.db");
db.OpenDB("data source = E:/**/**/**.db");///这里的星号是你的数据库db文件的位置，我这里是绝对位置，后期导出后可以改为相对位置
///用于流程工程文件存储数据用///
// FsmString szGet = FsmVariables.GlobalVariables.GetFsmString("szGet");
//FsmInt whichone = FsmVariables.GlobalVariables.GetFsmInt("分数组");
//FsmInt stepNub = FsmVariables.GlobalVariables.GetFsmInt("机器操作步数记录");
db.ExecuteQuery("PRAGMA synchronous = OFF;"); ///关闭数据库写协同
for (int i = 0; i < 分数组.Length; i++)
{
string store = 分数组[i].GetComponent<TMP_Text>().text;
switch (模块)
{
case "X35PGJ":
sql = string.Format("update IC_X35 set 得分='{0}' where 序号='{1}'", store, i + 1);
db.ExecuteQuery(sql);
Debug.Log(sql);
break;
case "D2":
sql = string.Format("update SMT_D2 set 得分='{0}' where 序号='{1}'", store, i + 1);
db.ExecuteQuery(sql);
Debug.Log(sql);
break;
case "A3":
sql = string.Format("update SMT_A3 set 得分='{0}' where 序号='{1}'", store, i + 1);
db.ExecuteQuery(sql);
Debug.Log(sql);
break;
case "C3":
sql = string.Format("update SMT_C3 set 得分='{0}' where 序号='{1}'", store, i + 1);
db.ExecuteQuery(sql);
Debug.Log(sql);
break;
case "E1":
sql = string.Format("update SMT_E1 set 得分='{0}' where 序号='{1}'", store, i + 1);
db.ExecuteQuery(sql);
Debug.Log(sql);
break;
/*default:
//用于处理以上情况都不满足，需要执行的代码
Console.WriteLine("其他月份");
break;*/
}
}
db.CloseSqlConnection();
}
void Start()
{ ///针对playmaker里的数据读取
//myString = myFSM.FsmVariables.GetFsmString(variableName).Value;
}
void Update()
{
}
}
1158
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
