首页 > 代码库 > 实现查询与存储过程编译转换

实现查询与存储过程编译转换

2024-07-10 15:23:04   221人阅读

使用标准查询可方便将查询编译为存储过程,以后再查询时会匹配对应的存储过程进行调用

方法原型

 1 /// <summary> 2         /// 使用lamada设置条件查询 3         /// </summary> 4         /// <typeparam name="TItem"></typeparam> 5         /// <param name="expression"></param> 6         /// <param name="compileSp">是否编译成储过程</param> 7         /// <returns></returns> 8         public List<TItem> QueryList<TItem>(Expression<Func<TItem, bool>> expression =null, bool compileSp = false) where TItem : IModelBase, new() 9         {10             LamadaQuery<TItem> query = new LamadaQuery<TItem>();11             query = query.Select(b => b.SelectField()).Where(expression);12             string key;13             return QueryList<TItem>(query, 0, out key, compileSp);14         }

调用:

public List<ProductReceiptDetail> QueryDetail(string batchNo)        {            var helper = dbHelper;            var list = helper.QueryList<ProductReceiptDetail>(b => b.BatchNo == batchNo,compileSp:true);            return list;        }

 

编译转换后的存储过程类似如下:

1  CREATE PROCEDURE [dbo].ZautoSp_1114C09A54B9CFAF2 (@0AccountId nvarchar(500),@1TradeType nvarchar(500),@3Hash nvarchar(500))3 AS4 set  nocount  on5     select top 1 t1.AccountId,t1.AddTime,t1.Amount,t1.CurrentBalance,t1.Hash,t1.Id,t1.LastBalance,t1.Name,t1.OperateType,t1.OutOrderId,t1.Remark,t1.TradeType,t1.TransactionNo,t1.TransactionType from [AccountTransaction] as t1 with (nolock)  where (((t1.AccountId=@0AccountId) AND (t1.TradeType=@1TradeType)) AND (t1.Hash=@3Hash)) order by  t1.Id desc

当然也可以用原生SQL直接转换:

public List<dynamic> GetSum(int storeId)        {            var helper = dbHelper;            string sql = "select sum(TotalNum) Num,sum(OrderAmount) Amount,datepart(month,AddTime) Month from OrderMain where AddTime  between dateadd(month,datediff(month,-1,getdate())-7,-1) and dateadd(month,datediff(month,-1,getdate())-1,-1) and OwnerId = @storeId and [Status] = @status group by datepart(month,AddTime) ";            helper.AddParam("storeId", storeId);            helper.AddParam("Status", OrderStatus.已确认收货);            return helper.AutoSpQueryDynamic(sql);        }

 


联系
我们