inabox/inabox.databaseproxy/Proxy.cs

using System.Net;
using System.Text;
using Newtonsoft.Json;

namespace InABox.DatabaseProxy
{
    public abstract class Proxy
    {
        private string server;

        public Proxy()
        {
            Data = new List<List<object>>();
        }

        public string Server
        {
            get => server;
            set => GetServerSettings(value);
        }

        public string URL { get; private set; }

        public string UserID { get; private set; }

        public string Password { get; private set; }

        public string SQL { get; set; }

        public List<List<object>> Data { get; }

        private void GetServerSettings(string Name)
        {
            server = Name;
            URL = "";
            UserID = "";
            Password = "";
            var settings = ConfigurationManager.Configuration.GetConfig("credentials", server).Split(',');
            URL = settings.Length > 0 ? settings[0] : "";
            UserID = settings.Length > 1 ? settings[1] : "";
            Password = settings.Length > 2 ? settings[2] : "";
        }

        protected abstract string SQLFormat(object value);

        private List<string> BuildFieldValues(Dictionary<string, object> data)
        {
            var result = new List<string>();
            foreach (var key in data.Keys)
                result.Add(SQLFormat(data[key]));
            return result;
        }

        public void CreateInsertSQL(string table, Dictionary<string, object> data)
        {
            var values = BuildFieldValues(data);
            var result = string.Format(
                "insert into {0} ({1}) values ({2})",
                table,
                string.Join(",", data.Keys),
                string.Join(",", values)
            );
            SQL = result;
        }

        public void CreateUpdateSQL(string table, Dictionary<string, object> data, Dictionary<string, object> criteria)
        {
            var fields = new List<string>();
            foreach (var key in data.Keys)
                fields.Add(string.Format("{0}={1}", key, SQLFormat(data[key])));

            var keys = new List<string>();
            foreach (var key in criteria.Keys)
                keys.Add(string.Format("{0}={1}", key, SQLFormat(criteria[key])));

            var result = string.Format(
                "update {0} set {1} where ({2})",
                table,
                string.Join(", ", fields),
                string.Join(") and (", keys)
            );
            SQL = result;
        }

        public void CreateSelectSQL(string table, List<string> fields, Dictionary<string, object> criteria)
        {
            var keys = new List<string>();
            if (criteria != null)
                foreach (var key in criteria.Keys)
                    keys.Add(string.Format("{0}={1}", key, SQLFormat(criteria[key])));
            else
                keys.Add("1=1");

            var result = string.Format(
                "select {1} from {0} where ({2})",
                table,
                string.Join(", ", fields),
                string.Join(") and (", keys)
            );
            SQL = result;
        }

        public void CreateDeleteSQL(string table, Dictionary<string, object> criteria)
        {
            var keys = new List<string>();
            foreach (var key in criteria.Keys)
                keys.Add(string.Format("{0}={1}", key, SQLFormat(criteria[key])));

            var result = string.Format(
                "delete from {0} where ({1})",
                table,
                string.Join(") and (", keys)
            );
            SQL = result;
        }

        public bool Execute()
        {
            Data.Clear();

            var request = new Request();
            request.Credentials.UserID = UserID;
            request.Credentials.Password = Password;
            request.SQL = SQL;

            var settings = new JsonSerializerSettings { Formatting = Formatting.Indented };
            var sRequest = JsonConvert.SerializeObject(request, settings);
            var array = Encoding.ASCII.GetBytes(sRequest);
            var remoterequest = WebRequest.Create(string.Format("http://{0}/query?format=json", URL));
            remoterequest.ContentType = "application/json";
            remoterequest.Method = "POST";
            remoterequest.ContentLength = array.Length;

            var dataStream = remoterequest.GetRequestStream();
            dataStream.Write(array, 0, array.Length);
            dataStream.Close();

            var remoteresponse = remoterequest.GetResponse();
            using (var stream = remoteresponse.GetResponseStream())
            {
                var reader = new StreamReader(stream, Encoding.UTF8);
                var responseString = reader.ReadToEnd();
                var response = (Response)JsonConvert.DeserializeObject(responseString, typeof(Response));
                if (response.Status.Equals("OK"))
                {
                    Data.Clear();
                    Data.AddRange(response.Data);
                    return true;
                }

                return false;
            }
        }

