/////////////////////////////////////////////////////////////////
// 
// (C) Copyright 2009, Kenneth, Inc.
// All rights reserved. Confidential. Except as pursuant
// to a written agreement with Kenneth, this software may
// not be used or distributed. This software may be covered
// by one or more patents.
//
// 本软件为Kenneth开发，版权所有，违者必究，320325198102218110
// Copyright(c)  : Kenneth Weihua Yao
// Creater       : yao_wh@hotmail.com;13776068374
//
/////////////////////////////////////////////////////////////////

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.SQLite;

namespace Kenneth.DotNet.EntityTool
{
    /// <summary>
    /// The SqlHelper class is intended to encapsulate high performance, 
    /// scalable best practices for common uses of SqlClient.
    /// </summary>
    public abstract class SqlHelper
    {
        /// <summary>
        /// 设置command执行超时时间为30分钟
        /// </summary>
        private static int commandTimeout = 1800;
        public static string connectionString = "";

        // Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Execute a SQLiteCommand (that returns no resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand();

            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
                catch (Exception ex)
                {
                    conn.Close();
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns no resultset) against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(SQLiteConnection connection, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns no resultset) using an existing SQL Transaction 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing sql transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(SQLiteTransaction trans, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 填充DataSet
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet Fill(SQLiteConnection connection, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteDataReader sdr = ExecuteReader(connection, cmdType, cmdText, commandParameters);
            DataSet ds = DataReaderToDataSet(sdr);
            sdr.Close();
            return ds;
        }

        public static DataTable ExecuteDataTable(SQLiteConnection connection, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteDataReader sdr = ExecuteReader(connection, cmdType, cmdText, commandParameters);
            DataSet ds = DataReaderToDataSet(sdr);
            sdr.Close();
            return ds.Tables[0];
        }
        public static DataTable ExecuteDataTable(SQLiteConnection connection, string cmdText)
        {
            SQLiteDataReader sdr = ExecuteReader(connection, CommandType.Text, cmdText);
            DataSet ds = DataReaderToDataSet(sdr);
            sdr.Close();
            return ds.Tables[0];
        }
        public static DataTable ExecuteDataTable(string cmdText)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                SQLiteDataReader sdr = ExecuteReader(connection, CommandType.Text, cmdText);
                DataSet ds = DataReaderToDataSet(sdr);
                sdr.Close();
                return ds.Tables[0];
            }
        }
        public static DataTable ExecuteDataTable(SQLiteConnection connection,string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteDataReader sdr = ExecuteReader(connection, CommandType.Text, cmdText, commandParameters);
            DataSet ds = DataReaderToDataSet(sdr);
            sdr.Close();
            return ds.Tables[0];
        }
        public static DataTable ExecuteDataTable( string cmdText, params SQLiteParameter[] commandParameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                SQLiteDataReader sdr = ExecuteReader(connection, CommandType.Text, cmdText, commandParameters);
                DataSet ds = DataReaderToDataSet(sdr);
                sdr.Close();
                return ds.Tables[0];
            }
        }
        public static DataTable ExecuteDataTable(SQLiteConnection connection, string cmdText, int startResord, int maxRecord)
        {
            DataSet ds = new DataSet();               
            SQLiteDataAdapter command = new SQLiteDataAdapter(cmdText, connection);
            command.Fill(ds, startResord, maxRecord, "ds");                
            return ds.Tables[0];
        }
        public static DataTable ExecuteDataTable(string cmdText, int startResord, int maxRecord)
        {
            DataSet ds = new DataSet();
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                SQLiteDataAdapter command = new SQLiteDataAdapter(cmdText, connection);
                command.Fill(ds, startResord, maxRecord, "ds");

                return ds.Tables[0];
            }
        }
        public static int ExecuteNonQuery(SQLiteConnection connection, string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        public static int ExecuteNonQuery(string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
        public static object ExecuteScalar(SQLiteConnection connection, string cmdText)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
        public static object ExecuteScalar(string cmdText)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }
        #region FillDataset
        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset and takes no parameters) against the database specified in 
        /// the connection string. 
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)</param>
        public static void FillDataset(CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (dataSet == null) throw new ArgumentNullException("dataSet");

            // Create & open a SQLiteConnection, and dispose of it after we are done
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                FillDataset(connection, commandType, commandText, dataSet, tableNames);
            }
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>
        public static void FillDataset(CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames,
            params SQLiteParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            // Create & open a SQLiteConnection, and dispose of it after we are done
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
            }
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset and takes no parameters) against the provided SQLiteConnection. 
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="connection">A valid SQLiteConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>    
        public static void FillDataset(SQLiteConnection connection, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames)
        {
            FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset) against the specified SQLiteConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SQLiteConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        public static void FillDataset(SQLiteConnection connection, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames,
            params SQLiteParameter[] commandParameters)
        {
            FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset and takes no parameters) against the provided SQLiteTransaction. 
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="transaction">A valid SQLiteTransaction</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>
        public static void FillDataset(SQLiteTransaction transaction, CommandType commandType,
            string commandText,
            DataSet dataSet, string[] tableNames)
        {
            FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset) against the specified SQLiteTransaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">A valid SQLiteTransaction</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        public static void FillDataset(SQLiteTransaction transaction, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames,
            params SQLiteParameter[] commandParameters)
        {
            FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
        }


        /// <summary>
        /// Private helper method that execute a SQLiteCommand (that returns a resultset) against the specified SQLiteTransaction and SQLiteConnection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SQLiteConnection</param>
        /// <param name="transaction">A valid SQLiteTransaction</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        private static void FillDataset(SQLiteConnection connection, SQLiteTransaction transaction, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames,
            params SQLiteParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (dataSet == null) throw new ArgumentNullException("dataSet");

            // Create a command and prepare it for execution
            SQLiteCommand command = new SQLiteCommand();
            bool mustCloseConnection = false;
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Create the DataAdapter & DataSet
            using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command))
            {

                // Add the table mappings specified by the user
                if (tableNames != null && tableNames.Length > 0)
                {
                    string tableName = "Table";
                    for (int index = 0; index < tableNames.Length; index++)
                    {
                        if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
                        dataAdapter.TableMappings.Add(tableName, tableNames[index]);
                        tableName = "Table" + (index + 1).ToString();
                    }
                }

                // Fill the DataSet using default values for DataTable names, etc
                dataAdapter.Fill(dataSet);

                // Detach the SQLiteParameters from the command object, so they can be used again
                command.Parameters.Clear();
            }

            if (mustCloseConnection)
                connection.Close();
        }
        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static DataSet DataReaderToDataSet(IDataReader reader)
        {
            DataTable table = new DataTable();
            int fieldCount = reader.FieldCount;
            for (int i = 0; i < fieldCount; i++)
            {
                table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }
            table.BeginLoadData();
            object[] values = new object[fieldCount];
            while (reader.Read())
            {
                reader.GetValues(values);
                table.LoadDataRow(values, true);
            }
            table.EndLoadData();
            DataSet ds = new DataSet();
            ds.Tables.Add(table);
            return ds;
        }


        /// <summary>
        /// Execute a SQLiteCommand that returns a resultset against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SQLiteDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>A SQLiteDataReader containing the results</returns> 
        public static SQLiteDataReader ExecuteReader(SQLiteConnection conn, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand();         
            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Execute a SQLiteCommand that returns the first column of the first record against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand();

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a SQLiteCommand that returns the first column of the first record against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(SQLiteConnection connection, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
        {

            SQLiteCommand cmd = new SQLiteCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey">Key to the parameter cache</param>
        /// <param name="cmdParms">an array of SqlParamters to be cached</param>
        public static void CacheParameters(string cacheKey, params SQLiteParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// Retrieve cached parameters
        /// </summary>
        /// <param name="cacheKey">key used to lookup parameters</param>
        /// <returns>Cached SqlParamters array</returns>
        public static SQLiteParameter[] GetCachedParameters(string cacheKey)
        {
            SQLiteParameter[] cachedParms = (SQLiteParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SQLiteParameter[] clonedParms = new SQLiteParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SQLiteParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SQLiteCommand object</param>
        /// <param name="conn">SQLiteConnection object</param>
        /// <param name="trans">SQLiteTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SQLiteParameters to use in the command</param>
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, CommandType cmdType, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandTimeout = commandTimeout;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                {
                    if (parm.Value == null)
                    {
                        parm.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command
        /// </summary>
        /// <param name="command">The SQLiteCommand to be prepared</param>
        /// <param name="connection">A valid SQLiteConnection, on which to execute this command</param>
        /// <param name="transaction">A valid SQLiteTransaction, or 'null'</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SQLiteParameters to be associated with the command or 'null' if no parameters are required</param>
        /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
        private static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, SQLiteTransaction transaction, CommandType commandType, string commandText, SQLiteParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
            command.CommandTimeout = commandTimeout;


            // If the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // Associate the connection with the command
            command.Connection = connection;

            // Set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;

            // If we were provided a transaction, assign it
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            // Set the command type
            command.CommandType = commandType;

            // Attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

        private static void AttachParameters(SQLiteCommand command, SQLiteParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SQLiteParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }

        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandTimeout = commandTimeout;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SQLiteParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 仅支持SQL语句查询，返回DataSet(Add by Jackie.Shi 2008-08-20)
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Fill(string SQLString, params SQLiteParameter[] cmdParms)
        {
            
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                    return ds;
                }
            }
        }

        /// <summary>
        /// DataSet转换为泛型集合 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="p_DataSet"></param>
        /// <param name="p_TableIndex"></param>
        /// <returns></returns>
        public static List<T> DataSetToList<T>(DataSet p_DataSet, int p_TableIndex)
        {
            IList<T> ilist = DataSetToIList<T>(p_DataSet, p_TableIndex);
            List<T> result = new List<T>(ilist);
            return result;
        }

        /// <summary> 
        /// DataSet转换为泛型集合 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="p_DataSet">DataSet</param> 
        /// <param name="p_TableIndex">待转换数据表索引</param> 
        /// <returns></returns> 
        public static IList<T> DataSetToIList<T>(DataSet p_DataSet, int p_TableIndex)
        {
            // 返回值初始化 
            IList<T> result = new List<T>();

            if (p_DataSet == null || p_DataSet.Tables.Count < 0)
                return result;

            if (p_TableIndex > p_DataSet.Tables.Count - 1)
                return result;

            if (p_TableIndex < 0)
                p_TableIndex = 0;

            DataTable p_Data = p_DataSet.Tables[p_TableIndex];

            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                System.Reflection.PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (System.Reflection.PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值 
                        string columnName = p_Data.Columns[i].ColumnName.ToUpper();
                        if (pi.Name.ToUpper().Equals(columnName) || pi.Name.ToUpper().Equals(columnName.Replace("_", string.Empty)))
                        {
                            // 数据库NULL值单独处理 
                            if (p_Data.Rows[j][i] != DBNull.Value && p_Data.Rows[j][i] != null)
                            {
                                if (pi.PropertyType == typeof(Boolean))
                                {
                                    pi.SetValue(_t, Convert.ToBoolean(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int16))
                                {
                                    pi.SetValue(_t, Convert.ToInt16(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int32))
                                {
                                    pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int64))
                                {
                                    pi.SetValue(_t, Convert.ToInt64(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Decimal))
                                {
                                    pi.SetValue(_t, Convert.ToDecimal(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int32?))
                                {
                                    pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(DateTime))
                                {
                                    pi.SetValue(_t, Convert.ToDateTime(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(byte))
                                {
                                    pi.SetValue(_t, Convert.ToByte(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(string))
                                {
                                    pi.SetValue(_t, Convert.ToString(p_Data.Rows[j][i]), null);
                                }
                                else
                                {
                                    pi.SetValue(_t, p_Data.Rows[j][i], null);
                                }
                            }
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                result.Add(_t);
            }

            return result;
        }

        /// <summary> 
        /// DataSet转换为泛型集合
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="p_DataSet">DataSet</param> 
        /// <param name="p_TableName">待转换数据表名称</param> 
        /// <returns></returns> 
        public static IList<T> DataSetToIList<T>(DataSet p_DataSet, string p_TableName)
        {
            int _TableIndex = 0;

            if (p_DataSet == null || p_DataSet.Tables.Count < 0)
                return new List<T>();

            if (string.IsNullOrEmpty(p_TableName))
                return new List<T>();

            for (int i = 0; i < p_DataSet.Tables.Count; i++)
            {
                // 获取Table名称在Tables集合中的索引值 
                if (p_DataSet.Tables[i].TableName.ToUpper().Equals(p_TableName.ToUpper()))
                {
                    _TableIndex = i;
                    break;
                }
            }
            return DataSetToIList<T>(p_DataSet, _TableIndex);
        }

        #region DataTable转换为泛型集合
        public static T DataRow0ToEntity<T>(DataTable p_Data)
        {
            // 返回值初始化 
            if (p_Data.Rows.Count > 0)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                System.Reflection.PropertyInfo[] propertys = _t.GetType().GetProperties();
                int j = 0;

                foreach (System.Reflection.PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值 
                        string columnName = p_Data.Columns[i].ColumnName.ToUpper();
                        if (pi.Name.ToUpper().Equals(columnName) || pi.Name.ToUpper().Equals(columnName.Replace("_", string.Empty)))
                        {
                            // 数据库NULL值单独处理 
                            if (p_Data.Rows[j][i] != DBNull.Value && p_Data.Rows[j][i] != null)
                            {
                                if (pi.PropertyType == typeof(Boolean))
                                {
                                    pi.SetValue(_t, Convert.ToBoolean(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int16?))
                                {
                                    pi.SetValue(_t, Convert.ToInt16(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int32?))
                                {
                                    pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int64?))
                                {
                                    pi.SetValue(_t, Convert.ToInt64(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Decimal?))
                                {
                                    pi.SetValue(_t, Convert.ToDecimal(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int32?))
                                {
                                    pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(DateTime?))
                                {
                                    pi.SetValue(_t, Convert.ToDateTime(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(byte?))
                                {
                                    pi.SetValue(_t, Convert.ToByte(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(string))
                                {
                                    pi.SetValue(_t, Convert.ToString(p_Data.Rows[j][i]), null);
                                }
                                else
                                {
                                    pi.SetValue(_t, p_Data.Rows[j][i], null);
                                }
                            }
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                return _t;
            }

            return default(T);
        }
        /// <summary> 
        /// DataTable转换为泛型集合 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="p_DataSet">DataSet</param> 
        /// <param name="p_TableIndex">待转换数据表索引</param> 
        /// <returns></returns> 
        public static IList<T> DataTableToIList<T>(DataTable p_Data)
        {
            // 返回值初始化 
            IList<T> result = new List<T>();
            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                System.Reflection.PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (System.Reflection.PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值 
                        string columnName = p_Data.Columns[i].ColumnName.ToUpper();
                        if (pi.Name.ToUpper().Equals(columnName) || pi.Name.ToUpper().Equals(columnName.Replace("_", string.Empty)))
                        {
                            // 数据库NULL值单独处理 
                            if (p_Data.Rows[j][i] != DBNull.Value && p_Data.Rows[j][i] != null)
                            {
                                if (pi.PropertyType == typeof(Boolean))
                                {
                                    pi.SetValue(_t, Convert.ToBoolean(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int16?))
                                {
                                    pi.SetValue(_t, Convert.ToInt16(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int32?))
                                {
                                    pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int64?))
                                {
                                    pi.SetValue(_t, Convert.ToInt64(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Decimal?))
                                {
                                    pi.SetValue(_t, Convert.ToDecimal(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(Int32?))
                                {
                                    pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(DateTime?))
                                {
                                    pi.SetValue(_t, Convert.ToDateTime(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(byte?))
                                {
                                    pi.SetValue(_t, Convert.ToByte(p_Data.Rows[j][i]), null);
                                }
                                else if (pi.PropertyType == typeof(string))
                                {
                                    pi.SetValue(_t, Convert.ToString(p_Data.Rows[j][i]), null);
                                }
                                else
                                {
                                    pi.SetValue(_t, p_Data.Rows[j][i], null);
                                }
                            }
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                result.Add(_t);
            }

            return result;
        }
        #endregion

        #region PageList Without Proc
        /// <summary>  
        /// 获取分页数据 在不用存储过程情况下  
        /// </summary>  
        /// <param name="records">总记录条数</param>  
        /// <param name="fields">选择的列逗号隔开,支持top num</param>  
        /// <param name="tableName">表名字</param>  
        /// <param name="whereStr">条件字符 必须前加 and</param>  
        /// <param name="orderby">排序 例如 ID</param>  
        /// <param name="pageIdex">当前索引页</param>  
        /// <param name="pageSize">每页记录数</param>  
        /// <returns></returns>  
        public static DataTable GetPager(out int records, string fields, string tableName, string whereStr, string orderby, string sortorder, int pageIdex, int pageSize)
        {
            int rows = 0;
            string sqlStr = string.Format("select {0} from {1} where 1=1 {2}", fields, tableName, whereStr);
            if (!string.IsNullOrEmpty(orderby)) { sqlStr += string.Format(" Order by {0} {1}", orderby, sortorder); }

            string sqlCount = string.Format("select count(*) from {0} where 1=1 {1} ", tableName, whereStr);
            //获取行数  
            object obj = ExecuteScalar(sqlCount);
            if (obj != null)
            {
                rows = Convert.ToInt32(obj);
            }
            DataTable dt = ExecuteDataTable(sqlStr, (pageIdex - 1) * pageSize, pageSize);
            records = rows;
            return dt;
        }
        #endregion
    }
}