ASP.NET & Oracle 中自定义分页 GridView。
4.92/5 (10投票s)
ASP.NET Gridview 具备默认和自定义分页、排序、导出数据、冻结标题行、列宽调整、行悬停、行选择、单元格工具提示等功能，以提高性能、功能性和 GUI 效果，并支持 Oracle 数据库。
引言
本文主要介绍自定义 Gridview ，同时也涵盖了 gridview 的一些基本功能。
- 自定义分页：
Gridview自带分页功能，但默认分页会从数据库检索所有数据并按页显示。自定义分页则只加载将要显示在页面上的数据，从而提高性能。 - 自定义排序：通常排序只应用于显示在网格中的数据，而这里的数据会先被排序，然后才呈现给用户。
- 自定义导出：通常导出网格的功能只导出显示在网格中的数据，而这里会将所有数据导出到 Excel。
- 冻结标题行
Gridview：标题行在水平和垂直滚动条下都会被冻结。 - 动态列宽调整：可以通过鼠标拖动事件动态调整列宽。
- 行悬停和选择：鼠标悬停和点击事件发生时，
Gridview行的背景色会发生变化，这改善了Gridview的 GUI 效果。 - 单元格工具提示：鼠标悬停在网格单元格时会显示工具提示。当单元格文本超出单元格宽度时，此功能非常有用，此时只显示带有“...”的简短文本，完整文本将在工具提示中显示。
普通 Gridview
Gridview 用于以表格形式显示数据，并带有主题和 CSS。对于少量数据，它工作得很好，但当数据量很大时，加载和渲染数据的性能会下降。为了克服渲染问题，默认分页是一个不错的选择。
默认分页
默认分页通过设置 Gridview 的属性 (AllowPaging = "true") 来实现。默认页大小为 10 ，默认页索引为 0。此外，实现默认分页还需要一个事件，即 OnPageIndexChanging = "grdView_PageIndexChanging"。因此，我们必须在页索引更改事件中绑定所有数据。此处通过修复渲染问题来提高一些性能，因为只有页面大小的数据会在页面上渲染，但加载所有数据对于大量数据来说也是一个大问题。为了解决这个问题，自定义分页就派上用场了。
protected void grdView_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
grdView.PageIndex = e.NewPageIndex;
pBindData(null);
}
自定义分页
如果数据量很大，那么应该考虑只加载要在页面上显示的数据，而不是加载所有数据。简单的存储过程在 Oracle 中不是解决方案。为此，需要实现一个 Oracle 包，该包将根据第一个和最后一个索引从数据库检索数据。要运行自定义分页，您需要 Oracle 数据库。按以下方式创建 Oracle 包，并在 CustomGridView 页面上设置连接字符串。
本文的自定义分页功能基于 GridView Custom Paging。
但是挑战在于如何将自定义分页应用于 Oracle 数据库，这也是 **发布本文的主要原因**。
从数据库检索数据的 Oracle 包
CREATE OR REPLACE PACKAGE SUPERVISOR.PKG_GetArea as
TYPE MyRefCur is REF CURSOR;
procedure GetArea(inStartRowIndex in number, inEndRowIndex
in number, inSortExp in varchar2, outTotalRows out number,
outAreaCur OUT MyRefCur);
END;
/
CREATE OR REPLACE PACKAGE BODY SUPERVISOR.PKG_GetArea as
PROCEDURE GetArea(inStartRowIndex in number, inEndRowIndex
in number, inSortExp in varchar2, outTotalRows out number,
outAreaCur OUT MyRefCur)
IS
BEGIN
select count(*) into outTotalRows from tb_area_mst;
if(inEndRowIndex = -1) then
open outAreaCur for select AREA_ID, AREA_NAME, AREA_CODE,
LEVEL_ID, PARENT_AREA_ID, ADDRESS, PHONE_NO,CONTACT_PERSON,STATUS,
AREATREECODE from TB_AREA_MST order by Area_ID;
else
begin
open outAreaCur for select AREA_ID, AREA_NAME, AREA_CODE,
LEVEL_ID, PARENT_AREA_ID, ADDRESS, PHONE_NO,CONTACT_PERSON,
STATUS,AREATREECODE from (select AREA_ID, AREA_NAME, AREA_CODE,
LEVEL_ID, PARENT_AREA_ID, ADDRESS,
PHONE_NO,CONTACT_PERSON,STATUS,AREATREECODE, ROW_NUMBER()
OVER
(
ORDER BY
Decode(inSortExp,'AREA_ID ASC',AREA_ID) ASC,
Decode(inSortExp,'AREA_ID DESC',AREA_ID) DESC,
Decode(inSortExp,'AREA_NAME ASC',AREA_NAME) ASC,
Decode(inSortExp,'AREA_NAME DESC',AREA_NAME) DESC,
Decode(inSortExp,'AREA_CODE ASC',AREA_CODE) ASC,
Decode(inSortExp,'AREA_CODE DESC',AREA_CODE) DESC,
Decode(inSortExp,'LEVEL_ID ASC',LEVEL_ID) ASC,
Decode(inSortExp,'LEVEL_ID DESC',LEVEL_ID) DESC,
Decode(inSortExp,'PARENT_AREA_ID ASC',PARENT_AREA_ID) ASC,
Decode(inSortExp,'PARENT_AREA_ID DESC',PARENT_AREA_ID) DESC,
Decode(inSortExp,'ADDRESS ASC',ADDRESS) ASC,
Decode(inSortExp,'ADDRESS DESC',ADDRESS) DESC,
Decode(inSortExp,'PHONE_NO ASC',PHONE_NO) ASC,
Decode(inSortExp,'PHONE_NO DESC',PHONE_NO) DESC,
Decode(inSortExp,'CONTACT_PERSON ASC',CONTACT_PERSON) ASC,
Decode(inSortExp,'CONTACT_PERSON DESC',CONTACT_PERSON) DESC,
Decode(inSortExp,'STATUS ASC',STATUS) ASC,
Decode(inSortExp,'STATUS DESC',STATUS) DESC,
Decode(inSortExp,'AREATREECODE ASC',AREATREECODE) ASC,
Decode(inSortExp,'AREATREECODE DESC',AREATREECODE) DESC,
AREA_ID
)
R FROM TB_AREA_MST)
WHERE R BETWEEN inStartRowIndex AND inEndRowIndex;
end;
End if;
END;
END;
/
用于将数据绑定到网格的 C# 代码
private void pBindData(string aSortExp, bool aIsCompleteData)
{
OracleConnection objCon = null;
OracleCommand objCmd = null;
DataSet ds = null;
OracleDataAdapter objAdp = null;
try
{
//Connect to Database
objCon = new OracleConnection("Data Source=ABC;
User ID=SUPERVISOR;Password=XYZ");
objCon.Open();
//Command Object
objCmd = new OracleCommand("PKG_GetArea.GetArea",
objCon);
//Stored Procedure
objCmd.CommandType = CommandType.StoredProcedure;
//Create Parameter Object
objCmd.Parameters.Add(new OracleParameter
("inStartRowIndex", OracleDbType.Int32)).Direction
= ParameterDirection.Input;
objCmd.Parameters["inStartRowIndex"].Value =
((currentPageNumber - 1) * PAGE_SIZE) + 1;
objCmd.Parameters.Add(new OracleParameter
("inEndRowIndex", OracleDbType.Int32)).Direction =
ParameterDirection.Input;
if (aIsCompleteData)
{
objCmd.Parameters["inEndRowIndex"].Value = -1;
}
else
{
objCmd.Parameters["inEndRowIndex"].Value =
(currentPageNumber * PAGE_SIZE);
}
objCmd.Parameters.Add(new OracleParameter
("inSortExp", OracleDbType.Varchar2)).Direction =
ParameterDirection.Input;
objCmd.Parameters["inSortExp"].Value = aSortExp;
objCmd.Parameters.Add(new OracleParameter
("outTotalRows", OracleDbType.Int32)).Direction =
ParameterDirection.Output;
objCmd.Parameters.Add(new OracleParameter
("AreaCur", OracleDbType.RefCursor)).Direction =
ParameterDirection.Output;
///Instantiate Dataset
ds = new DataSet();
///Instantiate Data Adopter
objAdp = new OracleDataAdapter(objCmd);
///Fill Data Set
objAdp.Fill(ds);
///Bind Data to Grids
grdView.DataSource = ds.Tables["Table"];
grdView.DataBind();
ViewState["ReportTime"] = DateTime.Now;
///get the total rows
double totalRows = (int)objCmd.
Parameters["outTotalRows"].Value;
lblTotalPages.Text = GetTotalPages(totalRows)
.ToString();
ddlPage.Items.Clear();
for (int i = 1; i < Convert.ToInt32
(lblTotalPages.Text) + 1; i++)
{
ddlPage.Items.Add(new ListItem(i.ToString()));
}
ddlPage.SelectedValue = currentPageNumber.ToString();
if (currentPageNumber == 1)
{
lnkbtnPre.Enabled = false;
lnkbtnPre.CssClass = "GridPagePreviousInactive";
lnkbtnFirst.Enabled = false;
lnkbtnFirst.CssClass = "GridPageFirstInactive";
if (Int32.Parse(lblTotalPages.Text) > 0)
{
lnkbtnNext.Enabled = true;
lnkbtnNext.CssClass = "GridPageNextActive";
lnkbtnLast.Enabled = true;
lnkbtnLast.CssClass = "GridPageLastActive";
}
else
{
lnkbtnNext.Enabled = false;
lnkbtnNext.CssClass = "GridPageNextInactive";
lnkbtnLast.Enabled = false;
lnkbtnLast.CssClass = "GridPageLastInactive";
}
}
else
{
lnkbtnPre.Enabled = true;
lnkbtnPre.CssClass = "GridPagePreviousActive";
lnkbtnFirst.Enabled = true;
lnkbtnFirst.CssClass = "GridPageFirstActive";
if (currentPageNumber == Int32.Parse
(lblTotalPages.Text))
{
lnkbtnNext.Enabled = false;
lnkbtnNext.CssClass = "GridPageNextInactive";
lnkbtnLast.Enabled = false;
lnkbtnLast.CssClass = "GridPageLastInactive";
}
else
{
lnkbtnNext.Enabled = true;
lnkbtnNext.CssClass = "GridPageNextActive";
lnkbtnLast.Enabled = true;
lnkbtnLast.CssClass = "GridPageLastActive";
}
}
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
finally
{
if (objCmd != null)
{
objCmd.Dispose();
}
if (objAdp != null)
{
objAdp.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if ((objCon != null) && (objCon.State ==
ConnectionState.Open))
{
objCon.Close();
objCon.Dispose();
}
objCmd = null;
objAdp = null;
ds = null;
objCon = null;
}
}
自定义排序
自定义排序与传统排序不同之处在于，它不仅对网格数据进行排序，还会对所有数据进行排序，然后将分页后的数据显示在网格中。为了完成此任务，排序表达式会传递到 Oracle 包中，然后使用 decode 方法根据排序表达式对数据进行排序。我知道对每列使用 decode 方法不是一个好方法，但我没有其他办法来完成这项任务。
protected void grdView_Sorting(object sender, GridViewSortEventArgs e)
{
if (string.Compare(Convert.ToString(ViewState["SortOrder"]),
" ASC", true) == 0)
{
ViewState["SortOrder"] = " DESC";
}
else
{
ViewState["SortOrder"] = " ASC";
}
pBindData(e.SortExpression + ViewState["SortOrder"], false);
}
自定义导出
默认情况下，会导出 Gridview 的当前页面，但此处会将所有数据导出到 Excel。为了完成此任务，我们只需从数据库检索所有数据，并在不将数据渲染到 gridview 的情况下导出它。对于 **默认分页**，只会直接导出带分页的当前页面数据。为了解决这个问题，我们首先将 gridview Allowpaging 属性设置为 false ，然后导出数据。
protected void lnkbtnExport_Click(object sender, EventArgs e)
{
if (grdView.Rows.Count > 0)
{
grdView.AllowPaging = false;
pBindData(null);
///export to excel
pExportGridToExcel(grdView, "CustomGridView_"
+ Convert.ToString(ViewState["ReportTime"]) + ".xls");
}
}
private void pExportGridToExcel(GridView grdGridView,
String fileName)
{
Response.Clear();
Response.AddHeader("content-disposition",
String.Format("attachment;filename={0}", fileName));
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite =
new HtmlTextWriter(stringWrite);
ClearControls(grdCustom);
grdGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString().
Replace(HttpUtility.HtmlDecode(" "), " "));
Response.End();
}
冻结标题行 Gridview
使用 CSS 实现冻结标题行 Gridview 。
.divGrid
{
border-style: solid;
border-width: 1px;
border-color: #4d4d4d;
overflow:auto;
}
.CustomGrid
{
table-layout: fixed;
width:902px;
cursor: pointer;
}
.GridRowSelect
{
background-color: #4B4B4B;
cursor: pointer;
}
.GridRowHover
{
background-color: #FFFFE1;
cursor: pointer;
}
.GridHeader
{
margin: 0px;
border: 1px solid #4D4D4D;
background-position: left top;
font-family: Tahoma;
letter-spacing:1pt;
font-size: 8pt;
text-decoration: none;
font-weight: normal;
font-style: normal;
font-variant: normal;
text-transform: none;
color: #E5E5E5;
text-align: center;
vertical-align: middle;
padding: 3px 6px 3px 6px;
background-image: url('Images/Header_Grid.png' );
background-repeat: repeat-x;
background-color:#4B4B4B;
word-wrap: break-word;
text-overflow:ellipsis;
overflow:hidden;
white-space: -moz-pre-wrap !important;
}
.GridHeader A
{
font-family: Tahoma;
letter-spacing:1pt;
font-size: 8pt;
text-decoration: none;
font-weight: normal;
font-style: normal;
font-variant: normal;
text-transform: none;
color: #E5E5E5;
text-align: center;
vertical-align: middle;
}
.GridLine
{
border: .25px solid #4D4D4D;
}
.GridItem1
{
border: 1px solid #4D4D4D;
text-align: left;
vertical-align: middle;
padding: 4px 6px 4px 6px;
font-family: Verdana;
font-size: 8pt;
color: #000000;
font-weight: normal;
font-style: normal;
font-variant: normal;
text-transform: none;
word-wrap: break-word;
text-overflow:ellipsis;
overflow:hidden;
white-space:nowrap;
}
div#customGridDiv
{
overflow: scroll;
position: relative;
}
div#customGridDiv th
{
top: expression(document.getElementById("customGridDiv")
.scrollTop-2);
left: expression(parentNode.parentNode.parentNode.
parentNode.scrollLeft);
position: relative;
z-index: 20;
}
动态列宽调整
使用 JS 实现动态列宽调整。我从 Matt Berseth 的文章 中找到了一个不错的 JS。
// true when a header is currently being resized
var _isResizing;
// a reference to the header column that is being resized
var _element;
// an array of all of the tables header cells
var _ths;
function pageLoad(args){
// get all of the th elements from the gridview
_ths = $get('grdCustom').getElementsByTagName('TH');
// if the grid has at least one th element
if(_ths.length > 1){
for(i = 0; i < _ths.length; i++){
// determine the widths
_ths[i].style.width = Sys.UI.DomElement
.getBounds(_ths[i]).width + 'px';
// attach the mousemove and mousedown events
if(i < _ths.length - 1){
$addHandler(_ths[i], 'mousemove', _onMouseMove);
$addHandler(_ths[i], 'mousedown', _onMouseDown);
}
}
// add a global mouseup handler
$addHandler(document, 'mouseup', _onMouseUp);
// add a global selectstart handler
$addHandler(document, 'selectstart', _onSelectStart);
}
}
function _onMouseMove(args){
if(_isResizing){
// determine the new width of the header
var bounds = Sys.UI.DomElement.getBounds(_element);
var width = args.clientX - bounds.x;
// we set the minimum width to 1 px, so make
// sure it is at least this before bothering to
// calculate the new width
if(width > 1){
// get the next th element so we can adjust
//its size as well
var nextColumn = _element.nextSibling;
var nextColumnWidth;
if(width < _toNumber(_element.style.width)){
// make the next column bigger
nextColumnWidth = _toNumber(nextColumn.style.width) +
_toNumber(_element.style.width) - width;
}
else if(width > _toNumber(_element.style.width)){
// make the next column smaller
nextColumnWidth = _toNumber(nextColumn.style.width) -
(width - _toNumber(_element.style.width));
}
// we also don't want to shrink this width to
// less than one pixel,
// so make sure of this before resizing ...
if(nextColumnWidth > 1){
_element.style.width = width + 'px';
nextColumn.style.width = nextColumnWidth + 'px';
}
}
}
else{
// get the bounds of the element. If the mouse cursor
//is within 2px of the border, display the e-cursor
//-> cursor:e-resize
var bounds = Sys.UI.DomElement.getBounds(args.target);
if(Math.abs((bounds.x + bounds.width) - (args.clientX))
<= 2) {
args.target.style.cursor = 'e-resize';
}
else{
args.target.style.cursor = '';
}
}
}
function _onMouseDown(args){
// if the user clicks the mouse button while
// the cursor is in the resize position, it means
// they want to start resizing. Set _isResizing to true
// and grab the th element that is being resized
if(args.target.style.cursor == 'e-resize') {
_isResizing = true;
_element = args.target;
}
}
function _onMouseUp(args){
// the user let go of the mouse - so
// they are done resizing the header. Reset
// everything back
if(_isResizing){
// set back to default values
_isResizing = false;
_element = null;
// make sure the cursor is set back to default
for(i = 0; i < _ths.length; i++){
_ths[i].style.cursor = '';
}
}
}
function _onSelectStart(args){
// Don't allow selection during drag
if(_isResizing){
args.preventDefault();
return false;
}
}
function _toNumber(m) {
// helper function to peel the px off of the widths
return new Number(m.replace('px', ''));
}
行悬停、选择和工具提示
使用 CSS 实现行悬停。
.GridRowHover
{
background-color: #FFFFE1;
cursor: pointer;
}
使用 JS 实现行选择。
var SelectedRow = null;
var SelectedRowIndex = null;
var UpperBound = null;
var LowerBound = null;
window.onload = function()
{
UpperBound = parseInt('<%# PAGE_SIZE %>') - 1;
LowerBound = 0;
SelectedRowIndex = -1;
}
function SelectRow(CurrentRow, RowIndex)
{
if(SelectedRow == CurrentRow || RowIndex > UpperBound ||
RowIndex < LowerBound) return;
if(SelectedRow != null)
{
SelectedRow.style.backgroundColor =
SelectedRow.originalBackgroundColor;
SelectedRow.style.color = SelectedRow.originalForeColor;
}
if(CurrentRow != null)
{
CurrentRow.originalBackgroundColor =
CurrentRow.style.backgroundColor;
CurrentRow.originalForeColor = CurrentRow.style.color;
CurrentRow.style.backgroundColor = '#FFFF00';
CurrentRow.style.color = 'Black';
}
SelectedRow = CurrentRow;
SelectedRowIndex = RowIndex;
setTimeout("SelectedRow.focus();",0);
}
function SelectSibling(e)
{
var e = e ? e : window.event;
var KeyCode = e.which ? e.which : e.keyCode;
if(KeyCode == 40)
SelectRow(SelectedRow.nextSibling, SelectedRowIndex + 1);
else if(KeyCode == 38)
SelectRow(SelectedRow.previousSibling, SelectedRowIndex - 1);
return false;
}
使用 Grid Cell 的 "title" 属性实现 Grid 单元格工具提示。
上述 JS、CSS 和 Title 属性在 Gridview RowDataBound 事件中被调用。
protected void grdView_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
for (int i = 0; i < e.Row.Cells.Count; i++)
{
e.Row.Cells[i].CssClass = "GridItem1";
if (e.Row.Cells[i].Text.Trim() != " ")
{
e.Row.Cells[i].Attributes.Add("title", e.Row.Cells[i].Text);
}
}
e.Row.Attributes.Add
("onmouseover", "javascript:this.className = 'GridRowHover'");
e.Row.Attributes.Add("onmouseout", "javascript:this.className = ''");
e.Row.TabIndex = -1;
e.Row.Attributes["onclick"] = string.Format("javascript:SelectRow(this, {0});",
e.Row.RowIndex);
e.Row.Attributes["onkeydown"] = "javascript:return SelectSibling(event);";
e.Row.Attributes["onselectstart"] = "javascript:return false;";
}
}
限制
- 对 ASC 和 DESC 条件下的每一列都使用了 Decode 方法来实施自定义排序。
- 冻结标题行
Gridview仅在 Internet Explorer 中有效。
参考文献
历史
- 1.0.0.0 版本是初始版本，其中包括 ASP.NET
Gridview，具备默认和自定义分页、排序、导出数据、冻结标题行、列宽调整、行悬停、行选择、单元格工具提示等功能，以提高性能、功能性和 GUI 效果。