How To Have Gridview Display All Data Or Queried Data Using Objectdatasource

Title:       Optimized Paging and Sorting          using          Object          Data Source Author:      Muhammad Akhtar Shiekh  E-mail:       akhhttar@gmail.com Member ID:          2243665          Linguistic communication:    C#          2.0          Platform:    Windows, .NET Applied science:  ASP.Internet Level:       Intermediate Clarification: An article on optimized paging and sorting          in          GridView. Department      ASP.NET SubSection   GridView License:     CPL

Download source - 340 Kb

Introduction

Paging and sorting are most ordinarily used features of ASP.Internet GridView. And it is very piece of cake to employ/implement these features in GridView with pocket-sized chunk of lines. In this article I am going to demonstrate what are the performance drawbacks of using conventional fashion to page and sort your GridView and then I will demonstrate 'An Optimized manner to implement Paging and Sorting'.

What are conventional steps for Paging and Sorting?

Usually we practice following steps to enable paging and sorting in our GridView,

Set AllowPaging and AllowSorting Properties of GridView to Truthful to enable paging and sorting respectively east.chiliad

              <              asp:GridView                                          ID              ="              GridView1"                                          runat              ="              server"                                          AllowPaging              ="              true"                                          AllowSorting              ="              true"                                          >              <              /asp:GridView              >

Ready PageSize property to mention how many records volition be display on each page.
Set up SortExpression belongings of each cavalcade. By default each DataBound columns has bounded column name as default value for SortExpression property.

Handle PageIndexChanging and Sorting Events of GridView to respond Paging and sorting actions respectively due east.chiliad

              <              asp:GridView                                          ID              ="              GridView1"                                          runat              ="              server"                                          AllowPaging              ="              true"                                          AllowSorting              ="              true"                                          onpageindexchanging              ="              GridView1_PageIndexChanging"                                          onsorting              ="              GridView1_Sorting"                                          >              <              /asp:GridView              >

              protected              void              GridView1_PageIndexChanging(object              sender, GridViewPageEventArgs eastward) {   }              protected              void              GridView1_Sorting(object              sender, GridViewSortEventArgs e) {  }

Put Some Logic in Event Handler to do their jobs

a. In PageIndexChanging Event Handler method, we usually go the data from database or somewhere from Cache and rebind our Grid with that data. And after rebinding nosotros change PageIndex belongings of GridView to new page index to display the page that was selected by user.

              protected              void              GridView1_PageIndexChanging(object              sender, GridViewPageEventArgs e) {     GridView1.DataSource = GetData();      GridView1.DataBind();      GridView1.PageIndex = e.NewPageIndex; }

b. In Sorting Issue Handler method, we get the sorted data co-ordinate to the sort expression from our data source (data source could exist database/enshroud/session etc) and and so rebind the Grid to brandish sorted records.

              protected              void              GridView1_Sorting(object              sender, GridViewSortEventArgs e) {     GridView1.DataSource = GetData(due east.SortExpression);       GridView1.DataBind(); }

And that'southward information technology.

Drawbacks

In conventional mode of paging and sorting we become consummate set up of data instead of getting function of data that is required to display on current/requested folio. As you lot can see that on each pageIndexChanging phone call, we are getting all the data from our data source and then bind information technology to GridView. Ideally we should get merely that data that is required to brandish on requested folio.

Hmmm…Sounds good merely HOW??

The question that may arise in your mind could be "Information technology seems expert in theory that we should just get required data, just practically if we bind only one page data with GridView then it would assume that this is the but information that it needs to display, so how GridView even display page numbers and total records count? It is a genuine question, and then let's attempt to answer this question!!!

An Optimized Mode of Paging and Sorting

Equally in the starting time of this article, we discuss conventional v steps to implement paging and sorting in ASP.NET GridView. In this purposed solution we will be using first three steps As It IS and instead of performing quaternary and 5th steps by our self, we will utilize ObjectDataSource that will perform these steps for us in optimized style.

High Level Overview

Nosotros will optimize the lawmaking on both Database and Presentation layer

At the Database Level we volition write a stored process in such a fashion that it would return only one page records. That stored procedure take Page Size, Folio Index and Sort Expression as input parameters and render sorted record for particular folio index.

At the Presentation layer, we will utilise ObjectDataSource'southward virtual paging feature to optimize the paging. Virtual paging is non a term defined past Microsoft, I used it by myself because ObjectDataSource expose some properties and method that let us to bind but one page data with GridView and to ascertain full number of records in database (not in one page), so that GridView can extract out total number of pages that needs to be display in folio surface area of GridView. In the adjacent sections we will see what these properties and methods are? and how to use them?

