/// <summary> /// publish a specific process by generating table with relations and properties. /// </summary> public ResultOperation DropTable(sysBpmsEntityDef entityDef) { DataBaseQueryService dataBaseQueryService = new DataBaseQueryService(base.UnitOfWork); ResultOperation resultOperation = new ResultOperation(); List <string> executeAlterQueries = new List <string>(); foreach (EntityPropertyModel rModel in entityDef.Properties.Where(c => c.DbType == EntityPropertyModel.e_dbType.Entity)) { executeAlterQueries.Add($@"ALTER TABLE {entityDef.FormattedTableName} DROP CONSTRAINT {rModel.RelationConstaintName}"); } foreach (string query in executeAlterQueries) { dataBaseQueryService.ExecuteBySqlQuery(query, false, null); } string sqlQuery = $@"Drop TABLE [{entityDef.FormattedTableName}] "; dataBaseQueryService.ExecuteBySqlQuery(sqlQuery, false, null); return(resultOperation); }
public ResultOperation CreateTable(sysBpmsEntityDef entityDef) { DataBaseQueryService dataBaseQueryService = new DataBaseQueryService(base.UnitOfWork); ResultOperation resultOperation = new ResultOperation(); List <string> executeAlterQueries = new List <string>(); if (entityDef.IsActive && entityDef.Properties != null && entityDef.Properties.Any()) { string paramsQuery = string.Empty; foreach (EntityPropertyModel property in entityDef.Properties) { paramsQuery += $"[{property.Name}] {property.SqlTypeName} {(property.Required ? "NOT NULL" : "NULL")} ,"; } //generate table create query. string sqlQuery = $@"CREATE TABLE [{entityDef.FormattedTableName}]( [ID][uniqueidentifier] NOT NULL DEFAULT newid() PRIMARY KEY, [ThreadID][uniqueidentifier] NULL, {paramsQuery.TrimEnd(',')}) "; foreach (EntityPropertyModel property in entityDef.Properties.Where(c => c.DbType == EntityPropertyModel.e_dbType.Entity)) { this.CreateConstraintQuery(property, entityDef, executeAlterQueries); } dataBaseQueryService.ExecuteBySqlQuery(sqlQuery, false, null); } foreach (EntityPropertyModel property in entityDef.Properties.Where(c => !string.IsNullOrWhiteSpace(c.DefaultValue))) { executeAlterQueries.Add($@" ALTER TABLE {entityDef.FormattedTableName} ADD CONSTRAINT def_{entityDef.FormattedTableName}_{property.Name} {property.SqlDefaultValue} FOR {property.Name} ;"); } foreach (string query in executeAlterQueries) { dataBaseQueryService.ExecuteBySqlQuery(query, false, null); } return(resultOperation); }
public ResultOperation UpdateTable(sysBpmsEntityDef newEntityDef) { DataBaseQueryService dataBaseQueryService = new DataBaseQueryService(base.UnitOfWork); sysBpmsEntityDef currentEntityDef = this.GetInfo(newEntityDef.ID); ResultOperation resultOperation = new ResultOperation(); List <string> addQuery = new List <string>(); if (newEntityDef.IsActive && newEntityDef.Properties != null && newEntityDef.Properties.Any()) { foreach (EntityPropertyModel newProperty in newEntityDef.Properties) { //change Property if (currentEntityDef.Properties.Any(c => c.ID == newProperty.ID)) { EntityPropertyModel currentProperty = currentEntityDef.Properties.FirstOrDefault(c => c.ID == newProperty.ID); //change property name. if (currentProperty.Name != newProperty.Name) { addQuery.Add($" EXEC sp_rename '{currentEntityDef.FormattedTableName}.{currentProperty.Name}', '{newProperty.Name}', 'COLUMN'; {Environment.NewLine} "); } if (currentProperty.DbType != newProperty.DbType || currentProperty.SqlTypeName != newProperty.SqlTypeName || currentProperty.Required != newProperty.Required) { addQuery.Add($@" ALTER TABLE {currentEntityDef.FormattedTableName} ALTER COLUMN {newProperty.Name} {newProperty.SqlTypeName} {newProperty.SqlDefaultValue} {(newProperty.Required ? "NOT NULL" : "NULL")} ; "); } if (currentProperty.DefaultValue != newProperty.DefaultValue) { if (!string.IsNullOrWhiteSpace(currentProperty.DefaultValue)) { addQuery.Add($@" ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT def_{currentEntityDef.FormattedTableName}_{currentProperty.Name} ; "); } addQuery.Add($@" ALTER TABLE {currentEntityDef.FormattedTableName} ADD CONSTRAINT def_{currentEntityDef.FormattedTableName}_{newProperty.Name} {newProperty.SqlDefaultValue} FOR {newProperty.Name} ;"); } //if property is no longer a entity type drop relation constraint if (currentProperty.RelationToEntityID.HasValue && !newProperty.RelationToEntityID.HasValue) { addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT {currentProperty.RelationConstaintName};"); } else { if (currentProperty.RelationToEntityID != newProperty.RelationToEntityID) { //if it had relation add drop constraint relation query if (currentProperty.RelationToEntityID.HasValue) { addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT {currentProperty.RelationConstaintName};"); } this.CreateConstraintQuery(newProperty, newEntityDef, addQuery); } } } else { addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} ADD {newProperty.Name} {newProperty.SqlTypeName} {newProperty.SqlDefaultValue} {(newProperty.Required ? "NOT NULL" : "NULL")} ;"); //Create Constraint for new properties if (newProperty.RelationToEntityID.HasValue) { this.CreateConstraintQuery(newProperty, newEntityDef, addQuery); } } } //deleted properties foreach (EntityPropertyModel currentProperty in currentEntityDef.Properties.Where(c => !newEntityDef.Properties.Any(d => d.ID == c.ID))) { //drop default CONSTRAINT if (!string.IsNullOrWhiteSpace(currentProperty.DefaultValue)) { addQuery.Add($@" ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT def_{currentEntityDef.FormattedTableName}_{currentProperty.Name} ; "); } //if it has relation add drop constraint relation query if (currentProperty.DbType == EntityPropertyModel.e_dbType.Entity) { addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT {currentProperty.RelationConstaintName};"); } //drop property query addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} DROP COLUMN {currentProperty.Name};"); } } foreach (string query in addQuery) { dataBaseQueryService.ExecuteBySqlQuery(query, false, null); } return(resultOperation); }