C# how to populate list of objects from RDBMS without ORM

Question

I'd like to populate a list of objects from a DataTable without explicitly assigning data elements column-by-column and without using an ORM. With Dapper (a small ORM) one can pass a list of POCO (ordinary objects) and have it auto-match "cells" from a query based on the "shape" of that POCO. I'd like to do something similar using a DataTable object. Is there a way to mirror that functionality without using Dapper? I suppose reflection can be used, but getting reflection to work reliably often requires "rocket science", especially in terms of debugging. Performance is a relatively minor concern.

public class Employee
{
    public string lastName { get; set; }
    public string firstAndMidName { get; set; }
    public int employeeNumber { get; set; }
    public int salary { get; set; }
}
// ...
public void runSample()
{
    List<Employee> employeeList = new List<Employee>();
    DataTable myDat = queryRDBMS("select * from Employees");  // typical query API (simplified)
    employeeList = convertDataTableToList(myDat, modelClass: Employee);  // the missing part
}

(Updated)

Yes, you can. Can you do so without significant performance hits? Probably not as well as an ORM, which is one of the reasons why people use them. — David L, Feb 01 '21 at 23:53
Your question is not clear. Are you asking about retrieving data from a database and populating a DataTable with it? If so, see [this answer](https://stackoverflow.com/a/21765430/2791540). Otherwise, please add more detail, or perhaps a code example showing where you are stuck. — John Wu, Feb 01 '21 at 23:55
Dapper is not an ORM, it is a micro-ORM. All it does is map properties, no change-tracking, so it's very lightweight. @DavidL a simple dictionary of delegates created from the property setters can be very fast (done it before), but beyond scope here as OP has not provided any code. — Charlieface, Feb 02 '21 at 00:35

score 0 · Answer 1 · answered Feb 02 '21 at 17:14

using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;

public static class DataTableExtensions
{
    public static List<T> MapToList<T>(this DataTable dt)
    {
        string currentPropName = string.Empty;

        try
        {
            List<T> list = new List<T>();
            T obj = default(T);

            if (dt?.Rows?.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    obj = Activator.CreateInstance<T>();
                    foreach (PropertyInfo prop in obj.GetType().GetProperties())
                    {
                        //  Check has column in case we have extension property
                        if (!dt.HasColumn(prop.Name)) continue;
                        currentPropName = prop.Name;

                        if (!object.Equals(row[prop.Name], DBNull.Value))
                        {
                            //  Need to check object type boolean here because return result from db type will be ulong64 which can't convert to bool
                            if (IsBoolean(prop))
                            {
                                prop.SetValue(obj, Convert.ToBoolean(row[prop.Name]), null);
                            }
                            else
                            {
                                prop.SetValue(obj, row[prop.Name], null);
                            }
                        }
                    }

                    list.Add(obj);
                }
            }

            return list;
        }
        catch (Exception ex)
        {
            throw new Exception($"Error occured while converting object '{currentPropName}' from data table to obj class ", ex);
        }
    }

    private static bool IsBoolean(PropertyInfo prop)
    {
        return prop.PropertyType == typeof(bool) || prop.PropertyType == typeof(bool?);
    }

    public static bool HasColumn(this DataTable dt, string columnName)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (dt.Columns[i].ColumnName.Equals(columnName, StringComparison.InvariantCultureIgnoreCase)) return true;
        }

        return false;
    }
}

C# how to populate list of objects from RDBMS without ORM

1 Answers1