protected virtual async Task <QueryResult <TEntity> > QueryAsync <TResult>(
SqlWhereStatement whereStatement,
string[] selectColumns,
int skip,
int take,
CancellationToken cancellationToken,
string[] orderByColumns,
bool orderByAscending = true,
IDictionary <string, object> additionalFilterValues = null,
bool distinct = false)
{
var filter = whereStatement.Where;
var filterValues = whereStatement.FilterValues;
using (var connection = await GetConnectionAsync(cancellationToken))
{
if (additionalFilterValues != null)
{
var filterValuesSql = SqlHelper.GetAndEqualsStatement(DatabaseDriver, additionalFilterValues);
filter = $"{filter} {DatabaseDriver.GetSqlStatementTemplate(SqlStatement.And)} ({filterValuesSql})";
filterValues = filterValues
.Union(additionalFilterValues)
.ToDictionary(k => k.Key, v => v.Value);
}
int totalCount = 0;
if (FetchQueryResultTotal)
{
using (var countCommand = connection.CreateSelectCountCommand(
DatabaseDriver,
Table,
filter,
filterValues,
distinct))
{
totalCount = Convert.ToInt32(
await countCommand.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false));
}
}
return(new QueryResult <TEntity>(
new DbDataReaderAsyncEnumerable <TEntity>(
GetConnectionAsync,
c =>
c.CreateSelectSkipTakeCommand(
DatabaseDriver,
Table,
selectColumns,
filter,
skip,
take,
orderByColumns,
orderByAscending,
filterValues,
distinct),
Mapper,
selectColumns),
totalCount));
}
}
protected override void BindGrid()
{
using (var cmd = new SqlCommand())
{
cmd.CommandText = SelectAllFromTableWhereStatus + " WHERE 1=1 ";
if (IsFiltered)
{
var where = new SqlWhereStatement(false);
where.AddWhere("PurchaseOrderID", cboxPurchaseOrderID.Text, true, txtPurchaseOrderID.Text.ToNullableInt32());
where.AddWhere("PurchaseOrderBranchID", WhereOperator.Equals, true, bbPurchaseOrderBranch.PrimaryKey);
where.AddWhere("PurchaseOrderDate", cboxPurchaseOrderDate.Text, true, ConvertHelper.ToNullableDateTime(datePurchaseOrderDate1.EditValue), ConvertHelper.ToNullableDateTime(datePurchaseOrderDate2.EditValue), DateTimePrecision.Days);
where.AddWhere("VendorID", cboxVendorID.Text, true, bbVendorID.PrimaryKey);
where.AddWhere("VendorName", cboxVendorName.Text, true, txtVendorName.Text.Trim());
where.AddWhere("SalesOrderID", cboxSalesOrderID.Text, true, txtSalesOrderID.Text.ToNullableInt32());
where.AddWhere("SalesOrderBranchID", WhereOperator.Equals, true, bbSalesOrderBranch.PrimaryKey);
//todo filter = FilterHelper.GetWhereClause("SalesOrderDate", cboxSalesOrderDate.Text, ConvertHelper.ToDateTime(dateSalesOrderDate1.EditValue), ConvertHelper.ToDateTime(dateSalesOrderDate2.EditValue), RoundingFormat.None, cmd);
//if (filter.Length > 0) cmd.CommandText += " AND " + filter;
//filter = FilterHelper.GetWhereClause("CustomerID", cboxCustomerID.Text, bbCustomerID.RecordValue);
//if (filter.Length > 0) cmd.CommandText += " AND " + filter;
//filter = FilterHelper.GetWhereClause("CustomerName", cboxCustomerName.Text, txtCustomerName.Text.Trim());
//if (filter.Length > 0) cmd.CommandText += " AND " + filter;
//filter = FilterHelper.GetWhereClause("ProductName", cboxProduct.Text, true, txtProduct.Text.Trim());
//if (filter.Length > 0) cmd.CommandText += " AND " + filter;
//filter = FilterHelper.GetWhereClause("ProductNumber", cboxProductNumber.Text, txtProductNumber.Text.Trim());
//if (filter.Length > 0) cmd.CommandText += " AND " + filter;
//filter = FilterHelper.GetWhereClause("ProductCurrencyCode", "=", lookCurrency.EditValue, SqlDbType.NChar, cmd);
//if (filter.Length > 0) cmd.CommandText += " AND " + filter;
cmd.CommandText += where;
}
cmd.CommandText += " ORDER BY SalesOrderDate DESC, SalesOrderID DESC";
ds.VW_SalesOrderDetailPivot.Clear();
ds.VW_SalesOrderDetailPivot.Merge(DictionaryManager.GetDataTable(cmd));
}
}
protected override void BindGrid()
{
using (var cmd = new SqlCommand())
{
cmd.CommandText = SelectAllFromTableWhereStatus;
if (IsFiltered)
{
var where = new SqlWhereStatement(false);
where.AddWhere("OrderDate", cboxDate.Text, true, ConvertHelper.ToDateTime(dateDate1.EditValue), ConvertHelper.ToDateTime(dateDate2.EditValue), DateTimePrecision.Days);
where.AddWhere("ActNumber", cboxActNumber.Text, true, txtActNumber.Text.Trim());
where.AddWhere("IssuedPerson", cboxIssuedPerson.Text, true, txtIssuedPerson.Text.Trim());
cmd.CommandText += where;
}
cmd.CommandText += " ORDER BY OrderDate DESC, ID DESC";
ds.VW_CashboxOrder.Clear();
ds.VW_CashboxOrder.Merge(DictionaryManager.GetDataTable(cmd));
}
}
protected override Task <QueryResult <TItem> > QueryAsync <TResult>(
SqlWhereStatement filter,
string[] selectColumns,
int skip,
int take,
CancellationToken cancellationToken,
string[] orderByColumns,
bool orderByAscending = true,
IDictionary <string, object> additionalFilterValues = null,
bool distinct = false)
{
if (additionalFilterValues == null)
{
additionalFilterValues = MapKeyColumnValues;
}
else
{
additionalFilterValues = additionalFilterValues
.Union(MapKeyColumnValues)
.ToDictionary(k => k.Key, v => v.Value);
}
return(base.QueryAsync <TResult>(filter, selectColumns, skip, take, cancellationToken, orderByColumns, orderByAscending, additionalFilterValues, distinct));
}
/// <summary>
/// 计算总记录数
/// </summary>
/// <returns>记录总数</returns>
private int Calc()
{
int RecordCount = 0;
string sqlStatement = string.Empty;
if (string.IsNullOrEmpty(SqlWhereStatement.Trim()))
{
sqlStatement = "select count(*) as rowsCount from " + TableName;
}
else
{
sqlStatement = "select count(*) as rowsCount from " + TableName + " where " + SqlWhereStatement;
}
IDataReader dr = DbFactoryProvider.GetProvider(this.pageDbType, this.pageConnstring).ExecuteReader(sqlStatement);;
if (dr.Read())
{
RecordCount = Int32.Parse(dr["rowsCount"].ToString());
}
dr.Close();
dr.Dispose();
return(RecordCount);
}
private DataTable TDataBind()
{
//读取页码值保存到currentPage变量中进行按钮失效运算
currentPage = pageIndex;
//读取总页参数进行按钮失效运算
pages = pageCounts;
//判断四个按钮（首页、上一页、下一页、尾页）状态
if (currentPage + 1 > 1)
{
btnFirstPage.Enabled = true;
btnPrevPage.Enabled = true;
}
else
{
btnFirstPage.Enabled = false;
btnPrevPage.Enabled = false;
}
if (currentPage == pages)
{
btnNextPage.Enabled = false;
btnLastPage.Enabled = false;
}
else
{
btnNextPage.Enabled = true;
btnLastPage.Enabled = true;
}
//核心SQL语句，进行查询运算（决定了分页的效率:)）
StringBuilder sbSqlStatement = new StringBuilder();
switch (this.pageDbType)
{
case CurrentDbType.SqlServer:
case CurrentDbType.DB2:
if (string.IsNullOrEmpty(SqlWhereStatement.Trim()))
{
sbSqlStatement.Append("SELECT TOP " + PageSize + " " + QueryFieldList + " FROM " + TableName + " WHERE " + PrimaryKey + " NOT IN(SELECT TOP ");
sbSqlStatement.Append(PageSize * currentPage + " " + PrimaryKey + " FROM " + TableName);
sbSqlStatement.Append(" ORDER BY " + OrderField + " DESC) ORDER BY " + OrderField + " DESC");
}
else
{
sbSqlStatement.Append("SELECT TOP " + PageSize + " " + QueryFieldList + " FROM " + TableName + " WHERE " + SqlWhereStatement + " AND " + PrimaryKey + " NOT IN(SELECT TOP ");
sbSqlStatement.Append(PageSize * currentPage + " " + PrimaryKey + " FROM " + TableName + " WHERE " + SqlWhereStatement + " ORDER BY " + OrderField + " DESC) ORDER BY " + OrderField + " DESC");
}
break;
case CurrentDbType.Oracle:
string sqlCount = (this.PageIndex * this.PageSize).ToString();
string sqlStart = ((this.PageIndex - 1) * this.PageSize).ToString();
if (!string.IsNullOrEmpty(SqlWhereStatement))
{
SqlWhereStatement = "WHERE " + SqlWhereStatement;
}
sbSqlStatement.Append(string.Format("SELECT * FROM (SELECT T.*, ROWNUM RN FROM (SELECT * FROM {0} {1} ORDER BY {2}) T WHERE ROWNUM <= {3}) WHERE RN > {4}"
, TableName, SqlWhereStatement, OrderField, sqlCount, sqlStart));
break;
case CurrentDbType.Access:
sqlStart = ((this.PageIndex - 1) * this.PageSize).ToString();
sbSqlStatement.Append(string.Format("SELECT * FROM (SELECT TOP {0} * FROM (SELECT TOP {1} * FROM {2} T ORDER BY {3} ) T1 ORDER BY {4} DESC ) T2 ORDER BY {5} "
, PageSize, sqlStart, TableName, OrderField, OrderField, OrderField));
break;
}
_pageTable = DbFactoryProvider.GetProvider(this.pageDbType, this.pageConnstring).Fill(sbSqlStatement.ToString());
//显示Label控件LcurrentPaget和文本框控件gotoPage状态
lblCurrentPage.Text = (currentPage + 1).ToString();
txtGoToPage.Text = (currentPage + 1).ToString();
return(_pageTable);
}
