The Provider Design Pattern and Data Access Component - III

SqlCommand.ExecuteReader & ParameterDirection.ReturnValue

I've made a great mistake when using the SqlCommand.ExecuteReader method. As we know, the ParameterDirection.ReturnValue parameters cannot be accessed if the associated SqlDataReader is still open. So, the return value will always be zero by calling SqlDataProviderBase.RunDataReader(string procName, SqlParameter[] parameters, out int returnValue).

I wrote a new class to resolve this bug. This class was named SqlDataReaderHolder.

Show/hide code: public sealed class SqlDataReaderHolder : IDisposable

• public sealed class SqlDataReaderHolder : IDisposable
• {
• internal SqlDataReaderHolder(SqlDataReader reader) : this(reader, null)
• {}
•  
• internal SqlDataReaderHolder(SqlDataReader reader, SqlParameter param)
• {
• if ( reader == null ) {
• throw new ArgumentNullException("reader");
• }
• if ( param == null ) {
• _allowRequestReturnValue = false;
• }
• else {
• if ( param.Direction != ParameterDirection.ReturnValue ) {
• throw new ArgumentException(
• "Invalid sql parameter. It should be of return value type.",
• "param");
• }
• _allowRequestReturnValue = true;
• }
• _reader = reader;
• _parameter = param;
• }
•  
• /// <summary>
• /// Cleans up the resources.
• /// </summary>
• public void Dispose()
• {
• if ( !_disposed ) {
• _disposed = true;
•  
• // Closes the SqlDataReader associated with this class.
• _reader.Dispose();
• _reader = null;
•  
• if ( _allowRequestReturnValue ) {
• // Gets the return value
• object value = _parameter.Value;
• if ( value != null && !value.Equals(DBNull.Value) ) {
• _returnValue = (int) _parameter.Value;
• }
• }
• }
• }
•  
• /// <summary>
• /// Gets an instance of <see cref="SqlDataReader"/> class associated with this object.
• /// </summary>
• public SqlDataReader Reader
• {
• get { return _reader; }
• }
•  
• /// <summary>
• /// Gets the return value from the executed SQL query.
• /// </summary>
• /// <value>
• /// The return value for the executed SQL query, or -1 if no return value is found.
• /// </value>
• /// <exception cref="InvalidOperationException">
• /// You has not specified that a return value is required,
• /// - or-
• /// the associated <see cref="SqlDataReader"/> has not been closed.
• /// </exception>
• public int ReturnValue
• {
• get
• {
• if ( !_allowRequestReturnValue ) {
• throw new InvalidOperationException("No return value is found.");
• }
• if ( !_disposed ) {
• throw new InvalidOperationException(
• "You cannot get the return value, since the associated SqlDataReader has not been closed.");
• }
• return _returnValue;
• }
• }
•  
• private SqlDataReader _reader;
• private SqlParameter _parameter;
• private int _returnValue = -1;
• private bool _disposed;
• private bool _allowRequestReturnValue;
• }

The SqlDataProviderBase.RunDataReader method has to be rewritten too.

• protected SqlDataReaderHolder RunDataReader(string procName, SqlParameter[] parameters, bool requireReturnValue)
• {
• SqlConnectionHolder connectionHolder;
• SqlCommand sqlCommand;
• SqlDataReader dr;
•  
• // Creates an active sql connection
• using ( connectionHolder = GetConnectionHolder() ) {
• connectionHolder.AutoClose = false;
•  
• // Creates a SqlCommand
• using ( sqlCommand = BuildSQLCommand(
• connectionHolder, procName, parameters, requireReturnValue) ) {
• if ( _underTrans ) {
• // If the SQL query is a part of a transaction operation,
• // the associated SqlConnection won't be closed after the SqlDataReader is closed.
• dr = sqlCommand.ExecuteReader(CommandBehavior.Default);
• }
• else {
• // Otherwise, the associated SqlConnection must be closed while the SqlDataReader is closing.
• dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
• }
•  
• if ( requireReturnValue ) {
• return new SqlDataReaderHolder(dr, sqlCommand.Parameters[s_returnValueParamName]);
• }
• else {
• return new SqlDataReaderHolder(dr);
• }
• }
• }
• }

Sample code:

• SqlDataProviderBase dataProvider;
• SqlDataReaderHolder holder;
• SqlParameter[] parameters;
•  
• // Creates parameters
• parameters = new SqlParameter[]{
• CreateInputParam("@myParam", SqlDbType.Int, 10),
• ...
• };
•  
• // Executes the database query
• using( holder = RunDataReader("dbo.myStoredProc", parameters, true) ){
• while( holder.Reader.Read() ){
• // do something...
• }
• }
•  
• // Gets the return value here
• Console.Write( "The return value is {0}.", holder.ReturnValue );
•