If y'all are non familiar with ObjectDataSource then I would recommend that you should get-go read some commodity on that. Here are some articles

http://www.eggheadcafe.com/tutorials/aspnet/884994b3-0ec7-4e60-9a8f-cbf9127ef50b/nuts-objectdatasource.aspx
http://world wide web.c-sharpcorner.com/UploadFile/mahesh/ObjectDataSource08192005140915PM/ObjectDataSource.aspx

Implementation Details:

Database Layer:

We have an employee table in database with following schema

and we wrote a following stored process that has two select statements. Beginning select argument will return the full number of employee in Employee table and second dynamic select statement volition return i page sorted record according to the provided get-go index, page size, and sortby by parameters

          Create          Procedure          spGetAllEmployee 	(          @startIndex          int,          @pageSize          int,          @sortBy          nvarchar(30),          @totalEmployees          int          OUTPUT          )          Equally          SET          NOCOUNT          ON          DECLARE          @sqlStatement          nvarchar(max),          @upperBound          int          IF          @startIndex          <          1          SET          @startIndex          =          1          IF          @pageSize          <          one          SET          @pageSize          =          1          SET          @upperBound          =          @startIndex          +          @pageSize          Select          @totalEmployees=Count(*)          From          Employee          SET          @sqlStatement          =          '                      SELECT E.EmployeeID, Due east.EmployeeCode, Due east.Name, E.Department, East.Salary                 FROM (                       SELECT  ROW_NUMBER() OVER(ORDER BY '          +          @sortBy          +          '          ) As rowNumber, *                       FROM    Employee                      ) Equally Due east                 WHERE  rowNumber >= '          +          Convert(varchar(9),          @startIndex) +          '                      AND                        rowNumber <  '          +          CONVERT(varchar(9),          @upperBound)          exec          (@sqlStatement)

One affair that i want to explicate in above stored procedure is ROW_NUMBER() part that arrive possible for u.s.a. to select only one folio information. ROW_NUMBER() method is included in 2005 release of TSQL, it actually add together an integer cavalcade in selected tape ready, that contain the tape number for each record . It seems very simple simply infact it very helpful while we are going to perform nested queries. As we did in our stored procedure, in nested query we select all employees record sorted past the provided sort expression and add together a row number for each record using ROW_NUMBER() method, and in outer query nosotros filter the resulted rows by using lower and upper bound indexes so that we return only those rows which lies in lower and upper bounds.

Data Access layer:

In Data Access Layer we will write a grade that will be responsible to phone call spGetAllEmployee stored procedure to get employee records and return employee list to the business logic layer. To avoid the complexity and length of commodity i am only posting the code is used to fetch the records from database, I am non posting whatever helper code/classes, however consummate lawmaking is bachelor for download .

          public          Listing<EmployeeInfo> GetAllEmployee(int          startIndex,          int          pageSize,          string          sortBy,ref          int          totalEmployees)        {             IDbConnection connection=null          ;             IDbCommand selectCommand=null          ;             List<EmployeeInfo> employeeInfoList;             DataSet employeeDS =          new          DataSet();             IDbDataAdapter dataAdapter = DataObjectFactory.CreateDataAdapter();          try          {          using          (connection = DataObjectFactory.CreateConnectionObject())                 {          using          (selectCommand = DataObjectFactory.CreateStoredProcedureCommand(connexion,          "          spGetAllEmployee"))                     {                          selectCommand.Parameters.Add together(DataObjectFactory.CreateCommandParameter("          @startIndex", SqlDbType.Int, startIndex));                         selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("          @pageSize", SqlDbType.Int, pageSize));                         selectCommand.Parameters.Add together(DataObjectFactory.CreateCommandParameter("          @sortBy", SqlDbType.NVarChar,          30, sortBy));                         selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("          @totalEmployees", SqlDbType.Int,          0));                         ((SqlParameter)selectCommand.Parameters["          @totalEmployees"]).Direction = ParameterDirection.Output;          if          (connection.State != ConnectionState.Open)                             connection.Open();                          dataAdapter.SelectCommand = selectCommand;                          dataAdapter.Fill(employeeDS);                          totalEmployees = Catechumen.ToInt32(((SqlParameter)selectCommand.Parameters["          @totalEmployees"]).Value);                     }                 }             }          take hold of          (SqlException ex)             {          if          (connection.State != ConnectionState.Closed)                     connection.Close();                 DALException mineException =          new          DALException();                 mineException.ConnectingString = connection.ConnectionString;                 mineException.StoredProcedureName = selectCommand.CommandText;                 mineException.Source =          "          GetAllEmployee";          throw          mineException;                              }              employeeInfoList =  ConvertorUtility.ConvertToEmployeeInfoCollection(employeeDS);          return          employeeInfoList;                     }

