public static string ExecuteSql(int appDatabaseId, string sql, SqlParameter[] sqlParameters = null)
{
try
{
using (DodoBirdEntities Db = new DodoBirdEntities())
{
if (appDatabaseId > 0)
{
Db.Database.Connection.ConnectionString = SessionService.GetConnectionString(appDatabaseId);
}
else
{
Db.Database.Connection.ConnectionString = SessionService.DodoBirdConnectionString;
}
if (sqlParameters != null)
{
Db.Database.ExecuteSqlCommand(sql, sqlParameters);
}
else
{
Db.Database.ExecuteSqlCommand(sql);
}
return("SUCCESS");
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
public static TableSchema GetTableSchema(int appDatabaseId, string tableName)
{
using (DodoBirdEntities Db = new DodoBirdEntities())
{
Db.Database.Connection.ConnectionString = SessionService.GetConnectionString(appDatabaseId);
var sql = @"SELECT DISTINCT " + appDatabaseId + " AS AppDatabaseId,TABLE_SCHEMA AS Owner, TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName";
TableSchema tableSchema = Db.Database.SqlQuery <TableSchema>(sql, new SqlParameter("@TableName", tableName)).FirstOrDefault();
// get primarykeys
var primaryKeys = GetPrimaryKeys(Db, tableName);
tableSchema.PrimaryKeys.AddRange(primaryKeys);
// get columns
var columns = GetTableColumns(Db, tableName);
tableSchema.Columns.AddRange(columns);
// get dependent tables
List <DependentTable> dependentTables = GetDependentTables(appDatabaseId, tableSchema);
tableSchema.DependentTables.AddRange(dependentTables);
return(tableSchema);
}
}
private static List <DependentTable> GetDependentTables(int appDatabaseId, TableSchema parentTableSchema)
{
using (DodoBirdEntities Db = new DodoBirdEntities())
{
var sql = @"SELECT * FROM DependentTable WHERE AppDatabaseId = @AppDatabaseId AND ParentOwner = @ParentOwner AND ParentTableName = @ParentTableName ";
var dependentTables = Db.Database.SqlQuery <DependentTable>(sql, new SqlParameter("@AppDatabaseId", appDatabaseId), new SqlParameter("@ParentOwner", parentTableSchema.Owner), new SqlParameter("@ParentTableName", parentTableSchema.TableName)).ToList();
if (dependentTables.Count > 0)
{
using (DodoBirdEntities userDb = new DodoBirdEntities())
{
userDb.Database.Connection.ConnectionString = SessionService.GetConnectionString(appDatabaseId);
// get info for each dependent table
var i = 0;
foreach (var dependentTable in dependentTables)
{
sql = @"SELECT DISTINCT " + appDatabaseId + " AS AppDatabaseId,TABLE_SCHEMA AS Owner, TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = @Owner AND TABLE_NAME = @TableName";
var dependentTableSchema = userDb.Database.SqlQuery <TableSchema>(sql, new SqlParameter("@Owner", dependentTable.Owner), new SqlParameter("@TableName", dependentTable.TableName)).FirstOrDefault();
if (dependentTableSchema != null)
{
// get primarykeys
var primaryKeys = GetPrimaryKeys(userDb, dependentTable.TableName);
dependentTable.PrimaryKeys.AddRange(primaryKeys);
// get columns
var columns = GetTableColumns(userDb, dependentTable.TableName);
dependentTable.Columns.AddRange(columns);
// recursive.
sql = @"SELECT COUNT(1) FROM DependentTable WHERE AppDatabaseId = @AppDatabaseId AND ParentOwner = @ParentOwner AND ParentTableName = @ParentTableName";
var dependentCount = Db.Database.SqlQuery <int>(sql, new SqlParameter("@AppDatabaseId", appDatabaseId), new SqlParameter("@ParentOwner", dependentTableSchema.Owner), new SqlParameter("@ParentTableName", dependentTableSchema.TableName)).FirstOrDefault();
if (dependentCount > 0)
{
var dependentTables_ = GetDependentTables(appDatabaseId, dependentTableSchema);
dependentTables[i].DependentTables.AddRange(dependentTables_);
}
}
i++;
}
}
}
return(dependentTables);
}
}
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);
}
}