Manipulating data in ASP.net 2.0 26: Sorting Custom Paging data _ self-study process

Introduction

Custom paging increases the efficiency of several orders of magnitude compared to the default page-flipping method. We need to consider customizing pagination when we need to page through a lot of data, but implementing custom paging requires more work than default paging. This is also true for sorted custom paging data, which we will expand in the previous example to implement the sort of custom paging data.

　　Note: Now that this tutorial is based on the previous one, we need to copy the code from the <asp:Content> element in the previous Tutorial sample page efficientpaging.aspx to the Sortparameter.aspx sample page of this tutorial. For information on how to do this, see Add client Acknowledgement for deleting data

Step 1: Review the custom paging technology

To implement custom paging, we need to use some methods to return a subset of records based on the Start row index and the maximum row parameter. In the previous tutorial, we looked at how to use Microsoft SQL SERVER 2005 Row_number () for implementation. In short, row_number () assigns a row number to the row returned by each query. The following query demonstrates how to use this technique to get 11 to 20 of the product data sorted by ProductName.

SELECT ProductID, ProductName, ...
From
 (SELECT ProductID, ProductName, ..., row_number () over (order by
  ProductName) as Rowrank to products
 ) As Productswithrownumbers
WHERE rowrank > Rowrank <= 20

The above techniques can be satisfied for paging according to a fixed collation, but if we want to get records sorted by different sort expressions, ideally we should use parameters in the over clause to rewrite the query, as follows:

SELECT ProductID, ProductName, ...
 from (SELECT ProductID, ProductName, ..., row_number () over (order by
  @sortExpression) as Rowrank from
 Products) as Productswithrownumbers
WHERE rowrank > Rowrank <= 20

Unfortunately, parameters cannot be used in an ORDER BY clause. And we can only create stored procedures to accept @sortexpression input parameters, using any of the following methods:

hard-coded queries for all sort expressions, using the If/else T-SQL statement to determine which query to execute
Use the case statement to implement a dynamic order by expression based on the input parameter @sortexpression, for details, see used to dynamically Sort Query in the Power's SQL case statements S section.

Use strings to save query statements and then use sp_executesql system stored procedures to dynamically execute queries

Each of these implementation methods have their own shortcomings. The first scenario is less maintainable than the remaining two because it needs to create a query for every possible new expression. Therefore, if you add a field that allows sorting in the GridView, you also need to modify the stored procedure. For the second scenario, if our database column is not a string type, sorting can cause some efficiency problems, and maintainability is not as good as the first one. As for the last scenario of dynamically combining SQL statements, if you allow users to input parameters themselves and pass in stored procedures, it can be harmful to SQL injection attacks.

Although none of the solutions are perfect, I know that the third is the best of the three schemes. Because it uses dynamic SQL statements, the flexibility is better than the previous two. Furthermore, only if an attacker is able to pass parameters to a stored procedure at will can a SQL injection attack. Since the DAL uses parameterized queries, ADO. NET prevents these malicious parameters from being passed in to the database, which means that there is a risk of SQL injection only if the attacker executes the stored procedure directly.

To implement this feature, let's create a new stored procedure called getproductspagedandsorted in the Northwind database. This stored procedure accepts three parameters: @sortExpression, an input parameter of type nvarchar (100) that specifies the sort method, which is spliced directly behind the ORDER BY clause. Both @startRowIndex and @maximumRows are integer input parameters, as in the previous tutorial. You can refer to the following script to establish a getproductspagedandsorted stored procedure:

