SQLiteHelper

本文定义了一个DBHelper类,是SQLite的。需要添加一个System.Data.SQLite的dll,可以到SQLite的官网下载。类的代码如下

  1 using System;
  2 using System.Data;
  3 using System.Data.SQLite;
  4 using System.Configuration;
  5 using System.Collections;
  6 
  7 namespace Common
  8 {
  9     public abstract class SQLiteHelper
 10     {
 11         //Data Source=db file fullname
 12         public static readonly string connectionstring = ConfigurationManager.AppSettings["connectionstring"];
 13 
 14         private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
 15 
 16         public static int ExecuteNonQuery(string connectionstring, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
 17         {
 18             SQLiteCommand cmd = new SQLiteCommand();
 19             using (SQLiteConnection cn=new SQLiteConnection(connectionstring))
 20             {
 21                 PrepareCommand(cmd, cn, null, commandType, commandText, commandParameters);
 22                 int result = cmd.ExecuteNonQuery();
 23                 cmd.Parameters.Clear();
 24                 return result;
 25             }
 26         }
 27 
 28         public static int ExecuteNonQuery(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
 29         {
 30             SQLiteCommand cmd = new SQLiteCommand();
 31 
 32             PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters);
 33             int result = cmd.ExecuteNonQuery();
 34             cmd.Parameters.Clear();
 35             return result;
 36         }
 37 
 38         public static int ExecuteNonQuery(SQLiteTransaction trans, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
 39         {
 40             SQLiteCommand cmd = new SQLiteCommand();
 41 
 42             PrepareCommand(cmd, trans.Connection, trans, commandType, commandText, commandParameters);
 43             int result = cmd.ExecuteNonQuery();
 44             cmd.Parameters.Clear();
 45             return result;
 46         }
 47 
 48         public static SQLiteDataReader ExecuteReader(string connectionstring,CommandType commandType,string commandText,params SQLiteParameter [] commandParameters)
 49         {
 50             SQLiteCommand cmd = new SQLiteCommand();
 51             SQLiteConnection cn=new SQLiteConnection(connectionstring);
 52             
 53             try
 54             {
 55                 PrepareCommand(cmd, cn, null, commandType, commandText, commandParameters);
 56                 SQLiteDataReader result = null;
 57                 result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 58                 cmd.Parameters.Clear();
 59                 return result;
 60             }
 61             catch 
 62             {
 63                 cn.Close();
 64                 throw;
 65             }
 66             
 67         }
 68 
 69         public static object ExecuteScalar(string connectionstring, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
 70         {
 71             SQLiteCommand cmd = new SQLiteCommand();
 72 
 73             using (SQLiteConnection cn=new SQLiteConnection(connectionstring))
 74             {
 75                 PrepareCommand(cmd, cn, null, commandType, commandText, commandParameters);
 76                 object result = cmd.ExecuteScalar();
 77                 cmd.Parameters.Clear();
 78                 return result;
 79             }
 80         }
 81 
 82         public static object ExecuteScalar(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
 83         {
 84             SQLiteCommand cmd = new SQLiteCommand();
 85 
 86             PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters);
 87             object result = cmd.ExecuteScalar();
 88             cmd.Parameters.Clear();
 89             return result;
 90         }
 91 
 92         public static void CacheParameters(string cacheKey, params SQLiteParameter[] commandParameters)
 93         {
 94             parmCache[cacheKey] = commandParameters;
 95         }
 96 
 97         public static SQLiteParameter[] GetCacheParameters(string cacheKey)
 98         {
 99             SQLiteParameter[] cacheParams = (SQLiteParameter[])parmCache[cacheKey];
100 
101             if (cacheParams == null) return null;
102 
103             int cacheLength=cacheParams.Length;
104             SQLiteParameter[] cloneParams=new SQLiteParameter[cacheLength];
105 
106             for (int i = 0; i < cacheLength; i++)
107                 cloneParams[i] = (SQLiteParameter)((ICloneable)cacheParams[i]).Clone();
108 
109             return cloneParams;
110         }
111 
112         private static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, SQLiteTransaction trans, CommandType commandType, string commandText, SQLiteParameter[] commandParameters)
113         {
114             command.Connection = connection;
115             command.CommandType = commandType;
116             command.CommandText = commandText;
117 
118             if (commandParameters != null) 
119                 command.Parameters.AddRange(commandParameters);
120 
121             if (trans != null)
122                 command.Transaction = trans;
123 
124             if (connection.State != ConnectionState.Open)
125                 connection.Open();
126         }
127     }
128 }

 

posted @ 2013-02-08 20:49 猴健居士 阅读(...) 评论(...) 编辑 收藏