2

I am getting interesting error that I want to fix. Here is my class,

public class SqlCommandWrapper
{
    private readonly ISettings _settings;
    private readonly ILogger _logger;

    public SqlCommandWrapper(ISettings settings, ILogger logger)
    {
        _settings = settings;
        _logger = logger;
    }

    private enum ExecutionType
    {
        Reader,
        NonQuery,
        Scaler

    }

    public async Task<SqlDataReader> ExecuteReaderAsync(string commandText, params SqlParameter[] parameters)
    {
        return (SqlDataReader)await ExecuteAsync(ExecutionType.Reader, CommandType.StoredProcedure, commandText, IsolationLevel.ReadUncommitted, parameters).ConfigureAwait(false);
    }

    public async Task<SqlDataReader> ExecuteReaderAsync(CommandType commandType, string commandText, IsolationLevel isolationLevel,
        params SqlParameter[] parameters)
    {
        return (SqlDataReader)await ExecuteAsync(ExecutionType.Reader, commandType, commandText, isolationLevel, parameters).ConfigureAwait(false);
    }

    public async Task<SqlDataReader> ExecuteReaderAsync(CommandType commandType, string commandText, params SqlParameter[] parameters)
    {
        return
            (SqlDataReader)
                await ExecuteAsync(ExecutionType.Reader, commandType, commandText, IsolationLevel.ReadUncommitted, parameters).ConfigureAwait(false);
    }

    public async Task<int> ExecuteNonQueryAsync(string commandText, params SqlParameter[] parameters)
    {
        return (int)await ExecuteAsync(ExecutionType.NonQuery, CommandType.StoredProcedure, commandText, IsolationLevel.ReadUncommitted, parameters).ConfigureAwait(false);
    }

    public async Task<int> ExecuteNonQueryAsync(CommandType commandType, string commandText, IsolationLevel isolationLevel, params SqlParameter[] parameters)
    {
        return (int) await ExecuteAsync(ExecutionType.NonQuery, commandType, commandText, isolationLevel, parameters).ConfigureAwait(false);
    }

    public async Task<int> ExecuteNonQueryAsync(CommandType commandType, string commandText, params SqlParameter[] parameters)
    {
        return (int) await ExecuteAsync(ExecutionType.NonQuery, commandType, commandText, IsolationLevel.ReadUncommitted, parameters).ConfigureAwait(false);
    }

    public Task<object> ExecuteScalarAsync(string commandText, params SqlParameter[] parameters)
    {
        return ExecuteAsync(ExecutionType.Scaler, CommandType.StoredProcedure, commandText, IsolationLevel.ReadUncommitted, parameters);
    }

    public Task<object> ExecuteScalarAsync(CommandType commandType, string commandText, IsolationLevel isolationLevel, params SqlParameter[] parameters)
    {
        return ExecuteAsync(ExecutionType.Scaler, commandType, commandText, isolationLevel, parameters);
    }

    public Task<object> ExecuteScalarAsync(CommandType commandType, string commandText, params SqlParameter[] parameters)
    {
        return ExecuteAsync(ExecutionType.Scaler, commandType, commandText, IsolationLevel.ReadUncommitted, parameters);
    }


