SQLiteHelper

using System;
 using System.Data;
 using System.Data.SQLite;
 using System.Configuration;
 using System.Collections;
  
 namespace Common
 {
     public abstract class SQLiteHelper
     {
         //Data Source=db file fullname
         public static readonly string connectionstring = ConfigurationManager.AppSettings["connectionstring"];
  
         private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
  
         public static int ExecuteNonQuery(string connectionstring, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
         {
             SQLiteCommand cmd = new SQLiteCommand();
             using (SQLiteConnection cn=new SQLiteConnection(connectionstring))
             {
                 PrepareCommand(cmd, cn, null, commandType, commandText, commandParameters);
                 int result = cmd.ExecuteNonQuery();
                 cmd.Parameters.Clear();
                 return result;
             }
         }
  
         public static int ExecuteNonQuery(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
         {
             SQLiteCommand cmd = new SQLiteCommand();
  
             PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters);
             int result = cmd.ExecuteNonQuery();
             cmd.Parameters.Clear();
             return result;
         }
  
         public static int ExecuteNonQuery(SQLiteTransaction trans, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
         {
             SQLiteCommand cmd = new SQLiteCommand();
  
             PrepareCommand(cmd, trans.Connection, trans, commandType, commandText, commandParameters);
             int result = cmd.ExecuteNonQuery();
             cmd.Parameters.Clear();
             return result;
         }
  
         public static SQLiteDataReader ExecuteReader(string connectionstring,CommandType commandType,string commandText,params SQLiteParameter [] commandParameters)
         {
             SQLiteCommand cmd = new SQLiteCommand();
             SQLiteConnection cn=new SQLiteConnection(connectionstring);
              
             try
             {
                 PrepareCommand(cmd, cn, null, commandType, commandText, commandParameters);
                 SQLiteDataReader result = null;
                 result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                 cmd.Parameters.Clear();
                 return result;
             }
             catch
             {
                 cn.Close();
                 throw;
             }
              
         }
  
         public static object ExecuteScalar(string connectionstring, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
         {
             SQLiteCommand cmd = new SQLiteCommand();
  
             using (SQLiteConnection cn=new SQLiteConnection(connectionstring))
             {
                 PrepareCommand(cmd, cn, null, commandType, commandText, commandParameters);
                 object result = cmd.ExecuteScalar();
                 cmd.Parameters.Clear();
                 return result;
             }
         }
  
         public static object ExecuteScalar(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
         {
             SQLiteCommand cmd = new SQLiteCommand();
  
             PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters);
             object result = cmd.ExecuteScalar();
             cmd.Parameters.Clear();
             return result;
         }
  
         public static void CacheParameters(string cacheKey, params SQLiteParameter[] commandParameters)
         {
             parmCache[cacheKey] = commandParameters;
         }
  
         public static SQLiteParameter[] GetCacheParameters(string cacheKey)
         {
             SQLiteParameter[] cacheParams = (SQLiteParameter[])parmCache[cacheKey];
  
             if (cacheParams == null) return null;
  
             int cacheLength=cacheParams.Length;
             SQLiteParameter[] cloneParams=new SQLiteParameter[cacheLength];
  
             for (int i = 0; i < cacheLength; i++)
                 cloneParams[i] = (SQLiteParameter)((ICloneable)cacheParams[i]).Clone();
  
             return cloneParams;
         }
  
         private static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, SQLiteTransaction trans, CommandType commandType, string commandText, SQLiteParameter[] commandParameters)
         {
             command.Connection = connection;
             command.CommandType = commandType;
             command.CommandText = commandText;
  
             if (commandParameters != null) 
                 command.Parameters.AddRange(commandParameters);
  
             if (trans != null)
                 command.Transaction = trans;
  
             if (connection.State != ConnectionState.Open)
                 connection.Open();
         }
     }
 }