处理来自EF存储过程的多个结果集的通用方法 [英] Generic approach to dealing with multiple result sets from EF stored procedure
本文介绍了处理来自EF存储过程的多个结果集的通用方法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
- EF 6，.NET 4.51
我试图建立一个通用的帮助类帮助我将每个结果集翻译成一个类型安全类，如下所述处理多个存储过程的结果与SqlQuery
对于我的解决方案，我想将以下内容传递给我的助手类MultiResultsetsHelper）：
- 通用返回类型
- ObjectContext
- DataReader
- 返回结果集的 类型的列表
，然后让帮助器类完成填充1的繁重工作。以下是目前的代码：
结果类
public class Set1ReturnDto
{
public int CruiseCount {get;组; }
public string DisplayText {get;组; }
public int DisplayValue {get;组; }
}
public class Set2ReturnDto
{
public string DepartingFrom {get;组; }
public string Port_Code {get;组; }
$ b $ public class DummyReturnDto
{
public DummyReturnDto（）
{
Set1 = new List< Set1ReturnDto>（）;
Set2 = new List< Set2ReturnDto>（）;
}
public List< Set1ReturnDto> Set1 {get;组; }
public List< Set2ReturnDto> Set2 {get;组; }
}
低级别数据库呼叫
public static DummyReturnDto DonoUspGetSideBarList（DbContext aDbContext，out int aProcResult）
{
SqlParameter procResultParam = new SqlParameter {ParameterName = @procResult，SqlDbType = SqlDbType.Int，Direction = ParameterDirection.Output};
DbCommand dbCommand = aDbContext.Database.Connection.CreateCommand（）;
dbCommand.Parameters.Add（procResultParam）;
dbCommand.CommandText =EXEC @procResult = [dbo]。[usp_GetSideBarList];
dbCommand.Transaction = aDbContext.Database.CurrentTransaction.UnderlyingTransaction;
DbDataReader reader = dbCommand.ExecuteReader（）;
ProcResult = -1;
//下拉到包装的ObjectContext来访问`Translate`方法$ b $ ObjectContext objectContext =（（IObjectContextAdapter）aDbContext）.ObjectContext;
列表<类型> containedDtos =新列表<类型>
{
typeof（List< Set1ReturnDto>），
typeof（List< Set1ReturnDto>）
};
返回MultiResultsetsHelper.Process< DummyReturnDto>（reader，objectContext，containedDtos）;
}
返回的结果数据集包括：
助手类
public static class MultiResultsetsHelper
{
///< ;总结>
///给定一个包含多个结果集的数据读取器，使用提供的对象上下文将结果集中的
///行数据序列化到我们的属性中。
///< / summary>
///< typeparam name =T>包含所有不同结果集的包含对象的类型< / typeparam>
///< param name =aDbReader>数据库读取器，包含从数据库返回的所有结果集。< / param>
///< param name =aObjectContext>与数据读取器相关的数据上下文< / param>
///< param name =aContainedDataSetReturnedTypes>
///结果集包含在
///数据读取器中的类型列表。我们将顺序地对每个结果集进行serilize数据读取器包含
///< / param>
///< returns>重新返回表示由数据读取器返回的所有结果集的对象。< / returns>
public static T Process< T>（DbDataReader aDbReader，ObjectContext aObjectContext，List< Type> aContainedDataSetReturnedTypes）其中T：new（）
{
//我们将返回什么
T result = new T（）;
for（int datasetNdx = 0; datasetNdx< aContainedDataSetReturnedTypes.Count; datasetNdx ++）
{
//如果我们没有看第一个数据集$ b $， b if（datasetNdx！= 0）
aDbReader.NextResult（）;
//根据我们要填充的类的类型获取我们将要更新的属性
PropertyInfo propertyInfo = typeof（T）.GetProperties（）。Single（p = > p.PropertyType == aContainedDataSetReturnedTypes [datasetNdx]）;
//现在获取对象上下文以反序列化结果集中的内容到我们的类型
var valueForProperty = aObjectContext.Translate< aContainedDataSetReturnedTypes [datasetNdx]> （aDbReader）;
//最后我们用类型安全信息
propertyInfo.SetValue（result，valueForProperty，null）更新属性;
}
返回结果;
然而目前我无法得到这个编译。 p>
错误2操作符'<'不能应用于'method
group'和'System.Type'类型的操作数p>
有人可以帮忙吗？最终，它与我们如何使用反射以及在aContainedDataSetReturnedTypes中传递的内容有关。我很乐意改变这种情况，只要它仍然很容易调用MultiResultsetsHelper.Process<>（）
解决方案
以上所有帮助我想出了以下内容（仍然可以改进）：
public static class MultiResultsetsHelper
{
///< summary>
///给定一个包含多个结果集的数据读取器，使用提供的对象上下文将结果集中的
///行数据序列化到我们的属性中。
///< / summary>
///< typeparam name =T>包含所有不同结果集的包含对象的类型< / typeparam>
///< param name =aDbReader>数据库读取器，包含从数据库返回的所有结果集。< / param>
///< param name =aDbContext>与数据读取器相关的数据上下文。< / param>
///< param name =aDataSetTypes>在我们对数据读取器中的当前结果调用Translate（）时使用的每种类型的类型< / param>
///< param name =aContainedDataSetReturnedTypes>
///结果集包含在
///数据读取器中的类型列表。我们将顺序地对每个结果集进行serilize数据读取器包含
///< / param>
///< returns>重新返回表示由数据读取器返回的所有结果集的对象。< / returns>
public static T Process< T>（DbDataReader aDbReader，DbContext aDbContext，List< Type> aDataSetTypes，List< Type> aContainedDataSetReturnedTypes）其中T：new（）
{
//我们会返回
T result = new T（）;
//下拉到包装的ObjectContext来访问`Translate`方法$ b $ ObjectContext objectContext =（（IObjectContextAdapter）aDbContext）.ObjectContext;
//迭代传入的数据集类型，因为它们与读者包含的顺序相同
for（int datasetNdx = 0; datasetNdx< aContainedDataSetReturnedTypes.Count; datasetNdx ++）
{
//如果我们没有看第一个数据集，则提前阅读
if（datasetNdx！= 0）
aDbReader.NextResult（）;
//根据我们要填充的类的类型获取我们将要更新的属性
PropertyInfo propertyInfo = typeof（T）.GetProperties（）。Single（p = > p.PropertyType == aContainedDataSetReturnedTypes [datasetNdx]）;
//现在获取对象上下文以反序列化结果集中的内容到我们的类型
MethodInfo method = GetTranslateOverload（typeof（ObjectContext））;
MethodInfo generic = method.MakeGenericMethod（aDataSetTypes [datasetNdx]）;
//调用我们构造的通用方法Translate
object valueForProperty = generic.Invoke（objectContext，new object [] {aDbReader}）;
//最后我们用类型安全信息
propertyInfo.SetValue（result，valueForProperty）更新属性;
}
返回结果;
}
///< summary>
///获得我们需要的必要翻译超载的内部帮助方法：
/// ObjectContext.Translate< T>（DbReader）
///< / summary>
///< param name =aType> ObjectContext.GetType（）< / param>
///< returns>返回我们需要的方法，错误时为null。< / returns>
private static MethodInfo GetTranslateOverload（Type aType）
{
MethodInfo myMethod = aType
.GetMethods（）
.Where（m => m.Name ==转换）
.Select（m => new
{
Method = m，
Params = m.GetParameters（），
Args = m.GetGenericArguments ）
。）
.Where（x => x.Params.Length == 1
&& x.Args.Length == 1
&& x.Params [0] .ParameterType == typeof（DbDataReader）
//&& x.Params [0] .ParameterType == x.Args [0]
）
。选择（x => x.Method）
.First（）;
返回myMethod;
$ b因此假设你有：
public class UspGetSideBarListReturnDto
{
public List< Set1ReturnDto> Dummy1 {get;组; }
public List< Set2ReturnDto> Dummy2 {get;组; }
}
public class Set1ReturnDto
{
public Int32 CruiseCount {get;组; }
public string DisplayText {get;组; }
public Int64 DisplayValue {get;组; }
}
public class Set2ReturnDto
{
public string DepartingFrom {get;组; }
public string Port_Code {get;组; }
}
您可以将它称为：
DbDataReader reader = dbCommand.ExecuteReader（）;
return MultiResultsHelper.Process< UspGetSideBarListReturnDto>（reader，myDbContext，new List< Type> {typeof（Set1ReturnDto），typeof（Set2ReturnDto）}，new List< Type> {typeof（List
aDataSetTypes的顺序需要对应于结果集列表
改进之处在于：
- 只传递数据集列表（并让列表属性自动确定）
- EF 6, .NET 4.51
I am trying to build a generic helper class that will help me "translate" each of the result sets into a type safe class as described here Handle multiple result from a stored procedure with SqlQuery
For my solution I want to pass the following to my helper class (MultiResultsetsHelper):
- Generic Return Type
- ObjectContext
- DataReader
- List of class types in order of the result sets coming back
and then have the helper class do the heavy lifting of populating 1. Below is the code so far:
Classes For Result
public class Set1ReturnDto
{
public int CruiseCount { get; set; }
public string DisplayText { get; set; }
public int DisplayValue { get; set; }
}
public class Set2ReturnDto
{
public string DepartingFrom { get; set; }
public string Port_Code { get; set; }
}
public class DummyReturnDto
{
public DummyReturnDto()
{
Set1 = new List<Set1ReturnDto>();
Set2 = new List<Set2ReturnDto>();
}
public List<Set1ReturnDto> Set1 { get; set; }
public List<Set2ReturnDto> Set2 { get; set; }
}
Low Level Database Call
public static DummyReturnDto DonoUspGetSideBarList(DbContext aDbContext, out int aProcResult)
{
SqlParameter procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
DbCommand dbCommand = aDbContext.Database.Connection.CreateCommand();
dbCommand.Parameters.Add(procResultParam);
dbCommand.CommandText = "EXEC @procResult = [dbo].[usp_GetSideBarList] ";
dbCommand.Transaction = aDbContext.Database.CurrentTransaction.UnderlyingTransaction;
DbDataReader reader = dbCommand.ExecuteReader();
aProcResult = -1;
// Drop down to the wrapped `ObjectContext` to get access to the `Translate` method
ObjectContext objectContext = ((IObjectContextAdapter)aDbContext).ObjectContext;
List<Type> containedDtos = new List<Type>
{
typeof (List<Set1ReturnDto>),
typeof (List<Set1ReturnDto>)
};
return MultiResultsetsHelper.Process<DummyReturnDto>(reader, objectContext, containedDtos);
}
The resulting datasets returned are:
Helper Class
public static class MultiResultsetsHelper
{
/// <summary>
/// Given a data reader that contains multiple result sets, use the supplied object context to serialise the
/// rows of data in the result set into our property.
/// </summary>
/// <typeparam name="T">Type of the containing object that contains all the various result sets.</typeparam>
/// <param name="aDbReader">Database reader that contains all the result sets returned from the database.</param>
/// <param name="aObjectContext">Data context associated with the data reader.</param>
/// <param name="aContainedDataSetReturnedTypes">
/// List of types in order of which the result sets are contained within the
/// data reader. We will serilize sequentially each result set the data reader contains
/// </param>
/// <returns>Retuns an object representing all the result sets returned by the data reader.</returns>
public static T Process<T>(DbDataReader aDbReader, ObjectContext aObjectContext, List<Type> aContainedDataSetReturnedTypes) where T : new()
{
//What we will be returning
T result = new T();
for (int datasetNdx = 0; datasetNdx < aContainedDataSetReturnedTypes.Count; datasetNdx++)
{
//Advance the reader if we are not looking at the first dataset
if (datasetNdx != 0)
aDbReader.NextResult();
//Get the property we are going to be updating based on the type of the class we will be filling
PropertyInfo propertyInfo = typeof (T).GetProperties().Single(p => p.PropertyType == aContainedDataSetReturnedTypes[datasetNdx]);
//Now get the object context to deserialize what is in the resultset into our type
var valueForProperty = aObjectContext.Translate <aContainedDataSetReturnedTypes[datasetNdx]> (aDbReader);
//Finally we update the property with the type safe information
propertyInfo.SetValue(result, valueForProperty, null);
}
return result;
}
}
However currently I cannot get this to compile.
Error 2 Operator '<' cannot be applied to operands of type 'method
group' and 'System.Type'
Can someone help out? Ultimately it has to do with how we use reflection and the passed in aContainedDataSetReturnedTypes. I am happy to change things around as long as it is still easy to call MultiResultsetsHelper.Process<>()
解决方案 With the help of all of the above I came up with the following (which can still be improved):
public static class MultiResultsetsHelper
{
/// <summary>
/// Given a data reader that contains multiple result sets, use the supplied object context to serialise the
/// rows of data in the result set into our property.
/// </summary>
/// <typeparam name="T">Type of the containing object that contains all the various result sets.</typeparam>
/// <param name="aDbReader">Database reader that contains all the result sets returned from the database.</param>
/// <param name="aDbContext">Data context associated with the data reader.</param>
/// <param name="aDataSetTypes">Type for each type to use when we call Translate() on the current result in the data reader.</param>
/// <param name="aContainedDataSetReturnedTypes">
/// List of types in order of which the result sets are contained within the
/// data reader. We will serilize sequentially each result set the data reader contains
/// </param>
/// <returns>Retuns an object representing all the result sets returned by the data reader.</returns>
public static T Process<T>(DbDataReader aDbReader, DbContext aDbContext, List<Type> aDataSetTypes, List<Type> aContainedDataSetReturnedTypes) where T : new()
{
//What we will be returning
T result = new T();
// Drop down to the wrapped `ObjectContext` to get access to the `Translate` method
ObjectContext objectContext = ((IObjectContextAdapter) aDbContext).ObjectContext;
//Iterate the passed in dataset types as they are in the same order as what the reader contains
for (int datasetNdx = 0; datasetNdx < aContainedDataSetReturnedTypes.Count; datasetNdx++)
{
//Advance the reader if we are not looking at the first dataset
if (datasetNdx != 0)
aDbReader.NextResult();
//Get the property we are going to be updating based on the type of the class we will be filling
PropertyInfo propertyInfo = typeof (T).GetProperties().Single(p => p.PropertyType == aContainedDataSetReturnedTypes[datasetNdx]);
//Now get the object context to deserialize what is in the resultset into our type
MethodInfo method = GetTranslateOverload(typeof (ObjectContext));
MethodInfo generic = method.MakeGenericMethod(aDataSetTypes[datasetNdx]);
//Invoke the generic method which we hvae constructed for Translate
object valueForProperty = generic.Invoke(objectContext, new object[] {aDbReader});
//Finally we update the property with the type safe information
propertyInfo.SetValue(result, valueForProperty);
}
return result;
}
/// <summary>
/// Internal helper method to get the necessary translate overload we need:
/// ObjectContext.Translate<T>(DbReader)
/// </summary>
/// <param name="aType">ObjectContext.GetType()</param>
/// <returns>Returns the method we require, null on error.</returns>
private static MethodInfo GetTranslateOverload(Type aType)
{
MethodInfo myMethod = aType
.GetMethods()
.Where(m => m.Name == "Translate")
.Select(m => new
{
Method = m,
Params = m.GetParameters(),
Args = m.GetGenericArguments()
})
.Where(x => x.Params.Length == 1
&& x.Args.Length == 1
&& x.Params[0].ParameterType == typeof (DbDataReader)
// && x.Params[0].ParameterType == x.Args[0]
)
.Select(x => x.Method)
.First();
return myMethod;
}
}
So assuming you have:
public class UspGetSideBarListReturnDto
{
public List<Set1ReturnDto> Dummy1 { get; set; }
public List<Set2ReturnDto> Dummy2 { get; set; }
}
public class Set1ReturnDto
{
public Int32 CruiseCount { get; set; }
public string DisplayText { get; set; }
public Int64 DisplayValue { get; set; }
}
public class Set2ReturnDto
{
public string DepartingFrom { get; set; }
public string Port_Code { get; set; }
}
You can call it as:
DbDataReader reader = dbCommand.ExecuteReader();
return MultiResultsHelper.Process<UspGetSideBarListReturnDto>(reader, myDbContext, new List<Type>{typeof(Set1ReturnDto), typeof(Set2ReturnDto)}, new List<Type>{typeof(List<Set1ReturnDto>), typeof(List<Set2ReturnDto>});
The order of the aDataSetTypes needs to correspond to the list of result sets in the aDbReader.
Improvements would be:
- Only pass the list of dataset types. (And have the List properties automatically determined)
这篇关于处理来自EF存储过程的多个结果集的通用方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
