ASP.NET中使用jQGrid

按下面步骤一步一步操作即可完成效果，效果图：

第一步：SQL构造测试数据

1.创建一个产生随机数据的SQL函数：

CREATE FUNCTION [GenerateRandomName] ( @LENGTH INT )
RETURNS NVARCHAR(255)
AS 
    BEGIN
  
	--DECLARE VARIABLES
        DECLARE @RandomNumber NVARCHAR(255)
        DECLARE @I SMALLINT
        DECLARE @RandNumber FLOAT
        DECLARE @Position TINYINT
        DECLARE @ExtractedCharacter VARCHAR(1)
        DECLARE @ValidCharacters VARCHAR(255)
        DECLARE @VCLength INT
    
    --SET VARIABLES VALUE
        SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'    
        SET @VCLength = LEN(@ValidCharacters)
        SET @ExtractedCharacter = ''
        SET @RandNumber = 0
        SET @Position = 0
        SET @RandomNumber = ''


        SET @I = 1

        WHILE @I < ( @Length + 1 ) 
            BEGIN

                SET @RandNumber = ( SELECT  RandNumber
                                    FROM    [RandNumberView]
                                  )
                SET @Position = CONVERT(TINYINT, ( ( @VCLength - 1 )
                                                   * @RandNumber + 1 ))
                SELECT  @ExtractedCharacter = SUBSTRING(@ValidCharacters,
                                                        @Position, 1)
                SET @I = @I + 1

                SET @RandomNumber = @RandomNumber + @ExtractedCharacter

            END

	
        RETURN @RandomNumber

  
    END
GO

CREATE VIEW [RandNumberView]
AS  

SELECT  RAND() AS [RandNumber]

2.创建测试表并写入测试数据：

CREATE TABLE [Users]
    (
      [UserID] INT IDENTITY ,
      [UserName] NVARCHAR(50),
      [FirstName] NVARCHAR(50),
      [LastName] NVARCHAR(50),
      [MiddleName] NVARCHAR(50),
      [EmailID] NVARCHAR(50)
    )
GO
 
INSERT INTO Users
        ( UserName ,
          FirstName ,
          LastName ,
          MiddleName ,
          EmailID
        )
SELECT dbo.GenerateRandomName(10),
dbo.GenerateRandomName(10),
dbo.GenerateRandomName(10),
dbo.GenerateRandomName(10),
dbo.GenerateRandomName(10)+'@'+ dbo.GenerateRandomName(3)+'.com'
GO 500

3.编写分页时的查询存储过程：

CREATE PROC [SelectjqGridUsers]
    @PageIndex INT ,
    @SortColumnName VARCHAR(50) ,
    @SortOrderBy VARCHAR(4) ,
    @NumberOfRows INT ,
    @TotalRecords INT OUTPUT
