Custom Paging GridView in ASP.NET & Oracle.

• Download source - 48.33 KB

Introduction

This article is about Custom Gridview and it also covers some basic features of gridview.

1. Custom Paging: Gridview paging is available with this control but default paging retrieves all data from the database and shows it page-wise. Custom paging loads only that data which will be displayed on a page and hence improves performance.
2. Custom Sorting: Normally sorting is applied only for that data which is shown in grid but here data is first sorted and then shown to user.
3. Custom Export: Normally export grid features will export only that data which is shown in grid but here complete data is exported into Excel.
4. Freeze Header Gridview: Header is frozen for both horizontal and vertical scrollbar.
5. Dynamic Column Resizing: Column can be re-sized dynamically by Mouse Drag event.
6. Row Hover and Selection: Gridview Row background color changes on mouse hover and mouse click event, it improves Gridview GUI.
7. Row Cell Tooltip: Tooltip is shown on Grid cell Mouse Hover. This feature is useful when cell text is larger than cell width so here only small text with "..." is shown and complete text is visible in tooltip.

Normal Gridview

Gridview is used to show data in a tabular form with theme and CSS. It works fine for small amounts of data but when volume of data is high then performance gets reduced in loading and rendering the data. To overcome rendering issue, default paging is a good option.

Default Paging

Default paging is implemented by setting a property of Gridview (AllowPaging = "true"). Default page size is 10 and default page index is 0. Also, one event is required to implement default paging, i.e. OnPageIndexChanging = "grdView_PageIndexChanging". So we have to bind whole data on page index changing event. Here we improve some performance by fixing rendering issue because only page size data renders on page but loading whole data is also a big issue for huge amount of data. To overcome this issue, custom paging comes into the picture.

protected void grdView_PageIndexChanging(object sender,
                                           GridViewPageEventArgs e)
{
     grdView.PageIndex = e.NewPageIndex;
     pBindData(null);
} 

Custom Paging

If the volume of data is high, then the thought should be to load only that data which is to be displayed on a page rather than loading all the data. Simple stored procedure will not be the solution in Oracle. For that, an Oracle package is implemented which will retrieve data from the database according to the first and the last index. To run custom Paging, you need Oracle database. Create Oracle package as shown below and Set connection string on CustomGridView Page.

The Custom paging feature of this article is based on GridView Custom Paging.

But the challenge was to apply custom paging with Oracle database and this is the prime reason to publish this article.

Oracle Package to Retrieve Data from Database

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# Code to Bind Data to Grid

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

Custom Sorting

Custom sorting is different than traditional sorting in the way that it sorts not only grid data but it sorts whole data and then displays the paged data into grid. To do this task, sort expression is passed into Oracle package and then data is sorted according to sort expression using decode method. I know that applying decode method for each column is not a good way, but I have no other option to do this task.

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

Custom Export

By default, the current page of the Gridview is exported but here whole data is exported into Excel. To do this task, we simply retrieve whole data from database and export it without rendering the data into gridview. In case of Default Paging, only current page data with paging is exported directly. To fix this issue, we first set gridview Allowpaging property to false and then export the data.

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

Freeze Header Gridview

Freeze Header Gridview is implemented using CSS.

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

Dynamic Column Resizing

Dynamic Column Resizing is implemented using JS. I have found one good JS from Matt Berseth Article.

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

Row Hover, Selection and Tooltip

Row Hover is implemented using CSS.

.GridRowHover
{
	background-color: #FFFFE1;
	cursor: pointer;
} 

Row Selection is implemented using 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 Tootip is implemented using "title" property of Grid Cell.

Above JS, CSS and Title Property is called on Gridview RowDataBound Event.

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

Limitations

1. Decode method is used for each column for both ASC and DESC condition to implement custom sorting.
2. Freeze Header Gridview will work on Internet Explorer only.

References

• Custom Paging in DataGrid
• GridView Custom Paging
• Selecting the first n rows with Oracle
• Creating GridView with Resizable Column Header

History

• Version 1.0.0.0 is the initial version that includes ASP.NET Gridview with Default and Custom Paging, Sorting, Export Data, Freeze Header, Column Resizing, Row Hover, Row Selection, Grid Cell Tooltip features for better Performance, functionality and GUI.