sqlhelper.cs

很不错的公文流转系统

            // Attach the command parameters if they are provided
            // 命令参数提供后，将它们附上
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

        #endregion private utility methods & constructors

        #region ExecuteNonQuery

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in 
        /// the connection string
        /// 执行SQL命令（无返回值且不需要参数）以取代由连接字符串指定的数据库
        /// </summary>
        /// <remarks>
        /// e.g.:
        /// 例子：  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="connectionString">为SQL连接提供的合法的连接字符串</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandType">命令类型（存储过程、文本及其他）</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        /// <returns>返回一个整型数，表示由命令影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            // 将给SqlParameters参数集赋空值的调用传递
            return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
        /// using the provided parameters
        /// 利用提供的参数值，执行SQL命令（不返回结果集）以取代由连接字符串指定的数据库.
        /// </summary>
        /// <remarks>
        /// e.g.:
        /// 例：
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="connectionString">为SQL连接提供的合法的连接字符串</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandType">命令类型（存储过程、文本及其他）</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <param name="commandParameters">用于执行SQL命令的SqlParamters参数集</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        /// <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" );

            // Create & open a SqlConnection, and dispose of it after we are done
            // 创建并打开一个SQL连接，并在使用完后清除(dispose)
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                // 调用一个用于连接的过载(overload)来代替连接字符串
                return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns no 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.
        /// 利用提供的参数值，通过SQL命令（不返回结果集）执行存储过程以取代由连接字符串指定的数据库.
        /// 此方法查询数据库以发现为存储过程提供的参数（每个存储过程第一次被调用时），并依参数顺序给它们分配值.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 此方法不能获取输出参数或存储过程的返回值参数.
        /// e.g.:  
        /// 例：
        ///  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="connectionString">为SQL连接提供的合法的连接字符串</param>
        /// <param name="spName">The name of the stored prcedure</param>
        /// <param name="spName">存储过程的名称</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
        /// <param name="parameterValues">分配为所存储过程的输入值的对象集</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        /// <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 an array of SqlParameters
                // 调用获取SqlParameters参数集的过载
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else 
            {
				// Otherwise we can just call the SP without params
                                // 否则只能调用无参数的SP
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. 
        /// 执行SQL命令（无返回值且不需要参数）以取代已有的SQL连接
        /// </summary>
        /// <remarks>
        /// e.g.:  
        /// 例：
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="connection">合法的SQL连接</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandType">命令类型（存储过程、文本及其他）</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        /// <returns>返回一个整型数，表示由命令影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            // 将给SqlParameters参数集赋空值的调用传递
            return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
        /// using the provided parameters.
        /// 执行SQL命令（不返回结果集）以取代由所提供的参数指定的SQL连接.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        /// 例：
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="connection">合法的SQL连接</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandType">命令类型（存储过程、文本及其他）</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <param name="commandParameters">用于执行命令的SqlParamters参数集</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        /// <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 );
    		
            // Finally, execute the command
            // 执行此命令
            int retval = cmd.ExecuteNonQuery();
    		
            // Detach the SqlParameters from the command object, so they can be used again
            // 将SqlParameters从命令对象中分离，以便再次使用
            cmd.Parameters.Clear();
			if( mustCloseConnection )
				connection.Close();
            return retval;
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns no 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.
        /// 利用提供的参数值，通过SQL命令（不返回结果集）执行存储过程以取代由连接字符串指定的数据库.
        /// 此方法查询数据库以发现用于存储过程参数（每个存储过程第一次被调用时），并依参数顺序给它们分配值.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 此方法不能获取输出参数或存储过程的返回值参数.
        /// e.g.:  
        /// 例：
        ///  int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">A valid SqlConnection</param>
        /// <param name="connection">合法的SQL连接</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="spName">存储过程的名称</param>
        /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
        /// <param name="parameterValues">作为存储过程的输入值对象集</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        /// <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" );