SQLite Helper 帮助类
View Code
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
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data.SQLite;// 6 using System.Data;// 7 using System.IO;// 8 using System.Data.Common;// 9 using System.Configuration;// 10 11 namespace SQLiteHelper 12 { 13 public class SQLiteHelper41 14 { 15 #region http://www.cnblogs.com/bytime/archive/2011/10/27/2226004.html 16 private string connStr = ""; 17 public SQLiteHelper41(string dbName, bool isFullPath) 18 { 19 if (isFullPath) 20 { 21 connStr = @"Data Source=" + dbName + ";Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10"; 22 } 23 else 24 { 25 connStr = @"Data Source=" + System.Environment.CurrentDirectory + "\\" + dbName + ";Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10"; 26 } 27 } 28 29 /// <summary> 30 /// 功能: 创建数据库，带路径 31 /// </summary> 32 /// <param name="dbName"></param> 33 /// <param name="isFullPath"></param> 34 public void CreateDB(string dbName, bool isFullPath) 35 { 36 if (isFullPath) 37 { 38 if (!File.Exists(dbName)) 39 { 40 SQLiteConnection.CreateFile(dbName); 41 } 42 } 43 else 44 { 45 if (!File.Exists(System.Environment.CurrentDirectory + "\\" + dbName)) 46 { 47 SQLiteConnection.CreateFile(System.Environment.CurrentDirectory + "\\" + dbName); 48 } 49 } 50 } 51 52 /// <summary> 53 /// 功能: 执行sql，不返回 54 /// </summary> 55 /// <param name="sqlStr">要执行的sql</param> 56 public void ExecuteSql(string sqlStr) 57 { 58 using (DbConnection conn = new SQLiteConnection(connStr)) 59 { 60 conn.Open(); 61 DbCommand comm = conn.CreateCommand(); 62 comm.CommandText = sqlStr; 63 comm.CommandType = CommandType.Text; 64 comm.ExecuteNonQuery(); 65 } 66 } 67 68 /// <summary> 69 /// 功能: 执行sql语句数组 70 /// </summary> 71 /// <param name="sqlStr"></param> 72 public void ExecuteSqlList(string[] sqlStr) 73 { 74 using (DbConnection conn = new SQLiteConnection(connStr)) 75 { 76 conn.Open(); 77 DbCommand comm = conn.CreateCommand(); 78 foreach (string item in sqlStr) 79 { 80 comm.CommandText = item; 81 comm.CommandType = CommandType.Text; 82 comm.ExecuteNonQuery(); 83 } 84 } 85 } 86 87 /// <summary> 88 /// 功能: 执行sql返回deteset 89 /// </summary> 90 /// <param name="sqlStr"></param> 91 /// <returns></returns> 92 public DataSet ExecDataSet(string sqlStr) 93 { 94 using (SQLiteConnection conn = new SQLiteConnection(connStr)) 95 { 96 conn.Open(); 97 SQLiteCommand cmd = conn.CreateCommand(); 98 cmd.CommandText = sqlStr; 99 cmd.CommandType = CommandType.Text; 100 101 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); 102 DataSet ds = new DataSet(); 103 da.Fill(ds); 104 105 return ds; 106 } 107 } 108 109 /// <summary> 110 /// 功能: 判断表是否存在 111 /// </summary> 112 /// <param name="tableName"></param> 113 /// <returns>存在不存在</returns> 114 public bool IsTableExist(string tableName) 115 { 116 using (SQLiteConnection connection = new SQLiteConnection(connStr)) 117 { 118 connection.Open(); 119 using (SQLiteCommand command = new SQLiteCommand(connection)) 120 { 121 122 command.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + tableName + "'"; 123 int iaaa = Convert.ToInt32(command.ExecuteScalar()); 124 if (Convert.ToInt32(command.ExecuteScalar()) == 0) 125 { 126 return false; 127 } 128 else 129 { 130 return true; 131 } 132 } 133 } 134 } 135 #endregion 136 137 #region http://www.cnblogs.com/gym_sky/archive/2010/07/29/1788268.html 138 139 /// <summary> 140 /// 获得连接对象 141 /// </summary> 142 /// <returns></returns> 143 public static SQLiteConnection GetSQLiteConnection() 144 { 145 return new SQLiteConnection("Data Source=" + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["db"].ToString())); 146 } 147 148 private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p) 149 { 150 if (conn.State != ConnectionState.Open) 151 conn.Open(); 152 cmd.Parameters.Clear(); 153 cmd.Connection = conn; 154 cmd.CommandText = cmdText; 155 cmd.CommandType = CommandType.Text; 156 cmd.CommandTimeout = 30; 157 if (p != null) 158 { 159 foreach (object parm in p) 160 cmd.Parameters.AddWithValue(string.Empty, parm); 161 //for (int i = 0; i < p.Length; i++) 162 // cmd.Parameters[i].Value = p[i]; 163 } 164 } 165 166 public static DataSet ExecuteDataset(string cmdText, params object[] p) 167 { 168 DataSet ds = new DataSet(); 169 SQLiteCommand command = new SQLiteCommand(); 170 using (SQLiteConnection connection = GetSQLiteConnection()) 171 { 172 PrepareCommand(command, connection, cmdText, p); 173 SQLiteDataAdapter da = new SQLiteDataAdapter(command); 174 da.Fill(ds); 175 } 176 return ds; 177 } 178 179 public static DataRow ExecuteDataRow(string cmdText, params object[] p) 180 { 181 DataSet ds = ExecuteDataset(cmdText, p); 182 if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) 183 return ds.Tables[0].Rows[0]; 184 return null; 185 } 186 187 /// <summary> 188 /// 返回受影响的行数 189 /// </summary> 190 /// <param name="cmdText">a</param> 191 /// <param name="commandParameters">传入的参数</param> 192 /// <returns></returns> 193 public static int ExecuteNonQuery(string cmdText, params object[] p) 194 { 195 SQLiteCommand command = new SQLiteCommand(); 196 using (SQLiteConnection connection = GetSQLiteConnection()) 197 { 198 PrepareCommand(command, connection, cmdText, p); 199 return command.ExecuteNonQuery(); 200 } 201 } 202 203 /// <summary> 204 /// 返回SqlDataReader对象 205 /// </summary> 206 /// <param name="cmdText"></param> 207 /// <param name="commandParameters">传入的参数</param> 208 /// <returns></returns> 209 public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p) 210 { 211 SQLiteCommand command = new SQLiteCommand(); 212 SQLiteConnection connection = GetSQLiteConnection(); 213 try 214 { 215 PrepareCommand(command, connection, cmdText, p); 216 SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 217 return reader; 218 } 219 catch 220 { 221 connection.Close(); 222 throw; 223 } 224 } 225 226 /// <summary> 227 /// 返回结果集中的第一行第一列，忽略其他行或列 228 /// </summary> 229 /// <param name="cmdText"></param> 230 /// <param name="commandParameters">传入的参数</param> 231 /// <returns></returns> 232 public static object ExecuteScalar(string cmdText, params object[] p) 233 { 234 SQLiteCommand cmd = new SQLiteCommand(); 235 using (SQLiteConnection connection = GetSQLiteConnection()) 236 { 237 PrepareCommand(cmd, connection, cmdText, p); 238 return cmd.ExecuteScalar(); 239 } 240 } 241 242 /// <summary> 243 /// 分页 244 /// </summary> 245 /// <param name="recordCount"></param> 246 /// <param name="pageIndex"></param> 247 /// <param name="pageSize"></param> 248 /// <param name="cmdText"></param> 249 /// <param name="countText"></param> 250 /// <param name="p"></param> 251 /// <returns></returns> 252 public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params object[] p) 253 { 254 if (recordCount < 0) 255 recordCount = int.Parse(ExecuteScalar(countText, p).ToString()); 256 DataSet ds = new DataSet(); 257 SQLiteCommand command = new SQLiteCommand(); 258 using (SQLiteConnection connection = GetSQLiteConnection()) 259 { 260 PrepareCommand(command, connection, cmdText, p); 261 SQLiteDataAdapter da = new SQLiteDataAdapter(command); 262 da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result"); 263 } 264 return ds; 265 } 266 #endregion 267 } 268 }
View Code
1 using System; 2 using System.Collections.Generic; 3 using System.IO; 4 using System.Text.RegularExpressions; 5 using System.Threading; 6 using System.Data.SQLite; 7 using System.Data; 8 9 namespace CMS.DBUtility 10 { 11 12 public class SQLiteHelper : IDisposable 13 { 14 private SQLiteConnection _connection; 15 private string _dataSource = string.Empty; 16 private static SQLiteHelper _instance = new SQLiteHelper(); 17 private bool _isFirstUse; 18 private Dictionary<int, SQLiteTransaction> _localTransactionCollection; 19 private static object _locker = new object(); 20 private string _password = string.Empty; 21 private static int _refCount = 0; 22 private const string DATABASE_NAME = "FXRobot.dat"; 23 24 private SQLiteHelper() 25 { 26 27 } 28 29 public void CommitTransaction() 30 { 31 lock (_locker) 32 { 33 int managedThreadId = Thread.CurrentThread.ManagedThreadId; 34 if (this.LocalTransactionCollection.ContainsKey(managedThreadId)) 35 { 36 this.LocalTransactionCollection[managedThreadId].Commit(); 37 _refCount--; 38 this.LocalTransactionCollection.Remove(managedThreadId); 39 if (_refCount == 0) 40 { 41 this._connection.Close(); 42 } 43 } 44 } 45 } 46 47 public SQLiteCommand CreateCommand(string sql, params object[] parameters) 48 { 49 SQLiteCommand command = null; 50 int managedThreadId = Thread.CurrentThread.ManagedThreadId; 51 if (this.LocalTransactionCollection.ContainsKey(managedThreadId) && (this.LocalTransactionCollection[managedThreadId] != null)) 52 { 53 command = new SQLiteCommand(sql, this._connection, this.LocalTransactionCollection[managedThreadId]); 54 } 55 else 56 { 57 command = new SQLiteCommand(sql, this._connection); 58 } 59 if (parameters != null) 60 { 61 foreach (SQLiteParameter parameter in this.DeriveParameters(sql, parameters)) 62 { 63 command.Parameters.Add(parameter); 64 } 65 } 66 return command; 67 } 68 69 public List<SQLiteParameter> DeriveParameters(string commandText, object[] paramList) 70 { 71 if (paramList == null) 72 { 73 return null; 74 } 75 List<SQLiteParameter> list = new List<SQLiteParameter>(); 76 string input = commandText.Substring(commandText.IndexOf("@")).Replace(",", " ,").Replace(")", " )"); 77 string pattern = @"(@)\S*(.*?)\b"; 78 MatchCollection matchs = new Regex(pattern, RegexOptions.IgnoreCase).Matches(input); 79 List<string> list2 = new List<string>(); 80 foreach (Match match in matchs) 81 { 82 if (!list2.Contains(match.Value)) 83 { 84 list2.Add(match.Value); 85 } 86 } 87 string[] strArray = list2.ToArray(); 88 int index = 0; 89 Type type = null; 90 foreach (object obj2 in paramList) 91 { 92 if (obj2 == null) 93 { 94 SQLiteParameter item = new SQLiteParameter(); 95 item.DbType = DbType.Object; 96 item.ParameterName = strArray[index]; 97 item.Value = DBNull.Value; 98 list.Add(item); 99 } 100 else 101 { 102 type = obj2.GetType(); 103 SQLiteParameter parameter2 = new SQLiteParameter(); 104 switch (type.ToString()) 105 { 106 case "System.String": 107 parameter2.DbType = DbType.String; 108 parameter2.ParameterName = strArray[index]; 109 parameter2.Value = (string)paramList[index]; 110 list.Add(parameter2); 111 goto Label_0408; 112 113 case "System.Byte[]": 114 parameter2.DbType = DbType.Binary; 115 parameter2.ParameterName = strArray[index]; 116 parameter2.Value = (byte[])paramList[index]; 117 list.Add(parameter2); 118 goto Label_0408; 119 120 case "System.Int64": 121 parameter2.DbType = DbType.Int64; 122 parameter2.ParameterName = strArray[index]; 123 parameter2.Value = (long)paramList[index]; 124 list.Add(parameter2); 125 goto Label_0408; 126 127 case "System.Int32": 128 parameter2.DbType = DbType.Int32; 129 parameter2.ParameterName = strArray[index]; 130 parameter2.Value = (int)paramList[index]; 131 list.Add(parameter2); 132 goto Label_0408; 133 134 case "System.Boolean": 135 parameter2.DbType = DbType.Boolean; 136 parameter2.ParameterName = strArray[index]; 137 parameter2.Value = (bool)paramList[index]; 138 list.Add(parameter2); 139 goto Label_0408; 140 141 case "System.DateTime": 142 parameter2.DbType = DbType.DateTime; 143 parameter2.ParameterName = strArray[index]; 144 parameter2.Value = Convert.ToDateTime(paramList[index]); 145 list.Add(parameter2); 146 goto Label_0408; 147 148 case "System.Double": 149 parameter2.DbType = DbType.Double; 150 parameter2.ParameterName = strArray[index]; 151 parameter2.Value = Convert.ToDouble(paramList[index]); 152 list.Add(parameter2); 153 goto Label_0408; 154 155 case "System.Decimal": 156 parameter2.DbType = DbType.Decimal; 157 parameter2.ParameterName = strArray[index]; 158 parameter2.Value = Convert.ToDecimal(paramList[index]); 159 goto Label_0408; 160 161 case "System.Guid": 162 parameter2.DbType = DbType.Guid; 163 parameter2.ParameterName = strArray[index]; 164 parameter2.Value = (Guid)paramList[index]; 165 goto Label_0408; 166 167 case "System.Object": 168 parameter2.DbType = DbType.Object; 169 parameter2.ParameterName = strArray[index]; 170 parameter2.Value = paramList[index]; 171 list.Add(parameter2); 172 goto Label_0408; 173 } 174 throw new SystemException("Value is of unknown data type"); 175 } 176 Label_0408: 177 index++; 178 } 179 return list; 180 } 181 182 public void Dispose() 183 { 184 this.Dispose(true); 185 GC.SuppressFinalize(this); 186 } 187 188 protected void Dispose(bool disposed) 189 { 190 try 191 { 192 if (disposed) 193 { 194 if (this._localTransactionCollection != null) 195 { 196 lock (_locker) 197 { 198 foreach (SQLiteTransaction transaction in this._localTransactionCollection.Values) 199 { 200 try 201 { 202 transaction.Rollback(); 203 transaction.Dispose(); 204 continue; 205 } 206 catch 207 { 208 continue; 209 } 210 } 211 this._localTransactionCollection.Clear(); 212 this._localTransactionCollection = null; 213 } 214 } 215 if (this._connection != null) 216 { 217 this._connection.Close(); 218 this._connection.Dispose(); 219 } 220 } 221 } 222 catch 223 { 224 } 225 finally 226 { 227 this._connection = null; 228 } 229 } 230 231 public void EnableConnection() 232 { 233 234 if (this._connection == null) 235 { 236 // string connectionString = string.Format("Data Source={0};Password={1}", this._dataSource, this._password); 237 this._connection = new SQLiteConnection(ConnectionString); 238 if (!string.IsNullOrEmpty(this._password)) 239 this._connection.SetPassword(this._password); 240 241 } 242 if (this._connection.State == ConnectionState.Closed) 243 { 244 this._connection.Open(); 245 246 } 247 } 248 249 public int ExecuteNonQuery(string sql) 250 { 251 return ExecuteNonQuery(sql, null); 252 } 253 254 public int ExecuteNonQuery(string sql, params object[] parameters) 255 { 256 this.EnableConnection(); 257 return this.CreateCommand(sql, parameters).ExecuteNonQuery(); 258 } 259 260 public SQLiteDataReader ExecuteReader(string sql, params object[] parameters) 261 { 262 this.EnableConnection(); 263 return this.CreateCommand(sql, parameters).ExecuteReader(); 264 } 265 public int ExecuteScalar(string sql) 266 { 267 return ExecuteScalar(sql, null); 268 } 269 270 public int ExecuteScalar(string sql, params object[] parameters) 271 { 272 this.EnableConnection(); 273 object obj2 = this.CreateCommand(sql, parameters).ExecuteScalar(); 274 if (obj2 == DBNull.Value) 275 return 1; 276 if (obj2 != null) 277 { 278 return int.Parse(obj2.ToString()); 279 } 280 return 1; 281 } 282 283 public object GetSingle(string SQLString, params object[] cmdParms) 284 { 285 this.EnableConnection(); 286 return this.CreateCommand(SQLString, cmdParms).ExecuteScalar(); 287 } 288 289 public object GetSingle(string SQLString) 290 { 291 return GetSingle(SQLString, null); 292 } 293 public DataTable GetDataTable(string sql) 294 { 295 return GetDataTable(sql, null); 296 } 297 298 public DataTable GetDataTable(string sql, params object[] parameters) 299 { 300 //this.EnableConnection(); 301 302 //DataTable dt = new DataTable(); 303 //SQLiteDataReader reader = this.ExecuteReader(sql, parameters); 304 //dt.Load(reader); 305 //reader.Close(); 306 //return dt; 307 308 309 this.EnableConnection(); 310 311 SQLiteCommand cmd = new SQLiteCommand(); 312 cmd = this.CreateCommand(sql, parameters); 313 //create the DataAdapter & DataSet 314 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); 315 DataSet ds = new DataSet(); 316 317 //fill the DataSet using default values for DataTable names, etc. 318 //da.FillSchema(ds, SchemaType.Source); 319 da.Fill(ds); 320 if (ds.Tables.Count == 0) 321 { 322 da.FillSchema(ds, SchemaType.Source); 323 } 324 cmd.Parameters.Clear(); 325 return ds.Tables[0]; 326 } 327 328 public bool Exists(string strSql) 329 { 330 return Exists(strSql, null); 331 } 332 333 public bool Exists(string strSql, params object[] cmdParms) 334 { 335 int cmdresult = ExecuteScalar(strSql, cmdParms); 336 337 if (cmdresult == 0) 338 { 339 return false; 340 } 341 else 342 { 343 return true; 344 } 345 } 346 347 public int GetMaxID(string FieldName, string TableName) 348 { 349 string strsql = "select max(" + FieldName + ")+1 from " + TableName; 350 351 return ExecuteScalar(strsql); 352 } 353 354 ~SQLiteHelper() 355 { 356 this.Dispose(false); 357 } 358 359 public string ConnectionString 360 { 361 get 362 { 363 return SetConnectionString(); 364 } 365 } 366 367 protected string SetConnectionString() 368 { 369 string m_strTempConnectionString = PubConstant.ConnectionString; 370 if (string.IsNullOrEmpty(m_strTempConnectionString)) 371 throw new System.Exception("未配置数据库连接字符串!"); 372 string[] m_strConnStr = m_strTempConnectionString.Split(';'); 373 foreach (string str in m_strConnStr) 374 { 375 string[] m_strs = str.Split('='); 376 if (m_strs[0] == "Data Source") 377 this._dataSource = m_strs[1]; 378 if (m_strs[0] == "Password") 379 this._password = m_strs[1]; 380 } 381 if (string.IsNullOrEmpty(this._dataSource)) 382 throw new System.Exception("未配置数据库连接字符串的Data Source的值!"); 383 this._dataSource = Path.Combine(CfgSystemDirectory, this._dataSource); 384 if(!string.IsNullOrEmpty(this._password)) 385 return string.Format("Data Source={0};Password={1}", this._dataSource, this._password); 386 else 387 return string.Format("Data Source={0}", this._dataSource); 388 389 } 390 391 /// <summary> 392 /// 服务运行的目录,程序运行的目录,Web运行的目录 393 /// </summary> 394 public string CfgSystemDirectory 395 { 396 get 397 { 398 string m_CfgSystemDirectory = string.Empty; 399 400 if (CheckWhetherIsWeb()) 401 m_CfgSystemDirectory = System.Web.HttpContext.Current.Server.MapPath("~/"); 402 else 403 { 404 string m_strLine = @"\"; 405 string path = System.Reflection.Assembly.GetExecutingAssembly().Location; 406 path = path.Substring(0, path.LastIndexOf(m_strLine)); 407 408 m_CfgSystemDirectory = path; 409 } 410 return m_CfgSystemDirectory; 411 } 412 } 413 /// <summary> 414 /// true:Web Form ; false:非非Web Form方式 415 /// </summary> 416 /// <returns></returns> 417 private static bool CheckWhetherIsWeb() 418 { 419 bool result = false; 420 AppDomain domain = AppDomain.CurrentDomain; 421 try 422 { 423 if (domain.ShadowCopyFiles) 424 result = (System.Web.HttpContext.Current.GetType() != null); 425 } 426 catch (System.Exception) { } 427 return result; 428 } 429 430 public bool InitializeDatabase(string currentUserSid,ref string p_strMsg) 431 { 432 bool flag; 433 lock (_locker) 434 { 435 if (!this.Disposed) 436 { 437 this.Dispose(); 438 } 439 #region 以前的 440 //string app = System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); 441 //app = Path.Combine(app, "Fetion"); 442 //string path = Path.Combine(app, currentUserSid); 443 //if (!Directory.Exists(path)) 444 //{ 445 // Directory.CreateDirectory(path); 446 //} 447 //this._dataSource = Path.Combine(path, DATABASE_NAME); 448 //this._password = currentUserSid; 449 450 //this._localTransactionCollection = new Dictionary<int, SQLiteTransaction>(); 451 //try 452 //{ 453 // if (!File.Exists(this._dataSource)) 454 // { 455 // SQLiteConnection.CreateFile(this._dataSource); 456 // string connectionString = string.Format("Data Source={0};Password={1}", this._dataSource, this._password); 457 // this._connection = new SQLiteConnection(connectionString); 458 // this._connection.SetPassword(this._password); 459 // } 460 // flag = true; 461 //} 462 //catch 463 //{ 464 // this.Dispose(); 465 // File.Delete(this._dataSource); 466 // flag = false; 467 //} 468 #endregion 469 470 this._localTransactionCollection = new Dictionary<int, SQLiteTransaction>(); 471 try 472 { 473 if (!File.Exists(this._dataSource)) 474 { 475 this._connection = new SQLiteConnection(ConnectionString); 476 SQLiteConnection.CreateFile(this._dataSource); 477 if (!string.IsNullOrEmpty(this._password)) 478 this._connection.SetPassword(this._password); 479 } 480 flag = true; 481 } 482 catch(System.Exception ex) 483 { 484 this.Dispose(); 485 if (File.Exists(this._dataSource)) 486 File.Delete(this._dataSource); 487 p_strMsg = ex.Message; 488 flag = false; 489 } 490 } 491 return flag; 492 } 493 494 public void JoinTransaction() 495 { 496 lock (_locker) 497 { 498 this.EnableConnection(); 499 _refCount++; 500 int managedThreadId = Thread.CurrentThread.ManagedThreadId; 501 if (!this.LocalTransactionCollection.ContainsKey(managedThreadId)) 502 { 503 this.LocalTransactionCollection.Add(managedThreadId, this._connection.BeginTransaction()); 504 } 505 } 506 } 507 508 public void RollbackTransaction() 509 { 510 lock (_locker) 511 { 512 int managedThreadId = Thread.CurrentThread.ManagedThreadId; 513 if (this.LocalTransactionCollection.ContainsKey(managedThreadId)) 514 { 515 this.LocalTransactionCollection[managedThreadId].Rollback(); 516 _refCount--; 517 this.LocalTransactionCollection.Remove(managedThreadId); 518 if (_refCount == 0) 519 { 520 this._connection.Close(); 521 } 522 } 523 } 524 } 525 526 public bool Disposed 527 { 528 get 529 { 530 return (this._connection != null); 531 } 532 } 533 534 public static SQLiteHelper Instance 535 { 536 get 537 { 538 return _instance; 539 } 540 } 541 542 public bool IsFirstUse 543 { 544 get 545 { 546 return this._isFirstUse; 547 } 548 } 549 550 private Dictionary<int, SQLiteTransaction> LocalTransactionCollection 551 { 552 get 553 { 554 lock (_locker) 555 { 556 if (this._localTransactionCollection == null) 557 { 558 this._localTransactionCollection = new Dictionary<int, SQLiteTransaction>(); 559 } 560 return this._localTransactionCollection; 561 } 562 } 563 } 564 565 public List<string> Objects 566 { 567 get 568 { 569 lock (_locker) 570 { 571 List<string> list = new List<string>(); 572 using (SQLiteDataReader reader = this.ExecuteReader("SELECT [Name] FROM [SQLITE_MASTER] WHERE ([type] = 'table') OR ([type] = 'view')", null)) 573 { 574 while (reader.Read()) 575 { 576 list.Add(reader["name"].ToString()); 577 } 578 } 579 return list; 580 } 581 } 582 } 583 } 584 }
