C# SqlConnectionOwnership类代码示例

        /// <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">a valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParameters 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)
        {
            //create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);

            //create a reader
            SqlDataReader dr;

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

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

            return dr;
        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <remarks>
        /// 如果是SqlHelper打开连接,当连接关闭DataReader也将关闭.
        /// 如果是调用都打开连接,DataReader由调用都管理.
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="transaction">一个有效的事务,或者为 'null'</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名或T-SQL语句</param>
        /// <param name="commandParameters">SqlParameters参数数组,如果没有参数则为'null'</param>
        /// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由SqlHelper提供</param>
        /// <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");

            var mustCloseConnection = false;
            // 创建命令
            var cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

                // 创建数据阅读器
                SqlDataReader dataReader;

                if (connectionOwnership == SqlConnectionOwnership.External)
                {
                    dataReader = cmd.ExecuteReader();
                }
                else
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }

                // 清除参数,以便再次使用..
                // 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.
                var canClear = true;
                foreach (SqlParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                    {
                        canClear = false;
                        break;
                    }
                }

                if (canClear)
                {
                    cmd.Parameters.Clear();
                }

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

        /// <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">A valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">A valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParameters 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´t set its values. 
                // When this happen, the parameters can´t 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;
            }
        }

 private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
 {
     SqlDataReader reader;
     SqlCommand command = new SqlCommand();
     PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
     if (connectionOwnership == SqlConnectionOwnership.External)
     {
         reader = command.ExecuteReader();
     }
     else
     {
         reader = command.ExecuteReader(CommandBehavior.CloseConnection);
     }
     command.Parameters.Clear();
     return reader;
 }

 private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
 {
     SqlDataReader reader2;
     if (connection == null)
     {
         throw new ArgumentNullException("connection");
     }
     bool mustCloseConnection = false;
     SqlCommand command = new SqlCommand();
     try
     {
         SqlDataReader reader;
         PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
         if (connectionOwnership == SqlConnectionOwnership.External)
         {
             reader = command.ExecuteReader();
         }
         else
         {
             reader = command.ExecuteReader(CommandBehavior.CloseConnection);
         }
         bool flag2 = true;
         foreach (SqlParameter parameter in command.Parameters)
         {
             if (parameter.Direction != ParameterDirection.Input)
             {
                 flag2 = false;
             }
         }
         if (flag2)
         {
             command.Parameters.Clear();
         }
         reader2 = reader;
     }
     catch
     {
         if (mustCloseConnection)
         {
             connection.Close();
         }
         throw;
     }
     return reader2;
 }

        /// <summary>
        /// 执行数据库操作命令（返回Reader）
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令内容</param>
        /// <param name="commandParameters">命令内容</param>
        /// <param name="connectionOwnership">连接属性</param>
        /// <returns>执行结果</returns>
        private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction,
            CommandType commandType, string commandText, IEnumerable<SqlParameter> commandParameters,
            SqlConnectionOwnership connectionOwnership)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            var mustCloseConnection = false;
            var cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
                var dataReader = connectionOwnership ==
                                   SqlConnectionOwnership.External ? cmd.ExecuteReader() :
                                                                     cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var 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>
        /// ˽�з���--OleDbDataReader
        /// </summary>
        /// <param name="connection">���ݿ����Ӵ�</param>
        /// <param name="transaction">����</param>
        /// <param name="commandType">ָ������</param>
        /// <param name="commandText">ִ���ı�</param>
        /// <param name="commandParameters">������</param>
        /// <param name="connectionOwnership"></param>
        /// <returns></returns>
        private static OleDbDataReader ExecuteReader(OleDbConnection connection, OleDbTransaction transaction, CommandType commandType, string commandText, OleDbParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            bool mustCloseConnection = false;

            OleDbCommand cmd = new OleDbCommand();
            try
            {
                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

                OleDbDataReader dataReader;

                if (connectionOwnership == SqlConnectionOwnership.External)
                {
                    dataReader = cmd.ExecuteReader();
                }
                else
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                bool canClear = true;
                foreach (OleDbParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                        canClear = false;
                }

                if (canClear)
                {
                    cmd.Parameters.Clear();
                }

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

 private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
 {
     return ExecuteReader(connection, transaction, commandType, commandText, 300, commandParameters, connectionOwnership);
 }

        /// <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">a valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParameters 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)
        {
            //create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);

            //create a reader
            SqlDataReader dr;

            // call ExecuteReader with the appropriate CommandBehavior
            try
            {
                if (connectionOwnership == SqlConnectionOwnership.External)
                {
                    dr = cmd.ExecuteReader();
                }
                else
                {
                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception ex)
            {
                "SQLHelper".Log().Error("Error in ExecuteReader: `{0}`: {1} - {2}",
                    commandText,
                    ex.Message,
                    ex.StackTrace);
                ErrorLogParameters(commandText, commandParameters);
                throw;
            }

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

            return dr;
        }

        /// <summary>
        /// Create and prepare an 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">a valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
        /// <param name="commandParameters">an array of SqlParameters 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)
        {
            //create a command and prepare it for execution
            SqlCommand cmd = connection.CreateCommand();
            PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);

            //create a reader
            SqlDataReader dr = null;

            try
            {
                // call ExecuteReader with the appropriate CommandBehavior
                if (connectionOwnership == SqlConnectionOwnership.External)
                {
                    dr = cmd.ExecuteReader();
                }
                else
                {
                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                //if (transaction != null)
                //{
                //    transaction.Commit();
                //}
                return dr;
            }
            catch
            {
                //if (transaction != null)
                //{
                //    // Rollback the transaction
                //    transaction.Rollback();
                //}
                throw;
            }
        }

        /// <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">a valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="timeout">seconds before a TimeoutException is thrown</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParameters 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, int timeout, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
        {
            SqlDataReader dr=null;

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

            int retrycount = 0;
            if (connection.ConnectionString.ToLower().IndexOf("failover") >0)
                retrycount = FAILOVER_RETRIES;

            for (int retry = 0; retry <= retrycount; retry++)
            {

                try
                {

                    PrepareCommand(cmd, connection, timeout, transaction, commandType, commandText, commandParameters);

                    //create a reader

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

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

                    break;

                }
                catch (SqlException ex)
                {
                    if (retry == retrycount || (!ex.Message.Contains("transport-level error") && !ex.Message.Contains("Unable to connect")))
                        throw new SqlServerException(ex, commandText, commandParameters);   // if it isn't during a failover
                }

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

            return dr;
        }

 private static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, SqlConnectionOwnership connectionOwnership, params SqlParameter[] commandParameters)
 {
     return ExecuteReader(connection, null, commandType, commandText, connectionOwnership, commandParameters);
 }

        /// <summary>
        /// Creates a SqlDataReader by running the stored procedure or query  and placing the results
        /// of the query/proc into the given tablename. 
        /// this is used in the transaction
        /// </summary>
        /// <param name="connection">a SqlConnection instance<</param>
        /// <param name="transaction">the SqlTransaction object,by it we get the SqlConnection object</param>
        /// <param name="commandType">the value of enum <c>CommandType</c></param>
        /// <param name="commandText">the query string or the stored procedure</param>
        /// <param name="commandParameters">Array of SqlParameter objects containing parameters to the stored proc</param>
        /// <param name="connectionOwnership">the value of enum <c>SqlConnectionOwnership</c></param>
        /// <returns>Newly instantiated SqlDataReader instance</returns>
        private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText
            , SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
        {
            SqlDataReader sqlDataReader;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandTimeout = 1800;

            SqlHelper.PrepareCommand(sqlCommand, connection, transaction, commandType, commandText, commandParameters);
            if (connectionOwnership == SqlConnectionOwnership.External)
            {
                sqlDataReader = sqlCommand.ExecuteReader();
            }
            else
            {
                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            sqlCommand.Parameters.Clear();
            return sqlDataReader;
        }

 /// <summary>
 /// 创建并准备数据执行命令对象，同时以适当的 CommandBehavior 属性调用 ExecuteReader 方法。
 /// </summary>
 /// <remarks>
 /// 如果创建并打开数据库连接对象，当 DataReader 被关闭时必须关闭数据库连接。
 /// 如果是由调用方提供数据库连接对象，不必进行任何关闭操作，由调用方进行管理。
 /// </remarks>
 /// <param name="connection">有效的数据库连接对象</param>
 /// <param name="transaction">有效的事务对象，或者为 null</param>
 /// <param name="commandType">获取或设置一个值，该值指示如何解释 CommandText 属性</param>
 /// <param name="commandText">获取或设置要对数据源执行的 Transact-SQL 语句或存储过程</param>
 /// <param name="commandParameters">用来执行命令的参数数组，如果不必提供参数数组可设置为 null</param>
 /// <param name="connectionOwnership">指示调用方是否提供数据库连接，或者由数据访问帮助类创建</param>
 /// <param name="commandTimeout">设定Command执行时间，秒，大于0有效</param>
 /// <returns>执行命令后返回包含结果的数据读取对象</returns>
 private static SqlDataReader ExecuteReaderWithTimeout(SqlConnection connection,SqlTransaction transaction,CommandType commandType,string commandText,int commandTimeout,IEnumerable<SqlParameter> commandParameters,SqlConnectionOwnership connectionOwnership)
 {
     if(connection == null){
         throw new ArgumentNullException("connection");
     }
     bool mustCloseConnection = false;
     SqlCommand cmd = new SqlCommand();
     SqlDataReader result;
     try{
         PrepareCommand(cmd,connection,transaction,commandType,commandText,commandParameters,out mustCloseConnection);
         if(commandTimeout > 0){
             cmd.CommandTimeout = commandTimeout;
         }
         SqlDataReader dataReader;
         if(connectionOwnership == SqlConnectionOwnership.External){
             dataReader = cmd.ExecuteReader();
         }
         else{
             dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
         }
         bool canClear = true;
         foreach(SqlParameter commandParameter in cmd.Parameters){
             if(commandParameter.Direction != ParameterDirection.Input){
                 canClear = false;
             }
         }
         if(canClear){
             cmd.Parameters.Clear();
         }
         result = dataReader;
     }
     catch{
         if(mustCloseConnection){
             connection.Close();
         }
         throw;
     }
     return result;
 }