public ResultOperation Update(sysBpmsEntityDef entityDef) { ResultOperation resultOperation = new ResultOperation(); try { List <sysBpmsEntityDef> listEntity = this.GetList(string.Empty, null); this.BeginTransaction(); if (listEntity.Any(c => c.Name == entityDef.Name && c.ID != entityDef.ID)) { resultOperation.AddError(LangUtility.Get("SameEntity.Text", nameof(sysBpmsEntityDef))); } if (resultOperation.IsSuccess) { this.UpdateTable(entityDef); entityDef.DesignXML = new EntityDesignXmlModel() { EntityPropertyModel = entityDef.Properties, }.BuildXml(); this.UnitOfWork.Repository <IEntityDefRepository>().Update(entityDef); this.UnitOfWork.Save(); } } catch (Exception ex) { return(base.ExceptionHandler(ex)); } base.FinalizeService(resultOperation); return(resultOperation); }
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); }
/// <param name="containerQuery">It is generally used in combosearch which add a parent query that filter table's rows according to query parameter and text field</param> /// <param name="includes">It is used in List variable in DataGrid and chart is each item in array is like (Product.Name,Order.Name)</param> public DataTable GetEntity(sysBpmsEntityDef entityDef, sysBpmsVariable variable, List <QueryModel> additionalParams, PagingProperties currentPaging, string containerQuery = null, string[] includes = null) { string columnName = string.Empty; string includesQuery = string.Empty; switch ((sysBpmsVariable.e_VarTypeLU)variable.VarTypeLU) { case sysBpmsVariable.e_VarTypeLU.Integer: case sysBpmsVariable.e_VarTypeLU.String: case sysBpmsVariable.e_VarTypeLU.Uniqueidentifier: case sysBpmsVariable.e_VarTypeLU.Decimal: case sysBpmsVariable.e_VarTypeLU.DateTime: case sysBpmsVariable.e_VarTypeLU.Boolean: columnName = variable.FieldName; break; case sysBpmsVariable.e_VarTypeLU.List when variable.RelationTypeLU == (int)sysBpmsVariable.e_RelationTypeLU.Entity && includes?.Any() == true: case sysBpmsVariable.e_VarTypeLU.Object when includes?.Any() == true: EntityDefService entityDefService = new EntityDefService(base.UnitOfWork); foreach (var p in entityDef.Properties.Where(c => c.DbType == EntityPropertyModel.e_dbType.Entity && includes.Any(d => d.Contains(c.Name)))) { string foreingName = entityDefService.GetInfo(p.RelationToEntityID.Value).FormattedTableName; string includesColumnNames = string.Join(",", includes.Where(d => d.Split('.')[0] == p.Name).Select(c => $"{c.Split('.')[1]} as {p.Name}__{c.Split('.')[1]}")); if (!includesColumnNames.Contains($"{p.Name}__ID")) { includesColumnNames += "," + $"ID as {p.Name}__ID"; } includesQuery += $" left join (select {includesColumnNames} from {foreingName}) {foreingName} on {entityDef.FormattedTableName}.{p.Name}={foreingName}.{p.Name}__ID " + Environment.NewLine; } break; } columnName = string.IsNullOrWhiteSpace(columnName) ? "*" : columnName; 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 {columnName} from {entityDef.FormattedTableName} {includesQuery} {whereClause} {orderClause} "; //If containerQuery is not null if (!string.IsNullOrWhiteSpace(containerQuery)) { sqlQuery = (sqlQuery.Contains("order") && (!sqlQuery.ToLower().Contains("top") && !sqlQuery.ToLower().Contains("offset"))) ? $"{sqlQuery} OFFSET 0 ROWS " : sqlQuery; sqlQuery = string.Format(containerQuery, sqlQuery); } this.AddDefaultParams(sqlQuery, queryParams); this.AddVariableParameters(ref sqlQuery, queryParams); queryParams = queryParams.Where(c => sqlQuery.Contains(c.ParameterName)).ToList(); return(new DataBaseQueryService(this.UnitOfWork).GetBySqlQuery(sqlQuery, true, currentPaging, queryParams.ToArray())); }
public List <EntityPropertyModel> GetEntityProperties(Guid ID) { using (EntityDefService entityDefService = new EntityDefService()) { sysBpmsEntityDef sysBpmsEntityDef1 = entityDefService.GetInfo(ID); return(sysBpmsEntityDef1.AllProperties); } }
public void Delete(Guid entityDefId) { sysBpmsEntityDef EntityDef = this.Context.sysBpmsEntityDefs.FirstOrDefault(d => d.ID == entityDefId); if (EntityDef != null) { this.Context.sysBpmsEntityDefs.Remove(EntityDef); } }
private void CheckMandatory(ResultOperation resultOperation, sysBpmsEntityDef entityDef, List <SqlParameter> columnParams) { if (entityDef.Properties.Any(c => c.Required && !columnParams.Any(d => d.ParameterName.TrimStart('@') == c.Name))) { string errorMsg = string.Join("<br/>", entityDef.Properties.Where(c => c.Required && !columnParams.Any(d => d.ParameterName.TrimStart('@') == c.Name)). Select(c => string.Format(SharedLang.Get("FormatRequired.Text"), c.Name))); resultOperation.AddError(errorMsg); } }
public EntityDefDTO(sysBpmsEntityDef entityDef) { if (entityDef != null) { this.ID = entityDef.ID; this.DesignXML = entityDef.DesignXML; this.IsActive = entityDef.IsActive; this.Name = entityDef.Name; this.DisplayName = entityDef.DisplayName; } }
public ResultOperation Active(Guid EntityDefId) { ResultOperation resultOperation = new ResultOperation(); if (resultOperation.IsSuccess) { sysBpmsEntityDef entityDef = this.UnitOfWork.Repository <IEntityDefRepository>().GetInfo(EntityDefId); entityDef.IsActive = true; this.UnitOfWork.Repository <IEntityDefRepository>().Update(entityDef); this.UnitOfWork.Save(); } return(resultOperation); }
/// <summary> /// /// </summary> /// <param name="rModel">current EntityRelationModel which is evaluated.</param> /// <returns></returns> private void CreateConstraintQuery(EntityPropertyModel rModel, sysBpmsEntityDef entityDef, List <string> listQueries) { sysBpmsEntityDef foreignEntity = this.GetInfo(rModel.RelationToEntityID.Value); string constaintName = this.GetConstraintName(rModel.Name, entityDef.FormattedTableName, foreignEntity.FormattedTableName); //generate foreign key query listQueries.Add($@"ALTER TABLE [{entityDef.FormattedTableName}] WITH CHECK ADD CONSTRAINT [{constaintName}] FOREIGN KEY([{rModel.Name}]) REFERENCES [{foreignEntity.FormattedTableName}] ([ID])"); listQueries.Add($@"ALTER TABLE [{entityDef.FormattedTableName}] CHECK CONSTRAINT [{constaintName}]"); rModel.RelationConstaintName = constaintName; }
/// <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)); }
public object PostAddEdit(PostAddEditEntityDefDTO postAddEdit) { using (EntityDefService entityDefService = new EntityDefService()) { sysBpmsEntityDef entityDef = postAddEdit.EntityDefDTO.ID != Guid.Empty ? entityDefService.GetInfo(postAddEdit.EntityDefDTO.ID) : new sysBpmsEntityDef(); if (postAddEdit.listProperties != null) { foreach (var Item in postAddEdit.listProperties) { if (string.IsNullOrWhiteSpace(Item.ID)) { Item.ID = Guid.NewGuid().ToString(); } Item.IsActive = true; postAddEdit.EntityDefDTO.Properties.Add(Item); } } ResultOperation resultOperation = entityDef.Update(postAddEdit.EntityDefDTO.DisplayName, postAddEdit.EntityDefDTO.Name, postAddEdit.EntityDefDTO.DesignXML, true, postAddEdit.EntityDefDTO.Properties); if (resultOperation.IsSuccess) { if (entityDef.ID != Guid.Empty) { resultOperation = entityDefService.Update(entityDef); } else { resultOperation = entityDefService.Add(entityDef); } if (resultOperation.IsSuccess) { return(new PostMethodMessage(SharedLang.Get("Success.Text"), DisplayMessageType.success, new { entityDef.ID, Name = (entityDef.Name + $"({entityDef.DisplayName})") })); } else { return(new PostMethodMessage(resultOperation.GetErrors(), DisplayMessageType.error)); } } else { return(new PostMethodMessage(resultOperation.GetErrors(), DisplayMessageType.error)); } } }
/// <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 object ExecuteQuery(ExecuteQueryDTO model) { using (EntityDefService entityDefService = new EntityDefService()) { using (DataBaseQueryService dataBaseQueryService = new DataBaseQueryService()) { try { sysBpmsEntityDef entityDef = entityDefService.GetInfo(model.EntityId); model.Query = string.IsNullOrWhiteSpace(model.Query) ? $" select top(200) * from {entityDef.FormattedTableName} " : model.Query; DataTable data = dataBaseQueryService.GetBySqlQuery(model.Query, true, null); return(Json(new { Data = data, Columns = data.Columns, EntityId = model.EntityId, Query = model.Query })); } catch (Exception ex) { return(new PostMethodMessage(ex.ToStringObj(), DisplayMessageType.error)); } } } }
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 void Add(sysBpmsEntityDef entityDef) { entityDef.ID = Guid.NewGuid(); this.Context.sysBpmsEntityDefs.Add(entityDef); }
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); }
public void Update(sysBpmsEntityDef entityDef) { this.Context.Entry(entityDef.Clone()).State = EntityState.Modified; }
private List <SqlParameter> GetColumsAsParams(sysBpmsEntityDef sysBpmsEntityDef, Dictionary <string, object> dataList, sysBpmsVariable sysBpmsVariable) { List <EntityPropertyModel> Properties = sysBpmsEntityDef.Properties; List <SqlParameter> columnParams = new List <SqlParameter>(); for (int index = 0; index < dataList.Count; index++) { var item = dataList.ToList()[index]; string propertyName = (sysBpmsVariable == null || sysBpmsVariable.VarTypeLU == (int)sysBpmsVariable.e_VarTypeLU.Object) ? item.Key : sysBpmsVariable.FieldName; EntityPropertyModel _Property = Properties.FirstOrDefault(c => c.Name == propertyName); if (Properties.Any(c => c.Name == propertyName)) { switch (_Property.DbType) { case EntityPropertyModel.e_dbType.Decimal: if (_Property.Required) { dataList[item.Key] = item.Value.ToDecimalObj(); } else { dataList[item.Key] = item.Value != null?item.Value.ToDecimalObjNull() : (decimal?)null; } break; case EntityPropertyModel.e_dbType.Integer: if (_Property.Required) { dataList[item.Key] = item.Value.ToIntObj(); } else { dataList[item.Key] = item.Value != null?item.Value.ToIntObjNull() : (int?)null; } break; case EntityPropertyModel.e_dbType.Long: if (_Property.Required) { dataList[item.Key] = Convert.ToInt64(item.Value); } else { dataList[item.Key] = item.Value != null?Convert.ToInt64(item.Value) : (long?)null; } break; case EntityPropertyModel.e_dbType.String: dataList[item.Key] = item.Value.ToStringObj(); break; case EntityPropertyModel.e_dbType.DateTime: if (string.IsNullOrWhiteSpace(item.Value.ToStringObj())) { dataList[item.Key] = DBNull.Value; } else { dataList[item.Key] = Convert.ToDateTime(dataList[item.Key]); } break; case EntityPropertyModel.e_dbType.Uniqueidentifier: if (string.IsNullOrWhiteSpace(item.Value.ToStringObj())) { dataList[item.Key] = DBNull.Value; } break; } columnParams.Add(new SqlParameter("@" + propertyName, dataList[item.Key])); } } return(columnParams); }
public EntityVariableTypeEngine(EngineSharedModel engineSharedModel, sysBpmsVariable variable, Guid?processID, Guid?threadID, List <QueryModel> additionalParams, IUnitOfWork unitOfWork = null) : base(engineSharedModel, variable, processID, threadID, additionalParams, unitOfWork) { this.EntityDef = new EntityDefService(base.UnitOfWork).GetInfo(Variable.EntityDefID.Value); }