How to return MySQL data to generic classes

Question

I have a number of classes for example

    public class CountryModel
    {
        public string CountryCode { get; set; }
        public string CountryName { get; set; }
    }

    public class RegionModel
    {
        public string RegionId{ get; set; }
        public Int64 RegionName { get; set; }
        public string RegionType { get; set; }
    }

Currently I call using different stored procedures to popular the classes with the relevant data for the class properties. For example;

    while (CountryResultSet.Read())
    {
      CountryModel MyObj = new CountryModel();
      MyObj.CountryCode = oRes["CountryCode"].ToString();
      MyObj.CountryName = oRes["CountryName"].ToString();
    }

Then

    while (RegionResultSet.Read())
    {
      RegionModel MyRegionObj = new RegionModel();
      MyRegionObj.RegionId = oRes["RegionId "];
      MyRegionObj.RegionName = oRes["RegionName"].ToString();
      MyRegionObj.RegionType = oRes["RegionType"].ToString();
    }

This works fine, however I'm going to need to have a to repeat this many times over for different requests of getting the data from the MySQL data. It seems like a waste of repeated code.

Can anybody recommend a method so that I can use just 1 generate method that can cater for all class types.

Answer 1

I use a combination of code gen classes mapped to the DB tables and C#'s Activator.

The following is used with internal dev tools and fills a list with records retrieved from the database:

    public void FillDbObjectList<T>(out List<T> list, string tableName) where T : CommonUtilities.DbObjectTableBase
    {
        list = new List<T>();

        ADODB.Recordset rcd;
        this.FillRecordset(EDbRecordSource.Table, tableName, out rcd);

        while (!rcd.EOF)
        {
            object[] paramArray = this.FillActivatorParameterArray(rcd);
            list.Add((T)Activator.CreateInstance(typeof(T), paramArray));
            rcd.MoveNext();
        }

        this.TryCloseRecordset(rcd);
    }

The FillActivatorParameterArray method just creates an array of objects tied to field order.

    private object[] FillActivatorParameterArray(ADODB.Recordset rcd)
    {
        object[] paramArray = new object[rcd.Fields.Count];

        for (int i = 0; i < rcd.Fields.Count; i++)
        {
            if (rcd.Fields[i].Value == DBNull.Value)
            {
                paramArray[i] = null;
            }
            else
            {
                paramArray[i] = rcd.Fields[i].Value;
            }
        }

        return paramArray;
    }

And this is an example of one of the code generated classes.

public class Table_Archetype : DbObjectTableBase
{
    #region Fields
    private System.String name = "ChangeMe";
    private System.Int32 fK_ToolField_ID_DataSource = 0;
    private System.String description__DEV = "";
    private System.Int32 fK_EnumArray_ID_DataExportType = 19;
    private System.Boolean isDeleted = false;
    #endregion

    public Table_Archetype() { }

    public Table_Archetype(System.Int32 inID) { base.id = inID; }

    public Table_Archetype(
        System.Int32 inID
        , System.String inName
        , System.Int32 inFK_ToolField_ID_DataSource
        , System.String inDescription__DEV
        , System.Int32 inFK_EnumArray_ID_DataExportType
        , System.Boolean inIsDeleted
    )
    {
        base.id = inID;
        this.name = inName;
        this.fK_ToolField_ID_DataSource = inFK_ToolField_ID_DataSource;
        this.description__DEV = inDescription__DEV;
        this.fK_EnumArray_ID_DataExportType = inFK_EnumArray_ID_DataExportType;
        this.isDeleted = inIsDeleted;
    }

    #region Properties
    public System.String Name
    {
        get { return this.name; }
        set
        {
            if (this.name != value)
            {
                this.name = value;
                this.isDirty = true;
            }
        }
    }
    public System.Int32 FK_ToolField_ID_DataSource
    {
        get { return this.fK_ToolField_ID_DataSource; }
        set
        {
            if (this.fK_ToolField_ID_DataSource != value)
            {
                this.fK_ToolField_ID_DataSource = value;
                this.isDirty = true;
            }
        }
    }
    public System.String Description__DEV
    {
        get { return this.description__DEV; }
        set
        {
            if (this.description__DEV != value)
            {
                this.description__DEV = value;
                this.isDirty = true;
            }
        }
    }
    public System.Int32 FK_EnumArray_ID_DataExportType
    {
        get { return this.fK_EnumArray_ID_DataExportType; }
        set
        {
            if (this.fK_EnumArray_ID_DataExportType != value)
            {
                this.fK_EnumArray_ID_DataExportType = value;
                this.isDirty = true;
            }
        }
    }
    public System.Boolean IsDeleted
    {
        get { return this.isDeleted; }
        set
        {
            if (this.isDeleted != value)
            {
                this.isDeleted = value;
                this.isDirty = true;
            }
        }
    }
    #endregion