CREATE PROCEDURE dbo. getproductspagedandsorted (@sortExpression nvarchar, @startRowIndex int, @maximumRows int) As--make sure a @so Rtexpression is specified IF LEN (@sortExpression) = 0 SET @sortExpression = ' ProductID '--Issue query DECLARE @sql nvarc Har (4000) SET @sql = ' SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, discontinued, CategoryName, SupplierName from (SELECT ProductID, ProductName, P.supplier IDs, P.categoryid, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, discontinued, C.Cat Egoryname, S.companyname as SupplierName, Row_number () over (order by ' + @sortExpression + ") as Rowrank from Pro Ducts as P INNER join Categories as C on C.categoryid = P.categoryid INNER join suppliers as S.
    SupplierID = P.supplierid) as productswithrownumbers WHERE rowrank > ' + CONVERT (nvarchar), @startRowIndex) +' and Rowrank <= (' + convert (nvarchar), @startRowIndex) + ' + ' + convert (nvarchar), @maximumRows) + ') '--

 Execute the SQL query EXEC sp_executesql @sql

The stored procedure first ensures that the value of the @sortexpression parameter has been specified. If not specified, sort by ProductID. Next, start building a dynamic SQL query. Notice that the dynamic SQL query here is somewhat different from the previous query used to get all the rows from the Products table. In the previous example, we used subqueries to get the categories and vendor names associated with each product. In getproductspagedandsorted we can only use joins because the results need to be sorted by category or by vendor name.

We compose dynamic queries by connecting static query statements and @sortexpression, @startRowIndex, @maximumRows parameters. Because @startrowindex and @maximumrows are integer parameters, they must be converted to nvarchar types before they are connected. You can use sp_executesql to perform a dynamic SQL query after the connection is complete.

Let's take some time to test the stored procedure using the values of various @sortexpression, @startRowIndex, and @maximumrows parameters. Right-click the stored procedure in Server Explorer and select Execute. The IDE starts the Run Stored Procedures dialog box and we enter various input parameters (see Figure 1). For example, to have the results sorted by category name, set the value of the @sortexpression parameter to CategoryName, or use CompanyName if you want to sort by company name. All parameter values are set correctly and then click OK. The results are displayed in the Output window. Figure 2 shows the records from 11 to 20 according to UnitPrice in reverse order.

Figure 1: Trying to set three input parameters for a stored procedure

Figure 2: The results of the stored procedure are displayed in the input window

Step 2: Add data access and business logic layers

Now that we have established the getproductspagedandsorted stored procedure, the next step is to execute it through our application architecture. We need to add a correct method for the Dal and BLL. First let's add a method for the DAL. Open the Northwind.xsd strongly typed dataset, right-click ProductsTableAdapter, and choose Add Query from the menu. As we did in the previous tutorial, we need to configure a new Dal method to use the established stored procedures-getproductspagedandsorted. Choose to use an existing stored procedure option.

Figure 3: Selecting an existing stored procedure

In the next step, we use it by selecting the getproductspagedandsorted stored procedure from the Drop-down list.

Figure 4: Using the getproductspagedandsorted stored procedure

In the next screen, we select it to return tabular information.

Figure 5: Indicates that the stored procedure returns tabular information

Finally, we create the Dal method to populate the DataTable and return the DataTable, named Fillpagedandsorted and getproductspagedandsorted respectively.

Figure 6: Selecting a Method name

Now that we've expanded the DAL, let's look at the BLL. Open the Productsbll class file and add a new method getproductspagedandsorted. This method accepts three parameters-sortexpression,startrowindex and maximumrows. Simply to invoke the Getproductspagedandsorted method of the DAL, the code is as follows:

[System.ComponentModel.DataObjectMethodAttribute (
 System.ComponentModel.DataObjectMethodType.Select, False) ] public
northwind.productsdatatable getproductspagedandsorted (
 string sortexpression, int startrowindex, int maximumrows)
{return
 adapter.getproductspagedandsorted
  (SortExpression, startRowIndex, maximumrows);
}

Step 3: Configure ObjectDataSource to pass in the SortExpression parameter

Well, we've added a method for the Dal and BLL to invoke the getproductspagedandsorted stored procedure. The rest of the work is to configure the ObjectDataSource of the Sortparameter.aspx page to pass SortExpression parameters to the new BLL method based on the ordering of the user's request.