AS 
    BEGIN

        SET NOCOUNT ON 

        SELECT  @TotalRecords = ( SELECT    COUNT(1)
                                  FROM      [Users]
                                )

        DECLARE @StartRow INT
        SET @StartRow = ( @PageIndex * @NumberOfRows ) + 1 ;
        
        
        WITH    CTE
                  AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY CASE
                                                              WHEN @SortColumnName = 'UserID'
                                                              AND @SortOrderBy = 'asc'
                                                              THEN UserID
                                                             END ASC, CASE
                                                              WHEN @SortColumnName = 'UserID'
                                                              AND @SortOrderBy = 'desc'
                                                              THEN UserID
                                                              END DESC, CASE
                                                              WHEN @SortColumnName = 'UserName'
                                                              AND @SortOrderBy = 'asc'
                                                              THEN UserName
                                                              END ASC, CASE
                                                              WHEN @SortColumnName = 'UserName'
                                                              AND @SortOrderBy = 'desc'
                                                              THEN UserName
                                                              END DESC, CASE
                                                              WHEN @SortColumnName = 'FirstName'
                                                              AND @SortOrderBy = 'asc'
                                                              THEN FirstName
                                                              END ASC, CASE
                                                              WHEN @SortColumnName = 'FirstName'
                                                              AND @SortOrderBy = 'desc'
                                                              THEN FirstName
                                                              END DESC , CASE
                                                              WHEN @SortColumnName = 'MiddleName'
                                                              AND @SortOrderBy = 'asc'
                                                              THEN MiddleName
                                                              END ASC, CASE
                                                              WHEN @SortColumnName = 'MiddleName'
                                                              AND @SortOrderBy = 'desc'
                                                              THEN MiddleName
                                                              END DESC , CASE
                                                              WHEN @SortColumnName = 'LastName'
                                                              AND @SortOrderBy = 'asc'
                                                              THEN LastName
                                                              END ASC, CASE
                                                              WHEN @SortColumnName = 'LastName'
                                                              AND @SortOrderBy = 'desc'
                                                              THEN LastName
                                                              END DESC, CASE
                                                              WHEN @SortColumnName = 'EmailID'
                                                              AND @SortOrderBy = 'asc'
                                                              THEN EmailID
                                                              END ASC, CASE
                                                              WHEN @SortColumnName = 'EmailID'
                                                              AND @SortOrderBy = 'desc'
                                                              THEN EmailID
                                                              END DESC ) AS RN ,
                                UserID ,
                                UserName ,
                                FirstName ,
                                MiddleName ,
                                LastName ,
                                EmailID
                       FROM     [Users]
                     )
            SELECT  UserID ,
                    UserName ,
                    FirstName ,
                    LastName ,
                    MiddleName ,
                    EmailID
            FROM    CTE
            WHERE   RN BETWEEN @StartRow - @NumberOfRows
                       AND     @StartRow - 1
       
        SET NOCOUNT OFF


    END

第二步：aspx页面：

1.HTML DOM：

<body>
    <form id="HtmlForm" runat="server">
    <table id="UsersGrid" cellpadding="0" cellspacing="0">
        <div id="UsersGridPager">
        </div>
    </table>
    </form>
</body>

2.脚本及样式表引用：

 <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.8/themes/ui-darkness/jquery-ui.css" type="text/css" media="all" />   
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js" type="text/javascript"></script> 
    <script src="js/i18n/grid.locale-en.js" type="text/javascript"></script> <!--注：这里的js去<a href="http://www.trirand.com/blog/">http://www.trirand.com/blog/</a>下载即可-->
    <script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>  <!--注：这里的js去<a href="http://www.trirand.com/blog/">http://www.trirand.com/blog/</a>下载即可-->

<pre name="code" class="html">    <link href="css/ui.jqgrid.css" rel="stylesheet" type="text/css" /> <!--注：这里的css去<a href="http://www.trirand.com/blog/">http://www.trirand.com/blog/</a>下载即可-->

  <script type="text/javascript">
        $(function() {
            $("#UsersGrid").jqGrid({
                url: 'jqGridHandler.ashx',
                datatype: 'json',
                height: 250,
                colNames: ['UserID', 'UserName', 'FirstName', 'MiddleName', 'LastName', 'EmailID'],
                colModel: [
                        { name: 'UserID', index: 'UserID',  100, sortable: true },
                        { name: 'UserName',  100, sortable: true },
                        { name: 'FirstName',  100, sortable: true },
                        { name: 'MiddleName',  100, sortable: true },
                        { name: 'LastName',  100, sortable: true },
                        { name: 'EmailID',  150, sortable: true }
                    ],
                rowNum: 10,
                rowList: [10, 20, 30],
                pager: '#UsersGridPager',
                sortname: 'UserID',
                viewrecords: true,
                sortorder: 'asc',
                caption: 'JSON Example'
            });
 
            $("#UsersGrid").jqGrid('navGrid', '#UsersGridPager', { edit: false, add: false, del: false });
        });
         
    </script>

第三步：处理程序：

