ASP.NET中使用jQGrid

2017-01-05 11:12:39来源:oschina作者:清山博客人点击

按下面步骤一步一步操作即可完成效果,效果图:



第一步: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 = ( SELECTRandNumber
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 @RandomNumberEND
GO
CREATE VIEW [RandNumberView]
AS
SELECTRAND() AS [RandNumber]2.创建测试表并写入测试数据:


CREATE TABLE [Users]
(
[UserID] INT IDENTITY ,
[UserName] NVARCHAR(50),
[FirstName] NVARCHAR(50),
[LastName] NVARCHAR(50),
[MiddleName] NVARCHAR(50),
[EmailID] NVARCHAR(50)
)
GOINSERT 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 5003.编写分页时的查询存储过程:


CREATE PROC [SelectjqGridUsers]
@PageIndex INT ,
@SortColumnName VARCHAR(50) ,
@SortOrderBy VARCHAR(4) ,
@NumberOfRows INT ,
@TotalRecords INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
SELECT@TotalRecords = ( SELECTCOUNT(1)
FROM[Users]
)
DECLARE @StartRow INT
SET @StartRow = ( @PageIndex * @NumberOfRows ) + 1 ;


WITHCTE
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]
)
SELECTUserID ,
UserName ,
FirstName ,
LastName ,
MiddleName ,
EmailID
FROMCTE
WHERE RN BETWEEN @StartRow - @NumberOfRows
AND @StartRow - 1 SET NOCOUNT OFFEND第二步:aspx页面:

1.HTML DOM:










2.脚本及样式表引用:



<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>
<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>
<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 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 users,int numberOfRows, int pageIndex,int totalRecords)
{
JQGridResults result = new JQGridResults();
List rows = new List();
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);
}
privateCollectionGetUsers(string numberOfRows,string pageIndex,string sortColumnName, string sortOrderBy,out int totalRecords)
{
Collection users = new Collection();
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; }
}原文地址:http://codeasp.net/articles/asp-net/229/using-jqgrid-with-asp-net打包下载


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台