/// <summary> /// Method to append HAVING clause to query. /// </summary> /// <param name="table">Table data from parsed table.</param> protected virtual void Having(QueryDataModel table) { var clause = table.TableClause.FirstOrDefault(n => n.ClauseType.Equals("HAVING", StringComparison.OrdinalIgnoreCase)); if (clause != null) { Query.Append(string.Concat(" HAVING ", clause.ClauseValue)); } }
/// <summary> /// Method to append JOIN clause to the query. /// </summary> /// <param name="table">Table data from parsed table.</param> protected virtual void Join(QueryDataModel table) { var joinClauseList = table.TableClause.Where(n => n.ClauseType.Equals("JOIN", StringComparison.OrdinalIgnoreCase)); foreach (var clause in joinClauseList) { Query.Append(string.Concat(" ", clause.JoinType.ToUpper(), " JOIN ", clause.JoinTableName, " ON ", clause.ClauseValue)); } }
public MNIST28x28BackQueryData(QueryDataModel dataModel) { base.InputValues = dataModel.InputValues; base.Marker = dataModel.Marker; base.OutputValues = dataModel.OutputValues; for (int i = 0; i < dataModel.InputValues.Length; i++) { base.InputValues[i] = (dataModel.InputValues[i] * 255.0f / 0.99f) - 0.01f; } }
/// <summary> /// Method to generate ALTER TABLE query. /// </summary> /// <param name="tableName">Table data from parsed table.</param> protected virtual void AlterTable(QueryDataModel table) { //TODO: handle column contraints. string alterTableBaseQuery = string.Concat("ALTER TABLE ", Utility.GetTableName(table.TableName), " "); List <ColumnModel> columnList; /* Build query to alter columns. */ columnList = table.ColumnList.FindAll(x => string.Equals(x.ColumnOperation, "alter", StringComparison.OrdinalIgnoreCase)); if (columnList.Count > 0) { foreach (var columnConfig in columnList) { /* Drop constraints before altering column if exists. */ Query.Append(CreateDropConstraintQuery(table.TableName, columnConfig)); Query.Append(string.Concat(alterTableBaseQuery, "ALTER COLUMN ", CreateColumnConfigString(columnConfig, true), ";")); } } /* Build query to add new columns. */ columnList = table.ColumnList.FindAll(x => string.Equals(x.ColumnOperation, "add", StringComparison.OrdinalIgnoreCase)); if (columnList.Count > 0) { List <string> columnConfigStringList = new List <string>(); foreach (var columnConfig in columnList) { columnConfigStringList.Add(CreateColumnConfigString(columnConfig)); } Query.Append(string.Concat(alterTableBaseQuery, "ADD ", string.Join(", ", columnConfigStringList), ";")); } /* Build query to drop columns. */ columnList = table.ColumnList.FindAll(x => string.Equals(x.ColumnOperation, "drop", StringComparison.OrdinalIgnoreCase)); if (columnList.Count > 0) { List <string> columnNameString = new List <string>(); foreach (var columnConfig in columnList) { /* Drop constraints before droping column if exists. */ Query.Append(CreateDropConstraintQuery(table.TableName, columnConfig)); columnNameString.Add(columnConfig.ColumnName); } Query.Append(string.Concat(alterTableBaseQuery, "DROP COLUMN ", string.Join(", ", columnNameString), ";")); } }
/// <summary> /// Method to generate CREATE TABLE query. /// </summary> /// <param name="table">Table data from parsed table.</param> protected virtual void CreateTable(QueryDataModel table) { List <string> columnConfigList = new List <string>(); Query.Append(string.Concat("CREATE TABLE ", Utility.GetTableName(table.TableName), " ( ")); /* Build column config string for each column. */ foreach (var column in table.ColumnList) { /* Add column config string to list. */ columnConfigList.Add(CreateColumnConfigString(column)); } /* Append column config string to query. */ Query.Append(string.Concat(string.Join(", ", columnConfigList), ")")); }
/// <summary> /// Method to generate INSERT query. /// </summary> /// <param name="requestData">Table data to generate INSERT query.</param> protected virtual void InsertRecord(QueryDataModel requestData) { /* Get number of records to be filled by getting count in row value. */ int numberOfRecords = requestData.ColumnList.First().Values.Count; /* Variable to store record number and records data. */ Dictionary <int, List <string> > recordsData = new Dictionary <int, List <string> >(); /* Variable to store list of column name. */ List <string> columnNameList = new List <string>(), /* Variable to store list of parsed record values as string */ parsedRecords = new List <string>(); Query.Append(string.Concat("INSERT INTO ", Utility.GetTableName(requestData.TableName), " (")); /* To create values group. */ for (int i = 0; i < numberOfRecords; i++) { /* Loop through each column. */ foreach (var column in requestData.ColumnList) { /* Add column name to columnNameList if it does not exists. */ if (!columnNameList.Contains(column.ColumnName)) { columnNameList.Add(column.ColumnName); } /* Add recordsData item if does not exists. */ if (!recordsData.ContainsKey(i)) { recordsData.Add(i, new List <string>()); } /* Add value to recordsData. */ recordsData[i].Add(string.Concat("'", column.Values[i], "'")); } } /* Construct parsed record string from value group. */ foreach (var valueGroup in recordsData) { parsedRecords.Add(string.Concat("(", string.Join(", ", valueGroup.Value), ")")); } /* Append column name list and values record. */ Query.Append(string.Concat(string.Join(", ", columnNameList), ") VALUES ", string.Join(", ", parsedRecords), ";")); }
/// <summary> /// Method to generate UPADTE query. /// </summary> /// <param name="requestData">Table data to generate INSERT query.</param> protected virtual void UpdateRecord(QueryDataModel requestData) { /* Variable to store list of column name and values to be set. */ List <string> nameValueList = new List <string>(); Query.Append(string.Concat("UPDATE ", Utility.GetTableName(requestData.TableName), " SET ")); /* Construct list of column name and values to be set. */ foreach (var column in requestData.ColumnList) { nameValueList.Add(string.Concat(column.ColumnName, " = '", column.Values.FirstOrDefault(), "'")); } /* Append column name and values to be updated. */ Query.Append(string.Join(", ", nameValueList)); /* Append where condition if exists. */ Where(requestData); /* Append semicolon to mark end of statement. */ Query.Append(";"); }
/// <summary> /// Query Dynamic Data /// </summary> /// <param name="model"></param> /// <returns></returns> public QueryDataModel QueryData(QueryDataModel model) { model.UserId = CurrentUserId; model.Data = _dm.QueryData(model); return(model); }
public QueryDataModel QueryData(QueryDataModel model) { return(_service.QueryData(model)); }
public async Task <QueryDataModel> GetDataModelinfo(int UserId, int Year, int Mounth) { var data = await _ISysUserServices.GetModelAsync(x => x.Id == UserId && x.IsDelete == false); if (data == null) { return(new QueryDataModel()); } { QueryDataModel queryDataModel = new QueryDataModel { Id = data.Id, UserName = data.UserName, Data = new List <Data>() }; var formData = await _FillFormService.Query(x => x.UserId == data.Id && x.Year == Year && x.Mounth == Mounth); if (formData == null) { return(new QueryDataModel()); } foreach (var item in formData) { var datas = new Data { DutyPeople = item.DutyPeople, Mounth = item.Mounth, Year = item.Year, TableData = _Mapper.Map <List <ProjectDto> >(JsonConvert.DeserializeObject <List <ProjectDto> >(item.TableData)), TableFour = new List <ProjectFourDto>(), TableFive = new List <ProjectFiveDto>() }; if (item.TableFive == "[]") { item.TableFive = string.Empty; } else { datas.TableFive = _Mapper.Map <List <ProjectFiveDto> >(JsonConvert.DeserializeObject <List <Conversation> >(item.TableFive)); } if (item.TableFour == "[]") { item.TableFour = string.Empty; } else { datas.TableFour = _Mapper.Map <List <ProjectFourDto> >(JsonConvert.DeserializeObject <List <ProjectFourDto> >(item.TableFour)); } if (datas.TableFour == null || datas.TableFour.Count == 0) { datas.TableFour = new List <ProjectFourDto>(); } if (datas.TableFive == null || datas.TableFive.Count == 0) { datas.TableFive = new List <ProjectFiveDto>(); } queryDataModel.Data.Add(datas); } queryDataModel.Count = queryDataModel.Data.Count; return(queryDataModel); } }
/// <summary> /// Query Dynamic Data /// </summary> /// <param name="model"></param> /// <returns></returns> public IList <object> QueryData(QueryDataModel model) { // 1. Get Columns var viewColumns = _viewColumnRep.Table.Where(p => p.ViewId == model.ViewId && p.IsActived).ToList(); var columnIds = viewColumns.Select(s => s.ColumnId).ToArray(); var columns = _columnRep.Include(i => i.DataType).Where(p => columnIds.Contains(p.Id)).ToList(); model.Columns = new List <Common.Models.DataColumn>(); foreach (var item in columns) { var viewColumn = viewColumns.Single(s => s.ColumnId == item.Id); model.Columns.Add(new Common.Models.DataColumn { Id = item.Id, Name = item.Name, NameDict = item.NameDict, EnumId = item.EnumId, DataTypeCode = item.DataType.Code, IsForeignKey = item.IsForeignKey, IsPrimaryKey = item.IsPrimaryKey, Position = viewColumn.Position, Visible = viewColumn.Visible, Searchable = item.Searchable, Sortable = item.Sortable, Width = viewColumn.Width, TableAlias = item.TableAlias, SqlName = StringHelper.ConverToCamelCase(item.SqlName) }); } // 2. Create SqlParameters var moduleIdParam = new SqlParameter("moduleId", model.ModuleId); var viewIdParam = new SqlParameter("viewId", model.ViewId); var orderExpressionParam = model.OrderExpression != null ? new SqlParameter("orderExpression", model.OrderExpression) : new SqlParameter("orderExpression", DBNull.Value); var pageIndexParam = new SqlParameter("pageIndex", model.PageIndex); var pageSizeParam = new SqlParameter("pageSize", model.PageSize); var currentUserIdParam = new SqlParameter("userId", model.UserId); var totalRowParam = new SqlParameter { ParameterName = "total", SqlDbType = SqlDbType.Int, Value = DBNull.Value, Direction = ParameterDirection.Output }; var groupJsonParam = new SqlParameter { ParameterName = "groupJson", SqlDbType = SqlDbType.NVarChar, Size = 4000, Value = DBNull.Value, Direction = ParameterDirection.Output }; var typeInfo = ObjectBuilder.CompileResultTypeInfo("QueryDataResult", "QueryDataResult", model.Columns); // 3. Excute query var result = _rep.ExcuteStoreProcedure(typeInfo, SqlStoreProcedureConstant.SpQueryData, moduleIdParam, viewIdParam, orderExpressionParam, pageIndexParam, pageSizeParam, currentUserIdParam, totalRowParam, groupJsonParam); model.TotalRecord = (int)totalRowParam.Value; model.GroupJson = (string)groupJsonParam.Value; //model.GroupByObjs = JsonConvert.DeserializeObject<List<GroupByObj>>((string)groupJsonParam.Value); return(result.ToList()); }
public QueryDataVM(QueryDataModel model) { Marker = model.Marker; DataModel = model; }