private async Task <GridOutcome> GetDataQueryAsync(string GridConfigId, GridDataRequest gridDataRequest) { StringBuilder queryBuilder = new StringBuilder(); Task <SqlSourceConfiguration> sourceConfigTask = GetSourceConfig(GridConfigId); Task <GridConfigurationEO> gridConfigurationTask = GetGridConfiguration(GridConfigId); await Task.WhenAll(sourceConfigTask, gridConfigurationTask); SqlSourceConfiguration sourceConfig = sourceConfigTask.Result; GridConfigurationEO gridConfiguration = gridConfigurationTask.Result; gridConfiguration.Controls = DynamicFormRepository.GetFormControls(gridConfiguration.SearchConfigId).AsList(); Query query = null; Query countQuery = null; if (sourceConfig == null) { query = db.Query(gridConfiguration.TableName); } else { query = db.Query().FromRaw(sourceConfig.SqlQuery); if (sourceConfig.Condition.IsNotNullOrEmpty()) { query = query.Where(sourceConfig.Condition); } } if (gridConfiguration.Controls.IsNotNullOrEmpty() && gridDataRequest.Parameters.IsNotNullOrEmpty()) { IEnumerable <FormFieldConfiguration> controls = gridConfiguration.Controls.Where(control => gridDataRequest.Parameters.ContainsKey(control.Name)); foreach (FormFieldConfiguration control in controls) { HandleSearchOperators(gridDataRequest, control, query); } } countQuery = db.FromQuery(query).AsCount(); if (gridDataRequest.OrderBy.IsNotNullOrEmpty()) { query = query.OrderBy(gridDataRequest.OrderBy); } if (gridDataRequest.PageNo > 0 && gridDataRequest.PageSize > 0) { query = query.Skip((gridDataRequest.PageNo - 1) * gridDataRequest.PageSize).Take(gridDataRequest.PageSize); } Task <IEnumerable <dynamic> > resultSet = query.GetAsync <dynamic>(); int cnt = db.ExecuteScalar <int>(countQuery); Task.WaitAll(resultSet); Task <GridOutcome> gridOutcome = new Task <GridOutcome>(() => { return(new GridOutcome { ResultSet = resultSet.Result.ToList(), PageNo = gridDataRequest.PageNo, TotalRecords = cnt }); }); gridOutcome.RunSynchronously(); return(await gridOutcome); }
private string GetDataQuery(string GridConfigId, GridDataRequest gridDataRequest) { StringBuilder queryBuilder = new StringBuilder(); string sqlSource = GetQuery("SqlSourceConfiguration"); string sql = GetQuery("GridConfiguration"); SqlSourceConfiguration sourceConfig = Connection.Query <SqlSourceConfiguration>(sqlSource, new { GridConfigId }).FirstOrDefault(); GridConfigurationEO gridConfiguration = Connection.Query <GridConfigurationEO>(sql, new { GridConfigId }).FirstOrDefault(); gridConfiguration.Controls = DynamicFormRepository.GetFormControls(gridConfiguration.SearchConfigId).AsList(); if (sourceConfig == null) { queryBuilder.Append("Select "); queryBuilder.Append("* "); queryBuilder.Append(" from "); queryBuilder.Append(gridConfiguration.TableName); } else { queryBuilder.Append(sourceConfig.SqlQuery); if (sourceConfig.Condition.IsNotNullOrEmpty()) { queryBuilder.Append(sourceConfig.Condition); } } if (gridConfiguration.Controls.IsNotNullOrEmpty() && gridDataRequest.Parameters.IsNotNullOrEmpty()) { if (!queryBuilder.ToString().Contains(" where ")) { queryBuilder.Append(" where "); } gridConfiguration.Controls.ForEach (control => { if (gridDataRequest.Parameters.ContainsKey(control.Name)) { queryBuilder.Append(control.Name); queryBuilder.Append(" "); queryBuilder.Append(control.SearchOperator); queryBuilder.Append(" @"); queryBuilder.Append(control.Name); queryBuilder.Append(" And "); } } ); queryBuilder = queryBuilder.Remove(queryBuilder.Length - 4, 4); } if (gridDataRequest.OrderBy.IsNotNullOrEmpty()) { PaginationSettings paginationSettings = new PaginationSettings { OrderBy = gridDataRequest.OrderBy, PageNo = gridDataRequest.PageNo, PageSize = gridDataRequest.PageSize }; string query = queryBuilder.ToString(); queryBuilder.Clear(); queryBuilder.Append(Paginate(query, paginationSettings)); queryBuilder.Append(Environment.NewLine); queryBuilder.Append(GetCountQuery(queryBuilder.ToString())); } return(queryBuilder.ToString()); }