        private object BlankValue(object value)
        {
            if (value == null)
                return value;

            if (value is string)
                return "";

            return Activator.CreateInstance(value.GetType());
        }

        public bool Synchronise(string table, Dictionary<string, object> criteria, Dictionary<string, object> fallbackcriteria,
            Dictionary<string, object> fields, string seqfield)
        {
            var bChangedKey = false;
            var bBlankKey = false;
            var bFallback = false;

            var newcriteria = new Dictionary<string, object>();
            foreach (var key in criteria.Keys)
            {
                if (criteria[key] == BlankValue(criteria[key]))
                    bBlankKey = true;

                if (!fields[key].Equals(criteria[key]))
                    bChangedKey = true;

                newcriteria[key] = fields[key];
            }

            CreateSelectSQL(table, fields.Keys.ToList(), newcriteria);
            var bNewExists = Execute() && Data.Count > 0;

            if (!bNewExists && fallbackcriteria != null)
            {
                CreateSelectSQL(table, fields.Keys.ToList(), fallbackcriteria);
                bNewExists = Execute() && Data.Count > 0;
                bFallback = bNewExists;
            }

            bool bOldExists;
            if (!bBlankKey)
            {
                if (bChangedKey)
                {
                    CreateSelectSQL(table, fields.Keys.ToList(), criteria);
                    bOldExists = Execute() && Data.Count > 0;
                }
                else
                {
                    bOldExists = bNewExists;
                }
            }
            else
            {
                bOldExists = false;
            }

            if (!bNewExists)
            {
                if (!bOldExists)
                {
                    if (!string.IsNullOrEmpty(seqfield))
                    {
                        CreateSelectSQL(table, new List<string> { "max(" + seqfield + ")+1" }, null);
                        if (Execute())
                            fields[seqfield] = Data.First()[0];
                    }

                    CreateInsertSQL(table, fields);
                }
                else

                {
                    CreateUpdateSQL(table, fields, bFallback ? fallbackcriteria : criteria);
                }
            }
            else
            {
                CreateUpdateSQL(table, fields, bFallback ? fallbackcriteria : criteria);
            }

            return Execute();
        }
    }

    public class FoxproProxy : Proxy
    {
        protected override string SQLFormat(object value)
        {
            if (value == null)
                return "''";

            if (value is double)
                return value.ToString();

            if (value is int)
                return value.ToString();

            if (value is bool)
                return (bool)value ? ".t." : ".f.";

            if (value is DateTime)
            {
                var date = (DateTime)value;

                return string.Format("CAST('{0:D4}-{1:D2}-{2:D2}T{3:D2}:{4:D2}:{5:D2}.{6:D3}' as DATETIME)", date.Year, date.Month, date.Day,
                    date.Hour,
                    date.Minute, date.Second, date.Millisecond);
            }

            if (value is string)
                return string.Format("'{0}'", value);

            //throw new Exception("Invalid Type: " + value.GetType().ToString() + " -> " + value.ToString());
            return string.Format("'{0}'", value);
        }
    }

    public class SQLServerProxy : Proxy
    {
        protected override string SQLFormat(object value)
        {
            if (value == null)
                return "''";

            if (value is double)
                return value.ToString();

            if (value is int)
                return value.ToString();

            if (value is bool)
                return (bool)value ? "1" : "0";

            if (value is DateTime)
            {
                var date = (DateTime)value;

                return string.Format("CAST('{0:D4}-{1:D2}-{2:D2}T{3:D2}:{4:D2}:{5:D2}.{6:D3}' as DATETIME)", date.Year, date.Month, date.Day,
                    date.Hour,
                    date.Minute, date.Second, date.Millisecond);
            }

            if (value is string)
                return string.Format("'{0}'", value);

            //throw new Exception("Invalid Type: " + value.GetType().ToString() + " -> " + value.ToString());
            return string.Format("'{0}'", value);
        }
    }
}