Click here to Skip to main content
15,867,765 members
Articles / Web Development / ASP.NET

Custom Paging GridView in ASP.NET & Oracle.

Rate me:
Please Sign up or sign in to vote.
4.92/5 (11 votes)
1 Dec 2009CPOL4 min read 86.3K   2.9K   37   12
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 with Oracle Database
Image 1

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.

C#
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

SQL
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

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

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.

C#
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.

C#
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");
       }
}
C#
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.

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.

JavaScript
//  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.

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

Row Selection is implemented using JS.

JavaScript
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.

C#
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() != "&nbsp;")
             {
                 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

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Secure Meters Ltd.
India India
• A competent, ambitious & result oriented professional with Master degree with 12+ years of functional expertise in engineering of application software development.
• Expert in System architecture and software architecture design of high performance, high security, high availability of n-tire systems.
• Expert in web-based, n-tier and service based architecture.
• Specialized in web application level security.
• Expert in requirement analysis, designing, developing and implementing web, desktop, service & Android based mobile application.
• Proficient in communication protocols like TCP/IP, HTTP/HTTPS, SMTP, FTP/SFTP, SOAP, REST, BLE, DLMS, MQTT, Z-Wave.
• Excellent experience in process-driven software development with SDLC, SEI CMMI process and Agile methodologies.
• Strong experience in UML tool such as Enterprise Architecture.
• Solid experience in Software design & development.
• Delivered more than 7 products from concept to realization that caters to Metering, Energy monitoring, automated vehicle parking, GPS based fleet monitoring and Assisted living domains.
• Led a team of 7 members towards completion of products with planned activities.
• Strategic thinker, decision maker and deft in continually monitoring the ways for improvement of team, organizational and individual development.
• Extensive experience in Project Management, Assisted Living/home applications, IoT and intranet & internet based technologies.
• Received many rewards & awards in project executions & issue resolutions.
• Solid Analytical and Interpretation skills.

Comments and Discussions

 
GeneralI don't know everbody else say.But I think this very very good article Pin
coskuncinar27-Jul-10 4:17
coskuncinar27-Jul-10 4:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.