Ane thing you might notice that last parameter totalEmployee of GetAllEmployee is by reference, information technology is because of the optimization, we don't want to perform ii seprate database calls to get employees records and full count. Considering of the same reason nosotros are returning both things (employee data and full count) from i stored process. In presentation layer information technology will be more clearer to you that what was the necessity of this approach and how information technology volition be helping us.

Business Logic Layer:

Business logic layer will only be calling Information Access layer code to fetch the records. Source lawmaking is available in download.

Presentation Layer:

As nosotros discusses earlier in the artcile that we will use the virtual paging feature of ObjectDataSource. Object Information Source exposes some interesting properties which is used for virtual paging purpose. To go benefits from these properties you should set up EnablePaging belongings of ObjectDataSource to True. These properties are

StartRowIndexParameterName: Specify the parameter name of Select method of ObjectDataSource's divisional Type. ObjectDataSource volition pass the value to this parameter when user changes the page alphabetize. For example, if Filigree View folio size is ten and user click page number 3 to view tertiary page so the ObjectDataSource will calculate the value of StartRowIndexParameter past using ( page Size * folio Alphabetize) formula , in this case it will be 10 * 3= xxx. Default value of this property is startRowIndex, which means if we don't mention whatever value for this belongings then Select method should take startRowIndex parameter.
MaximumRowsParameterName: Specify the parameter name of Select method of ObjectDataSurce'south bounded Type. ObjectDataSource will laissez passer the value to this parameter when user changes the page Index. ObjectDataSource source get its value from GridView's PageSize holding. Default value of this property is maximumRow. which means if we don't mention any value for this property and then Select method should have maximumRow parameter.
SelectCountMethod: Specify the method name of ObjectDataSource's Type. This method volition be called past ObjectDataSource to get the total number of records in database. This count will assistance ObjectDataSource to create virtual paging in the Grid. For case, if GridView page size is 10, and SelectCountMethod return 100 as total number of employees in database then ObjectDataSource will display ten page indexes in GridView page areas ( However we didn't get all 100 records from information base of operations)
SortParameterName: Specify the parameter name of Select method of ObjectDataSource's bounded Type. ObjectDataSource will pass the value to this parameter when user click on any column header to sort the data according to that cavalcade. ObjectDataSource fetch the value from SortExpression property of detail GirdView column

Now if you see all these three properties togather then yous will understand that SelectCountMethod volition use to display virtual page indexes in GridView and on each page index modify nosotros will apply values of StartRowIndexParameterName and MaximumRowsParameterName parameters to query database to fetch desired page data only.

As you know that nosotros already written stored procedures, data access and concern logic code which accpet these parameters and return the merely one page sorted data. So it's fourth dimension to use them past passing these parameters values to them.

It'due south enough in theory, at present let'due south talk something in C# and ASPX,

