SQLiteHelper-SQLite帮助类

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

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

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

不多说了，附代码：

001	using System;
002	using System.Collections.Generic;

003	using System.Linq;
004	using System.Text;

005	using System.Data;
006	using System.Data.Common;

007	using System.Data.SQLite;
008

009	namespace Tools.Data
010	{

011	/// <summary>
012	/// 本类为SQLite数据库帮助静态类,使用时只需直接调用即可,无需实例化

013	/// </summary>
014	public static class SQLiteHelper

015	{
016	#region ExecuteNonQuery

017	/// <summary>
018	/// 执行数据库操作(新增、更新或删除)

019	/// </summary>
020	/// <param name="connectionString">连接字符串</param>

021	/// <param name="cmd">SqlCommand对象</param>
022	/// <returns>所受影响的行数</returns>

023	public static int ExecuteNonQuery(string connectionString, SQLiteCommand cmd)
024	{

025	int result = 0;
026	if (connectionString == null || connectionString.Length == 0)

027	throw new ArgumentNullException("connectionString");
028		using (SQLiteConnection con = newSQLiteConnection(connectionString))

029	{
030	SQLiteTransaction trans = null;

031	PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);
032	try

033	{
034	result = cmd.ExecuteNonQuery();

035	trans.Commit();
036	}

037	catch (Exception ex)
038	{

039	trans.Rollback();
040	throw ex;

041	}
042	}

043	return result;
044	}

045
046	/// <summary>

047	/// 执行数据库操作(新增、更新或删除)
048	/// </summary>

049	/// <param name="connectionString">连接字符串</param>
050	/// <param name="commandText">执行语句或存储过程名</param>

051	/// <param name="commandType">执行类型</param>
052	/// <returns>所受影响的行数</returns>

053	public static int ExecuteNonQuery(string connectionString,string commandText, CommandType commandType)
054		{

055	int result = 0;
056	if (connectionString == null || connectionString.Length == 0)

057	throw new ArgumentNullException("connectionString");
058		if (commandText == null || commandText.Length == 0)

059	throw new ArgumentNullException("commandText");
060	SQLiteCommand cmd = new SQLiteCommand();

061	using (SQLiteConnection con = newSQLiteConnection(connectionString))
062	{

063	SQLiteTransaction trans = null;
064	PrepareCommand(cmd, con, ref trans, true, commandType, commandText);

065	try
066	{

067	result = cmd.ExecuteNonQuery();
068	trans.Commit();

069	}
070	catch (Exception ex)

071	{
072	trans.Rollback();

073	throw ex;
074	}

075	}
076	return result;

077	}
078

079	/// <summary>
080	/// 执行数据库操作(新增、更新或删除)

081	/// </summary>
082	/// <param name="connectionString">连接字符串</param>

083	/// <param name="commandText">执行语句或存储过程名</param>
084	/// <param name="commandType">执行类型</param>

085	/// <param name="cmdParms">SQL参数对象</param>
086	/// <returns>所受影响的行数</returns>

087	public static int ExecuteNonQuery(string connectionString,string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)
088		{

089	int result = 0;
090	if (connectionString == null || connectionString.Length == 0)

091	throw new ArgumentNullException("connectionString");
092		if (commandText == null || commandText.Length == 0)

093	throw new ArgumentNullException("commandText");
094

095	SQLiteCommand cmd = new SQLiteCommand();
096	using (SQLiteConnection con = newSQLiteConnection(connectionString))

097	{
098	SQLiteTransaction trans = null;

099	PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
100	try

101	{
102	result = cmd.ExecuteNonQuery();

103	trans.Commit();
104	}

105	catch (Exception ex)
106	{

107	trans.Rollback();
108	throw ex;

109	}
110	}

111	return result;
112	}

113	#endregion
114

115	#region ExecuteScalar
116	/// <summary>

117	/// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
118	/// </summary>

119	/// <param name="connectionString">连接字符串</param>
120	/// <param name="cmd">SqlCommand对象</param>

121		/// <returns>查询所得的第1行第1列数据</returns>
122	public static object ExecuteScalar(string connectionString, SQLiteCommand cmd)

123	{
124	object result = 0;

125		if (connectionString == null || connectionString.Length == 0)
126	throw new ArgumentNullException("connectionString");

127	using (SQLiteConnection con = newSQLiteConnection(connectionString))
128	{

129	SQLiteTransaction trans = null;
130	PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);

131	try
132	{

133	result = cmd.ExecuteScalar();
134	trans.Commit();

135	}
136	catch (Exception ex)

137	{
138	trans.Rollback();

139	throw ex;
140	}

141	}
142	return result;

143	}
144

