5

I'm getting an odd issue where I'm able to return results from a call to a stored procedure, but the code retrospectively fails.

public IEnumerable<T> ExecuteStoredProcedure<T>(string storedProcedureName, IDataMapper<T> mapper, IDictionary<string, object> parameters)
{
    using (var connection = new SqlConnection(connectionString))
    {
        using (var cmd = new SqlCommand(storedProcedureName, connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (var key in parameters.Keys)
            {
                cmd.Parameters.AddWithValue(key, parameters[key]);
            }
            connection.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            //return MapRecordsToDTOs(reader, mapper);

            //let's test:
            IEnumerable<T> result = MapRecordsToDTOs(reader, mapper);
            var x = (new List<T>(result)).Count;
            System.Diagnostics.Debug.WriteLine(x);
            return result;
        }
    }
}


private static IEnumerable<T> MapRecordsToDTOs<T>(SqlDataReader reader, IDataMapper<T> mapper)
{
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            System.Diagnostics.Debug.WriteLine(reader["Id"]); //what's going on...
            yield return mapper.MapToDto((IDataRecord)reader);
        }
    }
}

Calling this code shows that variable x always represents the number of rows I'd expect to see from a call to my stored procedures.

Additionally my debug output shows the ID values I'd expect to see.

However, after those results are returned, I get the error An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code from the line if (reader.HasRows) (i.e. which has already been executed). The browser from which I invoke this request shows HTTP Error 502.3 - Bad Gateway.

Screenshot of Error

Screenshot of HasRows Behaviour

I suspect the reason is the system's calculating the ID and X values for debug separately to how it would return the real user output. As such, it performs a lazy operation to get the IEnumerable values at the point at which it has to return them; only by this point the using statements have caused the dispose methods to be called, and thus the reader's connection is null (this is what I see when I inspect the reader variable's properties whilst debugging).

Has anyone seen behaviour like this before / is it a bug; or have I just missed something obvious?


Additional Code:

public interface IDataMapper<T>
{
    T MapToDto(IDataRecord record);
}

public class CurrencyMapper: IDataMapper<CurrencyDTO>
{
    const string FieldNameCode = "Code";
    const string FieldNameId = "Id";
    const string FieldNameName = "Name";
    const string FieldNameNum = "Num";
    const string FieldNameE = "E";
    const string FieldNameSymbol = "Symbol";

    public CurrencyMapper() { }

    public CurrencyDTO MapToDto(IDataRecord record)
    {
        var code = record[FieldNameCode] as string;
        var id = record[FieldNameId] as Guid?;
        var name = record[FieldNameName] as string;
        var num = record[FieldNameNum] as string;
        var e = record[FieldNameE] as int?;
        var symbol = record[FieldNameSymbol] as char?;
        return new CurrencyDTO(id, code, num, e, name, symbol);
    }
}

public class CurrencyRepository
{

    const string SPReadAll = "usp_CRUD_Currency_ReadAll";

    readonly SqlDatabase db;
    public CurrencyRepository()
    {
        db = new SqlDatabase(); //stick to SQL only for the moment for simplicity
    }
    public IEnumerable<CurrencyDTO> GetCurrencyCodes()
    {
        var mapper = new CurrencyMapper();
        return db.ExecuteStoredProcedure(SPReadAll, mapper);
    }
}

public class CurrencyDTO
{

    readonly Guid? id;
    readonly string code;
    readonly string num;
    readonly int? e;
    readonly string name;
    readonly char? symbol;

    public CurrencyDTO(Guid? id,string code,string num,int? e,string name, char? symbol)
    {
        this.id = id;
        this.code = code;
        this.num = num;
        this.e = e;
        this.name = name;
        this.symbol = symbol;
    }

    public Guid? Id { get { return id; } }
    public string Code { get { return code; } }
    public string Num { get { return num; } }
    public int? E { get { return e; } }
    public string Name { get { return name; } }
    public char? Symbol { get { return symbol; } }
}
12
  • 1
    have you tried to inspect (or use in your code) reader.HasRows just after SqlDataReader reader = cmd.ExecuteReader(); and before to call MapRecordsToDTOs? Commented Nov 24, 2016 at 19:41
  • 1
    you could try with: reader = await cmd.ExecuteReaderAsync(); Commented Nov 24, 2016 at 19:44
  • @mcNets: The MapRecordsToDTOs function confirms that reader.HasRows is true as it's able to iterate through those rows to allow x to get the correct value, and for the IDs to be output correctly by the Debug.WriteLine. statements. Commented Nov 24, 2016 at 21:03
  • 1
    but you receive the error message in reader.HasRows does it?, and 502.3 error indicates a timeout operation. SqlConnection should not be disposed at this point. Commented Nov 24, 2016 at 21:13
  • 1
    @mcNets: Confirmed; however those lines of code execute successfully when I run in debug mode/ in the debug's output; but then when I get to the line of code which returns the output to the debugger jumps back to this line; i.e. implying that the first pass was for the debugger's benefit, and the second is the real thing lazy loading the data now that we need to do something with it outside of debug... i.e. I only make 1 call to this method, yet it appears to be executed twice when viewed by the debugger; once when called, and again when the results are required (though it's not called then). Commented Nov 24, 2016 at 21:18

1 Answer 1

2

I've temporarily implemented a workaround which resolves this issue.

This works:

private static IEnumerable<T> MapRecordsToDTOs<T>(SqlDataReader reader, IDataMapper<T> mapper)
{
    var list = new List<T>(); //use a list to force eager evaluation
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            list.Add(mapper.MapToDto((IDataRecord)reader));
        }
    }
    return list.ToArray();
}

As opposed to the original:

private static IEnumerable<T> MapRecordsToDTOs<T>(SqlDataReader reader, IDataMapper<T> mapper)
{
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            yield return mapper.MapToDto((IDataRecord)reader);
        }
    }
}

The difference being I move the code affected by the iterator such that it only iterates through the results in the list; and doesn't rely on the compiler sensibly understanding the requirements related to IDisposable objects.

It's my understanding that the compiler should be able to handle this for me (confirmed here: https://stackoverflow.com/a/13504789/361842), so I suspect it's a bug in the compiler.

Reported here: https://connect.microsoft.com/VisualStudio/feedback/details/3113138

Additional demo code here: https://gist.github.com/JohnLBevan/a910d886df577e442e2f5a9c2dd41293/

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.