<%@ WebHandler Language="C#" Class="jqGridHandler" %>
 
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Script.Serialization;


public class jqGridHandler : IHttpHandler
{

    public void ProcessRequest(HttpContext context)
    {
        HttpRequest request = context.Request;
        HttpResponse response = context.Response;

        string _search = request["_search"];
        string numberOfRows = request["rows"];
        string pageIndex= request["page"];
        string sortColumnName= request["sidx"];
        string sortOrderBy = request["sord"];


        int totalRecords;
        Collection<User> users = GetUsers(numberOfRows, pageIndex, sortColumnName, sortOrderBy, out totalRecords);
        string output = BuildJQGridResults(users, Convert.ToInt32(numberOfRows), Convert.ToInt32(pageIndex), Convert.ToInt32(totalRecords));
        response.Write(output);
    }

    private string BuildJQGridResults(Collection<User> users,int numberOfRows, int pageIndex,int totalRecords)
    {

        JQGridResults result = new JQGridResults();
        List<JQGridRow> rows = new List<JQGridRow>();
        foreach (User user in users)
        {
            JQGridRow row = new JQGridRow();
            row.id = user.UserID;
            row.cell = new string[6];
            row.cell[0] = user.UserID.ToString();
            row.cell[1] = user.UserName;
            row.cell[2] = user.FirstName;
            row.cell[3] = user.MiddleName;
            row.cell[4] = user.LastName;
            row.cell[5] = user.EmailID;
            rows.Add(row);
        }
        result.rows = rows.ToArray();
        result.page = pageIndex;
        result.total = totalRecords / numberOfRows;
        result.records = totalRecords;
        return new JavaScriptSerializer().Serialize(result);
    }

    private  Collection<User>  GetUsers(string numberOfRows,string pageIndex,string sortColumnName, string sortOrderBy,out int totalRecords)
    {
        Collection<User> users = new Collection<User>();
                string connectionString =
        "Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = "SelectjqGridUsers";
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter paramPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
                paramPageIndex.Value =Convert.ToInt32(pageIndex);
                command.Parameters.Add(paramPageIndex);

                SqlParameter paramColumnName = new SqlParameter("@SortColumnName", SqlDbType.VarChar, 50);
                paramColumnName.Value = sortColumnName;
                command.Parameters.Add(paramColumnName);

                SqlParameter paramSortorderBy = new SqlParameter("@SortOrderBy", SqlDbType.VarChar, 4);
                paramSortorderBy.Value = sortOrderBy;
                command.Parameters.Add(paramSortorderBy);

                SqlParameter paramNumberOfRows = new SqlParameter("@NumberOfRows", SqlDbType.Int);
                paramNumberOfRows.Value =Convert.ToInt32(numberOfRows);
                command.Parameters.Add(paramNumberOfRows);

                SqlParameter paramTotalRecords= new SqlParameter("@TotalRecords", SqlDbType.Int);
                totalRecords = 0;
                paramTotalRecords.Value = totalRecords;
                paramTotalRecords.Direction = ParameterDirection.Output;
                command.Parameters.Add(paramTotalRecords);                                                                
                
                
                connection.Open();
                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    User user;
                    while (dataReader.Read())
                    {
                        user = new User();
                        user.UserID = (int) dataReader["UserID"];
                        user.UserName = Convert.ToString(dataReader["UserName"]);
                        user.FirstName = Convert.ToString(dataReader["FirstName"]);
                        user.MiddleName = Convert.ToString(dataReader["MiddleName"]);
                        user.LastName = Convert.ToString(dataReader["LastName"]);
                        user.EmailID = Convert.ToString(dataReader["EmailID"]);
                        users.Add(user);
                    }
                }
                totalRecords = (int)paramTotalRecords.Value;
            }
            
            return users;
        }

    }
    public bool IsReusable
    {
        // To enable pooling, return true here.
        // This keeps the handler in memory.
        get { return false; }
    }
}