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 }

posted on 2012-06-25 06:43 xqghp 阅读(876) 评论(0) 收藏举报

刷新页面返回顶部

SQLite Helper 帮助类

导航

公告