我遇到了一个奇怪的问题,我能够从调用存储过程中返回结果,但代码回溯失败。
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);
}
}
}
调用此代码可显示变量
x
总是表示我期望从调用存储过程中看到的行数。此外,我的调试输出显示了我期望看到的 ID 值。
然而,在返回这些结果之后,我收到错误消息
An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code
,它来自于已执行的行 if (reader.HasRows)
。我调用此请求的浏览器显示 HTTP Error 502.3 - Bad Gateway
。
我怀疑的原因是系统分别计算调试时的ID
和X
值与返回实际用户输出的方式不同。因此,在它必须返回这些值的时候,它执行了一个惰性操作来获取IEnumerable值;直到这个时候,using
语句已经导致Dispose方法被调用,因此读取器的连接为null(在调试时检查reader
变量的属性时可以看到这一点)。有人以前见过这种行为吗/这是一个bug吗?还是我错过了什么显而易见的东西?
附加代码:
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; } }
}
MapRecordsToDTOs
之前,在SqlDataReader reader = cmd.ExecuteReader();
后检查(或在代码中使用)reader.HasRows
吗? - McNetsreader = await cmd.ExecuteReaderAsync();
- McNetsreader.HasRows
的错误信息,502.3 错误表示超时操作。此时不应该释放SqlConnection
。 - McNets