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