备案控制台
探索云世界
开发者社区 数据库 文章 正文

SQLiteHelper-SQLite帮助类

2017-10-04 1109
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

最近做项目用到了SQLite数据库,就自己写了个SQLite帮助类,类似于SQLHelper。

不过是按照我常用方式写的,主要与SQLHelper不同的是

1、这个帮助类并没有内置ConnectionString，是需要在调用方法的时候指定的,这样的好处的是：在一般的三层架构时都会在Helper里指定一个数据库连接,但是如果我又想用这个帮助类但是我想查询其他数据库的时候就无法使用了。

2、PrepareCommand这个方法我也修改了下，由于增删改查4个方法都用到这个方法来减少重复代码，但是我目前就在插入和更新用到事务操作，查询没有用到，但不知道这样写到底对不对，哪位看了可以帮忙给出个了比较好的解决方法？

3、由于SQLite内置了limit，给我们分页提供了很大的便利，所以我在这个帮助类里面也自己封装了分页方法。

不多说了，附代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
using  System;
using  System.Collections.Generic;
using  System.Linq;
using  System.Text;
using  System.Data;
using  System.Data.Common;
using  System.Data.SQLite;
 
namespace  Tools.Data
{
     /// <summary>
     /// 本类为SQLite数据库帮助静态类,使用时只需直接调用即可,无需实例化
     /// </summary>
     public  static  class  SQLiteHelper
     {
         #region ExecuteNonQuery
         /// <summary>
         /// 执行数据库操作(新增、更新或删除)
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="cmd">SqlCommand对象</param>
         /// <returns>所受影响的行数</returns>
         public  static  int  ExecuteNonQuery( string  connectionString, SQLiteCommand cmd)
         {
             int  result = 0;
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             using  (SQLiteConnection con = new  SQLiteConnection(connectionString))
             {
                 SQLiteTransaction trans = null ;
                 PrepareCommand(cmd, con, ref  trans, true , cmd.CommandType, cmd.CommandText);
                 try
                 {
                     result = cmd.ExecuteNonQuery();
                     trans.Commit();
                 }
                 catch  (Exception ex)
                 {
                     trans.Rollback();
                     throw  ex;
                 }
             }
             return  result;
         }
 
         /// <summary>
         /// 执行数据库操作(新增、更新或删除)
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="commandText">执行语句或存储过程名</param>
         /// <param name="commandType">执行类型</param>
         /// <returns>所受影响的行数</returns>
         public  static  int  ExecuteNonQuery( string  connectionString, string  commandText, CommandType commandType)
         {
             int  result = 0;
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             if  (commandText == null  || commandText.Length == 0)
                 throw  new  ArgumentNullException( "commandText" );
             SQLiteCommand cmd = new  SQLiteCommand();
             using  (SQLiteConnection con = new  SQLiteConnection(connectionString))
             {
                 SQLiteTransaction trans = null ;
                 PrepareCommand(cmd, con, ref  trans, true , commandType, commandText);
                 try
                 {
                     result = cmd.ExecuteNonQuery();
                     trans.Commit();
                 }
                 catch  (Exception ex)
                 {
                     trans.Rollback();
                     throw  ex;
                 }
             }
             return  result;
         }
 
         /// <summary>
         /// 执行数据库操作(新增、更新或删除)
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="commandText">执行语句或存储过程名</param>
         /// <param name="commandType">执行类型</param>
         /// <param name="cmdParms">SQL参数对象</param>
         /// <returns>所受影响的行数</returns>
         public  static  int  ExecuteNonQuery( string  connectionString, string  commandText, CommandType commandType, params  SQLiteParameter[] cmdParms)
         {
             int  result = 0;
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             if  (commandText == null  || commandText.Length == 0)
                 throw  new  ArgumentNullException( "commandText" );
 
             SQLiteCommand cmd = new  SQLiteCommand();
             using  (SQLiteConnection con = new  SQLiteConnection(connectionString))
             {
                 SQLiteTransaction trans = null ;
                 PrepareCommand(cmd, con, ref  trans, true , commandType, commandText);
                 try
                 {
                     result = cmd.ExecuteNonQuery();
                     trans.Commit();
                 }
                 catch  (Exception ex)
                 {
                     trans.Rollback();
                     throw  ex;
                 }
             }
             return  result;
         }
         #endregion
 
