GOSettings

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Runtime.CompilerServices;
using System.Data.SqlClient;

namespace Greenshades.Online.Data.Settings {
    abstract class GOSettings : INotifyPropertyChanged {
        protected List<string> DirtyProperties { get; set; }
        protected String MasterConnectionString { get; set; }
        protected String WorkspaceName { get; set; }
        protected String TableName { get; set; }

        protected void NotifyPropertyChanged([CallerMemberName] String propertyName) {
            DirtyProperties.Add(propertyName);
        }

        public GOSettings(string masterConnectionString, string workspaceName, string tableName) {
            DirtyProperties = new List<String>();
            MasterConnectionString = masterConnectionString;
            WorkspaceName = workspaceName;
            TableName = tableName;

            HydrateProperties();
        }

        private void HydrateProperties() {
            using (var con = new SqlConnection(MasterConnectionString)) {
                con.Open();
                con.ChangeDatabase(WorkspaceName);

                var properties = GetType().GetProperties();

                var sql = "Select * From " + TableName;
                var com = new SqlCommand(sql, con);
                var reader = com.ExecuteReader();
                while (reader.Read()) {
                    var property = properties.SingleOrDefault(p => p.Name == reader["Key"]);
                    if (property != null) {
                        var type = property.PropertyType;
                        var value = reader["Value"];
                        property.SetValue(this, value);
                    }
                }
            }
        }

        public void Save() {
            var dirtyProperties = GetType().GetProperties().Where(p => DirtyProperties.Contains(p.Name));
            using (var con = new SqlConnection(MasterConnectionString)) {
                con.Open();
                con.ChangeDatabase(WorkspaceName);

                var sqlFormat = "Update {0} Set Value = '{1}' Where Key = {2}";
                foreach (var property in dirtyProperties) {
                    var sql = String.Format(sqlFormat, TableName, property.GetValue(this), property.Name);
                    var com = new SqlCommand(sql, con);
                    com.ExecuteNonQuery();
                }
            }
        }
    }
}