C#: Mapping a database to class properties when DB names and column names will only be known at run time

Question

I currently have a program which interacts with and queries data from a database. However, I want to figure out how I could possibly take a database and a config file and map the table contents to my existing contents.

For example: I have the class Person. A person has a ID(long), accountName(string), Title(string), firstName(string) and lastName(string).

The new connected database has the same properties for Person but stores them in a different way. They might call the table Individual, with columns UniqueID(long), accName(string), personalTitle(string), fName(string), lName(string).

I know from the config file the names of each column that are the equivalents to the properties, but I'll only be able to read that at run time. Is there a way I could read the titles of what I am looking for and load them into these classes so that I don't have to hard code it every time?

Thanks in advance!

Answer 1

The best thought that comes to my mind is skipping ORM of any kind and just using query strings:

Assuming we are using just plain ADO this way

your query string would look something like:

string queryString =
        $"SELECT {ConfigurationManager.AppSettings[productColumn]}
 , {ConfigurationManager.AppSettings[PriceColumn]} 
 , {ConfigurationManager.AppSettings[productNameColumn]} 
 from dbo.{ConfigurationManager.AppSettings[ProductTableName]} "
            + "WHERE {ConfigurationManager.AppSettings[UnitPriceColumn]}  > @pricePoint "
            + "ORDER BY {ConfigurationManager.AppSettings[UnitPriceColumn]}  DESC;";

It feels sort of weird, but so are the requirements.

Answer 2

Is it really always random new databases or is it a finite amount ? You could have a class which includes all of those possible case but will check "on the fly" which one should be used or not.

I haven't tryed that with any ORM, but I remember the "bool ShouldSerializeXXXXXX" prefix when dealing with XML serialization/deserialization, maybe there is an equivalent for your case.

Answer 3

This is pretty easy to implement. When your procedure returns a datatable, you read the mapping from the configuration file and set the value for the class property based on what field name is mapped to the property using reflection.

Here is some code that you can put to work.

/// Your result objects list
List<DBObject> result = new List<DBObject>();
System.Reflection.ConstructorInfo ci = objectType.GetConstructor(new Type[] { });
System.Reflection.PropertyInfo[] props = objectType.GetProperties();
if ((table != null) && (table.Rows.Count > 0) && (ci != null) && (props != null) && (props.Length > 0))
{
    for (int i = 0; i < table.Rows.Count; i++)
    {
        DBObject dob = (DBObject)ci.Invoke(new object[] { });
        if (dob != null)
        {
            foreach (System.Reflection.PropertyInfo pi in props)
            {
                /// This is your method to get DB column name from config file
                string dbColumnName = GetDBColumnFromMappingConfigFile(pi.Name);
                object value = null;
                if (table.Columns.Contains(dbColumnName))
                {
                    if (table.Rows[i][dbColumnName] != DBNull.Value)
                    {
                        value = table.Rows[i][dbColumnName];
                    }
                }
                if (value != null)
                {
                    if ((pi.PropertyType.IsGenericType) && (pi.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))))
                    {
                        Type t = pi.PropertyType.GetGenericArguments()[0];
                        if (t != null)
                        {
                            object val = Convert.ChangeType(value, t);
                            pi.SetValue(dob, val, null);
                        }
                    }
                    else
                        pi.SetValue(dob, Convert.ChangeType(value, pi.PropertyType), null);
                }
            }
        }
    }
}

I usually use PropertyAttributes instead of Property names, because attributes allow you to define default values in case if DB has it NULL, define setters, etc.

Answer 4

A couple of ways this could be done, you could do this either application, database, or a combination

Application Side

1. Hard-code your program classes and build your application on that.
2. Build the queries to populate the classes based on known queries.
3. Build a configuration section in the app for selecting which query for which class
4. Use that configuration to populate your model

Database Side is a little more complicated, but ends up being cleaner in the application. You still create the classes as normal.

On the DB server, there are a multitude of options. You still are creating your classes and queries in the application

1. You would do all of your CRUD ops in Stored Procedures, which have the ability to determine if a table exists or not; and then you would simply go through a stack of IF Exists (select tablename...) to determine which query to run
2. You have a "setup" stored procedure which converts the tables present into a structure that aligns with your apps classes
3. and many other options