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