/// <summary> /// Executa uma consulta páginaa no banco e retorna o resultado /// </summary> /// <param name="pQuery">Consulta</param> /// <param name="pPage">Página</param> /// <param name="pRowsByPage">Linhas por página</param> /// <param name="pSortFieldName">Campo para ordenação</param> /// <param name="pSortDirection">Direação da Ordenação</param> /// <returns>Um consulta com o resultado</returns> public TableQueryPaged SqlQuery(TableQuery pQuery, int pPage, int pRowsByPage, string pSortFieldName, eOrder pSortDirection) { var conn = Connection.Instance; var queryCount = new TableQuery(pQuery); var script = new SqlServerScriptWizard(); var result = new TableQueryPaged(); queryCount.Fields = new TableAdapterFieldCollection(); var fieldSort = pQuery.Fields.First(c => c.Name == pSortFieldName); queryCount.Fields.Add(new TableAdapterField() { Name = "Records", FieldType = eFieldType.eValueField, Value = string.Format("Count ( isNull({0},'0') )", script.GetFieldName(fieldSort.TableAlias, fieldSort.Name, string.Empty)) }); var selectCommandForCount = script.GetSelectComplexStatment(queryCount); result.Count = conn.SqlExecuteScalar <int>(selectCommandForCount.Item1, selectCommandForCount.Item2); result.RowsByPage = pRowsByPage == 0 ? 1 : pRowsByPage; result.PageCount = (int)Math.Ceiling(result.Count / (decimal)result.RowsByPage); result.Page = pPage <1 ? 1 : pPage> result.PageCount ? result.PageCount : pPage; var start = (result.Page - 1) * result.RowsByPage; var end = start + result.RowsByPage; var selectCommand = script.GetSelectStatment(pQuery, PageIdFieldName, start, end, pSortFieldName, pSortDirection); result.Data = conn.SqlServerQuery(selectCommand.Item1, selectCommand.Item2); return(result); }
/// <summary> /// Pega o camando de Seleção de Registros /// </summary> /// <returns>Um comando com o select</returns> private Tuple <string, List <TableAdapterField> > GetSelectCommand() { var script = new SqlServerScriptWizard { TableQuery = new TableQuery(this, true) }; return(script.GetSelectStatment()); }
/// <summary> /// Altera uma tabela /// </summary> /// <param name="pTable">Tabela</param> /// <param name="pFields">Campos as ser aterado</param> public void AlterTable(TableAdapter pTable, IEnumerable <TableAdapterField> pFields) { var script = new SqlServerScriptWizard(); var table = new TableAdapter(pTable.DBName, pTable.TableName); table.Collumns.AddRange(pFields.ToList()); Connection.Instance.SqlServerExecute(script.GetAlterTableCommand(table)); pTable.RaiseAfeterAlterTable(table); }
/// <summary> /// Pega um dataTabloe de Acordo com uma Consulta TableQuery /// </summary> /// <param name="pTableQuery">Um TableQuery</param> /// <returns>Um DataTable</returns> public DataTable GetData(TableQuery pTableQuery) { if (pTableQuery == null) { pTableQuery = new TableQuery(this); } var query = new SqlServerScriptWizard(pTableQuery); var selectCommand = query.GetSelectStatment(); return(Connection.SqlServerQuery(selectCommand.Item1, selectCommand.Item2)); }
private void button3_Click(object sender, EventArgs e) { ProfitabilityBase obj = DBTeste.CreateObject <ProfitabilityBase>(); TableQuery query = new TableQuery(obj); WhereCollection where = new WhereCollection() { new QueryParam(obj.Collumns[0], "1") }; WhereCollection where1 = new WhereCollection() { new QueryParam(obj.Collumns[1], eCondition.ecIsNull), }; query.Wheres.Add(where); query.Wheres.Add(where1); SqlServerScriptWizard scrip = new SqlServerScriptWizard(query); var result = scrip.GetSelectStatment(); }
private bool HasOneLine(string pFieldName, ref ChooseFromListEventArgs e) { var query = new TableQuery(Query) { Top = 2 }; var serarchField = new List <TableAdapterField> { query.Fields[pFieldName] }; if (pFieldName == FieldNameDescription) { serarchField.AddRange(WhereColumns); } var where = new WhereCollection(); foreach (var column in serarchField) { where.Add(column.DbTypeIsNumeric() ? new QueryParam(column, e.SearchBy == null ? 0 : e.SearchBy.To <Int64>()) : new QueryParam(column, e.SearchBy ?? string.Empty)); where.Last().Relationship = eRelationship.erOr; if (ConditionQuery != eCondition.ecNone) { where.Last().Condition = ConditionQuery; } } query.Wheres.Add(where); var script = new SqlServerScriptWizard(query); var command = script.GetSelectStatment(); var data = Connection.Instance.SqlServerQuery(command.Item1, command.Item2); if (data.Rows.Count == 1) { e.Record = data.Rows[0]; } else { serarchField = new List <TableAdapterField> { query.Fields[pFieldName] }; if (pFieldName == FieldNameDescription) { serarchField.AddRange(WhereColumns); } where = new WhereCollection(); foreach (var column in serarchField) { @where.Add(column.DbTypeIsNumeric() ? new QueryParam(column, e.SearchBy == null ? 0 : e.SearchBy.To <Int64>()) : new QueryParam(column, eCondition.ecLike, e.SearchBy ?? String.Empty)); where.Last().Relationship = eRelationship.erOr; } query.Wheres.Add(where); var comand = script.GetSelectStatment(); data = Connection.Instance.SqlServerQuery(comand.Item1, comand.Item2); if (data.Rows.Count == 1) { e.Record = data.Rows[0]; } } return(data.Rows.Count == 1); }
private void FillResult() { try { Cursor.Current = Cursors.WaitCursor; var query = new TableQuery(Query); if (!String.IsNullOrEmpty(SearchBy)) { var where = new WhereCollection(); foreach (var item in SearchColumns) { QueryParam param; if (item.DbTypeIsNumeric()) { int intConvert; if (!int.TryParse(SearchBy, out intConvert)) { continue; } param = new QueryParam(item, intConvert); } else { param = new QueryParam(item, eCondition.ecLike, SearchBy); } param.Relationship = eRelationship.erOr; where.Add(param); query.OrderBy.Add(new OrderBy(param)); } query.Wheres.Add(where); } var command = new SqlServerScriptWizard(query).GetSelectStatment(); grdResult.DataSource = Connection.Instance.SqlServerQuery(command.Item1, command.Item2); ViewResult.BestFitColumns(); ViewResult.ClearSorting(); if (!SearchColumns.IsEmpty()) { ViewResult.FocusedColumn = ViewResult.Columns[SearchColumns.First().Name]; ViewResult.FocusedColumn.SortOrder = DevExpress.Data.ColumnSortOrder.Ascending; if (Record != null) { var fieldValue = Record[ViewResult.FocusedColumn.FieldName].ToString(); ViewResult.StartIncrementalSearch(fieldValue); } } } finally { Cursor.Current = Cursors.Default; } }