145	/// <summary>
146	/// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据

147	/// </summary>
148	/// <param name="connectionString">连接字符串</param>

149	/// <param name="commandText">执行语句或存储过程名</param>
150	/// <param name="commandType">执行类型</param>

151		/// <returns>查询所得的第1行第1列数据</returns>
152	public static object ExecuteScalar(string connectionString,string commandText, CommandType commandType)

153	{
154	object result = 0;

155		if (connectionString == null || connectionString.Length == 0)
156	throw new ArgumentNullException("connectionString");

157	if (commandText == null || commandText.Length == 0)
158	throw new ArgumentNullException("commandText");

159	SQLiteCommand cmd = new SQLiteCommand();
160	using (SQLiteConnection con = newSQLiteConnection(connectionString))

161	{
162	SQLiteTransaction trans = null;

163	PrepareCommand(cmd, con, ref trans, true, commandType, commandText);
164	try

165	{
166	result = cmd.ExecuteScalar();

167	trans.Commit();
168	}

169	catch (Exception ex)
170	{

171	trans.Rollback();
172	throw ex;

173	}
174	}

175	return result;
176	}

177
178	/// <summary>

179	/// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
180	/// </summary>

181	/// <param name="connectionString">连接字符串</param>
182	/// <param name="commandText">执行语句或存储过程名</param>

183	/// <param name="commandType">执行类型</param>
184	/// <param name="cmdParms">SQL参数对象</param>

185		/// <returns>查询所得的第1行第1列数据</returns>
186	public static object ExecuteScalar(string connectionString,string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)

187	{
188	object result = 0;

189		if (connectionString == null || connectionString.Length == 0)
190	throw new ArgumentNullException("connectionString");

191	if (commandText == null || commandText.Length == 0)
192	throw new ArgumentNullException("commandText");

193
194	SQLiteCommand cmd = new SQLiteCommand();

195	using (SQLiteConnection con = newSQLiteConnection(connectionString))
196	{

197	SQLiteTransaction trans = null;
198	PrepareCommand(cmd, con, ref trans, true, commandType, commandText);

199	try
200	{

201	result = cmd.ExecuteScalar();
202	trans.Commit();

203	}
204	catch (Exception ex)

205	{
206	trans.Rollback();

207	throw ex;
208	}

209	}
210	return result;

211	}
212	#endregion

213
214	#region ExecuteReader

215	/// <summary>
216	/// 执行数据库查询，返回SqlDataReader对象

217	/// </summary>
218	/// <param name="connectionString">连接字符串</param>

219	/// <param name="cmd">SqlCommand对象</param>
220	/// <returns>SqlDataReader对象</returns>

221	public static DbDataReader ExecuteReader(stringconnectionString, SQLiteCommand cmd)
222	{

223	DbDataReader reader = null;
224	if (connectionString == null || connectionString.Length == 0)

225	throw new ArgumentNullException("connectionString");
226

227	SQLiteConnection con = newSQLiteConnection(connectionString);
228	SQLiteTransaction trans = null;

229	PrepareCommand(cmd, con,ref trans, false, cmd.CommandType, cmd.CommandText);
230	try

231	{
232	reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

233	}
234	catch (Exception ex)

235	{
236	throw ex;

237	}
238	return reader;

239	}
240

241	/// <summary>
242	/// 执行数据库查询，返回SqlDataReader对象

243	/// </summary>
244	/// <param name="connectionString">连接字符串</param>

245	/// <param name="commandText">执行语句或存储过程名</param>
246	/// <param name="commandType">执行类型</param>

