public Guid?GetEntityID(sysBpmsEntityDef entityDef, sysBpmsVariable variable, List <QueryModel> additionalParams) { string whereClause = VariableEngine.GenerateWhereClause(variable); string orderClause = VariableEngine.GenerateOrderClause(variable); //get sql query parameter from additionalParams List <SqlParameter> queryParams = QueryModel.GetSqlParameter(base.GetAllQueryModels(additionalParams) ?? new List <QueryModel>()).ToList(); //add variable dependies to whereclause which have relation with current variable. this.AddDependencyClause(variable, queryParams, null, ref whereClause, additionalParams); string sqlQuery = $" select {entityDef.FormattedTableName}.ID from {entityDef.FormattedTableName} {whereClause} {orderClause} "; this.AddDefaultParams(sqlQuery, queryParams); this.AddVariableParameters(ref sqlQuery, queryParams); queryParams = queryParams.Where(c => sqlQuery.Contains(c.ParameterName)).ToList(); DataTable dataTable = new DataBaseQueryService(this.UnitOfWork).GetBySqlQuery(sqlQuery, true, null, queryParams.ToArray()); if (dataTable.Rows.Count > 0) { return(Guid.Parse(dataTable.Rows[0][0].ToString())); } return(null); }
/// <summary> /// this method save entityDef after form post action. /// </summary> public Tuple <ResultOperation, Guid?> SaveIntoDataBase(sysBpmsEntityDef entityDef, sysBpmsVariable variable, DataModel dataModel, List <QueryModel> additionalParams, Dictionary <string, DataModel> allSavedEntities) { ResultOperation resultOperation = new ResultOperation(); List <SqlParameter> columnParams = this.GetColumsAsParams(entityDef, dataModel.ToList(), variable); string whereClause = VariableEngine.GenerateWhereClause(variable); string orderClause = VariableEngine.GenerateOrderClause(variable); //get sql query parameter from additionalParams List <SqlParameter> queryParams = QueryModel.GetSqlParameter(base.GetAllQueryModels(additionalParams) ?? new List <QueryModel>()).ToList(); //variable.VariableDependencies is null retrieve it from database variable.DependentVariableDependencies = variable.DependentVariableDependencies ?? new VariableDependencyService(base.UnitOfWork).GetList(variable.ID, null); //add variable dependies to whereclause which have relation with current variable. this.AddDependencyClause(variable, queryParams, columnParams, ref whereClause, additionalParams, allSavedEntities); string sqlQueryIsExist = $@"(select top(1) {entityDef.FormattedTableName}.ID from {entityDef.FormattedTableName} {whereClause} {orderClause})"; string sqlInsertQuery = $@"INSERT INTO {entityDef.FormattedTableName} ({(base.EngineSharedModel.CurrentThreadID.HasValue ? "ThreadID," : "")}{string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@')))}) OUTPUT inserted.ID VALUES ({(base.EngineSharedModel.CurrentThreadID.HasValue ? ("'" + base.EngineSharedModel.CurrentThreadID.Value + "',") : "")}{string.Join(",", columnParams.Select(c => c.ParameterName))})"; this.AddDefaultParams((sqlInsertQuery + sqlQueryIsExist), queryParams); this.AddVariableParameters(ref sqlInsertQuery, queryParams); this.AddVariableParameters(ref sqlQueryIsExist, queryParams); queryParams = columnParams.Union(queryParams).Where(c => (sqlInsertQuery + sqlQueryIsExist).Contains(c.ParameterName)).GroupBy(c => c.ParameterName).Select(c => c.FirstOrDefault()).ToList(); DataTable dataTableIsExist = new DataBaseQueryService(base.UnitOfWork).GetBySqlQuery(sqlQueryIsExist, true, null, queryParams.ToArray()); Guid? entityIsExistId = dataTableIsExist != null && dataTableIsExist.Rows.Count > 0 ? dataTableIsExist.Rows[0][0].ToGuidObj() : (Guid?)null; if (entityIsExistId.HasValue) { string sqlUpdateQuery = $@"update {entityDef.FormattedTableName} set {string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@') + "=" + c.ParameterName))} where ID='{entityIsExistId.ToStringObj()}'"; //update entity new DataBaseQueryService(base.UnitOfWork).ExecuteBySqlQuery(sqlUpdateQuery, true, queryParams.ToArray()); } else { //insert entity this.CheckMandatory(resultOperation, entityDef, columnParams); if (resultOperation.IsSuccess) { entityIsExistId = new DataBaseQueryService(base.UnitOfWork).ExecuteScalar <Guid>(sqlInsertQuery, true, queryParams.ToArray()).ToGuidObjNull(); } } return(new Tuple <ResultOperation, Guid?>(resultOperation, entityIsExistId)); }
/// <summary> /// this method save entityDef using dynamic code. /// </summary> public Tuple <ResultOperation, Guid?> SaveIntoDataBase(VariableModel variableModel) { ResultOperation resultOperation = new ResultOperation(); sysBpmsEntityDef entityDef = new EntityDefService(base.UnitOfWork).GetInfo(variableModel.Name); List <SqlParameter> columnParams = this.GetColumsAsParams(entityDef, variableModel.Items.FirstOrDefault().ToList(), (base.EngineSharedModel != null ? new VariableService(base.UnitOfWork).GetInfo(base.EngineSharedModel.CurrentProcessID, base.EngineSharedModel.CurrentApplicationPageID, variableModel.Name) : null)); //get sql query parameter from additionalParams List <SqlParameter> queryParams = QueryModel.GetSqlParameter(base.GetAllQueryModels(null) ?? new List <QueryModel>()).ToList(); string sqlQueryIsExist = $@"(select top(1) {entityDef.FormattedTableName}.ID from {entityDef.FormattedTableName} where ID='{variableModel["ID"]}' )"; string sqlInsertQuery = $@"INSERT INTO {entityDef.FormattedTableName} ({string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@')))}) OUTPUT inserted.ID VALUES ({string.Join(",", columnParams.Select(c => c.ParameterName))})"; this.AddDefaultParams((sqlInsertQuery + sqlQueryIsExist), queryParams); this.AddVariableParameters(ref sqlInsertQuery, queryParams); this.AddVariableParameters(ref sqlQueryIsExist, queryParams); queryParams = columnParams.Union(queryParams).Where(c => (sqlInsertQuery + sqlQueryIsExist).Contains(c.ParameterName)).GroupBy(c => c.ParameterName).Select(c => c.FirstOrDefault()).ToList(); DataTable dataTableIsExist = new DataBaseQueryService(base.UnitOfWork).GetBySqlQuery(sqlQueryIsExist, true, null, queryParams.ToArray()); Guid? entityIsExistId = dataTableIsExist != null && dataTableIsExist.Rows.Count > 0 ? dataTableIsExist.Rows[0][0].ToGuidObj() : (Guid?)null; if (entityIsExistId.HasValue) { string sqlUpdateQuery = $@"update {entityDef.FormattedTableName} set {string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@') + "=" + c.ParameterName))} where ID='{entityIsExistId.ToStringObj()}' "; //update entity new DataBaseQueryService(base.UnitOfWork).ExecuteBySqlQuery(sqlUpdateQuery, true, queryParams.ToArray()); } else { //insert entity this.CheckMandatory(resultOperation, entityDef, columnParams); if (resultOperation.IsSuccess) { entityIsExistId = new DataBaseQueryService(base.UnitOfWork).ExecuteScalar <Guid>(sqlInsertQuery, true, queryParams.ToArray()).ToGuidObjNull(); } } return(new Tuple <ResultOperation, Guid?>(resultOperation, entityIsExistId)); }
/// <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); }
//It used used for built in upgrade system for now it is not in work. public static void UpdatedSqlQuery(int portalId) { if (!IsUpdatedSqlQueryVesrion(portalId)) { using (DataBaseQueryService dataBaseQueryService = new DataBaseQueryService()) { List <string> queries = new List <string>(); string XslFile = System.Web.Hosting.HostingEnvironment.MapPath(BPMSResources.SqlDataProvider); foreach (string fileName in Directory.GetFiles(XslFile, "*.SqlDataProvider").Select(Path.GetFileNameWithoutExtension)) { if (fileName != "Uninstall.SqlDataProvider" && fileName.CompareTo(LastSqlVersionExecuted) == 1) { queries.AddRange(File.ReadAllText(XslFile + fileName + ".SqlDataProvider").Split(new string[1] { "GO" }, StringSplitOptions.None).Where(c => !string.IsNullOrWhiteSpace(c)).ToList()); } } dataBaseQueryService.UpdatedSqlQuery(queries); using (ConfigurationService configurationService = new ConfigurationService()) { sysBpmsConfiguration settingValue = configurationService.GetList("", sysBpmsConfiguration.e_NameType.LastSqlUpdatedVersion.ToString()).LastOrDefault(); if (settingValue == null) { settingValue = new sysBpmsConfiguration(); settingValue.LastUpdateOn = DateTime.Now; settingValue.Value = CurrentBpmsVersion; configurationService.Add(settingValue); } else { settingValue.LastUpdateOn = DateTime.Now; settingValue.Value = CurrentBpmsVersion; configurationService.Update(settingValue); } LastSqlVersionExecuted = CurrentBpmsVersion; UrlUtility.NoSkinPath = configurationService.GetValue(sysBpmsConfiguration.e_NameType.NoSkinPath.ToString()); } } } }
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); }