前言
客户端存储信息的方法有好多种,在以往的项目中采用的是序列化记录到文件中的方式,即时通信项目中客户端的一些系统配置的保存也使用的这种方式,然而客户端保存聊天记录就不能使用这种方式(保存、读取、修改都需要进行序列化操作比较费时,不同会话聊天记录到不同文件中,将需要创建大量的文件),调研相关技术后,决定使用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();
删:
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();
改:
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();
查:
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; } }
帮助类
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 }
可视化工具
分享一个可视化工具,个人感觉非常好用:
:
问题
在项目中使用,不可能是直接在客户端的项目中直接写ado那套,一般都会封装sqlite调用层,在调用层中添加相关的引用,这样就会有一个问题,客户端项目直接调用,会报一个错误:
解决方法为:
在客户端项目中添加两个文件夹,内部添加sqlite.interop.dll(从sqlite调用层的debug中拷贝)
然后,右键两个dll,选择属性,更改输出目录为始终复制。
问题解决。
总结
sqlite在本地存储方面使用非常广泛,不得不说真的很好用。
如对本文有疑问, 点击进行留言回复!!
(精华)2020年7月21日 ASP.NET Core 容器伪属性注入
网友评论