Generalized DTO population method with different query select lists

Question

For reasons that I do not quite understand, I have chosen not to use an ORM Framework and have gone with a generalized ADO.NET data access layer. I initially created a single database class from which all my controllers had access. As anyone but myself could have predicted, this access object has become a monstrosity.

In an attempt to refactor my data layer, I have created a 'database adapter' class as a DI injected service and have created a 'service layer' to utilize it. So each controller now has a 'domain service' that will use the database adapter to query the database and return a generic data table. The service will then populate the result of the queries and return the domain objects back to the controller where it can assemble the view models.

I am running into an issue where I cannot seem to abstract the code designed to map the DataSets returned from the database access layer because each query may select different fields. For example, a simple reference data service:

public class ReferenceDataService : IReferenceDataService
{
    private IDatabaseAdapter _dbAdapter;

    public ReferenceDataService(IDatabaseAdapter dbAdapter)
    {
        _dbAdapter = dbAdapter;
    }

    public IEnumerable<ReferenceData> GetReferenceData(string table)
    {
        List<ReferenceData> rdList = new List<ReferenceData>();

        StringBuilder sb = new StringBuilder();
        sb.Append("SELECT [CODE], [LABEL] FROM [dbo].");
        sb.Append(table);
        sb.Append(" WHERE END_DATETIME > GETDATE()");

        DataSet ds = _dbAdapter.ExecuteDataSet(sb.ToString(), null);

        foreach (DataRow row in ds.Tables[0].Rows)
        {
            rdList.Add(PopulateRecord(row));
        }

        return rdList;
    }

    private ReferenceData PopulateRecord(DataRow row)
    {
        return new ReferenceData
        {
            ReferenceId = (int)row["REFERENCE_ID"],
            Code = (string)row["CODE"],
            Label = (string)row["LABEL"],
            Description = (string)row["DESCRIPTION"],
            BeginDatetime = (DateTime)row["BEGIN_DATETIME"],
            EndDatetime = (DateTime)row["END_DATETIME"],

            UpdatedBy = (string)row["UPDATED_BY"],
            UpdatedOn = (DateTime)row["UPDATED_ON"],
            CreatedBy = (string)row["CREATED_BY"],
            CreatedOn = (DateTime)row["CREATED_ON"]
        };
    }
}

In this example, I have an exception thrown from the populate method, because as you can see, I am only selecting code and label for this particular method. I'd like to avoid a custom mapping for every method but I also do not want to needlessly return ALL the data from each table row to the controller. I'd like to keep the populate method generic so that any query against that table will be mapped appropriately.

I realize I'm basically almost rolling my own ORM, but I'd like to use a service pattern without it because at this point I am way too invested.

Answer 1

After some digging around, it appears there was a very obvious and straightforward solution that I had been missing. The DataRow instance object has the ability to check it's parent table columns for existence. By wrapping each assignment from the table row in one of these checks, then the population method will not care what was actually selected into the DataTable and will be able to populate an object regardless of the amount of data returned from the query.

So in my example, if I want to keep a generic population method for ReferenceData but use a query that only retuns the CODE and LABEL columns, the following change would keep the population of the returned business object agnostic and error free:

    private ReferenceData PopulateRecord(DataRow row)
    {
        return new ReferenceData
        {
            ReferenceId = row.Table.Columns.Contains("REFERENCE_ID") ? (int)row["REFERENCE_ID"] : default(int),
            Code = row.Table.Columns.Contains("CODE") ? (string)row["CODE"] : default(string),
            Label = row.Table.Columns.Contains("LABEL") ? (string)row["LABEL"] : default(string),
            Description = row.Table.Columns.Contains("DESCRIPTION") ? (string)row["DESCRIPTION"] : default(string),
            BeginDatetime = row.Table.Columns.Contains("BEGIN_DATETIME") ? (DateTime)row["BEGIN_DATETIME"] : default(DateTime),
            EndDatetime = row.Table.Columns.Contains("END_DATETIME") ? (DateTime)row["END_DATETIME"] : default(DateTime),

            UpdatedBy = row.Table.Columns.Contains("UPDATED_BY") ? (string)row["UPDATED_BY"] : default(string),
            UpdatedOn = row.Table.Columns.Contains("UPDATED_ON") ? (DateTime)row["UPDATED_ON"] : default(DateTime),
            CreatedBy = row.Table.Columns.Contains("CREATED_BY") ? (string)row["CREATED_BY"] : default(string),
            CreatedOn = row.Table.Columns.Contains("CREATED_ON") ? (DateTime)row["CREATED_ON"] : default(DateTime)
        };
    }

This would allow me to use PopulateRecord on a select statement that only returned CODE and LABEL (as I would want to do if I was populating a SelectItemList for a dropdown for example).

I do not know what kind of performance hit this may or may not incur so that is something to possibly consider. But this allows for the flexibility I was looking for. I hope this post will help someone else who might be looking for the same type of solution.

If there are better ways to approach this please let me know. Thanks!