Introduction

The new GridView control introduced in ASP.NET 2.0 is part of a suite of new "view" and "data source" based controls offered by Microsoft. The new "view" based controls include the GridView, FormView and DetailsView. The new data source controls consist of the SqlDataSource, AccessDataSource, XMLDataSource, SiteMapDataSource, and ObjectDataSource, These controls are designed for declarative usage with an emphasis on automated data binding. The automated binding feature can be quite handy and powerful if you accept the code pattern that Microsoft would like you to use with the GridView. This code pattern is designed for automatic wireup between the view control and the data source control. The drawback is that the data source control requires direct access and intimate knowledge of the database. This breaks the nice tiered objects that most developers are accustomed to using. To get more automated features and less coding you must violate data separation through the use of data objects. This can make your code more difficult to maintain, extended, and debug. You may have read or attended a demonstration of the GridView and invariable heard or read the words, "That’s all there is to it-no code necessary!"

What was a complex task to bind to custom data sources has been greatly simplified. Now, with a little coding you can successfully bind the GridView in your ASP.NET 2.0 application to your custom data objects using the ObjectDataSource control. But I have found few detailed examples using the GridView and ObjectDataSource to implement sorting and paging using a stored procedure in SQL Server 2005. Therefore, in this article I help fill this gap by demonstrating how to bind a GridView control to a DataSourceObject using a custom data object layer architectural design. To make the solution more complete, I will include paging and sorting of the data using a SQL 2005 stored procedure. The demonstration project utilizes a fictitious recipe database and retrieves paginated data via a stored procedure utilizing the newly added row_number() function from SQL Server 2005.

System Requirements

To run the code for this sample you should have;

• The .NET Framework version 2.0


• VS 2005 or Visual Web Developer 2005 Express Edition


• SQL Server 2005 Express Edition or higher

Installing and Compiling the Sample Code

The sample download for this article contains a Visual Studio 2.0 web project named GridViewDemo. This project demonstrates how to bind custom data objects to the GridView control.

To install the sample, unzip the sample download to a folder on your machine and simply open the folder using either Visual Studio 2005 or the free Visual Web Developer 2005 Express Edition.

To install the database, run the SQL script GridViewDemo.sql on a SQL Server 2005 instance. The script will create a database called GridViewDemo, a table named Recipe, some fake recipe data, a stored procedure named GetRecipes, and a login and user named gridviewdemo_user. If your SQL Server 2005 instance is a named instance or is located on a machine other than your local machine, you will need to modify the connection string setting located in the web.config file.

The Data Source and View Controls

The ASP.NET 2.0 GridView control is one of a group of new controls Microsoft has added to the ASP.NET toolbox. These new controls are designed to work together using a well defined data binding pattern freeing the developer to mix and match various data controls to bind to view controls. The following is a list of the data source controls available for your use in ASP.NET 2.0.

• SqlDataSource – presents a SQL database to data-bound controls


• AccessDataSource – presents a Microsoft Access database to data-bound controls


• XmlDataSource – presents an XML data source to data-bound controls


• SiteMapDataSource – a data control used to present hierarchical data


• ObjectDataSource – presents a business object or data tier object to data-bound controls

The GridView is designed to be a presentation control that binds to any data source control that implements the IDataSource interface. Yet, there are other presentation or view controls available for your use in ASP.NET 2.0 that can bind to any data source control list above. The following is an entire list of the view based controls available for your use in ASP.NET 2.0.

• GridView – tabular visual representation of data from a data source


• DetailsView – visual representation of a single row of data from a data source


• FormView – visual representation of a single row of data from a data source

The SqlDataSource control is a data control that is optimized and designed to communicate with a relational database. The SqlDataSource control supports any database that has an ADO.NET provider. There are many examples from Microsoft as well as others who have gone to great lengths to explain how to bind a GridView, DetailsView, and FormView controls to the SqlDataSource control.

