ASP.NET中使用jQGrid
来源：互联网 发布：举几个截面数据的例子 编辑：程序博客网 时间：2024/06/05 00:58
按下面步骤一步一步操作即可完成效果，效果图：
第一步：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 ENDGOCREATE 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 OUTPUTAS 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; }}阅读全文
0 0
- ASP.NET中使用jQGrid
- ASP.NET中使用jQGrid
- ASP.NET中使用JqGrid完整实现
- ASP.NET中使用JqGrid完整实现
- jqGrid asp.net mvc 使用
- ASP.NET中使用jqGrid的完整实例及总结
- 在Asp.net MVC使用jqGrid
- JQGrid与ASP.NET
- 关于 ASP.net + JQGrid 的分页
- JQgrid for asp.net 不完全手记
- Asp.net+jQuery+jqGrid做的论坛
- asp.net中jqgrid根据不同类别头部显示不同的列的实现方法
- 对asp.net mvc 下使用JQGrid的一些个人想法
- jqGrid中事件的使用
- jqsuite 中使用jqgrid参数
- 项目中使用的jqGrid
- ASP.NET中使用Caching
- asp.net 中使用sqlcommandbuilder
- 680
- 搭建一个ssh框架的过程
- IGBT是什麽
- 理解dbutils对jdbc的封装
- echarts画k线图（折线）
- ASP.NET中使用jQGrid
- 悬挂式数字麦克风阵列 多点视频会议
- 一个完整的线上异常捕获类
- 源码阅读---Activity生命周期控制
- 【MongoDB】复合索引
- 对拍
- 使用xxl-job调度平台时，启动执行器工程报错：unknown code for readObject at 0x3c (<)
- 5.Linux内核
- 观察者模式
