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.
SqlDataReader- one from which no data has yet been read, and which is still open.