         #region ExecuteScalar
         /// <summary>
         /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="cmd">SqlCommand对象</param>
         /// <returns>查询所得的第1行第1列数据</returns>
         public  static  object  ExecuteScalar( string  connectionString, SQLiteCommand cmd)
         {
             object  result = 0;
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             using  (SQLiteConnection con = new  SQLiteConnection(connectionString))
             {
                 SQLiteTransaction trans = null ;
                 PrepareCommand(cmd, con, ref  trans, true , cmd.CommandType, cmd.CommandText);
                 try
                 {
                     result = cmd.ExecuteScalar();
                     trans.Commit();
                 }
                 catch  (Exception ex)
                 {
                     trans.Rollback();
                     throw  ex;
                 }
             }
             return  result;
         }
 
         /// <summary>
         /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="commandText">执行语句或存储过程名</param>
         /// <param name="commandType">执行类型</param>
         /// <returns>查询所得的第1行第1列数据</returns>
         public  static  object  ExecuteScalar( string  connectionString, string  commandText, CommandType commandType)
         {
             object  result = 0;
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             if  (commandText == null  || commandText.Length == 0)
                 throw  new  ArgumentNullException( "commandText" );
             SQLiteCommand cmd = new  SQLiteCommand();
             using  (SQLiteConnection con = new  SQLiteConnection(connectionString))
             {
                 SQLiteTransaction trans = null ;
                 PrepareCommand(cmd, con, ref  trans, true , commandType, commandText);
                 try
                 {
                     result = cmd.ExecuteScalar();
                     trans.Commit();
                 }
                 catch  (Exception ex)
                 {
                     trans.Rollback();
                     throw  ex;
                 }
             }
             return  result;
         }
 
         /// <summary>
         /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="commandText">执行语句或存储过程名</param>
         /// <param name="commandType">执行类型</param>
         /// <param name="cmdParms">SQL参数对象</param>
         /// <returns>查询所得的第1行第1列数据</returns>
         public  static  object  ExecuteScalar( string  connectionString, string  commandText, CommandType commandType, params  SQLiteParameter[] cmdParms)
         {
             object  result = 0;
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             if  (commandText == null  || commandText.Length == 0)
                 throw  new  ArgumentNullException( "commandText" );
 
             SQLiteCommand cmd = new  SQLiteCommand();
             using  (SQLiteConnection con = new  SQLiteConnection(connectionString))
             {
                 SQLiteTransaction trans = null ;
                 PrepareCommand(cmd, con, ref  trans, true , commandType, commandText);
                 try
                 {
                     result = cmd.ExecuteScalar();
                     trans.Commit();
                 }
                 catch  (Exception ex)
                 {
                     trans.Rollback();
                     throw  ex;
                 }
             }
             return  result;
         }
         #endregion
 
         #region ExecuteReader
         /// <summary>
         /// 执行数据库查询，返回SqlDataReader对象
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="cmd">SqlCommand对象</param>
         /// <returns>SqlDataReader对象</returns>
         public  static  DbDataReader ExecuteReader( string  connectionString, SQLiteCommand cmd)
         {
             DbDataReader reader = null ;
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
 
             SQLiteConnection con = new  SQLiteConnection(connectionString);
             SQLiteTransaction trans = null ;
             PrepareCommand(cmd, con, ref  trans, false , cmd.CommandType, cmd.CommandText);
             try
             {
                 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
             }
             catch  (Exception ex)
             {
                 throw  ex;
             }
             return  reader;
         }
 
         /// <summary>
         /// 执行数据库查询，返回SqlDataReader对象
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="commandText">执行语句或存储过程名</param>
         /// <param name="commandType">执行类型</param>
         /// <returns>SqlDataReader对象</returns>
         public  static  DbDataReader ExecuteReader( string  connectionString, string  commandText, CommandType commandType)
         {
             DbDataReader reader = null ;
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             if  (commandText == null  || commandText.Length == 0)
                 throw  new  ArgumentNullException( "commandText" );
 
             SQLiteConnection con = new  SQLiteConnection(connectionString);
             SQLiteCommand cmd = new  SQLiteCommand();
             SQLiteTransaction trans = null ;
             PrepareCommand(cmd, con, ref  trans, false , commandType, commandText);
             try
             {
                 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
             }
             catch  (Exception ex)
             {
                 throw  ex;
             }
             return  reader;
         }
 
