数据源分页与排序

GridView控件要同时启用AllowSorting和AllowPaging属性。

ShowDSSorting.aspx 
 1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ShowDSSorting.aspx.cs" Inherits="Chapter16_ShowDSSorting" %>
 2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 3 <html xmlns="http://www.w3.org/1999/xhtml">
 4 <head runat="server">
 5     <title>ShowDSSorting</title>
 6     <style type="text/css">
 7         .employee td, .employee th
 8         { padding:5px;}
 9         a 
10         { text-decoration:none;
11           font-size:12px;
12           color:Black; }
13     </style>
14 </head>
15 <body>
16     <form id="form1" runat="server">
17     <div>
18         <asp:GridView ID="grdEmployee" runat="server" DataSourceID="orcEmployee" AllowSorting="True"
19                                      AllowPaging="True" PageSize="3" 
20             CssClass="employee">
21         </asp:GridView>
22         <asp:ObjectDataSource ID="orcEmployee" EnablePaging="True" 
23             SortParameterName="sortExpression" runat="server" SelectMethod="GetEmployee" 
24             TypeName="EmployeeDSSorting" SelectCountMethod="GetEmployeeCount">
25         </asp:ObjectDataSource>
26     </div>
27     </form>
28 </body>
29 </html>
30

这里需要注意的是，ObjectDataSource控件所提供的SortParameterName属性。当这个属性被启用时，ObjectDataSource控件会使用数据源排序方式代替用户界面排序方式。

EmployeeDSSorting.cs 
 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 
 5 public class EmployeeDSSorting
 6 {
 7     private static readonly string _conString = String.Empty;
 8 
 9     public static SqlDataReader GetEmployee(string sortExpression, int startRowIndex,int maximumRows)
10     {
11         SqlConnection con = new SqlConnection(_conString);
12         SqlCommand cmd = new SqlCommand();
13         cmd.Connection = con;
14         cmd.CommandType = CommandType.StoredProcedure;
15         cmd.CommandText = "GetSortedEmployee";
16         cmd.Parameters.AddWithValue("@SortExpression", sortExpression);
17         cmd.Parameters.AddWithValue("@StartRowIndex", startRowIndex);
18         cmd.Parameters.AddWithValue("@MaximumRows", maximumRows);
19 
20         con.Open();
21         return cmd.ExecuteReader(CommandBehavior.CloseConnection);
22     }
23 
24     public static int GetEmployeeCount()
25     {
26         int count = 0;
27         string cmdText =  "SELECT COUNT(*) FROM Employees";
28         SqlConnection con = new SqlConnection(_conString);
29         SqlCommand cmd = new SqlCommand(cmdText, con);
30         using (con)
31         {
32             con.Open();
33             count = (int)cmd.ExecuteScalar();
34         }
35         return count;
36     }
37 
38     static EmployeeDSSorting()
39     {
40         _conString = System.Configuration.ConfigurationManager.ConnectionStrings["EMPLOYEESDBConnectionString"].ConnectionString;
41     }
42 }

存储过程GetSortedEmployee从数据库表Employees中获得一个已被排序的记录。代码如下：

GetSortedEmployee.sql 
 1 CREATE PROCEDURE [dbo].[GetSortedEmployee] 
 2 (
 3     @SortExpression NVARCHAR(10),
 4     @StartRowIndex INT,
 5     @MaximumRows INT
 6 )    
 7 AS
 8 
 9 SET NOCOUNT ON 
10 
11 DECLARE @PageLowerBound INT
12 DECLARE @PageUpperBound INT 
13 
14 SET @PageLowerBound = @StartRowIndex 
15 SET @PageUpperBound = @StartRowIndex + @MaximumRows +1 
16 
17 CREATE TABLE #tmp 
18 (
19     RecId INT IDENTITY(1,1) NOT NULL,
20     EmpId INT
21 )
22 
23 INSERT INTO #tmp (EmpId) SELECT Id FROM Employees 
24 ORDER BY 
25 CASE WHEN @SortExpression='Id' THEN Id END ASC, 
26 CASE WHEN @SortExpression='Id DESC' THEN Id END DESC, 
27 CASE WHEN @SortExpression='FirstName' THEN FirstName END ASC,
28 CASE WHEN @SortExpression='FirstName DESC' THEN FirstName END DESC,
29 CASE WHEN @SortExpression='LastName' THEN LastName END ASC,
30 CASE WHEN @SortExpression='LastName DESC' THEN LastName END DESC
31 
32 SELECT Id,FirstName,LastName,Phone FROM Employees 
33 INNER JOIN 
34 #tmp WITH (nolock) 
35 ON Employees.Id=#tmp.EmpId 
36 WHERE 
37 #tmp.RecId > @PageLowerBound AND #tmp.RecId < @PageUpperBound 
38 ORDER BY #tmp.RecId

将记录加入临时表之前，存储过程使用SQL CASE函数对其进行了排序。由于不能使用带有ORDER BY子句的参数，所以被排序列的名称必须被硬编码到CASE函数中。接下来，所需的记录从临时表中被查询出来。

posted on 2011-01-06 13:35  DilibraLee  阅读(225)  评论(0)    收藏  举报

导航