대퍼 목록에 매개 변수 전달

dapper

문제

@StartDate, @EndDate, @AgentId, @Crc를 게시 된 메서드에 전달했지만 Dapper를 사용하여 정확하게 수행하는 방법을 알지 못하는 경우를 대비하여 여러 매개 변수를 전달하는 모범 사례를 알아야합니다. 미리 감사드립니다.

   public List<CallSearchRepositoryBO> GetCallSearchRecords(DateTime startDate, DateTime endDate, int agentId, string crc)
{
    try
    {
        using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["CCWorkforceConnectionString"].ConnectionString))
        {


            var items = connection.Query<CallSearchRepositoryBO>
                                                      ("SELECT  h.historyid " +
                                                       " ,h.Dialid " +
                                                       " ,h.Crc " +
                                                       " ,c.szExternalId ExternalId " +
                                                       " ,a.AgentId " +
                                                       " ,UPPER(a.LastName +',' +a.FirstName) Agent" +
                                                       " ,c.ContactId" +
                                                       " ,CallDateTime" +
                                                       " ,h.ProjName Campaign" +
                                                       " ,h.PhoneNum PhoneNumber" +
                                                       " ,szQ01 Q1" +
                                                       " ,szQ02 Q2" +
                                                       " ,szQ03 Q3" +
                                                       " ,szQ04 Q4" +
                                                       " ,szQ05 Q5" +
                                                       " ,szQ06 Q6" +
                                                       " ,szQ07 Q7" +
                                                       " ,szQ08 Q8" +
                                                       " ,szQ09 Q9" +
                                                       " ,szQ10 Q10" +
                                                       " ,txtQ33 Comments" +
                                                       " ,szFaxType AlertType" +
                                                       " ,r.VoxFilePath+VoxFileName RecordingPath" +
                                                       " ,r.RecLength" +
                                                       " ,CASE WHEN DataLength(txtQ33) > 0 THEN 1 ELSE 0 END HasComments " +
                                                       " FROM Touchstar..History h" +
                                                       " INNER JOIN Touchstar..Agent a WITH(NOLOCK) ON h.AgentId = a.AgentId" +
                                                       " INNER JOIN Touchstar..Recording r WITH(NOLOCK) ON h.HistoryId = r.HistoryId" +
                                                       " LEFT JOIN Touchstar..Contact c WITH(NOLOCK)  ON h.DialId = c.DialId" +
                                                       " LEFT JOIN Touchstar..Sales s WITH(NOLOCK) ON c.ContactId = s.ContactId" +
                                                       " WHERE h.CallDateTime BETWEEN @StartDate AND @EndDate" + 
                                                       " AND h.AgentId=@AgentId"+
                                                       " AND h.Crc=@Crc," +
                                                       " AND c.szCampaignId IN('UP2','UP4')" +
                                                       " ORDER BY CallDateTime DESC").ToList();

            return items;

        }


    }
    catch (Exception)
    {

        throw;
    }

}

수락 된 답변

쿼리 함수에 두 번째 매개 변수로 개체를 전달하십시오. 아래 코드의 추가 매개 변수에 주목하십시오. "new {StartDate = startDate, EndDate = endDate, AgentId = agentId, Crc = crc}"

var items = connection.Query<CallSearchRepositoryBO>
                                                      ("SELECT  h.historyid " +
                                                       " ,h.Dialid " +
                                                       " ,h.Crc " +
                                                       " ,c.szExternalId ExternalId " +
                                                       " ,a.AgentId " +
                                                       " ,UPPER(a.LastName +',' +a.FirstName) Agent" +
                                                       " ,c.ContactId" +
                                                       " ,CallDateTime" +
                                                       " ,h.ProjName Campaign" +
                                                       " ,h.PhoneNum PhoneNumber" +
                                                       " ,szQ01 Q1" +
                                                       " ,szQ02 Q2" +
                                                       " ,szQ03 Q3" +
                                                       " ,szQ04 Q4" +
                                                       " ,szQ05 Q5" +
                                                       " ,szQ06 Q6" +
                                                       " ,szQ07 Q7" +
                                                       " ,szQ08 Q8" +
                                                       " ,szQ09 Q9" +
                                                       " ,szQ10 Q10" +
                                                       " ,txtQ33 Comments" +
                                                       " ,szFaxType AlertType" +
                                                       " ,r.VoxFilePath+VoxFileName RecordingPath" +
                                                       " ,r.RecLength" +
                                                       " ,CASE WHEN DataLength(txtQ33) > 0 THEN 1 ELSE 0 END HasComments " +
                                                       " FROM Touchstar..History h" +
                                                       " INNER JOIN Touchstar..Agent a WITH(NOLOCK) ON h.AgentId = a.AgentId" +
                                                       " INNER JOIN Touchstar..Recording r WITH(NOLOCK) ON h.HistoryId = r.HistoryId" +
                                                       " LEFT JOIN Touchstar..Contact c WITH(NOLOCK)  ON h.DialId = c.DialId" +
                                                       " LEFT JOIN Touchstar..Sales s WITH(NOLOCK) ON c.ContactId = s.ContactId" +
                                                       " WHERE h.CallDateTime BETWEEN @StartDate AND @EndDate" + 
                                                       " AND h.AgentId=@AgentId"+
                                                       " AND h.Crc=@Crc," +
                                                       " AND c.szCampaignId IN('UP2','UP4')" +
                                                       " ORDER BY CallDateTime DESC", new {StartDate = startDate, EndDate = endDate, AgentId = agentId, Crc = crc}).ToList();


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