通用QueryMultiple与Dapper [英] Generic QueryMultiple with Dapper

问题描述

我有一个使用Generic Get方法的基础存储库，使用Dapper返回数据，如

  public T Get< T>（ Func< IDbConnection，T> query）
 {
 using（IDbConnection db = new SqlConnection（ConfigurationManager.ConnectionStrings [myDB] .ConnectionString））
 {
 return query.Invoke （D b）; 
} 
} 
  

然而我现在需要返回多个数据。 DAL查询如下：

  var multi = db.QueryMultiple（getCarDataSp，new {CustomerID = customerId，Year = year} ，
 commandType：CommandType.StoredProcedure））; 
 
 var cars = multi.Read< CarDTO>（）; 
 var options = multi.Read< CarOptionDTO>（）; 
 
 //将选项连接到汽车
 foreach（汽车中的var car）{
 var carOptions = options.Where（w => w.Car.CarID == car.CarID）; //我会一般重写Equals，这样你就可以写出w.Car.Equals（car）...在普通的DataModel类上做这个
 car.Options = carOptions.ToList（）; 
} 
  

有可能在我的BaseRepository中有一个通用的GetMultiple，在我的Get方法中包装get multi，然后汽车和选项在它们自己单独的Get调用中吗？

做这样的事情，它将返回一个包含每个类型的IEnumerables的元组。

在Base Repository中

（它基本上是一堆重载......如果你有更多的类型，你可以添加更多的重载）。

  public Tuple< IEnumerable< T1>，IEnumerable< T2>> GetMultiple< T1，T2>（字符串sql，对象参数，
 Func< GridReader，IEnumerable< T1>> func1，
 Func< GridReader，IEnumerable< T2>> func2）
 { 
 var objs = getMultiple（sql，parameters，func1，func2）; 
返回Tuple.Create（objs [0]作为IEnumerable< T1>，objs [1]作为IEnumerable< T2>）; 
} 
 
 public Tuple< IEnumerable< T1>，IEnumerable< T2>，IEnumerable< T3>> GetMultiple< T1，T2，T3>（string sql，object parameters，
 Func< GridReader，IEnumerable< T1> func1，
 Func< GridReader，IEnumerable< T2>> func2，
 Func< GridReader，IEnumerable< T3>> func3）
 {
 var objs = getMultiple（sql，parameters，func1，func2，func3）; 
返回Tuple.Create（objs [0]作为IEnumerable< T1>，objs [1]作为IEnumerable< T2>; objs [2]作为IEnumerable< T3> 
} 
 
私人列表< object> getMultiple（string sql，object parameters，params Func< GridReader，object> [] readerFuncs）
 {
 var returnResults = new List< object>（）; 
 using（IDbConnection db = new SqlConnection（ConfigurationManager.ConnectionStrings [myDB] .ConnectionString））
 {
 var gridReader = db.QueryMultiple（sql，parameters）; 
 
 foreach（readerFuncs中的var readerFunc）
 {
 var obj = readerFunc（gridReader）; 
 returnResults.Add（obj）; 
} 
} 
 
 return returnResults; 
} 
  

衍生版本库

（非常干净，更重要的是，输入！）

  public class Foo {} 
 $ b $ public class Bar {} 
 
 public void sample（）
 {
 var sql =select * from Foo; select * from Bar; 
 var foosAndBars = this.GetMultiple（sql，new {param =baz}，gr => gr.Read< Foo>（），gr => gr.Read< Bar>（））; 
 var foos = foosAndBars.Item1; 
 var bars = foosAndBars.Item2; 
} 
  

I have a base repository with a Generic Get method to return Data using Dapper like

  public T Get<T>(Func<IDbConnection, T> query)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
        {
            return query.Invoke(db);
        }
    }

However I now have the need to return multiple Data. The DAL query is as below:

var multi = db.QueryMultiple(getCarDataSp , new { CustomerID = customerId, Year = year },
                                commandType: CommandType.StoredProcedure));

var cars = multi.Read<CarDTO>();
var options = multi.Read<CarOptionDTO>();

//wire the options to the cars
foreach(var car in cars){
    var carOptions = options.Where(w=>w.Car.CarID == car.CarID);        //I would override Equals in general so you can write w.Car.Equals(car)...do this on a common DataModel class
    car.Options = carOptions.ToList();
}

Would it be possible to have a Generic GetMultiple in my BaseRepository or would it be a matter of wrapping the get multi in my Get method and then cars and options in there own separate Get calls?

解决方案

You could do something like this which will return a tuple containing IEnumerables of each type you are after.

In Base Repository

(It's basically a bunch of overloads...you can add more overloads if you have more types).

public Tuple<IEnumerable<T1>, IEnumerable<T2>> GetMultiple<T1, T2>(string sql, object parameters,
                                        Func<GridReader, IEnumerable<T1>> func1,
                                        Func<GridReader, IEnumerable<T2>> func2)
        {
            var objs = getMultiple(sql, parameters, func1, func2);
            return Tuple.Create(objs[0] as IEnumerable<T1>, objs[1] as IEnumerable<T2>);
        }

        public Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>> GetMultiple<T1, T2, T3>(string sql, object parameters,
                                        Func<GridReader, IEnumerable<T1>> func1,
                                        Func<GridReader, IEnumerable<T2>> func2,
                                        Func<GridReader, IEnumerable<T3>> func3)
        {
            var objs = getMultiple(sql, parameters, func1, func2, func3);
            return Tuple.Create(objs[0] as IEnumerable<T1>, objs[1] as IEnumerable<T2>, objs[2] as IEnumerable<T3>);
        }

        private List<object> getMultiple(string sql, object parameters,params Func<GridReader,object>[] readerFuncs )
        {
            var returnResults = new List<object>();
            using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
            {
                var gridReader = db.QueryMultiple(sql, parameters);

                foreach(var readerFunc in readerFuncs)
                {
                    var obj = readerFunc(gridReader);
                    returnResults.Add(obj);
                }
            }

            return returnResults;
        }

In Derived Repository

(pretty clean and more importantly, typed!)

public class Foo { }

        public class Bar { }

        public void sample()
        {
            var sql = "select * from Foo; select * from Bar";
            var foosAndBars = this.GetMultiple(sql, new { param = "baz" }, gr => gr.Read<Foo>(), gr => gr.Read<Bar>());
            var foos = foosAndBars.Item1;
            var bars = foosAndBars.Item2;
        }

这篇关于通用QueryMultiple与Dapper的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！