SQLiteHelper-SQLite帮助类
最近做项目用到了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://kyo-yo.cnblogs.com
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权
出处: http://kyo-yo.cnblogs.com
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权