文档章节

ASP.NET中使用jQGrid

清山博客
 清山博客
发布于 2016/07/15 20:03
字数 1058
阅读 12
收藏 0

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


第一步: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去http://www.trirand.com/blog/下载即可-->
    <script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>  <!--注:这里的js去http://www.trirand.com/blog/下载即可-->
    <link href="css/ui.jqgrid.css" rel="stylesheet" type="text/css" /> <!--注:这里的css去http://www.trirand.com/blog/下载即可-->
  <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; }
}
原文地址:http://codeasp.net/articles/asp-net/229/using-jqgrid-with-asp-net 打包下载



本文转载自:http://blog.csdn.net/a497785609/article/details/7075290

清山博客
粉丝 3
博文 142
码字总数 30397
作品 0
广州
私信 提问
框架page与JQgrid冲突

问题是这个样子的,ASP.NET后台继承框架page,page主要输出了一串JS代码,一个login的div,还有通过登录用户判断样式地址的link标签(登录后数据库取出存session里)。jqgrid for asp.net 要...

shihahayue
2012/11/05
0
0
jQGrid 导出 PDF

介绍 本文意为解决客户端网格框架 PDF 导出问题。解决方案是使用 Visual Studio 2012 和 ASP.NET MVC 4 的 iTextSharp。 jQGrid 是一种基于jQuery 框架的客户端网格框架。它提供了分页、排序...

oschina
2013/06/18
2.1K
0
解决jqGrid新增或编辑记录保存成功但提示错误的问题

在上一篇文章《》中,我们详细说明了一下如何创建一个可以使用增删改操作的jqGrid。 但是在实际的修改、新增保存中,会看到如下的错误提示:error Status:"OK".Error code: 900。实际上,修改...

yonge
2009/12/14
5.8K
1
jfinal与jqgrid结合实例。

直接上代码: index.html <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Insert title here</title> </head> <body> <link rel="stylesheet" ......

控制自己
2013/01/05
0
8
jQuery表格插件jqgrid加载数据的问题

最近的项目使用jqgrid来显示数据, 遇到个问题, 需求:页面初始化时表格是空的,只要显示列明就好,这个通过不指定url实现了。 加载数据是跟用用户输入到DB查询,返回数据集。尝试手动将数据...

jay_
2012/01/20
3.7K
1

没有更多内容

加载失败,请刷新页面

加载更多

面向对象方面的一些东东

<!DOCTYPE html> <html lang="zh"> <head> <meta charset="UTF-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <meta http-equiv="X-UA-Compatible" cont......

流年那么伤
24分钟前
2
0
git 过滤文件夹和文件(IDEA)

最近使用git版本管理工具遇到一件很烦的事情,commit时总会把.idea、.*.iml和target文件添加进来,实际开发中,这些是需要过滤掉的。在.gitnore文件添加了过滤不起作用。下面介绍一种成功过滤...

uug
32分钟前
2
0
Spark Streaming 实时统计商户当日累计PV流量

一、问题 对实时流量日志过滤筛选商户流量,对每个商户的流量进行累计,统计商户实时累计流量。 当时间超过24时时,重新统计当日商户的实时累计流量。 二、实现步骤 1、采用Spark Streaming...

boonya
36分钟前
2
0
如果Task与Event 创建了记录类型后,不出现在Lightning的Activities中

如果在Lightning的Activities没出现这两个Button,但是在页面布局的Lightning 按钮区也存在,全局操作的记录类型就需要选择一个,否则不会出现

在山的那边
45分钟前
2
0
ddd中的子域和界限上下文

我们先来说说子域是什么?子域在我的理解是在一个庞大的系统中可以明显感知的不同的区块,如果在电商模块中,商品目录,订单,物流,库存,发票等等都可以感知他们明显的不同,可以认为是子域...

算法之名
55分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部