Examples utilizing the SqlDataSource control require very little coding and provide a lot of very useful features such as paging and sorting data when combined with the GridView control. But, the control requires you to embed your SQL statements and/or stored procedure names directly within your HTML code to take advantage of the SqlDataSource control’s functionality. In addition, the SqlDataSource makes its own connection to the database. Finally, the pagination code is inefficient when working with large sets of data. Although this design may work well for a variety of applications, the architectural design does not work for many applications that utilize a custom data objects layer or a 3-tier approach. Fortunately, Microsoft included the ObjectDataSource control just for this situation. The ObjectDataSource control provides you, the developer, with a way to bind custom data objects to any of the view based controls.

To demonstrate the GridView binding, I will first give a quick highlight of what the Recipes GridView Demo does. Then, I will take a look at the custom data objects and stored procedure used in the example. Next, I will show you how to bind and wireup the DataSourceObject control to work with the custom data objects. This will lead into the custom SQL Server stored procedure used to page the data. Finally, I will bind the GridView control to the DataSourceObject and step though process of paging and sorting data.

The Sample Demo

The Recipes GridView Demo contains a simple ASP.NET page with only four controls. The page uses a Button, DropDownList, GridView, and ObjectDataSource control. Figure 1 shows what the GridView looks like when you first start the project and click the Load Grid button.

Figure 1. Recipes displayed in a GridView

Notice that the user can select the page size they are interested in at the top of the grid. Additionally, clicking a column heading changes the sort direction of the column. When the sort is changed, only those rows needed to display the current page are retrieved from the database. Figure 2shows the page after an ascending sort on Category column.

Figure 2. Recipe in GridView Sorted by the Category column

The user is not restricted to the current page size. By selecting a new page size, the GridView will re-adjust accordingly after the DropDownList control performs an auto post-back as shown in Figure 3.

Figure 3. Recipes in GridView with the Page Size set to 10

This GridView Demo is simple in function but there is some special wiring of objects going on behind the scenes. In the next few sections I will take you though the creation of a simple custom data object, the stored procedure that powers the selecting of data and finally the wiring up of the ObjectDataSource and GridView controls.

Database and Data Objects

The Recipes GridView Demo is powered by a database that consists of a single table and doesn’t really have much information about a recipe other than the name, category, serving size, and the creation date. The database can be created on your machine using the SQL script provided in the accompanying download files. There is a single table in the database named Recipe. It contains five columns including a RecipeId designated as a primary key of the table as shown in figure Figure 4.

Figure 4. Recipe data table design

There are two data objects used to retrieve recipe data. The Recipe class is designed to hold data for a single recipe. The recipe data is stored in the Recipe database table. There is also a RecipeDataObject used to retrieve a collection of Recipe objects from the recipe database. The RecipeDataObject has two public methods GetRecipes() and GetRecipesCount(). Figure 5shows the class diagram of the class Recipe and RecipesDataObject.

Figure 5. Class diagram of custom data objects

These two methods will be used by the ASP.NET ObjectDataSource control to retrieve the requested recipe objects and the count of all the recipes in the Recipes database table respectively. The GetRecipes() method is the only method that has any significant code in either object. This method accepts four parameters: startRowIndex, maximumRows, sortExpression, and sortDirection. The method opens a connection to the database, executes the stored procedure sp_GetRecipes, and returns a generic List<Recipe> collection containing Recipe data objects.

For each row of data returned by the stored procedure, a Recipe object is created and added to a generic List<Recipe> collection. Generics are new to the .NET Framework 2.0 and provide a means for the CLR to limit boxing and unboxing operations (which drastically improves performance in collections). Generics also provide the developer with a lot more equally important features such as the promotion of code reuse and strongly typed collections.

Before returning the List<Recipe> collection containing Recipe objects, the total count of rows is stored in a private class variable named _recipeCount. This count is very important and is used by the GridView control to determine how many page links to display on the GridView control. To limit the number of round trips to the database I will store this count in a local variable in the RecipesDataObject class instance for the ObjectDataSource to access later. I will go more into depth about this variable later in the article.

Paginating Stored Procedure

The stored procedure sp_GetRecipes has four input parameters and one output parameter.

• MinRow (int input)</li> <li>MaxRow (int input)


• SortExpression (varchar input)</li> <li>SortDirection (varchar input)