         /// <summary>
         /// 执行数据库查询，返回SqlDataReader对象
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="commandText">执行语句或存储过程名</param>
         /// <param name="commandType">执行类型</param>
         /// <param name="cmdParms">SQL参数对象</param>
         /// <returns>SqlDataReader对象</returns>
         public  static  DbDataReader ExecuteReader( string  connectionString, string  commandText, CommandType commandType, params  SQLiteParameter[] cmdParms)
         {
             DbDataReader reader = null ;
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             if  (commandText == null  || commandText.Length == 0)
                 throw  new  ArgumentNullException( "commandText" );
 
             SQLiteConnection con = new  SQLiteConnection(connectionString);
             SQLiteCommand cmd = new  SQLiteCommand();
             SQLiteTransaction trans = null ;
             PrepareCommand(cmd, con, ref  trans, false , commandType, commandText, cmdParms);
             try
             {
                 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
             }
             catch  (Exception ex)
             {
                 throw  ex;
             }
             return  reader;
         }
         #endregion
 
         #region ExecuteDataSet
         /// <summary>
         /// 执行数据库查询，返回DataSet对象
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="cmd">SqlCommand对象</param>
         /// <returns>DataSet对象</returns>
         public  static  DataSet ExecuteDataSet( string  connectionString, SQLiteCommand cmd)
         {
             DataSet ds = new  DataSet();
             SQLiteConnection con = new  SQLiteConnection(connectionString);
             SQLiteTransaction trans = null ;
             PrepareCommand(cmd, con, ref  trans, false , cmd.CommandType, cmd.CommandText);
             try
             {
                 SQLiteDataAdapter sda = new  SQLiteDataAdapter(cmd);
                 sda.Fill(ds);
             }
             catch  (Exception ex)
             {
                 throw  ex;
             }
             finally
             {
                 if  (cmd.Connection != null )
                 {
                     if  (cmd.Connection.State == ConnectionState.Open)
                     {
                         cmd.Connection.Close();
                     }
                 }
             }
             return  ds;
         }
 
         /// <summary>
         /// 执行数据库查询，返回DataSet对象
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="commandText">执行语句或存储过程名</param>
         /// <param name="commandType">执行类型</param>
         /// <returns>DataSet对象</returns>
         public  static  DataSet ExecuteDataSet( string  connectionString, string  commandText, CommandType commandType)
         {
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             if  (commandText == null  || commandText.Length == 0)
                 throw  new  ArgumentNullException( "commandText" );
             DataSet ds = new  DataSet();
             SQLiteConnection con = new  SQLiteConnection(connectionString);
             SQLiteCommand cmd = new  SQLiteCommand();
             SQLiteTransaction trans = null ;
             PrepareCommand(cmd, con, ref  trans, false , commandType, commandText);
             try
             {
                 SQLiteDataAdapter sda = new  SQLiteDataAdapter(cmd);
                 sda.Fill(ds);
             }
             catch  (Exception ex)
             {
                 throw  ex;
             }
             finally
             {
                 if  (con != null )
                 {
                     if  (con.State == ConnectionState.Open)
                     {
                         con.Close();
                     }
                 }
             }
             return  ds;
         }
 
         /// <summary>
         /// 执行数据库查询，返回DataSet对象
         /// </summary>
         /// <param name="connectionString">连接字符串</param>
         /// <param name="commandText">执行语句或存储过程名</param>
         /// <param name="commandType">执行类型</param>
         /// <param name="cmdParms">SQL参数对象</param>
         /// <returns>DataSet对象</returns>
         public  static  DataSet ExecuteDataSet( string  connectionString, string  commandText, CommandType commandType, params  SQLiteParameter[] cmdParms)
         {
             if  (connectionString == null  || connectionString.Length == 0)
                 throw  new  ArgumentNullException( "connectionString" );
             if  (commandText == null  || commandText.Length == 0)
                 throw  new  ArgumentNullException( "commandText" );
             DataSet ds = new  DataSet();
             SQLiteConnection con = new  SQLiteConnection(connectionString);
             SQLiteCommand cmd = new  SQLiteCommand();
             SQLiteTransaction trans = null ;
             PrepareCommand(cmd, con, ref  trans, false , commandType, commandText, cmdParms);
             try
             {
                 SQLiteDataAdapter sda = new  SQLiteDataAdapter(cmd);
                 sda.Fill(ds);
             }
             catch  (Exception ex)
             {
                 throw  ex;
             }
             finally
             {
                 if  (con != null )
                 {
                     if  (con.State == ConnectionState.Open)
                     {
                         con.Close();
                     }
                 }
             }
             return  ds;
         }
         #endregion
 
