using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;

namespace BatchService.Framework.Utility
{
    /// <summary> 
    /// SqlServerÊý¾Ý·ÃÎÊ°ïÖúÀà 
    /// </summary> 
    public sealed class SqlHelper
    {
        private static string g_connstring = "";

        #region ˽Óй¹Ô캯ÊýºÍ·½·¨

        private SqlHelper() { }

        /// <summary> 
        /// ½«SqlParameter²ÎÊýÊý×é(²ÎÊýÖµ)·ÖÅä¸øSqlCommandÃüÁî. 
        /// Õâ¸ö·½·¨½«¸øÈκÎÒ»¸ö²ÎÊý·ÖÅäDBNull.Value; 
        /// ¸Ã²Ù×÷½«×èֹĬÈÏÖµµÄʹÓÃ. 
        /// </summary> 
        /// <param name="command">ÃüÁîÃû</param> 
        /// <param name="commandParameters">SqlParametersÊý×é</param> 
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // ¼ì²éδ·ÖÅäÖµµÄÊä³ö²ÎÊý,½«Æä·ÖÅäÒÔDBNull.Value. 
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }

        /// <summary> 
        /// ½«DataRowÀàÐ͵ÄÁÐÖµ·ÖÅäµ½SqlParameter²ÎÊýÊý×é. 
        /// </summary> 
        /// <param name="commandParameters">Òª·ÖÅäÖµµÄSqlParameter²ÎÊýÊý×é</param> 
        /// <param name="dataRow">½«Òª·ÖÅä¸ø´æ´¢¹ý³Ì²ÎÊýµÄDataRow</param> 
        private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
        {
            if ((commandParameters == null) || (dataRow == null))
            {
                return;
            }

            int i = 0;
            // ÉèÖòÎÊýÖµ 
            foreach (SqlParameter commandParameter in commandParameters)
            {
                // ´´½¨²ÎÊýÃû³Æ,Èç¹û²»´æÔÚ,Ö»Å׳öÒ»¸öÒì³£. 
                if (commandParameter.ParameterName == null ||
                    commandParameter.ParameterName.Length <= 1)
                    throw new Exception(
                        string.Format("ÇëÌṩ²ÎÊý{0}Ò»¸öÓÐЧµÄÃû³Æ{1}.", i, commandParameter.ParameterName));
                // ´ÓdataRowµÄ±íÖлñȡΪ²ÎÊýÊý×éÖÐÊý×éÃû³ÆµÄÁеÄË÷Òý. 
                // Èç¹û´æÔںͲÎÊýÃû³ÆÏàͬµÄÁÐ,Ôò½«ÁÐÖµ¸³¸øµ±Ç°Ãû³ÆµÄ²ÎÊý. 
                if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                i++;
            }
        }

        /// <summary> 
        /// ½«Ò»¸ö¶ÔÏóÊý×é·ÖÅä¸øSqlParameter²ÎÊýÊý×é. 
        /// </summary> 
        /// <param name="commandParameters">Òª·ÖÅäÖµµÄSqlParameter²ÎÊýÊý×é</param> 
        /// <param name="parameterValues">½«Òª·ÖÅä¸ø´æ´¢¹ý³Ì²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }

