We have a procedure that is dynamically filtered and based on the input parameters, it generates the filter dynamically, and in the output, if there is a record for it, it is displayed based on the result set.
CREATE PROCEDURE dbo.GetSalesWithDetails
(@HistoryID INT = NULL,
@ShDetailID INT = NULL,
@DateOfSaleFrom DateTime = NULL,
@DateOfSaleTo DateTime = NULL,
@CustomerID INT = NULL)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Sql Nvarchar(Max),
@Paramlist Nvarchar(Max),
@AllColumn Nvarchar(max)
SET @Sql = N'
SELECT s.HistoryID,
shd.ShDetailID,
s.DateOfSale,
s.SaleCityID,
s.SalesPersonID,
s.CustomerID,
sf.StfName,
sf.StfSname,
gd.GoodsName,
cu.CsName,
cu.CsSname,
c.CityName,
shd.GoodsQuantity,
shd.UnitPrice,
shd.Discount,
shd.Tax,
shd.OtherCosts,
shd.TotalPriceBefore,
shd.TotalPriceAfterCosts
FROM dbo.tblCity AS c INNER JOIN
dbo.tblSalesHistory AS s ON c.CityID = s.SaleCityID INNER JOIN
dbo.tblCustomers AS cu ON s.CustomerID = cu.CsID INNER JOIN
dbo.tblSalesHistoryDetails AS shd ON s.HistiryID = shd.SaleID INNER JOIN
dbo.tblStaffs AS sf ON s.SalesPersonID = sf.StfID CROSS JOIN
dbo.tblGoods AS gd
where 1 = 1 '
IF NOT @HistoryID IS NULL SET @Sql = N' AND HistoryID = @HistoryID '
IF NOT @HistoryID IS NULL SET @Sql = N' AND ShDetailID = @ShDetailID '
IF NOT @CustomerID IS NULL SET @Sql = N' AND CustomerID = @CustomerID '
IF NOT @DateOfSaleFrom IS NULL SET @Sql = N' AND DateOfSale >= @DateOfSaleFrom '
IF NOT @DateOfSaleTo IS NULL SET @Sql = N' AND DateOfSale <= @DateOfSaleTo '
Set @Paramlist = '@HistoryID INT = NULL,
@ShDetailID INT = NULL,
@DateOfSaleFrom DateTime = NULL,
@DateOfSaleTo DateTime = NULL,
@CustomerID INT = NULL'
Exec sp_executesql @Sql ,@Paramlist,
@HistoryID,
@ShDetailID,
@DateOfSaleFrom,
@DateOfSaleTo,
@CustomerID
WITH RESULT SETS
((
SaleHistoryID INT Not Null,
ShDetailID INT Not Null,
DateOfSale DateTime Not Null,
SaleCityID INT Not Null,
SalesPersonID INT Not Null,
CustomerID INT Not Null,
StfName Nvarchar(150) Not Null,
StfSname Nvarchar(150) Not Null,
GoodsName Nvarchar(150) Not Null,
CsName Nvarchar(150) Not Null,
CsSname Nvarchar(150) Not Null,
CityName Nvarchar(150) Not Null,
GoodsQuantity Decimal(18,2) not null,
UnitPrice Decimal(18,2) not null,
Discount Decimal(18,2) not null,
Tax Decimal(18,2) not null,
OtherCosts Decimal(18,2) not null,
TotalPriceBefore Decimal(18,2) not null,
TotalPriceAfterCosts Decimal(18,2) not null
))
END
I have created the same structure used in the procedure in the Entity Framework, but I have encountered problems in creating filters with the Where condition. For example, it wants the conditions like the procedure to be used and the C# method to be able to automatically generate the filter.
public object GetSalesDetailsHistory(int? HistoryID = null, int? ShDetailID = null,
DateTime? DateOfSaleFrom = null, DateTime? DateOfSaleTo = null,
int? CustomerID = null)
{
EfM.Model1 db = new Model1();
var Query = (from row1 in db.tblSalesHistories
join row2 in db.tblSalesHistoryDetails on row1.HistiryID equals row2.ShDetailID
join row3 in db.tblCities on row1.SaleCityID equals row3.CityID
join row4 in db.tblCustomers on row1.CustomerID equals row4.CsID
join row5 in db.tblStaffs on row1.SalesPersonID equals row5.StfID
join row6 in db.tblGoods on row2.GoodsID equals row6.GoodsID
select new
{
row1.HistiryID,
row2.ShDetailID,
row1.DateOfSale,
row1.SaleCityID,
row1.SalesPersonID,
row1.CustomerID,
row5.StfName,
row5.StfSname,
row6.GoodsName,
row4.CsName,
row4.CsSname,
row3.CityName,
row2.GoodsQuantity,
row2.UnitPrice,
row2.Discount,
row2.Tax,
row2.OtherCosts,
row2.TotalPriceBefore,
row2.TotalPriceAfterCosts
}
).ToList();
return Query;
}
The next thing I am facing is that in the dynamic query method in the procedure, when the procedure is complex and heavy, it is very difficult and time-consuming to fix the procedure's bugs and develop or edit it. Is there any other way to filter the list without using dynamic query?
CodePudding user response：
You can do this by checking if a filter exists and if it does, apply it.
CREATE PROCEDURE dbo.GetSalesWithDetails
(
@HistoryID INT = NULL,
@ShDetailID INT = NULL,
@DateOfSaleFrom DateTime = NULL,
@DateOfSaleTo DateTime = NULL,
@CustomerID INT = NULL
)
AS
SELECT s.HistoryID,
shd.ShDetailID,
s.DateOfSale,
s.SaleCityID,
s.SalesPersonID,
s.CustomerID,
sf.StfName,
sf.StfSname,
gd.GoodsName,
cu.CsName,
cu.CsSname,
c.CityName,
shd.GoodsQuantity,
shd.UnitPrice,
shd.Discount,
shd.Tax,
shd.OtherCosts,
shd.TotalPriceBefore,
shd.TotalPriceAfterCosts
FROM dbo.tblCity AS c INNER JOIN
dbo.tblSalesHistory AS s ON c.CityID = s.SaleCityID INNER JOIN
dbo.tblCustomers AS cu ON s.CustomerID = cu.CsID INNER JOIN
dbo.tblSalesHistoryDetails AS shd ON s.HistiryID = shd.SaleID INNER JOIN
dbo.tblStaffs AS sf ON s.SalesPersonID = sf.StfID CROSS JOIN
dbo.tblGoods AS gd
where
(@HistoryID IS NULL OR HistoryID = @HistoryID)AND
(@HistoryID IS NULL OR ShDetailID = @ShDetailID)AND
(@CustomerID IS NULL OR CustomerID = @CustomerID)AND
(@DateOfSaleFrom IS NULL OR DateOfSale >= @DateOfSaleFrom)AND
(@DateOfSaleTo IS NULL OR DateOfSale <= @DateOfSaleTo)
CodePudding user response：
Change your procedure as follows:
CREATE PROCEDURE dbo.GetSalesWithDetails
(
@HistoryID INT = NULL,
@ShDetailID INT = NULL,
@DateOfSaleFrom DateTime = NULL,
@DateOfSaleTo DateTime = NULL,
@CustomerID INT = NULL
)
AS
BEGIN
SET NOCOUNT ON;
SELECT s.HistoryID,
shd.ShDetailID,
s.DateOfSale,
s.SaleCityID,
s.SalesPersonID,
s.CustomerID,
sf.StfName,
sf.StfSname,
gd.GoodsName,
cu.CsName,
cu.CsSname,
c.CityName,
shd.GoodsQuantity,
shd.UnitPrice,
shd.Discount,
shd.Tax,
shd.OtherCosts,
shd.TotalPriceBefore,
shd.TotalPriceAfterCosts
FROM dbo.tblCity AS c INNER JOIN
dbo.tblSalesHistory AS s ON c.CityID = s.SaleCityID INNER JOIN
dbo.tblCustomers AS cu ON s.CustomerID = cu.CsID INNER JOIN
dbo.tblSalesHistoryDetails AS shd ON s.HistiryID = shd.SaleID INNER JOIN
dbo.tblStaffs AS sf ON s.SalesPersonID = sf.StfID CROSS JOIN
dbo.tblGoods AS gd
where 1 = 1
AND (@HistoryID is null OR HistoryID = @HistoryID)
AND (@HistoryID IS NULL OR ShDetailID = @ShDetailID)
AND (@CustomerID IS NULL OR CustomerID = @CustomerID)
AND (@DateOfSaleFrom IS NULL OR DateOfSale >= @DateOfSaleFrom)
AND (@DateOfSaleTo IS NULL OR DateOfSale <= @DateOfSaleTo )
END
and change your EF method as below:
public object GetSalesDetailsHistory(int? HistoryID = null, int? ShDetailID = null,
DateTime? DateOfSaleFrom = null, DateTime? DateOfSaleTo = null,
int? CustomerID = null)
{
EfM.Model1 db = new Model1();
var Query = (from row1 in db.tblSalesHistories
join
row2 in db.tblSalesHistoryDetails on row1.HistiryID equals row2.ShDetailID
join
row3 in db.tblCities on row1.SaleCityID equals row3.CityID
join
row4 in db.tblCustomers on row1.CustomerID equals row4.CsID
join
row5 in db.tblStaffs on row1.SalesPersonID equals row5.StfID
join
row6 in db.tblGoods on row2.GoodsID equals row6.GoodsID
where
(
(HistoryID == null || row1.HistiryID == HistoryID)
&&
(ShDetailID == null || row2.ShDetailID == ShDetailID)
&&
(DateOfSaleFrom == null || row1.DateOfSale >= DateOfSaleFrom)
&&
(DateOfSaleTo == null || row1.DateOfSale <= DateOfSaleTo)
&&
(CustomerID == null || row1.CustomerID == CustomerID)
)
select new
{
row1.HistiryID,
row2.ShDetailID,
row1.DateOfSale,
row1.SaleCityID,
row1.SalesPersonID,
row1.CustomerID,
row5.StfName,
row5.StfSname,
row6.GoodsName,
row4.CsName,
row4.CsSname,
row3.CityName,
row2.GoodsQuantity,
row2.UnitPrice,
row2.Discount,
row2.Tax,
row2.OtherCosts,
row2.TotalPriceBefore,
row2.TotalPriceAfterCosts
}
).ToList();
return Query;
}
And it is better to create a class as follows instead of return value of object type and change your method as follows:
public class ClassSaleResults
{
public int HistiryID { set; get; }
public int ShDetailID { set; get; }
public DateTime DateOfSale { set; get; }
public int SaleCityID { set; get; }
public int SalesPersonID { set; get; }
public int CustomerID { set; get; }
public string StfName { set; get; }
public string StfSname { set; get; }
public string GoodsName { set; get; }
public string CsName { set; get; }
public string CsSname { set; get; }
public string CityName { set; get; }
public decimal GoodsQuantity { set; get; }
public decimal UnitPrice { set; get; }
public decimal Discount { set; get; }
public decimal Tax { set; get; }
public decimal OtherCosts { set; get; }
public decimal TotalPriceBefore { set; get; }
public decimal TotalPriceAfterCosts { set; get; }
}
public List<ClassSaleResults> GetSalesDetailsHistory(int? HistoryID = null, int? ShDetailID = null,
DateTime? DateOfSaleFrom = null, DateTime? DateOfSaleTo = null,
int? CustomerID = null)
{
EfM.Model1 db = new Model1();
List<ClassSaleResults> Query = (from row1 in db.tblSalesHistories
join
row2 in db.tblSalesHistoryDetails on row1.HistiryID equals row2.ShDetailID
join
row3 in db.tblCities on row1.SaleCityID equals row3.CityID
join
row4 in db.tblCustomers on row1.CustomerID equals row4.CsID
join
row5 in db.tblStaffs on row1.SalesPersonID equals row5.StfID
join
row6 in db.tblGoods on row2.GoodsID equals row6.GoodsID
where
(
(HistoryID == null || row1.HistiryID == HistoryID)
&&
(ShDetailID == null || row2.ShDetailID == ShDetailID)
&&
(DateOfSaleFrom == null || row1.DateOfSale >= DateOfSaleFrom)
&&
(DateOfSaleTo == null || row1.DateOfSale <= DateOfSaleTo)
&&
(CustomerID == null || row1.CustomerID == CustomerID)
)
select new ClassSaleResults()
{
HistiryID = row1.HistiryID,
ShDetailID = row2.ShDetailID,
DateOfSale = row1.DateOfSale,
SaleCityID = row1.SaleCityID,
SalesPersonID = row1.SalesPersonID,
CustomerID = row1.CustomerID,
StfName = row5.StfName,
StfSname = row5.StfSname,
GoodsName = row6.GoodsName,
CsName = row4.CsName,
CsSname = row4.CsSname,
CityName = row3.CityName,
GoodsQuantity = row2.GoodsQuantity,
UnitPrice = row2.UnitPrice,
Discount = row2.Discount,
Tax = row2.Tax,
OtherCosts = row2.OtherCosts,
TotalPriceBefore = row2.TotalPriceBefore,
TotalPriceAfterCosts = row2.TotalPriceAfterCosts
}
).ToList<ClassSaleResults>();
return Query;
}
With this method, you can access the structure and more details of the list and class.