public static string GetJsonData(int appDatabaseId, string sql, SqlParameter[] sqlParameters = null) { using (DodoBirdEntities Db = new DodoBirdEntities()) { if (appDatabaseId > 0) { Db.Database.Connection.ConnectionString = SessionService.GetConnectionString(appDatabaseId); } else { Db.Database.Connection.ConnectionString = SessionService.DodoBirdConnectionString; } StringBuilder sb = new StringBuilder(); if (sqlParameters != null) { var items = Db.Database.SqlQuery <string>(sql + " FOR JSON AUTO, INCLUDE_NULL_VALUES", sqlParameters).ToList(); foreach (var item in items) { sb.Append(item); } } else { var items = Db.Database.SqlQuery <string>(sql + " FOR JSON AUTO, INCLUDE_NULL_VALUES").ToList(); foreach (var item in items) { sb.Append(item); } } return(sb.ToString()); } }
public static string SaveFormData(int appDatabaseId, string tableName, string json) { try { dynamic jsonObj = Newtonsoft.Json.JsonConvert.DeserializeObject(json); var tableSchema = GetTableSchema(appDatabaseId, tableName); var recordId = ""; using (DodoBirdEntities Db = new DodoBirdEntities()) { Db.Database.Connection.ConnectionString = SessionService.GetConnectionString(appDatabaseId); string wherePrimaryKey = ""; string recordValue = ""; List <SqlParameter> insertParams = new List <SqlParameter>(); List <SqlParameter> updateParams = new List <SqlParameter>(); StringBuilder sbInsert = new StringBuilder(); StringBuilder sbValue = new StringBuilder(); StringBuilder sbUpdate = new StringBuilder(); sbUpdate.Append("UPDATE " + tableName + " SET "); sbInsert.Append("INSERT INTO " + tableName + "("); sbValue.Append(" VALUES("); Guid newGuid = Guid.NewGuid(); var newGuid_ = ""; // set primary keys foreach (var column in tableSchema.PrimaryKeys) { if (jsonObj[column.ColumnName] != null) { recordId = jsonObj[column.ColumnName].ToString(); if (recordId.Length > 0 && recordId != "0") { wherePrimaryKey += " " + column.ColumnName + " = '" + recordId + "' AND "; } } if (column.DataType == "uniqueidentifier") { sbInsert.Append(column.ColumnName + ","); sbValue.Append("'" + newGuid + "',"); newGuid_ = newGuid.ToString(); } } foreach (var column in tableSchema.Columns.Where(w => !(bool)w.IsComputed && !w.IsPrimaryKey)) { if (jsonObj[column.ColumnName] != null) { recordValue = jsonObj[column.ColumnName].ToString(); sbInsert.Append(column.ColumnName + ","); sbValue.Append("@" + column.ColumnName + ","); insertParams.Add(new SqlParameter("@" + column.ColumnName, recordValue)); sbUpdate.Append(column.ColumnName + " = @" + column.ColumnName + ","); updateParams.Add(new SqlParameter("@" + column.ColumnName, recordValue)); } } if (wherePrimaryKey.Length > 0) // update { var sql = sbUpdate.ToString().Substring(0, sbUpdate.ToString().Length - 1) + " WHERE " + wherePrimaryKey.Substring(0, wherePrimaryKey.Length - 4); SqlParameter[] sqlParameters = updateParams.ToArray(); Db.Database.ExecuteSqlCommand(sql, sqlParameters); } else { SqlParameter[] sqlParameters = insertParams.ToArray(); var sql = sbInsert.ToString().Substring(0, sbInsert.ToString().Length - 1) + ") " + sbValue.ToString().Substring(0, sbValue.ToString().Length - 1) + "); SELECT CAST(@@IDENTITY AS varchar(250));"; if (newGuid_.Length > 0) // is guid { sql = sbInsert.ToString().Substring(0, sbInsert.ToString().Length - 1) + ") " + sbValue.ToString().Substring(0, sbValue.ToString().Length - 1) + ");"; recordId = newGuid.ToString(); Db.Database.ExecuteSqlCommand(sql, sqlParameters); } else { recordId = Db.Database.SqlQuery <string>(sql, sqlParameters).FirstOrDefault(); } Db.SaveChanges(); } return(recordId.ToString()); } } catch (Exception ex) { return("Unable to process record - " + ex.Message); } }