            // È·±£¶ÔÏóÊý×é¸öÊýÓë²ÎÊý¸öÊýÆ¥Åä,Èç¹û²»Æ¥Åä,Å׳öÒ»¸öÒì³£. 
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("²ÎÊýÖµ¸öÊýÓë²ÎÊý²»Æ¥Åä.");
            }

            // ¸ø²ÎÊý¸³Öµ 
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                // If the current array value derives from IDbDataParameter, then assign its Value property 
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = paramInstance.Value;
                    }
                }
                else if (parameterValues[i] == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = parameterValues[i];
                }
            }
        }
        /// <summary> 
        /// Ô¤´¦ÀíÓû§ÌṩµÄÃüÁî,Êý¾Ý¿âÁ¬½Ó/ÊÂÎñ/ÃüÁîÀàÐÍ/²ÎÊý 
        /// </summary> 
        /// <param name="command">Òª´¦ÀíµÄSqlCommand</param> 
        /// <param name="connection">Êý¾Ý¿âÁ¬½Ó</param> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÊÂÎñ»òÕßÊÇnullÖµ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾, ÆäËü.)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»ò¶¼T-SQLÃüÁîÎı¾</param> 
        /// <param name="commandParameters">ºÍÃüÁîÏà¹ØÁªµÄSqlParameter²ÎÊýÊý×é,Èç¹ûûÓвÎÊýΪ'null'</param> 
        /// <param name="mustCloseConnection"><c>true</c> Èç¹ûÁ¬½ÓÊÇ´ò¿ªµÄ,ÔòΪtrue,ÆäËüÇé¿öÏÂΪfalse.</param> 
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

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

            // ¸øÃüÁî·ÖÅäÒ»¸öÊý¾Ý¿âÁ¬½Ó. 
            command.Connection = connection;

            // ÉèÖÃÃüÁîÎı¾(´æ´¢¹ý³ÌÃû»òSQLÓï¾ä) 
            command.CommandText = commandText;

            // ·ÖÅäÊÂÎñ 
            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;
            }

            // ÉèÖÃÃüÁîÀàÐÍ. 
            command.CommandType = commandType;

            // ·ÖÅäÃüÁî²ÎÊý 
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

        #endregion ˽Óй¹Ô캯ÊýºÍ·½·¨½áÊø

        #region Êý¾Ý¿âÁ¬½Ó
        /// <summary> 
        /// Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´® 
        /// </summary> 
        /// <returns></returns> 
        public static string GetConnSting()
        {
            return g_connstring;
        }
        /// <summary> 
        /// Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó 
        /// </summary> 
        /// <returns></returns> 
        public static SqlConnection GetConnection()
        {
            SqlConnection Connection = new SqlConnection(SqlHelper.GetConnSting());
            return Connection;
        }

        private static SqlConnection g_conn = null;
        public static SqlConnection GetConnection(string i_connstring)
        {
            if (g_conn != null && g_conn.State == ConnectionState.Open)
            {
                g_conn.Close();
            }
            g_conn = new SqlConnection(i_connstring);
            g_conn.Open();
            return g_conn;
        }

        public static void SetConnection(string i_connstring)
        {
            g_connstring = i_connstring;
        }

        public static bool TestConn(string i_connstring)
        {
            bool t_is_ok = false;
            try
            {
                int t_test = (int)SqlHelper.ExecuteScalar(
                    i_connstring,
                    CommandType.Text,
                    "select count(*) from bc_item");
                t_is_ok = true;
            }
            catch (Exception ex)
            {
                LogHelper.Error("SQL", ex.ToString());
                t_is_ok = false; 
            }
            return t_is_ok;
        }
        #endregion

        #region ExecuteNonQueryÃüÁî

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½Ó×Ö·û´®,ÀàÐ͵ÄSqlCommand. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾, ÆäËü.)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òSQLÓï¾ä</param> 
        /// <returns>·µ»ØÃüÁîÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½Ó×Ö·û´®,ÀàÐ͵ÄSqlCommand.Èç¹ûûÓÐÌṩ²ÎÊý,²»·µ»Ø½á¹û. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾, ÆäËü.)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òSQLÓï¾ä</param> 
        /// <param name="commandParameters">SqlParameter²ÎÊýÊý×é</param> 
        /// <returns>·µ»ØÃüÁîÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,½«¶ÔÏóÊý×éµÄÖµ¸³¸ø´æ´¢¹ý³Ì²ÎÊý, 
        /// ´Ë·½·¨ÐèÒªÔÚ²ÎÊý»º´æ·½·¨ÖÐ̽Ë÷²ÎÊý²¢Éú³É²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// Õâ¸ö·½·¨Ã»ÓÐÌṩ·ÃÎÊÊä³ö²ÎÊýºÍ·µ»ØÖµ. 
        /// ʾÀý:  
        ///  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®/param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="parameterValues">·ÖÅäµ½´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»ØÊÜÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹û´æÔÚ²ÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó̽Ë÷´æ´¢¹ý³Ì²ÎÊý(¼ÓÔص½»º´æ)²¢·ÖÅä¸ø´æ´¢¹ý³Ì²ÎÊýÊý×é. 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý¸³Öµ 
                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÇé¿öÏ 
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders"); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ(´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü.)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ(´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü.)</param> 
        /// <param name="commandText">T´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            LogHelper.Debug("SQL", string.Format("×¼±¸Ö´ÐÐSQL:connectionString:{0},commandType:{1},commandText:{2}",
                connection.ConnectionString, commandType.ToString(), commandText));
            // ´´½¨SqlCommandÃüÁî,²¢½øÐÐÔ¤´¦Àí 
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // Finally, execute the command 
            int retval = cmd.ExecuteNonQuery();

            LogHelper.Debug("SQL", string.Format("Ö´Ðнá¹û:{0}", retval));

            // Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ. 
            cmd.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return retval;
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,½«¶ÔÏóÊý×éµÄÖµ¸³¸ø´æ´¢¹ý³Ì²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ 
        /// ʾÀý:  
        ///  int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ¸ø´æ´¢¹ý³Ì·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐдøÊÂÎñµÄSqlCommand. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý.:  
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ(´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü.)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»ØÓ°ÏìµÄÐÐÊý/returns> 
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐдøÊÂÎñµÄSqlCommand(Ö¸¶¨²ÎÊý). 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ(´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü.)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            // Ô¤´¦Àí 
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Ö´ÐÐ 
            int retval = cmd.ExecuteNonQuery();

            // Çå³ý²ÎÊý¼¯,ÒÔ±ãÔÙ´ÎʹÓÃ. 
            cmd.Parameters.Clear();
            return retval;
        }

        /// <summary> 
        /// Ö´ÐдøÊÂÎñµÄSqlCommand(Ö¸¶¨²ÎÊýÖµ). 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ 
        /// ʾÀý:  
        ///  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»ØÊÜÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý¸³Öµ 
                AssignParameterValues(commandParameters, parameterValues);

                // µ÷ÓÃÖØÔØ·½·¨ 
                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÖµ 
                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteNonQuery·½·¨½áÊø

        #region ExecuteDataset·½·¨
        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,·µ»ØDataSet. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders"); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns> 
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,·µ»ØDataSet. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý: 
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">SqlParamters²ÎÊýÊý×é</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns> 
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            LogHelper.Debug("SQL", "Á¬½Ó×Ö·û´®:" + connectionString + "\r\n²éѯSQL:" + commandText);
            // ´´½¨²¢´ò¿ªÊý¾Ý¿âÁ¬½Ó¶ÔÏó,²Ù×÷Íê³ÉÊͷŶÔÏó. 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // µ÷ÓÃÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®ÖØÔØ·½·¨. 
                return ExecuteDataset(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ö±½ÓÌṩ²ÎÊýÖµ,·µ»ØDataSet. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ. 
        /// ʾÀý: 
        ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns> 
        public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмìË÷´æ´¢¹ý³Ì²ÎÊý 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý·ÖÅäÖµ 
                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,·µ»ØDataSet. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders"); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns> 
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
        }
        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ö¸¶¨´æ´¢¹ý³Ì²ÎÊý,·µ»ØDataSet. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns> 
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // Ô¤´¦Àí 
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // ´´½¨SqlDataAdapterºÍDataSet. 
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                // Ìî³äDataSet. 
                da.Fill(ds);
                LogHelper.Debug("SQL", "²é³ö¼Ç¼Êý:" + ds.Tables[0].Rows.Count); 
                cmd.Parameters.Clear();

                if (mustCloseConnection)
                    connection.Close();

                return ds;
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»ØDataSet. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊäÈë²ÎÊýºÍ·µ»ØÖµ. 
        /// ʾÀý.:  
        ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns> 
        public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ±È»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý·ÖÅäÖµ 
                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨ÊÂÎñµÄÃüÁî,·µ»ØDataSet. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders"); 
        /// </remarks> 
        /// <param name="transaction">ÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns> 
        public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨ÊÂÎñµÄÃüÁî,Ö¸¶¨²ÎÊý,·µ»ØDataSet. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="transaction">ÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns> 
        public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            LogHelper.Debug("SQL", string.Format("×¼±¸Ö´ÐÐSQL:connectionString:{0},commandType:{1},commandText:{2}",
                transaction.Connection.ConnectionString, commandType.ToString(), commandText));
            // Ô¤´¦Àí 
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // ´´½¨ DataAdapter & DataSet 
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨ÊÂÎñµÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»ØDataSet. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊäÈë²ÎÊýºÍ·µ»ØÖµ. 
        /// ʾÀý.:  
        ///  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36); 
        /// </remarks> 
        /// <param name="transaction">ÊÂÎñ</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet</returns> 
        public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý·ÖÅäÖµ 
                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteDatasetÊý¾Ý¼¯ÃüÁî½áÊø

        #region ExecuteReader Êý¾ÝÔĶÁÆ÷

        /// <summary> 
        /// ö¾Ù,±êʶÊý¾Ý¿âÁ¬½ÓÊÇÓÉSqlHelperÌṩ»¹ÊÇÓɵ÷ÓÃÕßÌṩ 
        /// </summary> 
        private enum SqlConnectionOwnership
        {
            /// <summary>ÓÉSqlHelperÌṩÁ¬½Ó</summary> 
            Internal,
            /// <summary>Óɵ÷ÓÃÕßÌṩÁ¬½Ó</summary> 
            External
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÊý¾ÝÔĶÁÆ÷. 
        /// </summary> 
        /// <remarks> 
        /// Èç¹ûÊÇSqlHelper´ò¿ªÁ¬½Ó,µ±Á¬½Ó¹Ø±ÕDataReaderÒ²½«¹Ø±Õ. 
        /// Èç¹ûÊǵ÷Óö¼´ò¿ªÁ¬½Ó,DataReaderÓɵ÷Óö¼¹ÜÀí. 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÊÂÎñ,»òÕßΪ 'null'</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">SqlParameters²ÎÊýÊý×é,Èç¹ûûÓвÎÊýÔòΪ'null'</param> 
        /// <param name="connectionOwnership">±êʶÊý¾Ý¿âÁ¬½Ó¶ÔÏóÊÇÓɵ÷ÓÃÕßÌṩ»¹ÊÇÓÉSqlHelperÌṩ</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;
            // ´´½¨ÃüÁî 
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

                // ´´½¨Êý¾ÝÔĶÁÆ÷ 
                SqlDataReader dataReader;

                if (connectionOwnership == SqlConnectionOwnership.External)
                {
                    dataReader = cmd.ExecuteReader();
                }
                else
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }

                // Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ.. 
                // HACK: There is a problem here, the output parameter values are fletched 
                // when the reader is closed, so if the parameters are detached from the command 
                // then the SqlReader can´t set its values. 
                // When this happen, the parameters can´t be used again in other command. 
                bool canClear = true;
                foreach (SqlParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                        canClear = false;
                }

                if (canClear)
                {
                    cmd.Parameters.Clear();
                }

                return dataReader;
            }
            catch
            {
                if (mustCloseConnection)
                    connection.Close();
                throw;
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÊý¾ÝÔĶÁÆ÷. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders"); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">SqlParamter²ÎÊýÊý×é(new SqlParameter("@prodid", 24))</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();

                return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
            }
            catch
            {
                // If we fail to return the SqlDatReader, we need to close the connection ourselves 
                if (connection != null) connection.Close();
                throw;
            }

        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊýÖµ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// ʾÀý:  
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÊý¾ÝÔĶÁÆ÷. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders"); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandParameters">SqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
        }

        /// <summary> 
        /// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊýÖµ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// ʾÀý:  
        ///  SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">T´æ´¢¹ý³ÌÃû</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊýÖµ. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders"); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///   SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
        }

        /// <summary> 
        /// [µ÷ÓÃÕß·½Ê½]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÊý¾ÝÔĶÁÆ÷,Ö¸¶¨²ÎÊýÖµ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// 
        /// ʾÀý:  
        ///  SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÖµ 
                return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteReaderÊý¾ÝÔĶÁÆ÷

        #region ExecuteScalar ·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
        {
            // Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨ 
            return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ö¸¶¨²ÎÊý,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            // ´´½¨²¢´ò¿ªÊý¾Ý¿âÁ¬½Ó¶ÔÏó,²Ù×÷Íê³ÉÊͷŶÔÏó. 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // µ÷ÓÃÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®ÖØÔØ·½·¨. 
                return ExecuteScalar(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// 
        /// ʾÀý:  
        ///  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý¸³Öµ 
                AssignParameterValues(commandParameters, parameterValues);

                // µ÷ÓÃÖØÔØ·½·¨ 
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÖµ 
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨ 
            return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ö¸¶¨²ÎÊý,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            LogHelper.Debug("SQL", string.Format("×¼±¸Ö´ÐÐSQL:connectionString:{0},commandType:{1},commandText:{2}",
                connection.ConnectionString, commandType.ToString(), commandText));
            // ´´½¨SqlCommandÃüÁî,²¢½øÐÐÔ¤´¦Àí 
            SqlCommand cmd = new SqlCommand();

            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // Ö´ÐÐSqlCommandÃüÁî,²¢·µ»Ø½á¹û. 
            object retval = cmd.ExecuteScalar();

            // Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ. 
            cmd.Parameters.Clear();

            if (mustCloseConnection)
                connection.Close();

            return retval;
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// 
        /// ʾÀý:  
        ///  int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý¸³Öµ 
                AssignParameterValues(commandParameters, parameterValues);

                // µ÷ÓÃÖØÔØ·½·¨ 
                return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÖµ 
                return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨ 
            return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ö¸¶¨²ÎÊý,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            // ´´½¨SqlCommandÃüÁî,²¢½øÐÐÔ¤´¦Àí 
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Ö´ÐÐSqlCommandÃüÁî,²¢·µ»Ø½á¹û. 
            object retval = cmd.ExecuteScalar();

            // Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ. 
            cmd.Parameters.Clear();
            return retval;
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ö¸¶¨²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// 
        /// ʾÀý:  
        ///  int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // PPull the parameters for this stored procedure from the parameter cache () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý¸³Öµ 
                AssignParameterValues(commandParameters, parameterValues);

                // µ÷ÓÃÖØÔØ·½·¨ 
                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÖµ 
                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteScalar

        #region ExecuteXmlReader XMLÔĶÁÆ÷
        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders"); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä using "FOR XML AUTO"</param> 
        /// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns> 
        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨ 
            return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø,Ö¸¶¨²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä using "FOR XML AUTO"</param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns> 
        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;
            // ´´½¨SqlCommandÃüÁî,²¢½øÐÐÔ¤´¦Àí 
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

                // Ö´ÐÐÃüÁî 
                XmlReader retval = cmd.ExecuteXmlReader();

                // Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ. 
                cmd.Parameters.Clear();

                return retval;
            }
            catch
            {
                if (mustCloseConnection)
                    connection.Close();
                throw;
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø,Ö¸¶¨²ÎÊýÖµ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// 
        /// ʾÀý:  
        ///  XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ using "FOR XML AUTO"</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns> 
        public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý¸³Öµ 
                AssignParameterValues(commandParameters, parameterValues);

                // µ÷ÓÃÖØÔØ·½·¨ 
                return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÖµ 
                return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders"); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä using "FOR XML AUTO"</param> 
        /// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns> 
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Ö´ÐвÎÊýΪ¿ÕµÄ·½·¨ 
            return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø,Ö¸¶¨²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä using "FOR XML AUTO"</param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        /// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns> 
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            // ´´½¨SqlCommandÃüÁî,²¢½øÐÐÔ¤´¦Àí 
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Ö´ÐÐÃüÁî 
            XmlReader retval = cmd.ExecuteXmlReader();

            // Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ. 
            cmd.Parameters.Clear();
            return retval;
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄSqlCommandÃüÁî,²¢²úÉúÒ»¸öXmlReader¶ÔÏó×öΪ½á¹û¼¯·µ»Ø,Ö¸¶¨²ÎÊýÖµ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// 
        /// ʾÀý:  
        ///  XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet.</returns> 
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý¸³Öµ 
                AssignParameterValues(commandParameters, parameterValues);

                // µ÷ÓÃÖØÔØ·½·¨ 
                return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÖµ 
                return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteXmlReader ÔĶÁÆ÷½áÊø

        #region FillDataset Ìî³äÊý¾Ý¼¯
        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.)</param> 
        public static void FillDataset(string connectionString, 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");

            // ´´½¨²¢´ò¿ªÊý¾Ý¿âÁ¬½Ó¶ÔÏó,²Ù×÷Íê³ÉÊͷŶÔÏó. 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // µ÷ÓÃÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®ÖØÔØ·½·¨. 
                FillDataset(connection, commandType, commandText, dataSet, tableNames);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯.Ö¸¶¨ÃüÁî²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.) 
        /// </param> 
        public static void FillDataset(string connectionString, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames,
            params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            // ´´½¨²¢´ò¿ªÊý¾Ý¿âÁ¬½Ó¶ÔÏó,²Ù×÷Íê³ÉÊͷŶÔÏó. 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // µ÷ÓÃÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®ÖØÔØ·½·¨. 
                FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®µÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨´æ´¢¹ý³Ì²ÎÊýÖµ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// 
        /// ʾÀý:  
        ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24); 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.) 
        /// </param>    
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        public static void FillDataset(string connectionString, string spName,
            DataSet dataSet, string[] tableNames,
            params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            // ´´½¨²¢´ò¿ªÊý¾Ý¿âÁ¬½Ó¶ÔÏó,²Ù×÷Íê³ÉÊͷŶÔÏó. 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // µ÷ÓÃÖ¸¶¨Êý¾Ý¿âÁ¬½Ó×Ö·û´®ÖØÔØ·½·¨. 
                FillDataset(connection, spName, dataSet, tableNames, parameterValues);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.) 
        /// </param>    
        public static void FillDataset(SqlConnection connection, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames)
        {
            FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.) 
        /// </param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        public static void FillDataset(SqlConnection connection, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames,
            params SqlParameter[] commandParameters)
        {
            FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏóµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨´æ´¢¹ý³Ì²ÎÊýÖµ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// 
        /// ʾÀý:  
        ///  FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.) 
        /// </param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        public static void FillDataset(SqlConnection connection, string spName,
            DataSet dataSet, string[] tableNames,
            params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý¸³Öµ 
                AssignParameterValues(commandParameters, parameterValues);

                // µ÷ÓÃÖØÔØ·½·¨ 
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÖµ 
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.) 
        /// </param> 
        public static void FillDataset(SqlTransaction transaction, CommandType commandType,
            string commandText,
            DataSet dataSet, string[] tableNames)
        {
            FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.) 
        /// </param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        public static void FillDataset(SqlTransaction transaction, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames,
            params SqlParameter[] commandParameters)
        {
            FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÊÂÎñµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,Ö¸¶¨´æ´¢¹ý³Ì²ÎÊýÖµ. 
        /// </summary> 
        /// <remarks> 
        /// ´Ë·½·¨²»Ìṩ·ÃÎÊ´æ´¢¹ý³ÌÊä³ö²ÎÊýºÍ·µ»ØÖµ²ÎÊý. 
        /// 
        /// ʾÀý:  
        ///  FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36); 
        /// </remarks> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.) 
        /// </param> 
        /// <param name="parameterValues">·ÖÅä¸ø´æ´¢¹ý³ÌÊäÈë²ÎÊýµÄ¶ÔÏóÊý×é</param> 
        public static void FillDataset(SqlTransaction transaction, string spName,
            DataSet dataSet, string[] tableNames,
            params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûÓвÎÊýÖµ 
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ¸ø´æ´¢¹ý³Ì²ÎÊý¸³Öµ 
                AssignParameterValues(commandParameters, parameterValues);

                // µ÷ÓÃÖØÔØ·½·¨ 
                FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
            }
            else
            {
                // ûÓвÎÊýÖµ 
                FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
            }
        }

        /// <summary> 
        /// [˽Óз½·¨][ÄÚ²¿µ÷ÓÃ]Ö´ÐÐÖ¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏó/ÊÂÎñµÄÃüÁî,Ó³ÉäÊý¾Ý±í²¢Ìî³äÊý¾Ý¼¯,DataSet/TableNames/SqlParameters. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ</param> 
        /// <param name="commandType">ÃüÁîÀàÐÍ (´æ´¢¹ý³Ì,ÃüÁîÎı¾»òÆäËü)</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû³Æ»òT-SQLÓï¾ä</param> 
        /// <param name="dataSet">ÒªÌî³ä½á¹û¼¯µÄDataSetʵÀý</param> 
        /// <param name="tableNames">±íÓ³ÉäµÄÊý¾Ý±íÊý×é 
        /// Óû§¶¨ÒåµÄ±íÃû (¿ÉÓÐÊÇʵ¼ÊµÄ±íÃû.) 
        /// </param> 
        /// <param name="commandParameters">·ÖÅä¸øÃüÁîµÄSqlParamter²ÎÊýÊý×é</param> 
        private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames,
            params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (dataSet == null) throw new ArgumentNullException("dataSet");

            // ´´½¨SqlCommandÃüÁî,²¢½øÐÐÔ¤´¦Àí 
            SqlCommand command = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Ö´ÐÐÃüÁî 
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
            {

                // ×·¼Ó±íÓ³Éä 
                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 += (index + 1).ToString();
                    }
                }

                // Ìî³äÊý¾Ý¼¯Ê¹ÓÃĬÈϱíÃû³Æ 
                dataAdapter.Fill(dataSet);

                // Çå³ý²ÎÊý,ÒÔ±ãÔÙ´ÎʹÓÃ. 
                command.Parameters.Clear();
            }

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

        #region UpdateDataset ¸üÐÂÊý¾Ý¼¯
        /// <summary> 
        /// Ö´ÐÐÊý¾Ý¼¯¸üе½Êý¾Ý¿â,Ö¸¶¨inserted, updated, or deletedÃüÁî. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); 
        /// </remarks> 
        /// <param name="insertCommand">[×·¼Ó¼Ç¼]Ò»¸öÓÐЧµÄT-SQLÓï¾ä»ò´æ´¢¹ý³Ì</param> 
        /// <param name="deleteCommand">[ɾ³ý¼Ç¼]Ò»¸öÓÐЧµÄT-SQLÓï¾ä»ò´æ´¢¹ý³Ì</param> 
        /// <param name="updateCommand">[¸üмǼ]Ò»¸öÓÐЧµÄT-SQLÓï¾ä»ò´æ´¢¹ý³Ì</param> 
        /// <param name="dataSet">Òª¸üе½Êý¾Ý¿âµÄDataSet</param> 
        /// <param name="tableName">Òª¸üе½Êý¾Ý¿âµÄDataTable</param> 
        public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
        {
            if (insertCommand == null) throw new ArgumentNullException("insertCommand");
            if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
            if (updateCommand == null) throw new ArgumentNullException("updateCommand");
            if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

            // ´´½¨SqlDataAdapter,µ±²Ù×÷Íê³ÉºóÊÍ·Å. 
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
            {
                // ÉèÖÃÊý¾ÝÊÊÅäÆ÷ÃüÁî 
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;

                // ¸üÐÂÊý¾Ý¼¯¸Ä±äµ½Êý¾Ý¿â 
                dataAdapter.Update(dataSet, tableName);

                // Ìá½»ËùÓиı䵽Êý¾Ý¼¯. 
                dataSet.AcceptChanges();
            }
        }
        #endregion

        #region CreateCommand ´´½¨Ò»ÌõSqlCommandÃüÁî
        /// <summary> 
        /// ´´½¨SqlCommandÃüÁî,Ö¸¶¨Êý¾Ý¿âÁ¬½Ó¶ÔÏó,´æ´¢¹ý³ÌÃûºÍ²ÎÊý. 
        /// </summary> 
        /// <remarks> 
        /// ʾÀý:  
        ///  SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName"); 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="sourceColumns">Ô´±íµÄÁÐÃû³ÆÊý×é</param> 
        /// <returns>·µ»ØSqlCommandÃüÁî</returns> 
        public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // ´´½¨ÃüÁî 
            SqlCommand cmd = new SqlCommand(spName, connection);
            cmd.CommandType = CommandType.StoredProcedure;

            // Èç¹ûÓвÎÊýÖµ 
            if ((sourceColumns != null) && (sourceColumns.Length > 0))
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ½«Ô´±íµÄÁе½Ó³Éäµ½DataSetÃüÁîÖÐ. 
                for (int index = 0; index < sourceColumns.Length; index++)
                    commandParameters[index].SourceColumn = sourceColumns[index];

                // Attach the discovered parameters to the SqlCommand object 
                AttachParameters(cmd, commandParameters);
            }

            return cmd;
        }
        #endregion

        #region ExecuteNonQueryTypedParams ÀàÐÍ»¯²ÎÊý(DataRow)
        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØÊÜÓ°ÏìµÄÐÐÊý. 
        /// </summary> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØÊÜÓ°ÏìµÄÐÐÊý. 
        /// </summary> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎïµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØÊÜÓ°ÏìµÄÐÐÊý. 
        /// </summary> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»ØÓ°ÏìµÄÐÐÊý</returns> 
        public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Sf the row has values, the store procedure parameters must be initialized 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

        #region ExecuteDatasetTypedParams ÀàÐÍ»¯²ÎÊý(DataRow)
        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataSet. 
        /// </summary> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet.</returns> 
        public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            //Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataSet. 
        /// </summary> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet.</returns> 
        /// 
        public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎñµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataSet. 
        /// </summary> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»ØÒ»¸ö°üº¬½á¹û¼¯µÄDataSet.</returns> 
        public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion

        #region ExecuteReaderTypedParams ÀàÐÍ»¯²ÎÊý(DataRow)
        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataReader. 
        /// </summary> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
        }


        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataReader. 
        /// </summary> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎïµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØDataReader. 
        /// </summary> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»Ø°üº¬½á¹û¼¯µÄSqlDataReader</returns> 
        public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

        #region ExecuteScalarTypedParams ÀàÐÍ»¯²ÎÊý(DataRow)
        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó×Ö·û´®µÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎñµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ. 
        /// </summary> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»Ø½á¹û¼¯ÖеĵÚÒ»ÐеÚÒ»ÁÐ</returns> 
        public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

        #region ExecuteXmlReaderTypedParams ÀàÐÍ»¯²ÎÊý(DataRow)
        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÁ¬½Ó¶ÔÏóµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØXmlReaderÀàÐ͵Ľá¹û¼¯. 
        /// </summary> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns> 
        public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary> 
        /// Ö´ÐÐÖ¸¶¨Á¬½ÓÊý¾Ý¿âÊÂÎñµÄ´æ´¢¹ý³Ì,ʹÓÃDataRow×öΪ²ÎÊýÖµ,·µ»ØXmlReaderÀàÐ͵Ľá¹û¼¯. 
        /// </summary> 
        /// <param name="transaction">Ò»¸öÓÐЧµÄÁ¬½ÓÊÂÎñ object</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="dataRow">ʹÓÃDataRow×÷Ϊ²ÎÊýÖµ</param> 
        /// <returns>·µ»ØXmlReader½á¹û¼¯¶ÔÏó.</returns> 
        public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // Èç¹ûrowÓÐÖµ,´æ´¢¹ý³Ì±ØÐë³õʼ»¯. 
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // ´Ó»º´æÖмÓÔØ´æ´¢¹ý³Ì²ÎÊý,Èç¹û»º´æÖв»´æÔÚÔò´ÓÊý¾Ý¿âÖмìË÷²ÎÊýÐÅÏ¢²¢¼ÓÔص½»º´æÖÐ. () 
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // ·ÖÅä²ÎÊýÖµ 
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

    }

    /// <summary> 
    /// SqlHelperParameterCacheÌṩ»º´æ´æ´¢¹ý³Ì²ÎÊý,²¢Äܹ»ÔÚÔËÐÐʱ´Ó´æ´¢¹ý³ÌÖÐ̽Ë÷²ÎÊý. 
    /// </summary> 
    public sealed class SqlHelperParameterCache
    {
        #region ˽Óз½·¨,×Ö¶Î,¹¹Ô캯Êý
        // ˽Óй¹Ô캯Êý,·ÁÖ¹À౻ʵÀý»¯. 
        private SqlHelperParameterCache() { }

        // Õâ¸ö·½·¨Òª×¢Òâ 
        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

        /// <summary> 
        /// ̽Ë÷ÔËÐÐʱµÄ´æ´¢¹ý³Ì,·µ»ØSqlParameter²ÎÊýÊý×é. 
        /// ³õʼ»¯²ÎÊýֵΪ DBNull.Value. 
        /// </summary> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû³Æ</param> 
        /// <param name="includeReturnValueParameter">ÊÇ·ñ°üº¬·µ»ØÖµ²ÎÊý</param> 
        /// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns> 
        private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            SqlCommand cmd = new SqlCommand(spName, connection);
            cmd.CommandType = CommandType.StoredProcedure;

            connection.Open();
            // ¼ìË÷cmdÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊýÐÅÏ¢,²¢Ìî³äµ½cmdµÄParameters²ÎÊý¼¯ÖÐ. 
            SqlCommandBuilder.DeriveParameters(cmd);
            connection.Close();
            // Èç¹û²»°üº¬·µ»ØÖµ²ÎÊý,½«²ÎÊý¼¯ÖеÄÿһ¸ö²ÎÊýɾ³ý. 
            if (!includeReturnValueParameter)
            {
                cmd.Parameters.RemoveAt(0);
            }

            // ´´½¨²ÎÊýÊý×é 
            SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
            // ½«cmdµÄParameters²ÎÊý¼¯¸´ÖƵ½discoveredParametersÊý×é. 
            cmd.Parameters.CopyTo(discoveredParameters, 0);

            // ³õʼ»¯²ÎÊýֵΪ DBNull.Value. 
            foreach (SqlParameter discoveredParameter in discoveredParameters)
            {
                discoveredParameter.Value = DBNull.Value;
            }
            return discoveredParameters;
        }

        /// <summary> 
        /// SqlParameter²ÎÊýÊý×éµÄÉî²ã¿½±´. 
        /// </summary> 
        /// <param name="originalParameters">ԭʼ²ÎÊýÊý×é</param> 
        /// <returns>·µ»ØÒ»¸öͬÑùµÄ²ÎÊýÊý×é</returns> 
        private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }

        #endregion ˽Óз½·¨,×Ö¶Î,¹¹Ô캯Êý½áÊø

        #region »º´æ·½·¨

        /// <summary> 
        /// ×·¼Ó²ÎÊýÊý×éµ½»º´æ. 
        /// </summary> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òSQLÓï¾ä</param> 
        /// <param name="commandParameters">Òª»º´æµÄ²ÎÊýÊý×é</param> 
        public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            paramCache[hashKey] = commandParameters;
        }

        /// <summary> 
        /// ´Ó»º´æÖлñÈ¡²ÎÊýÊý×é. 
        /// </summary> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û</param> 
        /// <param name="commandText">´æ´¢¹ý³ÌÃû»òSQLÓï¾ä</param> 
        /// <returns>²ÎÊýÊý×é</returns> 
        public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }

        #endregion »º´æ·½·¨½áÊø

        #region ¼ìË÷Ö¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯

        /// <summary> 
        /// ·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯ 
        /// </summary> 
        /// <remarks> 
        /// Õâ¸ö·½·¨½«²éѯÊý¾Ý¿â,²¢½«ÐÅÏ¢´æ´¢µ½»º´æ. 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns> 
        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }

        /// <summary> 
        /// ·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯ 
        /// </summary> 
        /// <remarks> 
        /// Õâ¸ö·½·¨½«²éѯÊý¾Ý¿â,²¢½«ÐÅÏ¢´æ´¢µ½»º´æ. 
        /// </remarks> 
        /// <param name="connectionString">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û.</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <param name="includeReturnValueParameter">ÊÇ·ñ°üº¬·µ»ØÖµ²ÎÊý</param> 
        /// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns> 
        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
            }
        }

        /// <summary> 
        /// [ÄÚ²¿]·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯(ʹÓÃÁ¬½Ó¶ÔÏó). 
        /// </summary> 
        /// <remarks> 
        /// Õâ¸ö·½·¨½«²éѯÊý¾Ý¿â,²¢½«ÐÅÏ¢´æ´¢µ½»º´æ. 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó×Ö·û</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns> 
        internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
        {
            return GetSpParameterSet(connection, spName, false);
        }

        /// <summary> 
        /// [ÄÚ²¿]·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯(ʹÓÃÁ¬½Ó¶ÔÏó) 
        /// </summary> 
        /// <remarks> 
        /// Õâ¸ö·½·¨½«²éѯÊý¾Ý¿â,²¢½«ÐÅÏ¢´æ´¢µ½»º´æ. 
        /// </remarks> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <param name="includeReturnValueParameter"> 
        /// ÊÇ·ñ°üº¬·µ»ØÖµ²ÎÊý 
        /// </param> 
        /// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns> 
        internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
            {
                return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
            }
        }

        /// <summary> 
        /// [˽ÓÐ]·µ»ØÖ¸¶¨µÄ´æ´¢¹ý³ÌµÄ²ÎÊý¼¯(ʹÓÃÁ¬½Ó¶ÔÏó) 
        /// </summary> 
        /// <param name="connection">Ò»¸öÓÐЧµÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó</param> 
        /// <param name="spName">´æ´¢¹ý³ÌÃû</param> 
        /// <param name="includeReturnValueParameter">ÊÇ·ñ°üº¬·µ»ØÖµ²ÎÊý</param> 
        /// <returns>·µ»ØSqlParameter²ÎÊýÊý×é</returns> 
        private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

            SqlParameter[] cachedParameters;

            cachedParameters = paramCache[hashKey] as SqlParameter[];
            if (cachedParameters == null)
            {
                SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }

            return CloneParameters(cachedParameters);
        }

        #endregion ²ÎÊý¼¯¼ìË÷½áÊø

    }
}

//Èç¹ûÒª»ñÈ¡Á¬½ÓÊý¾ÝÁ¬½Ó¶ÔÏó»ò×Ö·û´®µÄ»°£¬ÏÈÒªÐÞ¸ÄSQLHelperÀàÖÐGetConnSting() ·½·¨ÖеÄConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;²ÅÄܵ÷ÓÃ