代码之家  ›  专栏  ›  技术社区  ›  john Gu

SQL语句在sqlmanagementstudio中运行良好,而在Dapper[closed]中失败

  •  -4
  • john Gu  · 技术社区  · 4 年前

    我有下面的SQL语句,如果我在SQL管理中运行它,它运行得很好工作室:-

    SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester",aci.emailid "Email ID",wo.TITLE AS "Request Title", ti.FIRST_NAME AS "Technician",  srcmt.COMMENTTEXT AS "Comments",srm.result "Over All Satisfaction Level" 
    FROM Servicedesk.dbo.SurveyResponseRequestMapping srrm 
    INNER JOIN Servicedesk.dbo.Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID 
    INNER JOIN Servicedesk.dbo.Survey_Response_Answer sra ON srm.RESPONSEID=sra.RESPONSEID 
    LEFT JOIN Servicedesk.dbo.Survey_Response_Comment srcmt ON srm.RESPONSEID=srcmt.RESPONSEID 
    LEFT JOIN Servicedesk.dbo.WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID 
    LEFT JOIN Servicedesk.dbo.WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
    LEFT JOIN Servicedesk.dbo.SDUser td ON wos.OWNERID=td.USERID 
    LEFT JOIN Servicedesk.dbo.AaaUser ti ON td.USERID=ti.USER_ID 
    LEFT JOIN Servicedesk.dbo.SDUser sdu ON wo.REQUESTERID=sdu.USERID 
    LEFT JOIN Servicedesk.dbo.AaaUser aau ON sdu.USERID=aau.USER_ID 
    INNER JOIN Servicedesk.dbo.AccountSiteMapping asm ON wo.siteid=asm.siteid 
    INNER JOIN Servicedesk.dbo.AccountDefinition ad ON asm.accountid=ad.org_id
    left join Servicedesk.dbo.aaausercontactinfo auci on auci.user_id = sdu.userid 
    left join Servicedesk.dbo.aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
    where srm.RESPONSETIME>=(cast(Datediff(s, '19700101', '2020-03-22 00:00:00') as bigint)*1000) and srm.RESPONSETIME<=(cast(Datediff(s, '19700101', '2021-03-31 23:59:59') as bigint)*1000)
    

    现在我在我的C#控制台中使用Dapper来运行上面的SQL语句,所以我尝试了以下内容:-

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using Dapper;
    
    namespace ServiceDeskSharePointIntegration
    {
        class Program
        {
            static void Main(string[] args)
            {
                var connectionString = "Server=localhost;Database=ServiceDesk;Trusted_Connection=True";
    
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
     var eventName = connection.QueryFirst<string>("SELECT ad.ORG_NAME AS \"Account\", wo.WORKORDERID AS \"Request ID\", aau.FIRST_NAME AS \"Requester\",aci.emailid \"Email ID\",wo.TITLE AS \"Request Title\", ti.FIRST_NAME AS \"Technician\",  srcmt.COMMENTTEXT AS \"Comments\",srm.result \"Over All Satisfaction Level\" "+
    "FROM Servicedesk.dbo.SurveyResponseRequestMapping srrm" +
    "INNER JOIN Servicedesk.dbo.Survey_Response_Main srm ON srrm.RESPONSEID = srm.RESPONSEID" +
    "INNER JOIN Servicedesk.dbo.Survey_Response_Answer sra ON srm.RESPONSEID = sra.RESPONSEID" +
    "LEFT JOIN Servicedesk.dbo.Survey_Response_Comment srcmt ON srm.RESPONSEID = srcmt.RESPONSEID" +
    "LEFT JOIN Servicedesk.dbo.WorkOrder wo ON srrm.WORKORDERID = wo.WORKORDERID" +
    "LEFT JOIN Servicedesk.dbo.WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID" +
    "LEFT JOIN Servicedesk.dbo.SDUser td ON wos.OWNERID = td.USERID" +
    "LEFT JOIN Servicedesk.dbo.AaaUser ti ON td.USERID = ti.USER_ID" +
    "LEFT JOIN Servicedesk.dbo.SDUser sdu ON wo.REQUESTERID = sdu.USERID" +
    "LEFT JOIN Servicedesk.dbo.AaaUser aau ON sdu.USERID = aau.USER_ID" +
    "INNER JOIN Servicedesk.dbo.AccountSiteMapping asm ON wo.siteid = asm.siteid" +
    "INNER JOIN Servicedesk.dbo.AccountDefinition ad ON asm.accountid = ad.org_id" +
    "left join Servicedesk.dbo.aaausercontactinfo auci on auci.user_id = sdu.userid" +
    "left join Servicedesk.dbo.aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id" +
    "where srm.RESPONSETIME >= (cast(Datediff(s, '19700101', '2020-03-22 00:00:00') as bigint) * 1000) and srm.RESPONSETIME <= (cast(Datediff(s, '19700101', '2021-03-31 23:59:59') as bigint) * 1000)"
    );
                    Console.WriteLine(eventName);
                    Console.ReadLine();
                }
    
            }
        }
    }
    

    但我有这个错误:-

    System.Data.SqlClient.SqlException: 'Incorrect syntax near 'srm'.'
    

    有人能给我一些建议吗?为什么SQL在Dapper中不能正常工作,而在sqlmanagementstudio中却运行良好?

    谢谢

    1 回复  |  直到 4 年前
        1
  •  1
  •   Dale K    4 年前

    FROM Servicedesk.dbo.SurveyResponseRequestMapping srrmINNER JOIN Servicedesk.dbo.Survey_Response_Main srm ON srrm.RESPONSEID = srm.RESPONSEID
    

    所以要么用

    @"
    ...SQL
    "
    

    或者在每行末尾加一个空格。