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