/// <summary>
        /// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values
        /// </summary>
        /// <param name="connectionString">SQLite Connection String</param>
        /// <param name="commandText">SQL statment with embedded "@param" style parameters</param>
        /// <param name="paramList">object[] array of param values</param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, string commandText, object[] paramList)
        {
            SQLiteConnection cn  = new SQLiteConnection(connectionString);
            SQLiteCommand    cmd = cn.CreateCommand();

            cmd.CommandText = commandText;
            SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);

            if (null != parms)
            {
                foreach (SQLiteParameter p in parms)
                {
                    cmd.Parameters.Add(p);
                }
            }
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            object result = cmd.ExecuteScalar();

            cmd.Dispose();
            cn.Close();

            return(result);
        }
        /// <summary>
        /// Executes  non-query sql Statment with Transaction
        /// </summary>
        /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, and Command, all of which must be created prior to making this method call. </param>
        /// <param name="commandText">Command text.</param>
        /// <param name="paramList">Param list.</param>
        /// <returns>Integer</returns>
        /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
        public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, object[] paramList)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && transaction.Connection == null)
            {
                throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
            }
            IDbCommand cmd = transaction.Connection.CreateCommand();

            cmd.CommandText = commandText;
            if (null != paramList)
            {
                SQLiteParameterCollectionSub coll = DeriveParameters(cmd.CommandText, paramList);
                foreach (SQLiteParameter parm in coll)
                {
                    cmd.Parameters.Add(parm);
                }
            }
            if (transaction.Connection.State == ConnectionState.Closed)
            {
                transaction.Connection.Open();
            }
            int result = cmd.ExecuteNonQuery();

            cmd.Dispose();
            return(result);
        }
        /// <summary>
        /// Executes the dataset with Transaction and object array of parameter values.
        /// </summary>
        /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, and Command, all of which must be created prior to making this method call. </param>
        /// <param name="commandText">Command text.</param>
        /// <param name="commandParameters">object[] array of parameter values.</param>
        /// <returns>DataSet</returns>
        /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
        public static DataSet ExecuteDataSet(SQLiteTransaction transaction, string commandText, object[] commandParameters)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && transaction.Connection == null)
            {
                throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
            }
            IDbCommand cmd = transaction.Connection.CreateCommand();

            cmd.CommandText = commandText;
            SQLiteParameterCollectionSub coll = DeriveParameters(cmd.CommandText, commandParameters);

            foreach (SQLiteParameter parm in coll)
            {
                cmd.Parameters.Add(parm);
            }
            if (transaction.Connection.State == ConnectionState.Closed)
            {
                transaction.Connection.Open();
            }

            DataSet ds = ExecuteDataSet((SQLiteCommand)cmd);

            return(ds);
        }
        ///<overloads></overloads>
        /// <summary>
        /// Shortcut method to execute dataset from SQL Statement and object[] arrray of            /// parameter values
        /// </summary>
        /// <param name="cn">Connection.</param>
        /// <param name="commandText">Command text.</param>
        /// <param name="paramList">Param list.</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();


            cmd.CommandText = commandText;
            if (paramList != null)
            {
                SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
                foreach (SQLiteParameter p in parms)
                {
                    cmd.Parameters.Add(p);
                }
            }
            DataSet ds = new DataSet();

            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

            da.Fill(ds);
            da.Dispose();
            cmd.Dispose();
            cn.Close();
            return(ds);
        }
        /// <summary>
        /// 执行多条SQL语句,实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SQLiteParameter[])</param>
        public static bool ExecuteSqlTran(string connectionString, Hashtable SQLStringList)
        {
            SQLiteConnection conn = new SQLiteConnection(connectionString);

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (SQLiteTransaction trans = conn.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand();
                try
                {                    //循环
                    foreach (DictionaryEntry myDE in SQLStringList)
                    {
                        //string cmdText = myDE.Key.ToString();
                        string cmdText = (string)myDE.Value;
//                        SQLiteParameter[] cmdParms = (SQLiteParameter[])myDE.Value;
                        // object[] cmdParms = (object[])myDE.Value;
                        object[] cmdParms = (object[])myDE.Key;
                        SQLiteParameterCollectionSub parms = null;
                        if (null != cmdParms)
                        {
                            parms = DeriveParameters(cmdText, cmdParms);
                        }
                        PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, parms);

                        int val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    trans.Commit();

                    return(true);
                }
                catch (System.Data.SQLite.SQLiteException e)
                {
                    trans.Rollback();
                    conn.Close();
                    return(false);
                    //throw new Exception(e.Message);
                }
            }
        }
        /// <summary>
        /// ShortCut method to return IDataReader
        /// NOTE: You should explicitly close the Command.connection you passed in as
        /// well as call Dispose on the Command  after reader is closed.
        /// We do this because IDataReader has no underlying Connection Property.
        /// </summary>
        /// <param name="cmd">SQLiteCommand Object</param>
        /// <param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>
        /// <param name="paramList">object[] array of parameter values</param>
        /// <returns>IDataReader</returns>
        public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)
        {
            if (cmd.Connection == null)
            {
                throw new ArgumentException("Command must have live connection attached.", "cmd");
            }
            cmd.CommandText = commandText;
            if (null != paramList)
            {
                SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
                foreach (SQLiteParameter p in parms)
                {
                    cmd.Parameters.Add(p);
                }
            }
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            return(rdr);
        }
        public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();

            cmd.CommandText = commandText;
            if (null != paramList)
            {
                SQLiteParameterCollectionSub parms = DeriveParameters(commandText, paramList);
                foreach (SQLiteParameter p in parms)
                {
                    cmd.Parameters.Add(p);
                }
            }
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            int result = cmd.ExecuteNonQuery();

            cmd.Dispose();
            cn.Close();

            return(result);
        }
        /// <summary>
        /// Parses parameter names from SQL Statement, assigns values from object array , and returns fully populated ParameterCollection
        /// </summary>
        /// <param name="commandText">Sql Statement with SQL Server style "@param" style embedded parameters only [no OleDb style "?" placeholders]</param>
        /// <param name="paramList">object[] array of parameter values</param>
        /// <returns>SQLiteParameterCollection</returns>
        /// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same order as parameter names appear in SQL statement.</remarks>
        public static SQLiteParameterCollectionSub DeriveParameters(string commandText, object[] paramList)
        {
            if (paramList == null)
            {
                return(null);
            }
            //SQLiteParameterCollection coll = new SQLiteParameterCollection();
            SQLiteParameterCollectionSub coll = new SQLiteParameterCollectionSub();
            string parmString = commandText.Substring(commandText.IndexOf("@"));

            // pre-process the string so always at least 1 space after a comma.
            parmString = parmString.Replace(",", " ,");
            // get the named parameters into a match collection
            string          pattern = @"(@)\S*(.*?)\b";
            Regex           ex      = new Regex(pattern, RegexOptions.IgnoreCase);
            MatchCollection mc      = ex.Matches(parmString);

            string[] paramNames = new string[mc.Count];
            int      i          = 0;

            foreach (Match m in mc)
            {
                paramNames[i] = m.Value;
                i++;
            }

            // now let's type the parameters
            int  j = 0;
            Type t = null;

            foreach (object o in paramList)
            {
                t = o.GetType();

                SQLiteParameter parm = new SQLiteParameter();
                switch (t.ToString())
                {
                case ("DBNull"):
                case ("Char"):
                case ("SByte"):
                case ("UInt16"):
                case ("UInt32"):
                case ("UInt64"):
                    throw new SystemException("Invalid data type");


                case ("System.String"):
                    parm.DbType        = DbType.String;
                    parm.ParameterName = paramNames[j];
                    parm.Value         = (string)paramList[j];
                    coll.Add(parm);
                    break;

                case ("System.Byte[]"):
                    parm.DbType        = DbType.Binary;
                    parm.ParameterName = paramNames[j];
                    parm.Value         = (byte[])paramList[j];
                    coll.Add(parm);
                    break;

                case ("System.Int32"):
                    parm.DbType        = DbType.Int32;
                    parm.ParameterName = paramNames[j];
                    parm.Value         = (int)paramList[j];
                    coll.Add(parm);
                    break;

                case ("System.Boolean"):
                    parm.DbType        = DbType.Boolean;
                    parm.ParameterName = paramNames[j];
                    parm.Value         = (bool)paramList[j];
                    coll.Add(parm);
                    break;

                case ("System.DateTime"):
                    parm.DbType        = DbType.DateTime;
                    parm.ParameterName = paramNames[j];
                    parm.Value         = Convert.ToDateTime(paramList[j]);
                    coll.Add(parm);
                    break;

                case ("System.Double"):
                    parm.DbType        = DbType.Double;
                    parm.ParameterName = paramNames[j];
                    parm.Value         = Convert.ToDouble(paramList[j]);
                    coll.Add(parm);
                    break;

                case ("System.Decimal"):
                    parm.DbType        = DbType.Decimal;
                    parm.ParameterName = paramNames[j];
                    parm.Value         = Convert.ToDecimal(paramList[j]);
                    break;

                case ("System.Guid"):
                    parm.DbType        = DbType.Guid;
                    parm.ParameterName = paramNames[j];
                    parm.Value         = (System.Guid)(paramList[j]);
                    break;

                case ("System.Object"):

                    parm.DbType        = DbType.Object;
                    parm.ParameterName = paramNames[j];
                    parm.Value         = paramList[j];
                    coll.Add(parm);
                    break;

                default:
                    throw new SystemException("Value is of unknown data type");
                } // end switch

                j++;
            }
            return(coll);
            //return null;
        }
 /// <summary>
 /// 为执行命令做参数准备
 /// </summary>
 /// <param >SqlCommand object</param>
 /// <param >SqlConnection object</param>
 /// <param >SqlTransaction object</param>
 /// <param >一个存储过程,或者sql语句类型</param>
 /// <param >一个命令sql语句</param>
 /// <param >参数集合</param>
 public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, CommandType cmdType, string cmdText, SQLiteParameterCollectionSub cmdParms)
 {
     try
     {
         //判断连接的状态。如果是关闭状态,则打开
         if (conn.State != ConnectionState.Open)
         {
             conn.Open();
         }
         //cmd属性赋值
         cmd.Connection  = conn;
         cmd.CommandText = cmdText;
         //是否需要用到事务处理
         if (trans != null)
         {
             cmd.Transaction = trans;
         }
         cmd.CommandType = cmdType;
         //添加cmd需要的存储过程参数
         if (cmdParms != null)
         {
             foreach (SQLiteParameter parm in cmdParms)
             {
                 cmd.Parameters.Add(parm);
             }
         }
     }
     catch (Exception e) { throw new Exception(e.Message); }
 }