         /// <summary>
         /// 通用分页查询方法
         /// </summary>
         /// <param name="connString">连接字符串</param>
         /// <param name="tableName">表名</param>
         /// <param name="strColumns">查询字段名</param>
         /// <param name="strWhere">where条件</param>
         /// <param name="strOrder">排序条件</param>
         /// <param name="pageSize">每页数据数量</param>
         /// <param name="currentIndex">当前页数</param>
         /// <param name="recordOut">数据总量</param>
         /// <returns>DataTable数据表</returns>
         public  static  DataTable SelectPaging( string  connString, string  tableName, string  strColumns, string  strWhere, string  strOrder, int  pageSize, int  currentIndex, out  int  recordOut)
         {
             DataTable dt = new  DataTable();
             recordOut = Convert.ToInt32(ExecuteScalar(connString, "select count(*) from "  + tableName, CommandType.Text));
             string  pagingTemplate = "select {0} from {1} where {2} order by {3} limit {4} offset {5} " ;
             int  offsetCount = (currentIndex - 1) * pageSize;
             string  commandText = String.Format(pagingTemplate, strColumns, tableName, strWhere, strOrder, pageSize.ToString(), offsetCount.ToString());
             using  (DbDataReader reader = ExecuteReader(connString, commandText, CommandType.Text))
             {
                 if  (reader != null )
                 {
                     dt.Load(reader);
                 }
             }
             return  dt;
         }
 
         /// <summary>
         /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
         /// </summary>
         /// <param name="cmd">Command对象</param>
         /// <param name="conn">Connection对象</param>
         /// <param name="trans">Transcation对象</param>
         /// <param name="useTrans">是否使用事务</param>
         /// <param name="cmdType">SQL字符串执行类型</param>
         /// <param name="cmdText">SQL Text</param>
         /// <param name="cmdParms">SQLiteParameters to use in the command</param>
         private  static  void  PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref  SQLiteTransaction trans, bool  useTrans, CommandType cmdType, string  cmdText, params  SQLiteParameter[] cmdParms)
         {
 
             if  (conn.State != ConnectionState.Open)
                 conn.Open();
 
             cmd.Connection = conn;
             cmd.CommandText = cmdText;
 
             if  (useTrans)
             {
                 trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                 cmd.Transaction = trans;
             }
 
 
             cmd.CommandType = cmdType;
 
             if  (cmdParms != null )
             {
                 foreach  (SQLiteParameter parm in  cmdParms)
                     cmd.Parameters.Add(parm);
             }
         }
     }
}

当然你也可以在这里下载：点我下载

PS:大家如果觉得好帮忙点下推荐，谢谢大家了！



本文转自kyo-yo博客园博客，原文链接：http://www.cnblogs.com/kyo-yo/archive/2010/05/14/SQLite-Helper.html，如需转载请自行联系原作者


文章标签：
数据库
存储
SQL
数据库管理
zting科技
目录
相关文章
fensnote
|
17天前
|
JSON Linux API
一个C++版本的Sqlite3封装--SmartDb
一个C++版本的Sqlite3封装--SmartDb
fensnote
14 0
特立独行的猫a
|
10月前
|
SQL 数据库 数据库管理
Qt操作Sqlite类封装，及命令行导入csv文件到Sqlite数据库
Qt操作Sqlite类封装，及命令行导入csv文件到Sqlite数据库
特立独行的猫a
114 0
流楚丶格念
|
SQL 数据库 数据库管理
sqlite3——sqlite3应用相关函数
sqlite3——sqlite3应用相关函数
流楚丶格念
228 0
跑呀跑呀
|
SQL Java 数据库
SQLite 数据库访问
SQLite是一个很轻量的数据库。详细介绍我就不多说了，这个东西应该在嵌入式里面用得比较多。根据我看到的资料（几个月以前看的），现在安卓应用应该也会用这个，HTML5 也支持sqlite。 SQLite也支持 SQL 语句。
跑呀跑呀
2039 0
技术小能手
使用 C++ 类进行封装的 SQLite3
技术小能手
5904 0
范大脚脚
|
数据库连接 数据库 C++
ODBC 、DAO 、ADO 、OLEDB 数据库连接方式区别及联系
范大脚脚
2134 0
技术小甜
|
数据库 数据安全/隐私保护 数据库管理
关于SQLite，SQLCipher和FMDB
技术小甜
1602 0
橘子红了呐
|
SQL 数据库 数据库管理
Nhibernate+SQLite 入门实例指南一
橘子红了呐
886 0
技术小胖子
|
数据库 数据库管理 C语言
sqlite学习之一 初试c调用sqlite
技术小胖子
1091 0
zting科技
|
C# 数据库管理
System.Data.SQLite类Dll下载地址和移植C# SQLite
zting科技
1061 0