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