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