sqlhelper.cs

很不错的公文流转系统

			PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
    			
			// Create the DataAdapter & DataSet
                        // 创建DataAdapter和DataSet
			using( SqlDataAdapter da = new SqlDataAdapter(cmd) )
			{
				DataSet ds = new DataSet();

				// Fill the DataSet using default values for DataTable names, etc
                                // 用为DataTable名称等设定的默认值填充DataSet对象集
				da.Fill(ds);
    			
				// Detach the SqlParameters from the command object, so they can be used again
                                // 从命令对象中分离(Detach)SqlParameters，以便它们能再次使用
				cmd.Parameters.Clear();

				// Return the dataset
                                // 返回对象集
				return ds;
			}	
		}
		
        /// <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.
        /// 利用提供的参数值，通过SQL命令（返回一个结果集）执行存储过程以取代指定的SQL事务.
        /// 此方法查询数据库以发现用于存储过程参数（每个存储过程第一次被调用时），并依参数顺序给它们分配值.
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 此方法不能获取输出参数或存储过程的返回值参数.
        /// e.g.:  
        /// 例：
        ///  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">A valid SqlTransaction</param>
        /// <param name="transaction">一个合法的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>A dataset containing the resultset generated by the command</returns>
        /// <returns>返回一个包含由命令生成的结果集的数据集(dataset)</returns>
        public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
			if( transaction == null ) throw new ArgumentNullException( "transaction" );
			if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
			if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
			
			// If 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 ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else 
            {
				// Otherwise we can just call the SP without params
                                // 否则只能调用无参数的SP
                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteDataset
		
        #region ExecuteReader

        /// <summary>
        /// This enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that
        /// we can set the appropriate CommandBehavior when calling ExecuteReader()
        /// 这个计数器(enum)用来表明连接是由调用者提供，还是由SqlHelper创建，以便调用ExecuteReader()时设置适当的命令行为(CommandBehavior).
        /// </summary>
        private enum SqlConnectionOwnership	
        {
            /// <summary>Connection is owned and managed by SqlHelper</summary>
            /// <summary>连接由SqlHelper拥有并管理</summary>
            Internal, 
            /// <summary>Connection is owned and managed by the caller</summary>
            /// <summary>连接由调用者拥有并管理</summary>
            External
        }

        /// <summary>
        /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
        /// 创建并准备一个命令，以适当的命令行为(CommandBehavior)调用ExecuteReader.
        /// </summary>
        /// <remarks>
        /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
        /// 如果已经创建并打开了连接，当DataReader关闭时要相应关闭此连接.
        /// If the caller provided the connection, we want to leave it to them to manage.
        /// 如果是调用者提供的连接，将其留给调用者管理.
        /// </remarks>
        /// <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事务，或为空'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">与命令相关联的SqlParamters参数集，如果不要求提供参数，则为空'null'</param>
        /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
        /// <param name="connectionOwnership">表明连接参数是由调用者提供还是由SqlHelper创建</param>
        /// <returns>SqlDataReader containing the results of the command</returns>
        /// <returns>包含命令结果的SqlDataReader</returns>
        private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
        {	
			if( connection == null ) throw new ArgumentNullException( "connection" );

			bool mustCloseConnection = false;
            // 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
                                // 创建一个reader
				SqlDataReader dataReader;

				// Call ExecuteReader with the appropriate CommandBehavior
                                // 以适当的命令行为(CommandBehavior)调用ExecuteReader
				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.
                                // 从命令对象中分离(Detach)SqlParameters，以便它们能再次使用.
				// 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.
                                // HACK: 这儿有一个问题：当reader关闭时，输出参数值被fletched，
                                // 因此如果参数从命令中被分离，则SqlReader会设置它的值. 这时，参数就能被其他命令再次使用.
				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. 
        /// 执行SQL命令（返回一个结果集且不需要参数）以取代在连接字符串中指定的数据库.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        /// 例：
        ///  SqlDataReader dr = ExecuteReader(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 SqlDataReader containing the resultset generated by the command</returns>
        /// <returns>返回一个包含由命令生成的结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            // 将给SqlParameters参数集赋空值的调用传递
            return ExecuteReader(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.:  
        /// 例：
        ///  SqlDataReader dr = ExecuteReader(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 SqlDataReader containing the resultset generated by the command</returns>
        /// <returns>返回一个包含由命令生成的结果