Moving app.config settings to db and using value type to dynamically set the return data type

Question

Moving app.config settings to db and using value type to dynamically set the return data type

.net c# dapper

Question

I have a windows service application that I want to manage setting for from a asp.net site. As my application has grown, my list of settings in the app.config file has grown as well. So, I have decided to move these setting to a table in my SQLDB to be able to track them and give me a way to modify the settings from the admin site. I ran into an issue where I am trying to store the setting value type in the table and then use it to change the value property to the type stored. For instance I have quite a few TimeSpan defined. In the SQL table the data would look like this.

guid    settingName     settingValue    settingType
936767f5-63b5-4844-9991-29f6f92c53f2    SMTimeStart    12:00:00    TimeSpan

Im trying to use the following code to pull the settings and return it in the correct type.

    public class SettingDataValue
    {
        public Guid guid { get; set; }
        public string SettingName { get; set; }
        public string SettingValue { get; set; }
        public string SettingType { get; set; }


    }
    public static dynamic getSettingFromDB(string name)
    {
        SettingDataValue s = new SettingDataValue();
        using (IDbConnection _db = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString), commandTimeout = null)
        {
            s = _db.Query<SettingDataValue>("Select Guid, SettingName, SettingValue ,SettingType from SiteSettings where settingName = '" + name + "'").SingleOrDefault();
        }
        PropertyInfo propertyInfo = s.GetType().GetProperty(s.SettingType);
        propertyInfo.SetValue(s, Convert.ChangeType(s.SettingValue, propertyInfo.PropertyType), null);

        return s.SettingValue;
    }

However when I run this I get a null reference exception on the

propertyInfo.SetValue(s, Convert.ChangeType(s.SettingValue, propertyInfo.PropertyType), null);

I know the query works when I test it and watch it with the sql profiler. Any thoughts or suggestions?

Jeremy Hobbs

Popular Answer

The problem you're having isn't that you aren't getting data back, but that the PropertyInfo is null.

The line:

PropertyInfo propertyInfo = s.GetType().GetProperty(s.SettingType);

Is actually trying to find the property "TimeSpan" on the SettingDataValue object (using the line of data you provided). Since this property does not exist it is returning null. Then you're trying to set the value of the property and getting the null reference exception.

I think what you're trying to do is convert the string value to the type of value in the setting type. You don't need to use reflection for that. I'd recommend adding a read only property to your SettingDataValue object:

public object Value
{
    get
    {
        return SomeMethodThatConvertsYourStringValueToTarget();
    }
}

Then a private method to actually do the conversion.

private object SomeMethodThatConvertsYourStringValueToTarget();
{
    switch (SettingType)
    {
        case "TimeSpan":
            //conversion code
            break;
    }
}

Then change your getSettingsFromDb method to return object instead of dynamic. Then you can use it like:

TimeSpan ts = (TimeSpan)getSettingsFromDb("SMTimeStart");

Alternatively you may create a method for each data type so you don't have to case it when using it. So you could use it like:

TimeSpan ts = getTimeStampFromDb("SMTimeStart");

Dave Greilach

View more on Stack Overflow

Jeremy Hobbs · Accepted Answer · 10/4/2016 3:51:47 PM

So couple things I had wrong. As David mentioned, I didnt need to use reflection to get the type. Instead I needed to use the Type.GetType method to parse the text. Also the second thing was that the data types have to be the namespace recorded with them.

Here is the updated code that is working now.

    public class SettingDataValue
    {
        public Guid guid { get; set; }
        public string SettingName { get; set; }
        public string SettingValue { get; set; }
        public string SettingType { get; set; }

    }
    public static dynamic getSettingFromDB(string name)
    {

        SettingDataValue s = new SettingDataValue();
        using (IDbConnection _db = new SqlConnection(ConfigurationManager.ConnectionStrings["NetworkCafeConnectionString"].ConnectionString))
        {
            s = _db.Query<SettingDataValue>("Select guid, SettingName, SettingValue ,SettingType from SiteSettings where SettingName = '" + name + "'").FirstOrDefault();
        }
        Type type = Type.GetType(s.SettingType);
        var converter = TypeDescriptor.GetConverter(type);
        return converter.ConvertFrom(s.SettingValue);
    }

Here is a sample of the data in the sql table.

guid        SettingName            SettingValue    SettingType
95473a84    SMCreateTime           00:12:00        System.TimeSpan
81037bdc    SMCreateEnabled        True            System.Boolean
99e06df7    SMUsername             Username        System.String

Moving app.config settings to db and using value type to dynamically set the return data type

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator