123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306 |
- 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);
- }
- }
- }
|