First, we modify the ObjectDataSource SelectMethod from Getproductspaged to getproductspagedandsorted. You can modify it by configuring the Properties window of the Data Source Wizard or directly in the Declaration code. Next, we need to provide the ObjectDataSource SortParameterName properties. property is set, ObjectDataSource will pass the SortExpression attribute of the GridView to SelectMethod. In particular, ObjectDataSource will look for input warehousing based on sortparametername values, since the input parameters of the Getproductspagedandsorted method in BLL are called SortExpression, Our ObjectDataSource SortExpression attribute should also be set to "SortExpression".

After these two-step modifications, the ObjectDataSource declaration should read as follows:

<asp:objectdatasource id= "ObjectDataSource1" runat= "Server"
 oldvaluesparameterformatstring= "original_{0}" Typename= "PRODUCTSBLL"
 selectmethod= "getproductspagedandsorted" enablepaging= "True"
 selectcountmethod= " Totalnumberofproducts "sortparametername=" SortExpression ">
</asp:ObjectDataSource>

Note: As mentioned in the previous tutorial, make sure that the SortExpression, startRowIndex, and maximumrows input parameters are in the SelectParameters collection of ObjectDataSource.

To allow the GridView to turn on sorting, first check to see if the sorting multiple-selection box is selected. Setting the AllowSorting property of the GridView to True allows the header text of each column to be rendered as LinkButton. The following steps will be raised by the user clicking on the LinkButton of the title:

1.GridView modifies the value of its SortExpression property to the SortExpression value of the column in which the currently clicked header is located.

2.ObjectDataSource invokes the Getproductspagedandsorted method of BLL, Pass the value of the SortExpression property of the GridView as the SortExpression parameter to the method (and the correct startrowindex, maximumrows the value of the input parameter).

3.BLL calls the Getproductspagedandsorted method of the DAL.

4.DAL executes the getproductspagedandsorted stored procedure and passes in the @sortexpression parameters (and @startrowindex, @maximumRows input parameters).

5. The stored procedure returns the correct subset of records data back to the BLL,BLL to ObjectDataSource, which is then rendered HTML to the user after the data is bound to the GridView.

Figure 7 shows the first page recordset in the Order of UnitPrice.

Figure 7: Fruit arranged according to UnitPrice

Although our program can now be sorted correctly by product name, category name, bit quantity, and price, if we choose to sort by vendor name, we get a run-time exception, as shown in Figure 8.

Figure 8: Sorting by vendor name will get a Run-time exception

This exception is raised because the sortexpression of the SupplierName BoundField bound column of the GridView is set to SupplierName. However, this list is actually called Companyname,suppliername in the Supplier table and is our alias for this column. Because the Row_number () feature can only use real column names, we need to modify the BoundField sortexpression from "SupplierName" to "CompanyName" (Figure 9), Figure 10 shows the records sorted by vendor after modification.

Figure 9: Change the SortExpression of SupplierName BoundField to "CompanyName" (Translator note: Picture may not be correct)

Figure 10: The results are now sorted according to the vendor name

Summarize

In the previous tutorial, we implemented custom paging, which only fixed one sort at design time. In short, it's not true to customize pagination and to provide custom sorting. In this tutorial, we have solved this limitation by introducing @sortexpression to extend stored procedures.

After creating a new method in the stored procedure and Dal and BLL, we can implement sorting and custom paging by configuring ObjectDataSource to pass the value of the GridView current SortExpression to BLL SelectMethod.

Happy programming!

About the author

Scott Mitchell, with six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. Scott is an independent technical consultant, trainer, writer, recently completed a new book to be published by Sams Press, proficient in asp.net 2.0 within 24 hours. His contact email is mitchell@4guysfromrolla.com, or he can contact him through his blog Http://ScottOnWriting.NET.