SQLite使用笔记
前言
客户端存储信息的方法有好多种，在以往的项目中采用的是序列化记录到文件中的方式，即时通信项目中客户端的一些系统配置的保存也使用的这种方式，然而客户端保存聊天记录就不能使用这种方式（保存、读取、修改都需要进行序列化操作比较费时，不同会话聊天记录到不同文件中，将需要创建大量的文件），调研相关技术后，决定使用SQLite。
SQLite
我的认知如下：
SQLite是一款轻量级文件数据库，无需部署安装，特点是嵌入式与事务（原子性、一致性、隔离性和持久性）。使用时只需创建一个db文件（db文件即代表一个数据库），然后利用ADO.NET进行连接库、创建、插入、查询、修改表就可以了，非常的简单方便。
步骤
1.添加引用
右键项目引用选择 管理Nuget程序包 搜索SQLite,选择第一个进行安装
2.创建数据库文件
string dbPath = AppDomain.CurrentDomain.BaseDirectory + "local.db"; SQLiteConnection.CreateFile(dbPath);
注意需要添加引用：using System.Data.SQLite;
3.连接SQLite
SQLiteConnection conn = new SQLiteConnection(); SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; connstr.Password = "123456";//设置密码，SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open();
4.创建表
SQLiteCommand cmd = new SQLiteCommand(); string sql = "CREATE TABLE FirstTable(ID varchar(36),UserName varchar(30),PassWord varchar(30))"; cmd.CommandText = sql; cmd.Connection = conn; cmd.ExecuteNonQuery();
5.增删改查
增：
SQLiteCommand cmd = new SQLiteCommand(); string sql = "INSERT INTO FirstTable VALUES('1','ading3','123')"; cmd.Connection = conn; cmd.CommandText = sql; cmd.ExecuteNonQuery();
View Code
删：
string sql = "DELETE FROM FirstTable WHERE ID = @ID"; SQLiteParameter[] parms = { new SQLiteParameter("@ID",id) }; command.CommandText = sql; command.Parameters.AddRange(parameters); int counts = command.ExecuteNonQuery();
View Code
改：
string sql= @"UPDATE FirstTable SET UserName=@UserName, PassWord=@PassWord WHERE UserName='admin' "; SQLiteParameter[] parms1 = { new SQLiteParameter("@UserName","adminading"), new SQLiteParameter("@PassWord","2622020") }; command.CommandText = sql; command.Parameters.AddRange(parameters); int counts = command.ExecuteNonQuery();
View Code
查：
string query = "SELECT * FROM FirstTable"; DataTable dt = SQLiteHelper.ExecuteQuery(query, null); List<Test> tlist = new List<Test>(); foreach (var item in dt1.Rows) { Test tt = new Test(); tt.ID=(item as DataRow)["ID"].ToString(); tt.UserName = (item as DataRow)["UserName"].ToString(); tt.PassWord = (item as DataRow)["PassWord"].ToString(); tlist.Add(tt); } public class Test { public string ID { get; set; } public string UserName { get; set; } public string PassWord { get; set; } }
View Code
帮助类
1 /// <summary> 2 /// SQLite帮助类 3 /// 说明：使用SQLite很简单，只需要Nuget搜SQLite 使用第一个System.Data.SQLite安装即可 4 /// 使用CreateDB方法 创建一个数据库文件 传入路径即可 （注意：一个数据库文件代表一个数据库） 5 /// 使用前请先指定连接字符串 使用SetConnectionString()方法 6 /// SQLite作为本地文件数据库 具有独立运行、无服务器、零配置、支持事务、低内存、原子性等特点 7 /// </summary> 8 public class SQLiteHelper 9 { 10 #region 属性 11 /// <summary> 12 /// 连接字符串 13 /// </summary> 14 private static string connectionString = string.Empty; 15 #endregion 属性 16 17 #region 设置连接字符串与创建数据库文件 18 /// <summary> 19 /// 根据数据源、密码、版本号设置连接字符串。 20 /// </summary> 21 /// <param name="datasource">数据源。</param> 22 /// <param name="password">密码。</param> 23 /// <param name="version">版本号（缺省为3）。</param> 24 public static void SetConnectionString(string datasource, string password, int version = 3) 25 { 26 connectionString = string.Format("Data Source={0};Version={1};password={2}", 27 datasource, version, password); 28 } 29 30 /// <summary> 31 /// 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。 32 /// </summary> 33 /// <param name="dbName">数据库文件名。为null或空串时不创建。</param> 34 /// <param name="password">（可选）数据库密码，默认为空。</param> 35 /// <exception cref="Exception"></exception> 36 public static void CreateDB(string dbName) 37 { 38 if (!string.IsNullOrEmpty(dbName)) 39 { 40 try 41 { 42 CreateDirectory(dbName); 43 SQLiteConnection.CreateFile(dbName); 44 } 45 catch (Exception ex) 46 { 47 string errormes = ex.Message; 48 errormes += "\r\n"; 49 errormes += LogHelper.ToMessage(ex); 50 string path = string.Empty; 51 path += AppDomain.CurrentDomain.BaseDirectory; 52 path += @"Unlog\SQLiteError"; 53 path += DateTime.Now.ToString("yyyyMMddHHmm"); 54 path += ".txt"; 55 LogHelper.Instance.WriteLog(path, errormes); 56 } 57 } 58 } 59 60 #region 辅助方法 61 /// <summary> 62 /// 创建父级路径 63 /// </summary> 64 /// <param name="infoPath"></param> 65 private static void CreateDirectory(string infoPath) 66 { 67 DirectoryInfo directoryInfo = Directory.GetParent(infoPath); 68 if (!directoryInfo.Exists) 69 { 70 directoryInfo.Create(); 71 } 72 } 73 #endregion 辅助方法 74 #endregion 设置连接字符串与创建数据库文件 75 76 #region 命令参数封装 77 // <summary> 78 /// 准备操作命令参数 79 /// </summary> 80 /// <param name="cmd">SQLiteCommand</param> 81 /// <param name="conn">SQLiteConnection</param> 82 /// <param name="cmdText">Sql命令文本</param> 83 /// <param name="data">参数数组</param> 84 private static void PrepareCommand(SQLiteConnection conn, SQLiteCommand cmd, string cmdText, params SQLiteParameter[] parms) 85 { 86 if (conn.State != ConnectionState.Open) 87 conn.Open(); 88 cmd.Parameters.Clear(); 89 cmd.Connection = conn; 90 cmd.CommandText = cmdText; 91 cmd.CommandType = CommandType.Text; 92 cmd.CommandTimeout = 30; 93 if (parms != null && parms.Length > 0) 94 { 95 foreach (SQLiteParameter parameter in parms) 96 { 97 if ((parameter.Direction == ParameterDirection.Input || parameter.Direction == ParameterDirection.InputOutput) && (parameter.Value == null)) 98 { 99 parameter.Value = DBNull.Value; 100 } 101 } 102 cmd.Parameters.AddRange(parms); 103 } 104 } 105 106 #endregion 命令参数封装 107 108 #region 数据库操作 109 #region 创建表 110 /// <summary> 111 /// 创建表 112 /// </summary> 113 /// <param name="sql"></param> 114 /// <returns></returns> 115 public static bool CreateTable(string sql) 116 { 117 bool rr = true; 118 try 119 { 120 using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 121 { 122 using (SQLiteCommand command = new SQLiteCommand(connection)) 123 { 124 try 125 { 126 PrepareCommand(connection, command, sql, null); 127 int count = command.ExecuteNonQuery(); 128 if (count > 0) rr = true; 129 else rr = false; 130 } 131 catch (Exception ex) 132 { 133 return false; 134 } 135 } 136 } 137 } 138 catch (Exception ex) 139 { 140 141 return false; 142 } 143 return rr; 144 } 145 #endregion 创建表 146 147 #region 增删改操作 148 /// <summary> 149 /// 对SQLite数据库执行增删改操作，返回受影响的行数。 150 /// </summary> 151 /// <param name="sql">要执行的增删改的SQL语句。</param> 152 /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 153 /// <returns></returns> 154 /// <exception cref="Exception"></exception> 155 public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters) 156 { 157 int affectedRows = 0; 158 using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 159 { 160 using (SQLiteCommand command = new SQLiteCommand(connection)) 161 { 162 try 163 { 164 PrepareCommand(connection, command, sql, parameters); 165 //connection.Open(); 166 //command.CommandText = sql; 167 //if (parameters.Length != 0) 168 //{ 169 // command.Parameters.AddRange(parameters); 170 //} 171 affectedRows = command.ExecuteNonQuery(); 172 } 173 catch (Exception) { throw; } 174 } 175 } 176 return affectedRows; 177 } 178 #endregion 增删改操作 179 180 #region 批量操作 181 /// <summary> 182 /// 批量处理数据操作语句。 183 /// </summary> 184 /// <param name="list">SQL语句集合。</param> 185 /// <exception cref="Exception"></exception> 186 public static void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list) 187 { 188 using (SQLiteConnection conn = new SQLiteConnection(connectionString)) 189 { 190 try { conn.Open(); } 191 catch { throw; } 192 using (SQLiteTransaction tran = conn.BeginTransaction()) 193 { 194 using (SQLiteCommand cmd = new SQLiteCommand(conn)) 195 { 196 try 197 { 198 foreach (var item in list) 199 { 200 PrepareCommand(conn, cmd, item.Key, item.Value); 201 //cmd.CommandText = item.Key; 202 //if (item.Value != null) 203 //{ 204 // cmd.Parameters.AddRange(item.Value); 205 //} 206 cmd.ExecuteNonQuery(); 207 } 208 tran.Commit(); 209 } 210 catch (Exception) { tran.Rollback(); throw; } 211 } 212 } 213 } 214 } 215 #endregion 批量操作 216 217 #region 查询 返回第一个 218 /// <summary> 219 /// 执行查询语句，并返回第一个结果。 220 /// </summary> 221 /// <param name="sql">查询语句。</param> 222 /// <returns>查询结果。</returns> 223 /// <exception cref="Exception"></exception> 224 public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters) 225 { 226 using (SQLiteConnection conn = new SQLiteConnection(connectionString)) 227 { 228 using (SQLiteCommand cmd = new SQLiteCommand(conn)) 229 { 230 try 231 { 232 PrepareCommand(conn, cmd, sql, parameters); 233 //conn.Open(); 234 //cmd.CommandText = sql; 235 //if (parameters.Length != 0) 236 //{ 237 // cmd.Parameters.AddRange(parameters); 238 //} 239 return cmd.ExecuteScalar(); 240 } 241 catch (Exception) { throw; } 242 } 243 } 244 } 245 #endregion 查询 返回第一个 246 247 #region 查询 返回DT 248 /// <summary> 249 /// 执行一个查询语句，返回一个包含查询结果的DataTable。 250 /// </summary> 251 /// <param name="sql">要执行的查询语句。</param> 252 /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 253 /// <returns></returns> 254 /// <exception cref="Exception"></exception> 255 public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters) 256 { 257 using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 258 { 259 using (SQLiteCommand command = new SQLiteCommand(sql, connection)) 260 { 261 PrepareCommand(connection, command, sql, parameters); 262 //if (parameters != null) 263 //{ 264 // if (parameters.Length != 0) 265 // { 266 // command.Parameters.AddRange(parameters); 267 // } 268 //} 269 270 SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); 271 DataTable data = new DataTable(); 272 try { adapter.Fill(data); } 273 catch (Exception) { throw; } 274 return data; 275 } 276 } 277 } 278 #endregion 查询 返回DT 279 280 #region 查询 返回SQLiteDataReader 281 /// <summary> 282 /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。 283 /// </summary> 284 /// <param name="sql">要执行的查询语句。</param> 285 /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 286 /// <returns></returns> 287 /// <exception cref="Exception"></exception> 288 public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters) 289 { 290 SQLiteConnection connection = new SQLiteConnection(connectionString); 291 SQLiteCommand command = new SQLiteCommand(sql, connection); 292 try 293 { 294 PrepareCommand(connection, command, sql, parameters); 295 //if (parameters.Length != 0) 296 //{ 297 // command.Parameters.AddRange(parameters); 298 //} 299 //connection.Open(); 300 return command.ExecuteReader(CommandBehavior.CloseConnection); 301 } 302 catch (Exception) { throw; } 303 } 304 #endregion 查询 返回SQLiteDataReader 305 306 #region 查询数据库中的所有数据类型信息 307 /// <summary> 308 /// 查询数据库中的所有数据类型信息。 309 /// </summary> 310 /// <returns></returns> 311 /// <exception cref="Exception"></exception> 312 public static DataTable GetSchema() 313 { 314 using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 315 { 316 try 317 { 318 connection.Open(); 319 return connection.GetSchema("TABLES"); 320 } 321 catch (Exception) { throw; } 322 } 323 } 324 325 #endregion 查询数据库中的所有数据类型信息 326 327 #region 判断表是否存在 328 public static bool IsTableExist(string tableName) 329 { 330 bool isTableExist = true; 331 using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 332 { 333 string sql = "SELECT name FROM sqlite_master WHERE type='table' AND name = '"; 334 sql += tableName; 335 sql += "'"; 336 using (SQLiteCommand command = new SQLiteCommand(sql, connection)) 337 { 338 PrepareCommand(connection, command, sql, null); 339 int count = command.ExecuteNonQuery(); 340 if (count <= 0) isTableExist = false; 341 } 342 } 343 return isTableExist; 344 345 } 346 #endregion 判断表是否存在 347 348 #endregion 数据库操作 349 350 #region 整理数据库 351 /// <summary> 352 /// 重新组织数据库 353 /// SQLite 的自带命令 VACUUM。用来重新整理整个数据库达到紧凑之用，比如把删除的彻底删掉等等 354 /// </summary> 355 public static void ResetDataBass() 356 { 357 using (SQLiteConnection conn = new SQLiteConnection(connectionString)) 358 { 359 var cmd = new SQLiteCommand(); 360 361 if (conn.State != ConnectionState.Open) 362 conn.Open(); 363 cmd.Parameters.Clear(); 364 cmd.Connection = conn; 365 cmd.CommandText = "vacuum"; 366 cmd.CommandType = CommandType.Text; 367 cmd.CommandTimeout = 30; 368 cmd.ExecuteNonQuery(); 369 } 370 } 371 #endregion 整理数据库 372 }
View Code
可视化工具
分享一个可视化工具，个人感觉非常好用：
SQLiteStudio ：http://sqlitestudio.pl/
问题
在项目中使用，不可能是直接在客户端的项目中直接写ADO那套，一般都会封装SQLite调用层，在调用层中添加相关的引用，这样就会有一个问题，客户端项目直接调用，会报一个错误：
解决方法为：
在客户端项目中添加两个文件夹，内部添加SQLite.Interop.dll(从SQLite调用层的Debug中拷贝)
然后，右键两个dll，选择属性，更改输出目录为始终复制。
问题解决。
总结
SQLite在本地存储方面使用非常广泛，不得不说真的很好用。
版权声明：本文为adingfirstlove原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。