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