C# SQLite 数据库
数据库
Oracle。Oracle的应用，主要在传统行业的数据化业务中，比如：银行、金融这样的对可用性、健壮性、安全性、实时性要求极高的业务
MS SQL Server。windows生态系统的产品，好处坏处都很分明。好处就是，高度集成化，微软也提供了整套的软件方案，基本上一套win系统装下来就齐活了。因此，不那么缺钱，但很缺IT人才的中小企业，会偏爱 MS SQL Server 。例如，自建ERP系统、商业智能、垂直领域零售商、餐饮、事业单位等等。
MySQL。MySQL基本是生于互联网，长于互联网。其应用实例也大都集中于互联网方向
SQLite介绍
SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。
它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 C#、PHP、Java等，还有ODBC接口。SQLite第一个Alpha版本诞生于2000年5月。 至2015年已经有15个年头。
Windows平台使用
1、在Assets目录下创建Plugins目录，将Mono.Data.Sqlite.dll、System.Data.dll、sqlite3.dll三个文件放到工程Plugins目录下。
2、在Assets目录下创建StreamingAssets目录，把db放在该目录内。
3、将DbAccess.cs脚本添加到工程中
Android平台使用
1、在Assets目录下创建Plugins目录，将Mono.Data.Sqlite.dll、System.Data.dll、sqlite3.dll三个文件放到工程Plugins目录下。
2、然后在Plugins目录下建立Android目录，再将libsqlite3.so放到Android目录下。
3、在Assets目录下创建StreamingAssets目录，把db放在该目录内。 4、将DbAccess.cs脚本添加到工程中。
SQLite的数据库常规操作封装的通用类
1 using UnityEngine; 2 using System; 3 using System.Collections; 4 using Mono.Data.Sqlite; 5 /// <summary> 6 /// SQLite数据库操作类 7 /// </summary> 8 public class DbAccess 9 { 10 private SqliteConnection conn; // SQLite连接 11 private SqliteCommand cmd; // SQLite命令 12 private SqliteDataReader reader; 13 public DbAccess (string connectionString) 14 { 15 OpenDB (connectionString); 16 } 17 public DbAccess (){ } 18 /// <summary> 19 /// 打开数据库 20 /// </summary> 21 /// <param name="connectionString"></param> 22 public void OpenDB (string connectionString) 23 { 24 try 25 { 26 conn = new SqliteConnection (connectionString); 27 conn.Open (); 28 Debug.Log ("Connected to db,连接数据库成功！"); 29 } 30 catch(Exception e) 31 { 32 string temp1 = e.ToString(); 33 Debug.Log(temp1); 34 } 35 } 36 /// <summary> 37 /// 关闭数据库连接 38 /// </summary> 39 public void CloseSqlConnection () 40 { 41 if (cmd != null) { cmd.Dispose (); cmd = null; } 42 if (reader != null) { reader.Dispose (); reader = null;} 43 if (conn != null) { conn.Close (); conn = null;} 44 Debug.Log ("Disconnected from db.关闭数据库！"); 45 } 46 /// <summary> 47 /// 执行SQL语句 48 /// </summary> 49 /// <param name="sqlQuery"></param> 50 /// <returns></returns> 51 public SqliteDataReader ExecuteQuery ( string sqlQuery ) 52 { 53 Debug.Log( "ExecuteQuery:: " + sqlQuery ); 54 cmd = conn.CreateCommand (); 55 cmd.CommandText = sqlQuery; 56 reader = cmd.ExecuteReader (); 57 return reader; 58 } 59 60 /// <summary> 61 /// 查询表中全部数据 param tableName=表名 62 /// </summary> 63 public SqliteDataReader ReadFullTable (string tableName) 64 { 65 string query = "SELECT * FROM " + tableName; 66 return ExecuteQuery (query); 67 } 68 /// <summary> 69 /// 插入数据 param tableName=表名 values=插入数据内容 70 /// </summary> 71 public SqliteDataReader InsertInto (string tableName, string[] values) 72 { 73 string query = "INSERT INTO " + tableName + " VALUES (" + values[0]; 74 for (int i = 1; i < values.Length; ++i) { 75 query += ", " + values[i]; 76 } 77 query += ")"; 78 return ExecuteQuery (query); 79 } 80 /// <summary> 81 /// 更新数据 param tableName=表名 cols=更新字段 colsvalues=更新内容 selectkey=查找字段（主键) selectvalue=查找内容 82 /// </summary> 83 public SqliteDataReader UpdateInto (string tableName, string []cols,string []colsvalues,string selectkey,string selectvalue) 84 { 85 string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0]; 86 for (int i = 1; i < colsvalues.Length; ++i) { 87 query += ", " +cols[i]+" ="+ colsvalues[i]; 88 } 89 query += " WHERE "+selectkey+" = "+selectvalue+" "; 90 return ExecuteQuery (query); 91 } 92 93 /// <summary> 94 /// 删除数据 param tableName=表名 cols=字段 colsvalues=内容 95 /// </summary> 96 public SqliteDataReader Delete(string tableName,string []cols,string []colsvalues) 97 { 98 string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0]; 99 for (int i = 1; i < colsvalues.Length; ++i) { 100 query += " or " +cols[i]+" = "+ colsvalues[i]; 101 } 102 return ExecuteQuery (query); 103 } 104 /// <summary> 105 /// 插入数据 param tableName=表名 cols=插入字段 value=插入内容 106 /// </summary> 107 public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values) 108 { 109 if (cols.Length != values.Length) { 110 throw new SqliteException ("columns.Length != values.Length"); 111 } 112 string query = "INSERT INTO " + tableName + "(" + cols[0]; 113 for (int i = 1; i < cols.Length; ++i) { 114 query += ", " + cols[i]; 115 } 116 query += ") VALUES (" + values[0]; 117 for (int i = 1; i < values.Length; ++i) { 118 query += ", " + values[i]; 119 } 120 query += ")"; 121 return ExecuteQuery (query); 122 } 123 /// <summary> 124 /// 删除表中全部数据 125 /// </summary> 126 public SqliteDataReader DeleteContents (string tableName) 127 { 128 string query = "DELETE FROM " + tableName; 129 return ExecuteQuery (query); 130 } 131 /// <summary> 132 /// 创建表 param name=表名 col=字段名 colType=字段类型 133 /// </summary> 134 public SqliteDataReader CreateTable (string name, string[] col, string[] colType) 135 { 136 if (col.Length != colType.Length) { 137 throw new SqliteException ("columns.Length != colType.Length"); 138 } 139 string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0]; 140 for (int i = 1; i < col.Length; ++i) { 141 query += ", " + col[i] + " " + colType[i]; 142 } 143 query += ")"; 144 return ExecuteQuery (query); 145 } 146 /// <summary> 147 /// 按条件查询数据 param tableName=表名 items=查询字段 col=查找字段 operation=运算符 values=内容 148 /// </summary> 149 public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values) 150 { 151 if (col.Length != operation.Length || operation.Length != values.Length) { 152 throw new SqliteException ("col.Length != operation.Length != values.Length"); 153 } 154 string query = "SELECT " + items[0]; 155 for (int i = 1; i < items.Length; ++i) { 156 query += ", " + items[i]; 157 } 158 query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' "; 159 for (int i = 1; i < col.Length; ++i) { 160 query += " AND " + col[i] + operation[i] + "'" + values[i] + "' "; 161 } 162 return ExecuteQuery (query); 163 } 164 /// <summary> 165 /// 查询表 166 /// </summary> 167 public SqliteDataReader Select(string tableName, string col, string values) 168 { 169 string query = "SELECT * FROM " + tableName + " WHERE " + col + " = " + values; 170 return ExecuteQuery (query); 171 } 172 public SqliteDataReader Select(string tableName, string col,string operation, string values) 173 { 174 string query = "SELECT * FROM " + tableName + " WHERE " + col + operation + values; 175 return ExecuteQuery (query); 176 } 177 /// <summary> 178 /// 升序查询 179 /// </summary> 180 public SqliteDataReader SelectOrderASC (string tableName,string col) 181 { 182 string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " ASC"; 183 return ExecuteQuery (query); 184 } 185 /// <summary> 186 /// 降序查询 187 /// </summary> 188 public SqliteDataReader SelectOrderDESC (string tableName,string col) 189 { 190 string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " DESC"; 191 return ExecuteQuery (query); 192 } 193 /// <summary> 194 /// 查询表行数 195 /// </summary> 196 public SqliteDataReader SelectCount(string tableName) 197 { 198 string query = "SELECT COUNT(*) FROM " + tableName; 199 return ExecuteQuery (query); 200 } 201 }
1 using System.Collections; 2 using System.Collections.Generic; 3 using UnityEngine; 4 using Mono .Data .Sqlite; 5 6 namespace ns 7 { 8 /// <summary> 9 /// 打开和关闭数据库 10 /// </summary> 11 public class SQLiteHelper : MonoBehaviour 12 { 13 protected string dbName = "test2.db"; // 文件名 14 private string filePath // 文件路径 15 { 16 get { return Application .streamingAssetsPath + "/" + dbName; } 17 } 18 19 protected DbAccess db; // dbAccess实例 20 protected SqliteDataReader reader;// 21 /// <summary> 22 /// 打开数据库 23 /// </summary> 24 protected void OpenDB() 25 { 26 db = new DbAccess( "URI=file:" + filePath ); 27 } 28 /// <summary> 29 /// 关闭数据库 30 /// </summary> 31 protected void CloseDB() 32 { 33 if( reader != null ) 34 { 35 reader .Close(); 36 reader = null; 37 } 38 39 db .CloseSqlConnection(); 40 } 41 /// <summary> 42 /// 对象前后添加单引号 43 /// </summary> 44 /// <param name="o"></param> 45 /// <returns></returns> 46 protected string GetStr( object o ) 47 { 48 return "'" + o + "'"; 49 } 50 51 } 52 }
1 using System.Collections; 2 using System.Collections.Generic; 3 using UnityEngine; 4 using ns; 5 6 public class MySQLiteDemo : MySQLiteHelper 7 { 8 /// <summary> 9 /// 创建表 10 /// </summary> 11 private void CreateTable() 12 { 13 //打开数据库 14 OpenDB(); 15 16 db.CreateTable("MyRole", new string[] { "id", "name", "age", "lv", "exp" }, 17 new string[] { "int", "text", "int", "int", "float" }); 18 //关闭数据库 19 CloseDB(); 20 } 21 22 23 /// <summary> 24 /// 插入数据 25 /// </summary> 26 private void InsertData() 27 { 28 //打开数据库 29 OpenDB(); 30 //插入数据库 31 db.InsertInto 32 ("MyRole", new string[] { GetStr("1"), GetStr("张三"), GetStr("100"), GetStr("12"), GetStr("120") }); 33 db.InsertInto 34 ("MyRole", new string[] { GetStr("2"), GetStr("李四"), GetStr("110"), GetStr("13"), GetStr("130") }); 35 db.InsertInto 36 ("MyRole", new string[] { GetStr("3"), GetStr("王五"), GetStr("120"), GetStr("14"), GetStr("140") }); 37 db.InsertInto 38 ("MyRole", new string[] { GetStr("4"), GetStr("赵六"), GetStr("130"), GetStr("15"), GetStr("150") }); 39 40 //关闭数据库 41 CloseDB(); 42 } 43 44 /// <summary> 45 /// 删除数据库 46 /// </summary> 47 private void DeleteData() 48 { 49 //打开数据库 50 OpenDB(); 51 // 删除数据: 多个条件直接是或的关系 52 // DELETE FROM Role WHERE id = 1 or lv = 13 53 54 //db.Delete("MyRole", 55 // new string[] { "id", "lv" }, 56 // new string[] { "1", "130" } 57 // ); 58 db.DeleteContents("MyRole"); 59 //关闭数据库 60 61 CloseDB(); 62 } 63 /// <summary> 64 /// 更新数据 65 /// </summary> 66 private void UpdateData() 67 { 68 //打开数据库 69 OpenDB(); 70 // 更新数据: id为1数据 exp改为350 lv改为16 71 // UPDATE Role SET exp = 350, lv =16 WHERE id = 1 72 db.UpdateInto("MyRole", 73 new string[] { "exp", "lv" }, new string[] { "350", "16", }, "id", "1"); 74 //关闭数据库 75 CloseDB(); 76 } 77 // 查找数据 78 private void SearchData() 79 { 80 OpenDB(); 81 // 查询 查找id为3 lv为21的数据 82 // 找到 name 和 age 83 // 多个条件之间是与的关系 84 // SELECT name, age FROM Role WHERE id='3' AND lv='14' 85 reader = db.SelectWhere("MyRole", 86 new string[] { "name", "age" }, 87 new string[] { "id", "lv" }, 88 new string[] { "=", "=" }, 89 new string[] { "1", "14" } 90 ); 91 if (reader.HasRows) 92 { 93 reader.Read(); 94 print(reader.GetString(reader.GetOrdinal("name"))); 95 print(reader.GetInt32(reader.GetOrdinal("age"))); 96 } 97 98 CloseDB(); 99 } 100 101 /// <summary> 102 /// 查找多个数据 103 /// </summary> 104 private void SelectData() 105 { 106 // 打开数据库 107 OpenDB(); 108 109 //reader = db .Select( "Role" , "id" , "2" );// 查询所有id 为 2的数据 110 //reader = db .Select( "Role" , "id" , ">" , "1" ); // 查询所有id>1的数据 111 //reader = db .ReadFullTable("Role"); // 读取整张表 112 //reader = db .SelectOrderASC( "Role" , "age" ); // age从小到大排列 113 reader = db .SelectOrderDESC( "MyRole" , "lv" ); // lv从大到小 114 if ( reader.HasRows ) 115 { 116 while( reader .Read() ) 117 { 118 string s = ""; 119 s += reader .GetInt32( reader .GetOrdinal( "id" ) ) + " , " ; 120 s += reader .GetString( reader .GetOrdinal( "name" ) ) + " , "; 121 s += reader .GetInt32( reader .GetOrdinal( "age" ) ) + " , "; 122 s += reader .GetInt32( reader .GetOrdinal( "lv" ) ) + " , "; 123 s += reader .GetFloat( reader .GetOrdinal( "exp" ) ); 124 print( s ); 125 } 126 } 127 // 关闭数据库 128 CloseDB(); 129 } 130 131 private void OnGUI() 132 { 133 if (GUILayout.Button("创建MyRole表")) 134 { 135 CreateTable(); 136 } 137 if (GUILayout.Button("插入数据")) 138 { 139 InsertData(); 140 } 141 if (GUILayout.Button("删除数据库")) 142 { 143 DeleteData(); 144 } 145 if (GUILayout.Button("更新数据")) 146 { 147 UpdateData(); 148 } 149 if (GUILayout.Button("查询数据")) 150 { 151 SearchData(); 152 } 153 if (GUILayout.Button("多条数据查询")) 154 { 155 SelectData(); 156 } 157 } 158 159 160 }
