sqlhelper.cs

很不错的公文流转系统

            // 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 an array of SqlParameters
                // 调用获取SqlParameters参数集的过载
                return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else 
            {
				// Otherwise we can just call the SP without params
                                // 否则只能调用无参数的SP
                return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction. 
        /// 执行SQL命令（无返回值且不需要参数）以取代已有的SQL事务.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        /// 例：
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</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(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            // 将给SqlParameters参数集赋空值的调用传递
            return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
        /// using the provided parameters.
        /// 执行SQL命令（不返回结果集）以取代由所提供的参数指定的SQL事务.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        /// 例：
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</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(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
                        // 将SqlParameters从命令对象中分离，以便再次使用
			cmd.Parameters.Clear();
			return retval;
		}

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns no 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.
        /// 利用提供的参数值，通过SQL命令（不返回结果集）执行存储过程以取代指定的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, trans, "PublishOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="connection">合法的SQL连接</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="commandText">存储过程名称</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(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 an array of SqlParameters
                // 调用获取SqlParameters参数集的过载
                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else 
            {
				// Otherwise we can just call the SP without params
                                // 否则只能调用无参数的SP
                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteNonQuery

        #region ExecuteDataset

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
        /// the connection string. 
        /// 执行SQL命令（有一个返回值且不需要参数）以取代由连接字符串指定的数据库.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        /// 例：
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
        /// </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>A dataset containing the resultset generated by the command</returns>
        /// <returns>返回一个包含由命令生成的结果集的数据集(dataset)</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            // 将给SqlParameters参数集赋空值的调用传递
            return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// 利用提供的参数，执行SQL命令（返回一个结果集）以取代由连接字符串指定的数据库.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        /// 例：
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", 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">用于执行命令的SqlParamters参数集</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        /// <returns>返回一个包含由命令生成的结果集的数据集(dataset)</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
			if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );

            // 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 ExecuteDataset(connection, commandType, commandText, 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.
        /// 利用提供的参数值，通过SQL命令（返回一个结果集）执行存储过程以取代在连接字符串中指定的数据库.
        /// 此方法查询数据库以发现为存储过程提供的参数（每个存储过程第一次被调用时），并依参数顺序给它们分配值.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 此方法不能获取输出参数或存储过程的返回值参数.
        /// e.g.:  
        /// 例：
        ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);