SQLiteHelperSQLite帮助类

最近做项目用到了SQLite数据库,就自己写了个SQLite帮助类,类似于SQLHelper。
不过是按照我常用方式写的,主要与SQLHelper不同的是
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 = new SQLiteConnection(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 = new SQLiteConnection(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 = new SQLiteConnection(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 = new SQLiteConnection(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 = new SQLiteConnection(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 = new SQLiteConnection(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(string connectionString, SQLiteCommand cmd) 

222
        { 

223
            DbDataReader reader = null; 

224
            if (connectionString == null || connectionString.Length == 0) 

225
                throw new ArgumentNullException("connectionString"); 

226
  
227
            SQLiteConnection con = new SQLiteConnection(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(string connectionString, 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 = new SQLiteConnection(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(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] 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 = new SQLiteConnection(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 = new SQLiteConnection(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 = new SQLiteConnection(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 = new SQLiteConnection(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, string tableName, string strColumns, string strWhere, string strOrder, int pageSize, 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
}
当然你也可以在这里下载：点我下载
PS:大家如果觉得好帮忙点下推荐，谢谢大家了！
作者：kyo-yo
出处：http://kyo-yo.cnblogs.com
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。