• TotalRowCount (int output)</li> </ul><p>The stored procedure results are sorted based on the <span class='codeintext'>SortExpression and SortDirection</span> and then only those rows with numbers between <span class='codeintext'>MinRow and MaxRow</span> are returned. One output parameter named <span class='codeintext'>TotalRowCount is returned containing the total number of rows.
  
  

  Prior to SQL Server 2005, you had to use some tricks with the SQL count() function to perform paging at the database such as a temporary table in a stored procedure. This is no longer the case, SQL Server 2005 introduces the new row_count() function which efficiently produces a numeric count for each row of data in a query result set. The query containing the row count can then be filtered by another outer query to limit the number of rows needed in a paging scenario. A temporary table is no longer required.

  
  

  The SQL code below comes from the stored procedure sp_GetRecipes. You might notice there are three queries. The first query gets the number of rows in the Recipe table. The second query consists of an inner query and an outer query. The inner query is used to produce a sequential number value for each row in the Recipe table. The outer query limits the number of rows to the row MinRow</span> and <span class='codeintext'>MaxRow inclusively using the SQL between operator.

  
  
  

  —  get the total row count of all recipes
  
  SELECT TotalRowCount = Count(RecipeId) FROM Recipe
  -- retrieve one page of recipe data
  SELECT 
    r.RecipeId,
    r.[Name],
    r.Category,
    r.ServingSize,
    r.CreatedDate
  FROM
  (
    SELECT 
      ROW_NUMBER() OVER(ORDER BY 
        CASE WHEN SortExpression = ‘Name’ AND SortDirection = 'ASC' THEN 
          [Name] END ASC,
        CASE WHEN SortExpression = ‘Name’ AND SortDirection = 'DESC' THEN 
          [Name] END DESC,
        CASE WHEN SortExpression = ‘Category’ AND SortDirection = 'ASC'  
          THEN Category END ASC,
        CASE WHEN SortExpression = ‘Category’ AND SortDirection = 'DESC' 
          THEN Category END DESC,
        CASE WHEN SortExpression = ‘ServingSize’ AND SortDirection = 'ASC'  
          THEN ServingSize END ASC,
        CASE WHEN SortExpression = ‘ServingSize’ AND SortDirection = 'DESC' 
          THEN ServingSize END DESC, 
        CASE WHEN SortExpression = ‘CreatedDate’ AND SortDirection = 'ASC'  
          THEN CreatedDate END ASC,
        CASE WHEN SortExpression = ‘CreatedDate’ AND SortDirection = 'DESC'  
          THEN CreatedDate END DESC	) AS RowNumber,
      RecipeId,
      [Name],
      Category,
      ServingSize,
      CreatedDate
    FROM 
      Recipe
  ) AS r
  WHERE
    r.RowNumber BETWEEN MinRow AND MaxRow
  <p></pre><!-- @END —>
  
  
  In addition, the query used in sp_GetRecipes has several case statements defined in the order by clause. To support dynamic sorting I use a case statement to determine which type of sort to use. According to the estimated execution plan in SQL Server Management Studio, the database engine will do a sort operation and not take advantage of a column index when using this technique. This results in a performance penalty. If an index needs to be utilized, you can re-write the SQL code using multiple if statements with each select query containing a specific order by clause with no case statement. This will allow the query optimizer to identify the column for sorting ahead of time and utilize any indexes the column may have associated with it. If there is not index, a sort operation will still be performed.
  
  
  Wiring it All Up
  
  
  You have now seen the underlying mechanism and data objects to retrieve recipes using a stored procedure. The next section of the article is devoted to wiring up the ObjectDataSource and GridView controls to make it all come together.
  
  
  ObjectDataSource Wireup
  
  
  First I must create a new ObjectDataSource and bind the custom Recipe data objects to this data source. I do this by adding the following tag to my page.
  
  
  
  
  <asp:ObjectDataSource ID="odsRecipes" runat="server" EnablePaging="true" 
    TypeName="RecipesDataObject" SelectMethod="GetRecipes"   
    SelectCountMethod="GetRecipesCount" 
    OnObjectCreating="odsRecipes_ObjectCreating"  
    OnSelecting="odsRecipes_Selecting" />
  
  
  
  The EnablePaging attribute tells the ObjectDataSource to enable or disable paging functionality. When this attribute is set to true, the ObjectDataSource allows any consumer to invoke the paging of data. In addition, the ObjectDataSource will pass two parameters named maximumRows (first parameter) and startRowIndex (second parameter) to the specified method in the SelectMethod attribute.
  
  
  The TypeName attribute is used to indicate which object type the ObjectDataSource is to invoke via reflection in order to execute the SelectMethod and SelectCountMethod. I set the TypeName to my RecipesDataObject. I then set the SelectMethod attribute to the method name GetRecipes() defined in RecipesDataObject. Likewise I set the SelectCountMethod attribute to the method name GetRecipesCount() also defined in the RecipesDataObject.
  
  
  There are several events hooks on the ObjectDataSource. I am only interested in two for this article. The ObjectCreating event allows me to instantiate my own RecipeDataObject instance rather than relying on the ObjectDataSource to create an instance. Otherwise, the default behavior of the ObjectDataSource is to instantiate the data object using a default constructor. You may not have a default constructor in your data object. Your data object may require some sort of initialization prior to use. If you do not have a default constructor in your data object, this event allows you to create your own instance of the data object for use by the ObjectDataSource. In addition, there are two additional events ObjectCreated and ObjectDisposing which you can use to manage your own data objects if needed. ObjectCreating is an event that allows the developer to create their own object instance and then pass that instance back to the ObjectDataSource. ObjectCreated fires after ObjectCreating and is a hook that allows the developer to do additional initialization on the object.
  
  
  
  
  protected void odsRecipes_ObjectCreating(object sender,
    ObjectDataSourceEventArgs e)
  
  {
    // create a new object instance of our custom RecipesDataObject
    e.ObjectInstance = new RecipesDataObject(); 
  
  }
  
  
  
  The second event I use is the Selecting event. This event is fired prior to the ObjectDataSource obtaining the data from the defined method in the SelectMethod attribute. The Selecting event allows me to add to additional parameters on the method call used to support data sorting by the GridView control. In the following code, I add the additional parameters sortExpression and sortDirection to the InputParameters collection. I then set values that were previously stored in the ViewState on the page for each of these new parameters so that the values will be passed to my sorting method.
  
  
  
  
  protected void odsRecipes_Selecting(object sender,
    ObjectDataSourceSelectingEventArgs e)
  
  {
  
  //  the selecting event fires twice, once when calling GetRecipes()
  
  // and a second time when calling GetRecipesCount()
    if (e.ExecutingSelectCount  false)
    {
    //  create the two sorting parameters to pass to GetRecipes()
    //  use the sorting values stored in the viewstate
      e.InputParameters["sortExpression"] = 
        (string)ViewState["sortExpression"];
      e.InputParameters["sortDirection"] = (string)ViewState["sortDirection"];
    }
    else
    {
    //  clear all parameters before calling to GetRecipesCount() 
      e.InputParameters.Clear();
    }
  }
  
  GridView Wireup
  The first step to utilizing the GridView control is to create the ASP.NET tag and set the various attributes. The <asp:GridView /> tag has numerous attributes and several elements that you can set to customize the behavior. In addition there are several events you can hook into. To enable paging and sorting you must set the attribute AllowPaging attribute to true and the AllowSorting attribute to true. You can also customize the paging behavior by setting the PagerSettings-Mode attribute. There are several modes available, I use the NumericFirstLast for this example. This mode will display numbers for the each page of data that the grid allows the user to page to.
  
  <asp:GridView AllowPaging="true" PagerSettings-Mode="NumericFirstLast" 
    AllowSorting="true"  ID="gvRecipes" runat="server" DataKeyNames="RecipeId"
    AutoGenerateColumns="False" HeaderStyle-HorizontalAlign="Left" 
    GridLines="Both" OnPageIndexChanging="gvRecipes_PageIndexChanging" 
    OnSorting="gvRecipes_Sorting" >
    <Columns>
      <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name"
        ItemStyle-Width="250"  />
      <asp:BoundField DataField="Category" HeaderText="Category" 
        SortExpression="Category" ItemStyle-Width="150" />
      <asp:BoundField DataField="ServingSize" HeaderText="Serving Size" 
        SortExpression="ServingSize" ItemStyle-Width="100"  /> 
      <asp:BoundField DataField="CreatedDate" HeaderText="Created Date" 
        DataFormatString="{0:d}" HtmlEncode="false" 
        SortExpression="CreatedDate" ItemStyle-Width="100" /> 
    </Columns>
  </asp:GridView>
  
  There are two events on the GridView that I am interested in hooking into. The first is the PageIndexChanging event which I specify the event handler using the OnPageIndexChanging attribute. This event fires each time the user clicks the paging links on the GridView control. This allows me to set the new page index on the GridView control and rebind the control. The rebinding causes the ObjectDataSource to fire the GetRecipes() method and retrieve a generic List<Recipe> collection of recipes to display in the GridView.
  
  protected void gvRecipes_PageIndexChanging(object sender,
    GridViewPageEventArgs e)
  {
  //  set the new page index
    gvRecipes.PageIndex = e.NewPageIndex;
  //  rebind the gridview to the objectdatasource with the new page index
  //  only the page of interest will be retrieved
    BindGridView();
  }
  
  The second event I need to implement is the Sorting event. I can specify an event handler by setting the OnSorting attribute. This event fires when a designated row column name is clicked by the user. The following code is used to record the sorting options in the page ViewState. The sorting options need to persist beyond the page life so that they can be used when the user changes the page or page size. This logic enables the sorting to work for all pages.
  
  protected void gvRecipes_Sorting(object sender, GridViewSortEventArgs e)
  {
  //  store the new sorting options - flip the sort direction if necessary
    ViewState["sortDirection"] = e.SortExpression  
      (string)ViewState["sortExpression"] ? 
      ((string)ViewState["sortDirection"] == "ASC" ? "DESC" : "ASC") : "ASC";
    ViewState["sortExpression"] = e.SortExpression;
  
  //  rebind the gridview to the objectdatasource
    BindGridView();
  
  }
  
  
  
  As you can see, the sorting event uses a bit of logic to determine if the user has sorted this column previously. If so, I reverse the sort direction to allow the user to sort ascending or descending. The sorting options are then stored in the page ViewState for use by the ObjectDataSource Selecting event. The final step is to rebind the GridView so that the current page of data viewed by the user is retrieved and sorted based on the user’s sort selection.
  
  
  To enable column sorting I must set the SortExpression attribute on each <asp:BoundField /> tag. Sorting does not come for free. Each column that I want to enable sorting must be supported in the sp_GetRecipes stored procedure. The value I specify in the SortExpression attribute will be passed to my custom data object via the GetRecipes() method. Remember that this method in turn will pass the value to the stored procedure sp_GetRecipes to perform the sort.
  
  
  Further Work
  
  
  Additional filtering can be added to limit the result set query based on user input. These user specific search values can be added as parameters similar to the sorting parameters. The values can then be passed to the stored procedure to further limit the results returned to the user.
  
  
  Conclusion
  
  
  The new GridView control that Microsoft added to ASP.NET 2.0 is a powerful control designed to make you life easier. The GridView is designed to replace the now obsolete DataGrid control and is part of a suite of view and data source specific controls. These controls use a declarative programming model and hide many of the features of how they work under the hood. The GridView control comes with many compelling features such as built in paging, sorting, and editing capabilities. But, in order to take advantage of these features you must use the GridView with a corresponding data source control such as the SqlDataSource. Many existing and new architectures separate data access through a group of data access objects which do not work with the SqlDataSource control. Therefore, Microsoft has provided the ObjectDataSource control to use in such an application architecture.
  
  
  The goal of this article is to demonstrate not only how to wire up custom data objects to a ObjectDataSource and GridViewControl, but also to provide an example of the new row_count() function in SQL Server 2005 to paginate data in a stored procedure. There are many good articles and examples demonstrating how to wire up a GridView control to a SqlDataSource and implement sorting as well as paging. This article’s intent is to show how writing a little bit of code and understanding how to wire up various events, the ObjectDataSource control can be used to gain access to the feature rich GridView control without violating 3-tier architecture design. I hope you will benefit from this article and perhaps contribute to a refined implementation of binding data to the various view based controls.