Is there any way to create generic mapper to retrieve collection within object?

Question

I have setup a generic query executer using dapper but I wonder if there are ways to create mapper for object contain list of object using stored procedures.

For example: I need to retrieve company and related product

public class company
{
    public List<Product> products {get;set;}
}


  public static async Task<List<DTO>> ExecuteQueryAsync<DTO>(string query , object param) where DTO : class, new()
    {
        List<DTO> result = null;

        try
        {
            var connection = new DbConnection().GetConnection();
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            if (connection.State == ConnectionState.Open)
            {
                result = await SqlMapper.QueryAsync<DTO>(connection, query,param) as List<DTO>;
                connection.Close();

            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return result;
    }

Possible duplicate of [Is there a way to call a stored procedure with Dapper?](https://stackoverflow.com/questions/5962117/is-there-a-way-to-call-a-stored-procedure-with-dapper) — Adam, Jan 24 '19 at 16:14
@Adam no i already know how to call SP my problem if there is a way to map collection of another object inside object — Abdalrahman Makahleh, Jan 24 '19 at 16:29

score 1 · Answer 1 · answered Jan 24 '19 at 18:26

You can make your code much "cleaner" by injecting the method to handle the GridReader to Poco's.

This allows separation of concerns.

"DataLayer" object

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace MyNamespace.DataLayer
{ 

public class MyCustomObjectData : IMyCustomObjectData
{

    public async Task<ICollection<MyCustomObject>> MyMethodAsync(Func<GridReader, ICollection<MyCustomObject>> handleFunction)
    {
        ICollection<MyCustomObject> returnItems = null;
        string sqlProcedureName = "dbo.uspMyCustomObjectSelectStuff";

        try
        {
            using (IDbConnection dbConnection = /* your code here */)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add(/* your code here */);
                GridReader gr = await dbConnection.QueryMultipleAsync(sqlProcedureName, parameters, commandType: CommandType.StoredProcedure, commandTimeout: 120);
                if (null != handleFunction)
                {
                    returnItems = handleFunction(gr);
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);
        }

        return returnItems;
    }
}
}

"DomainDataLayer" object

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using static Dapper.SqlMapper;

namespace MyNamespace.DomainDataLayer
{    
    public class MyCustomObjectDomainData : IMyCustomObjectDomainData
    {

        public MyCustomObjectDomainData(IMyCustomObjectData crgDataLayer)
        {
            this.MyCustomObjectData = crgDataLayer ?? throw new ArgumentNullException("IMyCustomObjectData is null");
        }

        public async Task<ICollection<MyCustomObject>> MyCustomObjectGetMethodAsync()
        {
            ICollection<MyCustomObject> returnCollection = null;
            /* CALL the datalayer, but INJECT the method to handle the GridReader */
            returnCollection = await this.MyCustomObjectData.MyMethodAsync(this.HandleMyCustomObjectGridReaderResult);
            return returnCollection;
        }

        public ICollection<MyCustomObject> HandleMyCustomObjectGridReaderResult(GridReader gr)
        {
            ICollection<MyCustomObject> returnCollection = null;

            using (gr)
            {
                /*  Get objects from each SELECT statement in the stored procedure */
                returnCollection = gr.Read<MyCustomObject>().ToList();

                /* this would be how to handle a SECOND "select" statement in the stored procedure */
                IEnumerable<MyOtherCustomObjectFromASecondStoredProcedureSelectStatement> otherThings = gr.Read<MyOtherCustomObjectFromASecondStoredProcedureSelectStatement>().ToList();

                /* optionally, you can hand-map the pocos to each other */
                //returnCollection = new MyCustomObjectObjectMapper().MapMultipleMyCustomObject(returnCollection, otherThings);
            }

            return returnCollection;
        }
    }
}

Is there any way to create generic mapper to retrieve collection within object?

1 Answers1