Dapper的使用整理

Dapper's Usage

基础要点

基于IDbconnection进行的扩展

数据字段映射规则

Dapper根据SQL里的字段名称进行映射，所以即使实体里有很多字段，Dapper也只映射SQL里的字段数据，进行赋值操作。

使用动态参数化

var pms = new DynamicParameters();
 if (S.Empty!=query.SKU)
 {
     pms.Add("@SKU", query.SKU, DbType.String);
     sqlQuery += " AND c.SKU=@SKU";
 }
using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
    var rows = conn.Query<M>(sqlQuery, pms).AsList();

    if (rows.Count == 0)
        return new Tuple<IEnumerable<M>, int>(rows, 0);
    return new Tuple<IEnumerable<M>, int>(rows, rows[0].TotalCount);
}

查询返回多个结果集

返回实体数据：默认是一个集合，并且不为空，如果没有查询到数据，返回count==0，所以如果是单个实体的话，需要判断count>0的时候才执行：FirstOrDefault的操作。

using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
{
  var dbRlt = conn.QueryMultiple(S.Concat(sqlMain, sqlDetail), pms);
  var main = dbRlt.Read<M>().AsList();
  var mtpMain = main.Count() > 0 ? main.First() : new M();
  var details = dbRlt.Read<M.Detail>().AsList();
  mtpMain.Details = new Tuple<IEnumerable<M.Detail>, int>(details, details.Count());
  SingleOrderAmountLimit = mul.Read<decimal>().FirstOrDefault();
  rlt = mtpMain;
}

多嵌套数据集结果映射

其实就是通过：spliton参数进行字段实体的分开识别处理，然后对映射出来的结果进行分组处理

public IEnumerable<M> GetStockoutOrderConfirm(int topCount, int warehouseID, int customerID)
        {
            var rlt = new M();
            var pms = new DynamicParameters();
            var sqlMain = @$"
                    SELECT  TOP (@topCount)
                            --order
                            a.ID,a.OrderNbr
                            --orderItem
                            ,ca.ProduceDate
                            ,'sbatch' sbatch
                            ,ca.ItemProduceDate
                            --orderitem-batch
                    FROM x.StockoutOrder a 
                    JOIN x.LogisticsOrder b ON a.OrderID=b.ID
                    JOIN x.LogisticsOrderDetail c ON a.OrderID=c.OrderID
                    --orderitem信息
                    CROSS APPLY 
                    (
                        SELECT  ID
                        FROM x.StockoutOrderItem xa
                        CROSS APPLY
                        (
                            SELECT  xxa.InvLotAttID
                            FROM    x.InventoryLotAllocation xxa
                            JOIN    x.InventoryLotAtt xxb on xxa.InvLotAttID=  xxb.ID
                            WHERE   OrderLineID = xa.ID
                        )caa
                        WHERE StockoutOrderID=a.ID
                    )ca
                    WHERE   a.WarehouseID=@warehouseID
                            AND a.CustomerID = @customerID
                            AND a.EdiFlag = 10
                            AND a.[Status]>=60 
                    ORDER BY CheckFinishTime;";
            pms.Add("@topCount", topCount, DbType.Int32);
            pms.Add("@warehouseID", warehouseID, DbType.Int32);
            pms.Add("@customerID", customerID, DbType.Int32);
            var lookup = new Dictionary<string, M>();
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection())
            {

                var list = conn.Query<M, M.OrderLine, M.OrderLine.Batch, M>(sqlMain, (order, orderline, batch) =>
                 {
                     //订单信息
                     M tmp;
                     //没有订单信息执行增加
                     if (!lookup.TryGetValue(order.ID, out tmp))
                     {
                         tmp = order;
                         lookup.Add(order.ID, tmp);
                     }
                     //orderline增加
                     var tmpOrderLine = tmp.OrderLines.FirstOrDefault(f => f.OrderLineID == orderline.OrderLineID);
                     if (tmpOrderLine == null)
                     {
                         tmpOrderLine = orderline;
                         tmp.OrderLines.Add(tmpOrderLine);
                     }
                     //batch
                     if (tmpOrderLine.Batches.Count == 0
                      || tmpOrderLine.Batches.FirstOrDefault(x => x.InvLotAttID == batch.InvLotAttID) == null
                      )
                     {
                         tmpOrderLine.Batches.Add(batch);
                     }
                     return order;
                 }
                , pms, splitOn: "sorderlineId,sbatch").ToList();
            }
            return lookup.Values.ToList();
        }

使用in查询

dapper为了安全，所以in参数必须转换为数组形式传入

//dapper in写法，必须转换为array的形式
pms.Add("@Status", query.Status.Split(",", StringSplitOptions.RemoveEmptyEntries).ToArray());
sqlQuery += " AND Status IN @Status";

问题

系统数据库日期格式读取，处理成字符串接收的话：格式13/10/2020 16:10:19当前版本Dapper暂时还没有修复