private List <Model.ExternalDBField> GetFields(string tableName, Tuple <IEnumerable <Model.AdditionalField>, IEnumerable <Model.ExternalOpenXDAField> > collumns, T xdaObj) { List <Model.ExternalDBField> result = new List <Model.ExternalDBField>(); if (collumns.Item1.Count() < 1 && collumns.Item2.Count() < 1) { return(result); } IEnumerable <string> querycol = collumns.Item1.Select(item => item.ExternalDBTableKey).Union(collumns.Item2.Select(item => item.ExternalDBTableKey)); string query = "SELECT " + String.Join(", ", querycol); query = query + " FROM " + GetTableQuery(tableName) + " WHERE " + getDataQuery(xdaObj, tableName); Dictionary <string, string> extData = new Dictionary <string, string>(); if (extDBType == DatabaseType.Oracle) { CategorizedSettingsElementCollection reportSettings = ConfigurationFile.Current.Settings[extDBConnectionSetting]; string conString = reportSettings["ConnectionString"].Value; using (OracleConnection con = new OracleConnection(conString)) { con.Open(); using (OracleCommand cmd = con.CreateCommand()) { cmd.CommandText = query; using (OracleDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { for (int i = 0; i < (reader.FieldCount); i++) { if (reader.GetValue(i) == null) { extData.Add(reader.GetName(i).ToLower(), ""); } else { extData.Add(reader.GetName(i).ToLower(), reader.GetValue(i).ToString()); } } } else { throw new Exception("Key not found in DataBase"); } } } } } else { using (AdoDataConnection connection = new AdoDataConnection(extDBConnectionSetting)) { DataTable dataTable = connection.RetrieveData(query); foreach (DataRow row in dataTable.AsEnumerable()) { extData = row.Table.Columns.Cast <DataColumn>().ToDictionary(c => c.ColumnName.ToLower(), c => row[c].ToString()); } } } //Sort Through Data to get any Data that has changed or does not exist only using (AdoDataConnection connection = new AdoDataConnection("systemSettings")) { TableOperations <Model.AdditionalFieldValue> valueTable = new TableOperations <Model.AdditionalFieldValue>(connection); result = collumns.Item1.Select(item => { string value = item.ExternalDBTableKey; extData.TryGetValue(item.ExternalDBTableKey.ToLower(), out value); Model.ExternalDBField res = new Model.ExternalDBField() { FieldName = item.FieldName, Value = value, Error = false, PreviousValue = null, FieldValueID = null, AdditionalFieldID = item.ID, isXDAField = false, Changed = false, }; res = processExternalAdditionalField(xdaObj, res); if (valueTable.QueryRecordCountWhere("AdditionalFieldID = {0} AND ParentTableID = {1}", item.ID, res.ParentTableID) > 0) { Model.AdditionalFieldValue val = valueTable.QueryRecordsWhere("AdditionalFieldID = {0} AND ParentTableID = {1}", item.ID, res.ParentTableID).First(); res.FieldValueID = val.ID; res.PreviousValue = val.Value; } return(res); }).ToList(); result = result.Concat(collumns.Item2.Select(item => { string value = item.ExternalDBTableKey; extData.TryGetValue(item.ExternalDBTableKey.ToLower(), out value); Model.ExternalDBField res = new Model.ExternalDBField() { AdditionalFieldID = item.ID, FieldName = item.FieldName, Value = value, Error = false, PreviousValue = Convert.ToString(xdaObj.GetType().GetProperty(item.FieldName).GetValue(xdaObj)), isXDAField = true, Changed = false, }; res = processExternalopenXDAField(xdaObj, res); return(res); })).ToList(); } return(result.Where(item => item.PreviousValue != item.Value).ToList()); }
public IHttpActionResult ConfirmUpdate([FromBody] JObject record) { try { JToken data = record.GetValue("data"); List <Model.ExternalDBField> fields = data.ToObject <List <Model.ExternalDBField> >(); using (AdoDataConnection xdaConnection = new AdoDataConnection(Connection)) using (AdoDataConnection connection = new AdoDataConnection(AdditionalFieldConnection)) { TableOperations <Model.AdditionalFieldValue> valueTable = new TableOperations <Model.AdditionalFieldValue>(connection); foreach (Model.ExternalDBField fld in fields) { if (fld.Error) { continue; } if (fld.isXDAField) { T xdaObj = (new TableOperations <T>(xdaConnection)).QueryRecordWhere("ID = {0}", fld.ParentTableID); // Convert to int, string or other MethodInfo m = xdaObj.GetType().GetProperty(fld.FieldName).GetValue(xdaObj).GetType().GetMethod("Parse", new Type[] { typeof(string) }); if (m != null) { xdaObj.GetType().GetProperty(fld.FieldName).SetValue(xdaObj, m.Invoke(null, new object[] { fld.Value })); } else { try { xdaObj.GetType().GetProperty(fld.FieldName).SetValue(xdaObj, fld.Value); } catch { } } (new TableOperations <T>(xdaConnection)).UpdateRecord(xdaObj); continue; } if (fld.FieldValueID == null) { valueTable.AddNewRecord(new Model.AdditionalFieldValue() { AdditionalFieldID = fld.AdditionalFieldID, ParentTableID = fld.ParentTableID, Value = fld.Value, }); } else { Model.AdditionalFieldValue val = valueTable.QueryRecordWhere("ID={0}", fld.FieldValueID); val.Value = fld.Value; valueTable.UpdateRecord(val); } } } return(Ok(1)); } catch (Exception ex) { return(InternalServerError(ex)); } }