CODEWITHSUNDEEP
c#sqlormdapper
coding1223322
coding1223322

Reputation: 473

Dapper Mapping List of Results

My stored procedure returns back a list of Users along with organisation details from relational table.

I have used dapper to execute the stored procedure in my C# Web API application.

I am new to dapper. How do I map the results returned by stored procedure to my model?

Stored Procedure

CREATE PROCEDURE [dbo].[GetAllUsersByOrganisation]
    @OrganisationId int = 0

AS
    SELECT * from dbo.Users u
    inner join dbo.Organisations org on u.OrganisationId = org.Id
    Where u.OrganisationId = @OrganisationId

Execute Method

public List<User> GetUseryOrganisation(int organisationId)
{
    var users = DataContextFactory.GetConnectionString(_settings)
        .Execute("dbo.GetAllUsersByOrganisation", new {OrgantisationId = organisationId},
            commandType: CommandType.StoredProcedure);

    // Map to return List of User
}

Models

 public class User
    {
        public string Title { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string PhoneNumber { get; set; }
        public string EmailAddress { get; set; }
        public Organisation Organisation { get; set; }
    }

    public class Organisation
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

Upvotes: 4

Views: 3914

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

You should use Query overload which expects two types in the returned record set (user data and organization data), and then assigns organization data to user entity:

var users = DataContextFactory.GetConnectionString(_settings)
    .Query<User, Organisation, User>(
         "dbo.GetAllUsersByOrganisation",
         (u,o) => { u.Organisation = o; return u; }, // map function
         new {OrgantisationId = organisationId},
         commandType: CommandType.StoredProcedure);

return users.ToList();

Upvotes: 6

Related Questions