Dapper ORM에서 두 날짜 간의 데이터 필터링

.net c# dapper sql sql-server

문제

나는 Dapper ORM을 사용하여 객체 컬렉션을 필터링하고 가져옵니다. 내가 날짜로 개체를 필터링하려고하면 SQL 오류가 발생합니다. 하지만 동일한 쿼리는 SQL Server 2012에서 완벽하게 실행됩니다. 아래 코드는

                DataTableSettings DS = new DataTableSettings();
                DataTableElements DataTableElements = new DataTableElements();
                DataTableElements.Limit = param.iDisplayLength;
                DataTableElements.Start = param.iDisplayStart;
                DataTableElements.SortColumn = "a.Ser_Id"; //default sorting column
                DataTableElements.AType = "DESC";
                DataTableElements.ListingFields = "Ser_SlNo,a.Ser_Id as SERID,a.Ser_LogNo AS SLNO,Cas_Name,a.Ser_CallReason AS RSON," + 
                                                  "Ser_AssignedTo,Ser_CallTime,Ser_CallDate,a.Cus_Id AS CUSID,a.Cas_Id AS CSID," +
                                                  "Cus_Name,Add_Landmark,a.Prm_Id AS PRMID,Prm_Model, c.Cut_Id AS CUTID,Cut_Name";
                DataTableElements.TableName = "ServiceCalls as a inner join CallStatus as b on a.Cas_Id = b.Cas_Id" + 
                                                  "inner join Customers as c on a.Cus_Id = c.Cus_Id" +  
                                                  "inner join Products as d on a.Prd_Id = d.Prd_Id" + 
                                                  "inner join AddressDetailes as e on a.Add_id = e.Add_id" +
                                                  "inner join Companies as f on a.Com_Id = f.Com_Id" + 
                                                  "inner join ProductModels as g on a.Prm_Id = g.Prm_Id" + 
                                                  "inner join ProductSerials as h on a.Prs_Id = h.Prs_Id" + 
                                                  "inner join CustomerTypes as i on c.Cut_Id = i.Cut_Id" + 
                                                  "inner join UserServices as j on a.Ser_Id = j.Ser_Id" +
                                                  "where CONVERT(date, Ser_CallDate,101) BETWEEN CONVERT(date,'3/1/2016 12:00:00 AM', 101) AND CONVERT(date,'3/31/2016 12:00:00 AM', 101)" +
                                                  "and j.Usr_Id = 21";
                  var str = DS.GetDataTableElements(DataTableElements);


 public dynamic GetDataTableElements(DataTableElements DataTableElements)
        {
            _db = new SqlConnection(db.Database.Connection.ConnectionString);
            string Sql = "exec SelectFomTable    @TableName ='" + DataTableElements.TableName + "',@Limit =" + DataTableElements.Limit + ",@Start =" + DataTableElements.Start + ",@SortColumn ='" + DataTableElements.SortColumn + "'   ";

            Sql += ",@ListingFields='" + DataTableElements.ListingFields + "'";
            Sql += ",@AType='" + DataTableElements.AType + "'";
            Sql += ",@Filter='" + DataTableElements.Filter + "'";
            dynamic retuns = _db.Query<dynamic>(Sql).ToList();
            return retuns;
        }

내 저장 프로 시저는 다음과 같습니다.

ALTER PROCEDURE  [dbo].[SelectFomTable] 
-- Add the parameters for the stored procedure here
(@TableName varchar(max),@Limit Int=10,@Start int=0,@SortColumn varchar(50),@Filter varchar(max)=null,@ListingFields varchar(max)=null,@AType varchar(max)=null )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @sts varchar(max);
Declare @List varchar(max);
Declare @Assend varchar(max);
Declare @End int ;
set @End =@Limit+@Start;
set @List =isnull(@ListingFields,'*');
set @Assend =isnull(@AType,'ASC');
set @sts= 'WITH tab as (select  ROW_NUMBER() OVER(ORDER BY '+@SortColumn+'  '+@Assend+' ) AS RowIncrement, '+@List+'  from '+@TableName+@Filter+'  ),
  tab1 as (select count(*) as TotalCount  from '+@TableName+@Filter+' )
               select * from tab,tab1 where RowIncrement between '+CONVERT(varchar(10), @Start) +' and '+CONVERT(varchar(10), @End);
  set @sts=@sts+@Filter;
--if ( @Filter is not null)
--begin
--set @sts=@sts+@Filter;
--end 
print @sts;
    exec(@sts);

그리고 내가 받고있는 오류는 다음과 같습니다

{"Incorrect syntax near '3'."}

도와주세요.....

인기 답변

내가 올바르게 이해한다면 주된 질문은 SQL에서 정렬 된 데이터를 얻는 것이다. 그렇다면 다음과 같이하십시오.

private DataTable Method()
    {
        string connectionStringSQL = "Your connection string";
        string query = @"SELECT Ser_SlNo, a.Ser_Id AS SERID, a.Ser_LogNo AS SLNO, Cas_Name
                              ,a.Ser_CallReason AS RSON, Ser_AssignedTo, Ser_CallTime
                              ,Ser_CallDate, a.Cus_Id AS CUSID, a.Cas_Id AS CSID, Cus_Name
                              ,Add_Landmark, a.Prm_Id AS PRMID, Prm_Model, c.Cut_Id AS CUTID, Cut_Name
                        FROM   ServiceCalls AS a
                               INNER JOIN CallStatus AS b ON  a.Cas_Id = b.Cas_Id
                               INNER JOIN Customers AS c ON  a.Cus_Id = c.Cus_Id
                               INNER JOIN Products AS d ON  a.Prd_Id = d.Prd_Id
                               INNER JOIN AddressDetailes AS e ON  a.Add_id = e.Add_id
                               INNER JOIN Companies AS f ON  a.Com_Id = f.Com_Id
                               INNER JOIN ProductModels AS g ON  a.Prm_Id = g.Prm_Id
                               INNER JOIN ProductSerials AS h ON  a.Prs_Id = h.Prs_Id
                               INNER JOIN CustomerTypes AS i ON  c.Cut_Id = i.Cut_Id
                               INNER JOIN UserServices AS j ON  a.Ser_Id = j.Ser_Id
                        WHERE  (
                                   CONVERT(DATETIME ,Ser_CallDate ,101) BETWEEN '3/1/2016 12:00:00 AM'
                                   AND '3/31/2016 12:00:00 AM'
                               )
                               AND j.Usr_Id = 21
                        ORDER BY a.Ser_Id DESC";
        DataTable dt = new DataTable();
        using (SqlConnection sqlConnection = new SqlConnection(connectionStringSQL))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, sqlConnection);

            sqlConnection.Open();
            adapter.Fill(dt);
        }

        return dt;
    }

목록을 반환해야하는 경우

private dynamic Method()
    {
        .....

        using (SqlConnection sqlConnection = new SqlConnection(connectionStringSQL))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query1, sqlConnection);

            sqlConnection.Open();
            adapter.Fill(dt);
        }

        var list = dt.AsEnumerable().ToList();

        return list;
    }


아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow