Generate DTO models dynamically based on the data returned by the stored procedure

Question

I have a scenario where I am passing table name as parameter to SQL Server stored procedure and it returns the data from that table.

The data structure that stored procedure returns varies based on the parameter (table name) that I am passing to it.

In my existing implementation where the data structure is fixed, i.e the columns returned by the stored procedure do no change, we are using predefined DTOs which looks like this:

public IList<UserDetailsDTO> GetUserDetails(int userId)
{
    Dictionary<string, object> params = new Dictionary<string, object>();
    params.Add("@UserId",userId);

    return ExecuteProcedure<UserDetailsDTO>("spGetUserDetails", params);
}

Now with my new requirement where the data structure returned by the stored procedure varies based on the table name parameter being passed to it, I am not sure how I can have my DTOs be dynamic.

Any inputs or suggestions on this?

Answer 1

I would recommend reading the returned rows out into something like a Dictionary<string, object> as described here.

List<Dictionary<string, object>> items = new List<Dictionary<string, object>>();
using (var reader = await command.ExecuteReaderAsync())
{
    if (reader.HasRows)
    {
        while (await reader.ReadAsync())
        {
            Dictionary<string, object> obj = new Dictionary<string, object>();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                string columnName = reader.GetName(i);
                object columnValue = reader.GetValue(i);
                obj[columnName] = columnValue;
            }
            items.Add(obj);
        }
    }
}

Then just have your function return this list to be serialized.