locked
Why Dapper is only returning the first column. not all the columns and rows RRS feed

  • Question

  • User-540818677 posted

    I have the following .NET core console application and I am using dapper to get the result of an SQL statement:-

    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 "
    );
                    Console.WriteLine(eventName);
                    Console.ReadLine();
                }
    
            }
        }
    }

    But currently the "eventName" will only contain a single string - "OrgABc" which is the first ad.ORG_NAME of the first row only.. so can anyone advice, how i can get all the rows?

    Thanks

    Monday, March 29, 2021 8:40 PM

All replies

  • User475983607 posted

    You specifically coded the connection to return one record and a string type.  That's why you get one field.

    connection.QueryFirst<string>

    Please take the time to read the Dapper documentation to make sure you understand how the library works.

    https://dapper-tutorial.net/queryfirst

    https://dapper-tutorial.net/query

    Also, remove the column name aliases.  There is no logical reason to have an alias since the goal is to populate a strong type.  The type properties should have the same name as the columns or the alias.  But your aliases have spaces which makes the code a bit harder to deal with.  Anyway, use the [Display] attribute when presenting the property in the UI. 

    var eventName = connection.Query<MyModel>("SELECT ad.ORG_NAME, wo.WORKORDERID, aau.FIRST_NAME, aci.emailid, wo.TITLE, ti.FIRST_NAME, srcmt.COMMENTTEXT, srm.result" +
    "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 "
    );
    public class MyModel 
    {
        [Display(Name = "Account")]
        public string ORG_NAME {get; set;}
        
        [Display(Name = "Request ID")]
        public int WORKORDERID {get; set;}
        ...
    }

    Monday, March 29, 2021 10:04 PM