247	/// <returns>SqlDataReader对象</returns>
248	public static DbDataReader ExecuteReader(stringconnectionString, string commandText, CommandType commandType)

249	{
250	DbDataReader reader = null;

251		if (connectionString == null || connectionString.Length == 0)
252	throw new ArgumentNullException("connectionString");

253	if (commandText == null || commandText.Length == 0)
254	throw new ArgumentNullException("commandText");

255
256	SQLiteConnection con = newSQLiteConnection(connectionString);

257	SQLiteCommand cmd = new SQLiteCommand();
258	SQLiteTransaction trans = null;

259	PrepareCommand(cmd, con, ref trans, false, commandType, commandText);
260	try

261	{
262	reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

263	}
264	catch (Exception ex)

265	{
266	throw ex;

267	}
268	return reader;

269	}
270

271	/// <summary>
272	/// 执行数据库查询，返回SqlDataReader对象

273	/// </summary>
274	/// <param name="connectionString">连接字符串</param>

275	/// <param name="commandText">执行语句或存储过程名</param>
276	/// <param name="commandType">执行类型</param>

277	/// <param name="cmdParms">SQL参数对象</param>
278	/// <returns>SqlDataReader对象</returns>

279	public static DbDataReader ExecuteReader(stringconnectionString, string commandText, CommandType commandType, paramsSQLiteParameter[] cmdParms)
280	{

281	DbDataReader reader = null;
282	if (connectionString == null || connectionString.Length == 0)

283	throw new ArgumentNullException("connectionString");
284		if (commandText == null || commandText.Length == 0)

285	throw new ArgumentNullException("commandText");
286

287	SQLiteConnection con = newSQLiteConnection(connectionString);
288	SQLiteCommand cmd = new SQLiteCommand();

289	SQLiteTransaction trans = null;
290	PrepareCommand(cmd, con, ref trans, false, commandType, commandText, cmdParms);

291	try
292	{

293	reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
294	}

295	catch (Exception ex)
296	{

297	throw ex;
298	}

299	return reader;
300	}

301	#endregion
302

303	#region ExecuteDataSet
304	/// <summary>

305	/// 执行数据库查询，返回DataSet对象
306	/// </summary>

307	/// <param name="connectionString">连接字符串</param>
308	/// <param name="cmd">SqlCommand对象</param>

309	/// <returns>DataSet对象</returns>
310	public static DataSet ExecuteDataSet(string connectionString, SQLiteCommand cmd)

311	{
312	DataSet ds = new DataSet();

313	SQLiteConnection con = newSQLiteConnection(connectionString);
314	SQLiteTransaction trans = null;

315	PrepareCommand(cmd, con, ref trans, false, cmd.CommandType, cmd.CommandText);
316	try

317	{
318	SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);

319	sda.Fill(ds);
320	}

321	catch (Exception ex)
322	{

323	throw ex;
324	}

325	finally
326	{

327	if (cmd.Connection != null)
328	{

329	if (cmd.Connection.State == ConnectionState.Open)
330	{

331	cmd.Connection.Close();
332	}

333	}
334	}

335	return ds;
336	}

337
338	/// <summary>

339	/// 执行数据库查询，返回DataSet对象
340	/// </summary>

341	/// <param name="connectionString">连接字符串</param>
342	/// <param name="commandText">执行语句或存储过程名</param>

343	/// <param name="commandType">执行类型</param>
344	/// <returns>DataSet对象</returns>

345	public static DataSet ExecuteDataSet(string connectionString,string commandText, CommandType commandType)
346	{

347		if (connectionString == null || connectionString.Length == 0)
348	throw new ArgumentNullException("connectionString");

349	if (commandText == null || commandText.Length == 0)
350	throw new ArgumentNullException("commandText");

351	DataSet ds = new DataSet();
352	SQLiteConnection con = newSQLiteConnection(connectionString);

353	SQLiteCommand cmd = new SQLiteCommand();
354	SQLiteTransaction trans = null;

355	PrepareCommand(cmd, con, ref trans, false, commandType, commandText);
356	try

357	{
358	SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);

359	sda.Fill(ds);
360	}

