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