按下面步骤一步一步操作即可完成效果,效果图:
第一步: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', width: 100, sortable: true },
{ name: 'UserName', width: 100, sortable: true },
{ name: 'FirstName', width: 100, sortable: true },
{ name: 'MiddleName', width: 100, sortable: true },
{ name: 'LastName', width: 100, sortable: true },
{ name: 'EmailID', width: 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; }
}
}
相关的实体类:
public struct JQGridResults
{
public int page;
public int total;
public int records;
public JQGridRow[] rows;
}
public struct JQGridRow
{
public int id;
public string[] cell;
}
[Serializable]
public class User
{
public int UserID
{ get; set; }
public string UserName
{ get; set; }
public string FirstName
{ get; set; }
public string MiddleName
{ get; set; }
public string LastName
{ get; set; }
public string EmailID
{ get; set; }
}