ASP.NET中使用jQGrid

最新推荐文章于 2019-08-27 16:12:21 发布

weixin_34128501

最新推荐文章于 2019-08-27 16:12:21 发布

阅读量136

点赞数

文章标签： javascript c# ui ViewUI

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

第一步：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 5003.编写分页时的查询存储过程：

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 href="css/ui.jqgrid.css" rel="stylesheet" type="text/css" /> <!--注：这里的css去http://www.trirand.com/blog/下载即可-->

第三步：处理程序：

<%@ 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; } }原文地址： http://codeasp.net/articles/asp-net/229/using-jqgrid-with-asp-net 打包下载

weixin_34128501

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ASP.NET中使用jQGrid

按下面步骤一步一步操作即可完成效果，效果图：第一步：SQL构造测试数据1.创建一个产生随机数据的SQL函数：CREATE FUNCTION [GenerateRandomName] ( @LENGTH INT )RETURNS NVARCHAR(255)AS BEGIN --DECLARE VARIABLES DECLARE @Rand...
复制链接

扫一扫