361	catch (Exception ex)
362	{

363	throw ex;
364	}

365	finally
366	{

367	if (con != null)
368	{

369	if (con.State == ConnectionState.Open)
370	{

371	con.Close();
372	}

373	}
374	}

375	return ds;
376	}

377
378	/// <summary>

379	/// 执行数据库查询，返回DataSet对象
380	/// </summary>

381	/// <param name="connectionString">连接字符串</param>
382	/// <param name="commandText">执行语句或存储过程名</param>

383	/// <param name="commandType">执行类型</param>
384	/// <param name="cmdParms">SQL参数对象</param>

385	/// <returns>DataSet对象</returns>
386	public static DataSet ExecuteDataSet(string connectionString,string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)

387	{
388	if (connectionString == null || connectionString.Length == 0)

389	throw new ArgumentNullException("connectionString");
390		if (commandText == null || commandText.Length == 0)

391	throw new ArgumentNullException("commandText");
392	DataSet ds = new DataSet();

393	SQLiteConnection con = newSQLiteConnection(connectionString);
394	SQLiteCommand cmd = new SQLiteCommand();

395	SQLiteTransaction trans = null;
396	PrepareCommand(cmd, con, ref trans, false, commandType, commandText, cmdParms);

397	try
398	{

399	SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
400	sda.Fill(ds);

401	}
402	catch (Exception ex)

403	{
404	throw ex;

405	}
406	finally

407	{
408	if (con != null)

409	{
410	if (con.State == ConnectionState.Open)

411	{
412	con.Close();

413	}
414	}

415	}
416	return ds;

417	}
418	#endregion

419
420	/// <summary>

421	/// 通用分页查询方法
422	/// </summary>

423	/// <param name="connString">连接字符串</param>
424	/// <param name="tableName">表名</param>

425	/// <param name="strColumns">查询字段名</param>
426	/// <param name="strWhere">where条件</param>

427	/// <param name="strOrder">排序条件</param>
428	/// <param name="pageSize">每页数据数量</param>

429	/// <param name="currentIndex">当前页数</param>
430	/// <param name="recordOut">数据总量</param>

431		/// <returns>DataTable数据表</returns>
432	public static DataTable SelectPaging(string connString, stringtableName, string strColumns, string strWhere, string strOrder, intpageSize, int currentIndex, out int recordOut)

433	{
434	DataTable dt = new DataTable();

435	recordOut = Convert.ToInt32(ExecuteScalar(connString,"select count(*) from " + tableName, CommandType.Text));
436	string pagingTemplate = "select {0} from {1} where {2} order by {3} limit {4} offset {5} ";

437	int offsetCount = (currentIndex - 1) * pageSize;
438	string commandText = String.Format(pagingTemplate, strColumns, tableName, strWhere, strOrder, pageSize.ToString(), offsetCount.ToString());

439	using (DbDataReader reader = ExecuteReader(connString, commandText, CommandType.Text))
440	{

441	if (reader != null)
442	{

443	dt.Load(reader);
444	}

445	}
446	return dt;

447	}
448

449	/// <summary>
450	/// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化

451	/// </summary>
452	/// <param name="cmd">Command对象</param>

453	/// <param name="conn">Connection对象</param>
454	/// <param name="trans">Transcation对象</param>

455	/// <param name="useTrans">是否使用事务</param>
456	/// <param name="cmdType">SQL字符串执行类型</param>

457	/// <param name="cmdText">SQL Text</param>
458	/// <param name="cmdParms">SQLiteParameters to use in the command</param>

459	private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms)
460	{

461
462	if (conn.State != ConnectionState.Open)

463	conn.Open();
464

465	cmd.Connection = conn;
466	cmd.CommandText = cmdText;

467
468	if (useTrans)

469	{
470	trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

471	cmd.Transaction = trans;
472	}

473
474

475	cmd.CommandType = cmdType;
476

477	if (cmdParms != null)
478	{

479	foreach (SQLiteParameter parm in cmdParms)
480	cmd.Parameters.Add(parm);

481	}
482	}

483	}
484	}

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