我有下面的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中却运行良好?
谢谢