sqlhelper.cs

很不错的公文流转系统

// ===============================================================================
// 本例程示范了SqlHelper和SqlHelperParameterCache类的执行.

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

namespace Microsoft.ApplicationBlocks.Data
{
	/// <summary>	
        /// SqlHelper类意在综合SqlClient一般使用中的高性能和最好的应用能力.
	/// </summary>
    public sealed class SqlHelper
    {
        #region private utility methods & constructors

        // Since this class provides only static methods, make the default constructor private to prevent 
        // instances from being created with "new SqlHelper()"
        // 因为本类只提供静态方法，从而使得默认的私有构建器（constructor）防止由"new SqlHelper()"来创建实例.

        private SqlHelper() {}

        /// <summary>
        /// This method is used to attach array of SqlParameters to a SqlCommand.
        /// 
        /// 本方法用于将SqlParameters参数集赋予SqlCommand.
        /// This method will assign a value of DbNull to any parameter with a direction of
        /// InputOutput and a value of null.  
        /// 本方法将一个DbNull值赋予任意的具有输入输出方向和null值的参数.
        /// 
        /// This behavior will prevent default values from being used, but
        /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
        /// where the user provided no input value.
        /// 这样就可以避免默认值被使用，但是更多的情况是用户有意只提供输出参数而不提供输入值.
        /// 
        /// </summary>
        /// <param name="command">The command to which the parameters will be added</param>
        /// <param name="command">将被赋予参数的命令</param>
        /// 
        /// <param name="commandParameters">An array of SqlParameters to be added to command</param>
        /// <param name="commandParameters">将被赋予命令的SqlParameters参数集</param>
        /// 
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
			if( command == null ) throw new ArgumentNullException( "command" );
			if( commandParameters != null )
			{
				foreach (SqlParameter p in commandParameters)
				{
					if( p != null )
					{
						// 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>
        /// This method assigns dataRow column values to an array of SqlParameters
        /// 本方法将dataRow的列值赋予SqlParameters参数集
        /// </summary>
        /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
        /// <param name="commandParameters">将被赋值的SqlParameters参数集</param>
        /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
        /// <param name="dataRow">用于维持已有程序参数值的dataRow</param>
        private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
        {
            if ((commandParameters == null) || (dataRow == null)) 
            {
                // Do nothing if we get no data
                // 如果未得到数据则什么也不做
                return;
            }

			int i = 0;
            // Set the parameters values
            // 给参数赋值
            foreach(SqlParameter commandParameter in commandParameters)
            {
				// Check the parameter name
				// 检测参数名称
				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>
        /// This method assigns an array of values to an array of SqlParameters
        /// 本方法将一组值赋予SqlParameters参数集
        /// </summary>
        /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
        /// <param name="commandParameters">将被赋值的SqlParameters参数集</param>
        /// <param name="parameterValues">Array of objects holding the values to be assigned</param>
        /// <param name="parameterValues">持有用于分配的值的对象集</param>
        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null)) 
            {
                // Do nothing if we get no data
                // 如果未得到数据则什么也不做
                return;
            }

            // We must have the same number of values as we pave parameters to put them in
            // 值的数目必须和参数的数目一致
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }

            // Iterate through the SqlParameters, assigning the values from the corresponding position in the 
            // value array
            // 以SqlParameters参数迭代，分配值集中相应位置的值
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                // If the current array value derives from IDbDataParameter, then assign its Value property
                // 如果当前值集由IDbDataParameter导出，则分配其值属性
				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>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command
        /// 本方法打开（若需要）并分配一个连接、事务、命令种类及参数给所提供的命令
        /// </summary>
        /// <param name="command">The SqlCommand to be prepared</param>
        /// <param name="command">准备SqlCommand命令</param>
        /// 
        /// <param name="connection">A valid SqlConnection, on which to execute this command</param>
        /// <param name="connection">合法的SQL连接，用于在其上执行命令</param>
        /// 
        /// <param name="transaction">A valid SqlTransaction, or 'null'</param>
        /// <param name="transaction">合法的SQL事务(SqlTransaction), 或'null'</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 SqlParameters to be associated with the command or 'null' if no parameters are required</param>
        /// <param name="commandParameters">与命令关联的SqlParameters参数集，如果不需要参数，则为空'null'</param>
        /// 
        /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
        /// <param name="mustCloseConnection">如果连接由方法打开，为true，否则为false.</param>
        /// 
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection )
        {
			if( command == null ) throw new ArgumentNullException( "command" );
			if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );

            // If the provided connection is not open, we will open it
            // 如果提供的连接没有打开，则打开它
			if (connection.State != ConnectionState.Open)
			{
				mustCloseConnection = true;
				connection.Open();
			}
			else
			{
				mustCloseConnection = false;
			}

            // Associate the connection with the command
            // 将连接与命令相关联
            command.Connection = connection;

            // Set the command text (stored procedure name or SQL statement)
            // 设置命令文本（存储过程名或SQL描述）
            command.CommandText = commandText;

            // If we were provided a transaction, assign it
            // 如果提供有事务(transaction)，配置它
            if (transaction != null)
            {
				if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
                command.Transaction = transaction;
            }

            // Set the command type
            // 设置命令类型
            command.CommandType = commandType;