How to map a row of a database table to an object without Entity Framework

Question

I have something like the following C# Model:

public class ExampleModel
{
  public string A {get; set;}
  public string B {get; set;}
  //...
}

And the following SQL code to get the rows of a database table:

SELECT a, b, ...
FROM example_table;

I want to somehow map the result of that SQL query to a list of ExampleModel. What I've been doing so far is something like this:

using (DbCommand cmd = _conn.CreateCommand())
{
  string sql = "SELECT a, b, ... FROM example_table;";
  cmd.CommandType = System.Data.CommandType.Text;
  cmd.CommandText = sql;

  using(DbDataReader reader = cmd.ExecuteReader())
  {
    List<ExampleModel> exampleModelList = new();
    while(reader.Read())
    {
      ExampleModel exampleModel = new ExampleModel();
      exampleModel.A = (string)reader["a"];
      exampleModel.B = (string)reader["b"];
      //...
      exampleModelList.Add(exampleModel);
    }
  }
}

Assume that my database has a very high amount of columns. Is there a quicker way to do what I'm trying to do without having to manually map data in table rows to my C# object?

Note: I can't use Entity Framework to do this.

Use some type of ORM like mentioned above. You're reinventing the wheel. — GH DevOps
– GH DevOps, Commented Jul 27, 2023 at 13:22
So there is nothing I can do without some kind of external package? — Librapulpfiction
– Librapulpfiction, Commented Jul 27, 2023 at 13:23
yes, you can write a (likely) poorer version of the above mentioned package yourself — siggemannen
– siggemannen, Commented Jul 27, 2023 at 14:31

Mad hatter · Accepted Answer · 2023-07-28 09:04:17Z

Here is a naive implementation using custom attributes and reflection - not tested.

[AttributeUsage(AttributeTargets.Property)]
public class DataPropertyAttribute : Attribute
{
    public string Field { get; private set; }

    public PropertyInfo Property { get; set; }

    public DataPropertyAttribute(string pField)
    {
        this.Field = pField;
    }
}

public class Model<T> where T : Model<T>
{
    private static Dictionary<string, DataPropertyAttribute> Mapping { get; set; }

    static Model()
    {
        Mapping = new Dictionary<string, DataPropertyAttribute>();

        var type = typeof(T);
        var properties = type.GetProperties();

        foreach (var property in properties)
        {
            var attribute = property.GetCustomAttribute<DataPropertyAttribute>();

            if (attribute != null)
            {
                attribute.Property = property;
                Mapping[attribute.Field] = attribute;
            }
        }
    }

    public void Fill(DataRow row)
    {
        foreach (DataColumn column in row.Table.Columns)
        {
            var columnName = column.ColumnName;
            var item = row[column.ColumnName];
            var found = Mapping.TryGetValue(columnName, out var mapping);

            if (found)
            {
                mapping.Property.SetValue(this, item);
            }
        }
    }
}

public class ExampleModel : Model<ExampleModel>
{
    [DataProperty("a")]
    public string A { get; set; }

    [DataProperty("b")]
    public string B { get; set; }
}

You might need to do some conversions depending on the property type with a switch/case statement + Convert.ToXXX methods. You will also need to check DbNull.Value. Note that mapping an object from database using reflection will be much slower than "brute" code.

Collectives™ on Stack Overflow

How to map a row of a database table to an object without Entity Framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related