private SqliteConnection conn; // SQLite连接 private SqliteDataReader reader; private SqliteCommand command; // SQLite命令 private float timespeed = 0.001f ; /// <summary> /// 执行SQL语句 公共方法 /// </summary> /// <param name="sqlQuery"></param> /// <returns></returns> public SqliteDataReader ExecuteQuery( string sqlQuery) command = conn.CreateCommand(); command.CommandText = sqlQuery; reader = command.ExecuteReader(); return reader; #region 打开/关闭数据库 /// <summary> /// 打开数据库 /// </summary> /// <param name="connectionString"> @"Data Source = " + path </param> public SQLiteDataBase( string connectionString) // 构造数据库连接 conn = new SqliteConnection(connectionString); // 打开数据库 conn.Open(); Debug.Log( " 打开数据库 " ); catch (Exception e) Debug.Log(e.Message); /// <summary> /// 关闭数据库连接 /// </summary> public void CloseSqlConnection() if (command != null ) { command.Dispose(); command = null ; } if (reader != null ) { reader.Dispose(); reader = null ; } if (conn != null ) { conn.Close(); conn = null ; } Debug.Log( " 关闭数据库！ " ); #endregion ; #region 创建表单 /// <summary> /// 创建表单 第一种 /// </summary> /// <param name="name"> 表单名 </param> /// <param name="col"> 字段 </param> /// <param name="colType"> 类型 </param> public void CreationMenu( string name, string [] col, string [] colType) string query = " create table " + name + " ( " + col[ 0 ] + " " + colType[ 0 ]; for ( int i = 1 ; i < col.Length; ++ i) query += " , " + col[i] + " " + colType[i]; query += " ) " ; command = new SqliteCommand(query, conn); command.ExecuteNonQuery(); /// <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); #endregion ; #region 查询数据 /// <summary> /// 查询表中全部数据 param tableName=表名 /// </summary> public SqliteDataReader ReadFullTable( string tableName) string query = " SELECT * FROM " + tableName; 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[i] + " ' " ; 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); #endregion #region 插入数据 /// <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> /// 插入数据 插入多条数据 /// </summary> 经测试这个方法是可用的 /// 因为我的数据有两万条运行卡着不动了,所以用协程时间控制一下 虽然慢但是不卡死,写到数据库中之后用数据库就好了 /// <param name="tableName"> 表名字 </param> /// <param name="values"> 字典 </param> /// <returns></returns> public IEnumerator InsertInto( string tableName, Dictionary< string , List< string >> values) int ii = 0 ; foreach ( var item in values) string query = "" ; string value = "" ; foreach ( var ite in item.Value) value += " ',' " + ite; query = " INSERT INTO " + tableName + " VALUES (' " + item.Key + value + " ') " ; // Debug.Log(query); command = conn.CreateCommand(); command.CommandText = query; command.ExecuteNonQuery(); Debug.Log( " 写入成功 " + ii++ ); yield return new WaitForSeconds(timespeed); Debug.Log( " 写入成功 " ); #region 没测试过的 /// <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> /// 更新数据 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); /// 删除表 IF EXISTS判断表存不存在防止出错 已测试 /// </summary> public SqliteDataReader DeleteContents( string tableName) string query = " DROP TABLE IF EXISTS " + tableName; Debug.Log( " 删除表成功 " ); 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); #endregion public class SQLiteDataBaseTion : MonoBehaviour [Header( " Excel表数据长度 " )] // 表格一共有多少列数 最长的一个 public int tableint; public string [] fields, type; // 字段\类型 [Header( " 数据库名字 " )] public string dbname; private SQLiteDataBase _SQLiteData; private SqliteDataReader reader; private string path; private string connectionString; public static Dictionary< string , List< string >> JDDateDic = new Dictionary< string , List< string >>(); // 机电数据 public static Dictionary< string , List< string >> OneCDateDic = new Dictionary< string , List< string >>(); // 一层数据 private void Awake() fields = new string [tableint]; type = new string [tableint]; for ( int i = 0 ; i < tableint; i++ ) fields[i] = " sql " + i.ToString(); type[i] = " varchar " ; // Start is called before the first frame update void Start() // 读取excel表格数据 ReadExcelClick( " jiegou.xlsx " , 0 , OneCDateDic); path = Application.streamingAssetsPath + " / " + dbname + " .db " ; connectionString = @" Data Source = " + path; // 创建数据库文件 存在就打开 CreateSQL(dbname); // 创建表 // _SQLiteData.CreationMenu("jiegou", fields, type); // 将数据插入数据库 // StartCoroutine(_SQLiteData.InsertInto("jiegou", OneCDateDic)); // 删除表 // _SQLiteData.DeleteContents("jiegou"); /// <summary> /// 创建数据库文件 /// </summary> /// <param name="sqlname"> 文件名字 </param> public void CreateSQL( string sqlname) if (!File.Exists(Application.streamingAssetsPath + " / " + sqlname + " .db " )) // 不存在就创建 File.Create(Application.streamingAssetsPath + " / " + sqlname + " .db " ); // 创建之后再打开 _SQLiteData = new SQLiteDataBase(connectionString); Debug.Log( " 已存在 " ); // 打开数据库 _SQLiteData = new SQLiteDataBase(connectionString); /// 读取数据库某一行数据 "646c173c-7d14-47b0-80fe-53c1c8ce2b0e-0037044a" public List< string > SomeLine( string idname, out List < string > listidnames) reader = _SQLiteData.ReadFullTable( " jidian " ); List < string > idname_ = new List< string > (); while (reader.Read()) // Debug.Log(reader.GetString(reader.GetOrdinal("idname"))); // reader.ToString(); if (reader.GetString( 0 ).ToString() == idname) for ( int i = 0 ; i < reader.FieldCount; i++ ) if (reader.GetString(i) != null ) Debug.Log(reader.GetString(i)); idname_.Add(reader.GetString(i)); catch (Exception e) Debug.Log(e.Message); break ; listidnames = idname_; return listidnames; listidnames = idname_; return listidnames; // 读取 Excel表格 void ReadExcelClick( string _name, int _num, Dictionary< string , List< string >> _Dic) // 1.打开文件，创建一个文件流操作对象 // FileStream fileStream = new FileStream(Application.streamingAssetsPath + "/" + "机电.xlsx", FileMode.Open, FileAccess.Read); FileStream fileStream = new FileStream(Application.streamingAssetsPath + " / " + _name, FileMode.Open, FileAccess.Read); // 2.创建一个excel读取类 IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fileStream); // 方法1：读取 // while (reader.Read()) // string name = reader.GetString(0); // string birth = reader.GetString(1); // string brief = reader.GetString(2); // Debug.Log("姓名:" + name + " --- " + "生卒:" + birth + " --- " + "简介：" + brief); // 方法2：读取 DataSet result = reader.AsDataSet(); // 获取行数 int rows = result.Tables[_num].Rows.Count; Debug.Log(rows); // 获取列数 int column = result.Tables[_num].Columns.Count; for ( int i = 0 ; i < rows; i++ ) // 获取i行的第一列数据 string name = result.Tables[_num].Rows[i][ 0 ].ToString(); List < string > _S = new List< string > (); for ( int j = 1 ; j < column; j++ ) string birth = result.Tables[_num].Rows[i][j].ToString(); _S.Add(birth); if (_Dic.ContainsKey(name)) continue ; _Dic.Add(name, _S); Debug.Log(_Dic.Count); private void OnDisable() _SQLiteData.CloseSqlConnection(); // Update is called once per frame void Update() c#脚本亲测可用