ASPX: Markup of GridView and ObjectDataSource

          <          asp:GridView                              ID          ="          GridView1"                              DataKeyNames          ="          EmployeeID"                              runat          ="          server"                              AllowPaging          ="          True"                              AutoGenerateColumns          ="          Imitation"                              CellPadding          ="          4"                              DataSourceID          ="          ObjectDataSource1"                              ForeColor          ="          #333333"                              GridLines          ="          None"                              AllowSorting          ="          True"                              PageSize          ="          5"                              >          <          RowStyle                              BackColor          ="          #EFF3FB"                              /          >          <          Columns          >          <          asp:BoundField                              DataField          ="          EmployeeID"                              HeaderText          ="          EmployeeID"                              ReadOnly          ="          true"                              SortExpression          ="          EmployeeID"                              /          >          <          asp:BoundField                              DataField          ="          EmployeeCode"                              HeaderText          ="          EmployeeCode"                              SortExpression          ="          EmployeeCode"                              /          >          <          asp:BoundField                              DataField          ="          Name"                              HeaderText          ="          Name"                              SortExpression          ="          Name"                              /          >          <          asp:BoundField                              DataField          ="          Department"                              HeaderText          ="          Department"                              SortExpression          ="          Department"                              /          >          <          asp:BoundField                              DataField          ="          Salary"                              HeaderText          ="          Salary"                              SortExpression          ="          Salary"                              /          >          <          /Columns          >          <          FooterStyle                              BackColor          ="          #507CD1"                              Font-Bold          ="          Truthful"                              ForeColor          ="          White"                              /          >          <          PagerStyle                              BackColor          ="          #2461BF"                              ForeColor          ="          White"                              HorizontalAlign          ="          Center"                              /          >          <          SelectedRowStyle                              BackColor          ="          #D1DDF1"                              Font-Bold          ="          True"                              ForeColor          ="          #333333"                              /          >          <          HeaderStyle                              BackColor          ="          #507CD1"                              Font-Assuming          ="          Truthful"                              ForeColor          ="          White"                              /          >          <          EditRowStyle                              BackColor          ="          #2461BF"                              /          >          <          AlternatingRowStyle                              BackColor          ="          White"                              /          >          <          /asp:GridView          >          <          asp:ObjectDataSource                              ID          ="          ObjectDataSource1"                              runat          ="          server"                              SelectMethod          ="          GetAllEmployees"                              EnablePaging          ="          truthful"                              TypeName          ="          EmployeeData"                              StartRowIndexParameterName          ="          startIndex"                              MaximumRowsParameterName          ="          pageSize"                              SortParameterName          ="          sortBy"                              SelectCountMethod          ="          GetTotalEmployeesCount"                              >          <          /asp:ObjectDataSource          >

EmployeeData.cs: A class which is bound to ObjectDataSource

          public          class          EmployeeData {          private          static          int          employeesCount;          public          EmployeeData()     {                                }      [DataObjectMethod(DataObjectMethodType.Select)]          public          static          List<EmployeeInfo> GetAllEmployees(int          startIndex,          int          pageSize,          string          sortBy)     {         EmployeeBLL objEmployeeBLL =          new          EmployeeBLL();         List<EmployeeInfo> result;          int          totalEmployees=0;          if          (string.IsNullOrEmpty(sortBy))             sortBy =          "          EmployeeID";          upshot = objEmployeeBLL.GetAllEmployee(startIndex, pageSize, sortBy,          ref          totalEmployees);         employeesCount = totalEmployees;          return          result;      }          public          static          int          GetTotalEmployeesCount()     {          return          employeesCount;     }   }

Nothing special here to explicate considering we are calling already explained code to become one page sorted data by passing ObjectDataSource provided values. But one affair that I want to explain from optimization point of view, We are getting employeesCount in GetAllEmployees() method, and in GetTotalEmployeesCount() we only return that value, instead of fetching the count from database. What people commonly do is , they transport seprate database call to fetch the count which is an unnecessary step considering we can write our stored procedure and data acess and business concern logic layer in such a fashion in which we can get the bodily data and total count in 1 database phone call.

Comparing

I did comparing between conventional and optimized implementation of paging and sorting and there is a HUGE deviation between them, Here are the results

Ane noticeable thing from this performance table is, every bit we are increasing the data upward-optimized implementation is taking more fourth dimension to fetch the records from database while in that location is no noticeable divergence in optimized style.

No. of Records	Optimized Paging ( in seconds)	Un-optimized Paging ( in seconds)
500000	0.21	07.41
one thousand thousand	0.28	17.27
2000000	0.32	36.28
3000000	0.33	54.03

No. of Records	Optimized Sorting ( in seconds)	United nations-optimized Sorting ( in seconds)
500000	01.61	10.14
1000000	03.xiii	23.22
2000000	09:38	47.39
3000000	13.12	70:25

* Paging time is calculated on GridView page alphabetize changed effect.
* Sorting time is calculated on GridView sorting event.

Summary

In this article we learned an End to End solution to optimized paging and sorting in ASP.Internet GridView. In Database Layer we used ROWNUMBER() method which help us to select only ane page information. And In Presentation layer we used ObjectDataSource Virtual paging feature by using its properties StartRowIndexParameterName, MaximumRowsParameterName, SelectCountMethod,SortParameterName. Hope information technology helps.

Feedback

Y'all feedback will actually help me to be amend, and then delight leave your comments, suggestions. You can as well transport me whatsoever query at akhhttar at gmail dot com.

Happy Programming!!!