SQL查询语句中用like中用参数化查询

原创

Hello_Martin 2023-03-04 11:35:39 博主文章分类：.Net ©著作权

文章标签 Customer SAP 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者Hello_Martin的原创作品，请联系作者获取转载授权，否则将追究法律责任

public 
  async 
  Task<
  IEnumerable<
  LoadTaskViewModel>>
  GetLoadOrdersAsync(
  DeliveryOrderPagingFilter
 

  {
 
 
using (
  var connection
  = 
  new 
  SqlConnection(
  _connectionString))
 
 

  {
 
 
connection.
  Open();
 
 
var result 
  =
  await 
  connection.
  QueryAsync<
  LoadTaskViewModel>(
 
 
@"SELECT
 
DISTINCT 
 
LT.LoadTask_ID,
 
LT.CarrierCode,
 
LT.DeliveryDate,
 
LT.IsDelivery,
 
LT.QueueSequence,LT.AssignedDockDoor,
 
D.SAP_DeliveryOrder_ID,
 
D.SAP_RequiredDate,
 
C.Name,
 
D.SONumber,
 
D.CustomerPO,
 
P.SAP_Pallet_ID,
 
P.Weight
 
FROM 
 
LoadTask AS LT JOIN LoadTaskDetail AS LTD ON LT.LoadTask_ID = LTD.LoadTask_ID
 
JOIN DeliveryOrder AS D ON LTD.DeliveryOrder_ID = D.DeliveryOrder_ID
 
JOIN Customers AS C ON C.Customer_ID = D.Customer_ID
 
JOIN PickDetails AS PD ON D.DeliveryOrder_ID = PD.DeliveryOrder_ID
 
JOIN Pallets AS P ON P.Pallet_ID = PD.Pallet_ID
 
WHERE (@DeliveryOrderId = '' OR D.SAP_DeliveryOrder_ID like @DeliveryOrderId) AND
 
(@Customer = '' OR @Customer like D.Customer_ID) AND
 
(@SONumber = '' OR @SONumber like D.SONumber) AND
 
(@CustomerPO = '' OR @CustomerPO like D.CustomerPO) AND
 
(@Type = '' OR @Type = D.TypeCode) AND
 
(@Carrier = '' OR @Carrier = LT.CarrierCode) AND
 
(@DispatchDelivery = '' OR @DispatchDelivery = LT.DeliveryDate)
 
AND LT.Status = 'REQUEST'
 
AND P.IsLoadConfirmed = 0
 
AND P.Status = 'STAGED'
 
AND PD.IsCombined = 0
 
                    ORDER BY LT.IsDelivery, LT.QueueSequence DESC
 
                    OFFSET ( @pageNo - 1 ) * @pageSize ROWS
 
                    FETCH NEXT @pageSize ROWS ONLY",
  new
 
DeliveryOrderId 
  = 
  "%" 
  +
  ServiceUtli.
  ConvertNullIntoEmptyString(
  page.
  DeliveryOrderId),
 
 
Customer 
  =
  "%" 
  + 
  ServiceUtli.
  ConvertNullIntoEmptyString(
  page.
  Customer),
 
 
SONumber 
  =
  "%" 
  + 
  ServiceUtli.
  ConvertNullIntoEmptyString(
  page.
  SONumber),
 
 
CustomerPO 
  =
  "%" 
  + 
  ServiceUtli.
  ConvertNullIntoEmptyString(
  page.
  CustomerPO),
 
 
Type 
  =
  ServiceUtli.
  ConvertNullIntoEmptyString(
  page.
  Type),
 
 
Carrier 
  =
  ServiceUtli.
  ConvertNullIntoEmptyString(
  page.
  Carrier),
 
 
DispatchDelivery 
  = 
  ServiceUtli.
  ConvertNullIntoEmptyString(
  page.
  DispatchDelivery),
 
 
pageNo 
  =
  page.
  PageNumber,
  pageSize 
  = 
  page.
  Size,
 
 
progressCode 
  = 
  DeliveryOrderProgressCode.
  Exception
 

  );
 
 
if (
  result.
  AsList().
  Count
  == 
  0)
 
 

  {
 
 
return 
  null;
 
 

  }
 
 
return 
  result;
 
 

  }
 
 

  }