    private async Task<object> ExecuteAsync(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel,
        params SqlParameter[] parameters)
    {
        using (var connection = new SqlConnection(_settings.DatabaseConnectionString))
        {
            using (var command = new SqlCommand(commandText, connection) {CommandType = commandType})
            {
                command.Parameters.AddRange(parameters);
                command.CommandTimeout = _settings.CommandTimeout;
                await connection.OpenAsync().ConfigureAwait(false);
                var transaction = connection.BeginTransaction(isolationLevel);
                command.Transaction = transaction;
                try
                {
                    object result;
                    switch (executionType)
                    {
                        case ExecutionType.Reader:
                            result = await command.ExecuteReaderAsync().ConfigureAwait(false);
                            break;
                        case ExecutionType.NonQuery:
                            result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
                            break;
                        default:
                            result = await command.ExecuteScalarAsync().ConfigureAwait(false);
                            break;
                    }
                    transaction.Commit();
                    return result;
                }
                catch (Exception exception)
                {
                    _logger.Log(exception);
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }
}

and I am calling this using,

using (var reader = await _sqlCommandWrapper.ExecuteReaderAsync("MySP", parameters))
{
   // My code
}

When I run my class I am getting an error:

There is already an open DataReader associated with this Command which must be closed first.

5
  • 2
    I think that the problem is that the method is trying to return an open data reader but at the same time, it's trying to commit the transaction and dispose of the command and connection objects before it returns. You can't accomplish both of those goals. Commented Sep 22, 2014 at 14:17
  • @Damien_The_Unbeliever, no I am just getting data. Commented Sep 22, 2014 at 14:18
  • 3
    No, you're not just "getting data" - your methods are returning an SqlDataReader - one from which no data has yet been read, and which is still open. Commented Sep 22, 2014 at 14:21
  • If the error message says "must be closed first" I would look for Close/Dispose calls. I can't see any in the posted code. Commented Sep 22, 2014 at 14:54
  • @Damien_The_Unbeliever, I fixed it check below Commented Sep 22, 2014 at 17:28

1 Answer 1

1

I fix it by adding a callback method,

public class SqlCommandWrapper
{
    private readonly ISettings _settings;
    private readonly ILogger _logger;

    public SqlCommandWrapper(ISettings settings, ILogger logger)
    {
        _settings = settings;
        _logger = logger;
    }

    public enum ExecutionType
    {
        Reader,
        NonQuery,
        Scaler
    }

    public Task<IEnumerable<T>> ExecuteReaderAsync<T>(string commandText, Func<IDataReader, T> callback, params SqlParameter[] parameters)
    {
        return ExecuteReaderAsync(CommandType.StoredProcedure, commandText, callback, parameters);
    }

    public Task<IEnumerable<T>> ExecuteReaderAsync<T>(CommandType commandType, string commandText, Func<IDataReader, T> callback, params SqlParameter[] parameters)
    {
        return ExecuteReaderAsync(ExecutionType.Reader, commandType, commandText, IsolationLevel.ReadUncommitted, callback, parameters);
    }

    public async Task<IEnumerable<T>> ExecuteReaderAsync<T>(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel,
        Func<IDataReader, T> callback, params SqlParameter[] parameters)
    {
        return (IEnumerable<T>)await ExecuteAsync(executionType, commandType, commandText, isolationLevel, parameters, callback).ConfigureAwait(false);
    }

    public Task<int> ExecuteNonQueryAsync(string commandText, params SqlParameter[] parameters)
    {
        return ExecuteNonQueryAsync(CommandType.StoredProcedure, commandText, parameters);
    }

    public Task<int> ExecuteNonQueryAsync(CommandType commandType, string commandText, params SqlParameter[] parameters)
    {
        return ExecuteNonQueryAsync(ExecutionType.NonQuery, commandType, commandText, IsolationLevel.ReadUncommitted, parameters);
    }

    public async Task<int> ExecuteNonQueryAsync(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel, params SqlParameter[] parameters)
    {
        return (int)await ExecuteAsync(executionType, commandType, commandText, isolationLevel, parameters).ConfigureAwait(false);
    }

    public Task<object> ExecuteScalarAsync(string commandText, params SqlParameter[] parameters)
    {
        return ExecuteScalarAsync(CommandType.StoredProcedure, commandText, parameters);
    }

    public Task<object> ExecuteScalarAsync(CommandType commandType, string commandText, params SqlParameter[] parameters)
    {
        return ExecuteScalarAsync(ExecutionType.Scaler, commandType, commandText, IsolationLevel.ReadUncommitted, parameters);
    }

    public Task<object> ExecuteScalarAsync(ExecutionType executionType,CommandType commandType, string commandText, IsolationLevel isolationLevel, params SqlParameter[] parameters)
    {
        return ExecuteAsync(executionType, commandType, commandText, isolationLevel, parameters);
    }

    private Task<object> ExecuteAsync(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel,
        SqlParameter[] parameters)
    {
        return ExecuteAsync<object>(executionType, commandType, commandText, isolationLevel, parameters);
    }

    private async Task<object> ExecuteAsync<T>(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel, SqlParameter[] parameters, Func<IDataReader, T> callback = null)
    {
        using (var connection = new SqlConnection(_settings.DatabaseConnectionString))
        {
            using (var command = new SqlCommand(commandText, connection) {CommandType = commandType})
            {
                command.Parameters.AddRange(parameters);
                command.CommandTimeout = _settings.CommandTimeout;
                await connection.OpenAsync().ConfigureAwait(false);
                var transaction = connection.BeginTransaction(isolationLevel);
                command.Transaction = transaction;
                try
                {
                    object result;
                    switch (executionType)
                    {
                        case ExecutionType.Reader:
                            var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
                            using (reader)
                            {
                                var list = new List<T>();
                                while (reader.Read())
                                {
                                    if (callback != null)
                                    {
                                        list.Add(callback(reader));
                                    }
                                }
                                result = list;
                            }
                            break;
                        case ExecutionType.NonQuery:
                            result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
                            break;
                        default:
                            result = await command.ExecuteScalarAsync().ConfigureAwait(false);
                            break;
                    }
                    transaction.Commit();
                    return result;
                }
                catch (Exception exception)
                {
                    _logger.Log(exception);
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }
}

usage,

  (await _sqlCommandWrapper.ExecuteReaderAsync("MySP", r =>
            new Product
            {
                Id = (int) r["Id"],
                ........................
                ........................
                ........................

            }, parameters)).ToList()
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.