Jquery Easyui插件Datagrid获取后台mssql数据通过存储过程高效分页来获取

最新推荐文章于 2019-08-12 18:26:06 发布

myredhatlinux

最新推荐文章于 2019-08-12 18:26:06 发布

阅读量3.6k

点赞数

分类专栏： Net -- Web Jquery 开发文章标签： datagrid 存储 jquery dataset string stylesheet

本文链接：https://blog.csdn.net/myredhatlinux/article/details/6054525

版权

Jquery 开发同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

Net -- Web

1 篇文章 0 订阅

订阅专栏

前台Html-------Easyui Datagrid

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <title></title>
    <link rel="stylesheet" type="text/css" href="../themes/default/easyui.css"/>
<link rel="stylesheet" type="text/css" href="../themes/icon.css"/>
<script type="text/javascript" src="../jquery/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="../jquery/jquery.easyui.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $('#tt').datagrid({
                url: '../GetJson.ashx',
                title: '药品目录',
                width: 600,
                height: 300,
                fitColumns: true,
                loadMsg: '系统加载中了.....',
                frozenColumns: [[
                 { field: 'ck', checkbox: true },
                 { title: '药品编号', field: '药品编号', width: 80,sortable: true }
    ]],
                columns: [[
     { field: '药品名称', title: '药品名称', width: 120 },
     { field: '规格', title: '规格', width: 50, align: 'right' },
     { field: '单位', title: '单位', width: 50, align: 'right' },
     { field: '剂型', title: '剂型', width: 60 },
     { field: '打印时产地', title: '产地', width: 150, align: 'center' }
    ]],
                pagination: true,
                rownumbers:true
            });
        });

    </script>
</head>
<body>
<table id="tt"></table>
</body>
</html>

中间层用ashx文件来获取mssql数据

<%@ WebHandler Language="C#" Class="GetJson" %>
using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class GetJson : IHttpHandler {
    public void ProcessRequest (HttpContext context) {
        context.Response.ContentType = "text/plain";
        //获取由前台Datagrid以post送出的参数 page为当前页数,rows为每页条目
        string now_page = context.Request["page"];
        string now_rows=context.Request["rows"];
        //返回的数据
        string result = "";
        //以下为数据库访问层调用存储过程的参数
        string strWhere = string.Empty; //查询条件
        int PageSize = Convert.ToInt16(now_rows); //页面条目
        int CurPage = Convert.ToInt16(now_page);    //当前页
        int Count;//总记录数
        //查询数据
        DataSet dt = DBUtility.DbHelperSQL.m_QueryPagination("ypsz", "*", strWhere, "药品编号", PageSize, CurPage, out Count);
        DataTable rows = dt.Tables[0];
        //数据表转换为easyui的Datagrid Json数据格式
        result = DBUtility.DbHelperSQL.CoverDataTableToJson(rows, Count);
        //发送数据给客户端
        context.Response.Write(result);
    }
    public bool IsReusable {     get {        return false;    }   }
}

数据库访问层

/// <summary>
        /// 存储过程分页方法
        /// </summary>
        /// <param name="tb_name">要查询的表名称</param>
        /// <param name="select_field">显示的字段</param>
        /// <param name="where_str">查询条件</param>
        /// <param name="sort_str">排序字段</param>
        /// <param name="pagesiz">每页数据条目</param>
        /// <param name="page">当前页数</param>
        /// <param name="Rowcount">输出总记录条目数</param>
        /// <returns>返回DataSet</returns>
        public static DataSet m_QueryPagination(string tb_name, string select_field, string where_str, string sort_str, int pagesiz, int page, out int Rowcount)
        {
            SqlParameter[] mypar = {
            new SqlParameter("@tblName",SqlDbType.Char,20),
            new SqlParameter("@SelectFieldName",SqlDbType.Char,100),
            new SqlParameter("@strWhere",SqlDbType.Char,200),
            new SqlParameter("@OrderFieldName",SqlDbType.Char,100),
            new SqlParameter("@PageSize",SqlDbType.Int,100),
            new SqlParameter("@PageIndex",SqlDbType.Int,100),
            new SqlParameter("@iRowCount",SqlDbType.Int,100),
            new SqlParameter("@OrderType",SqlDbType.Bit,1)};
            mypar[0].Value = tb_name;
            mypar[1].Value = select_field;
            mypar[2].Value = where_str;
            mypar[3].Value = sort_str;
            mypar[4].Value = pagesiz;
            mypar[5].Value = page;
            mypar[6].Direction = ParameterDirection.Output;
            mypar[7].Value = 1;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("AspNetPage", mypar, "mytb");
            Rowcount = Convert.ToInt32(mypar[6].Value.ToString());
            return ds;
        }

/// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

分页存储过程

USE [tejia]
GO
/****** Object: StoredProcedure [dbo].[AspNetPage]    Script Date: 12/04/2010 11:38:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure   [dbo].[AspNetPage]
    @tblName         varchar(1000),        -- 表名
    @SelectFieldName    varchar(4000),     -- 要显示的字段名(不要加select)
    @strWhere       varchar(4000),       -- 查询条件(注意: 不要加 where)
    @OrderFieldName     varchar(255),     -- 排序索引字段名
    @PageSize       int ,                  -- 页大小
    @PageIndex      int = 1,               -- 页码
    @iRowCount      int output,            -- 返回记录总数
    @OrderType      bit = 0                -- 设置排序类型, 0 值则升序,1为降序

AS
declare @strSQL    varchar(4000)       -- 主语句
declare @strTmp    varchar(4000)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型
declare @strRowCount    nvarchar(4000)      -- 用于查询记录总数的语句
--去掉排序字段的空格
set @OrderFieldName=ltrim(rtrim(@OrderFieldName))
--如果降序
if @OrderType != 0
begin
  set @strTmp = '<(select min'
  set @strOrder = ' order by ' + @OrderFieldName +' desc'
end
--如果升序
else
begin
  set @strTmp = '>(select max'
  set @strOrder = ' order by ' + @OrderFieldName +' asc'
end
--查询主语句
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from ' + @tblName + ' where ' + @OrderFieldName + @strTmp + '('
  + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
  + @OrderFieldName + ' from ' + @tblName + @strOrder + ') as tblTmp)' + @strOrder
--如果条件不为空
if @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from ' + @tblName + ' where ' + @OrderFieldName + @strTmp + '('
        + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
        + @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
--如果页面为1
if @PageIndex = 1
begin
  set @strTmp = ''
  if @strWhere != ''
   set @strTmp = ' where ' + @strWhere
   set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from ' + @tblName + @strTmp + ' ' + @strOrder
end
--执行语句
exec(@strSQL)
--如果条件不为空
if @strWhere!=''
begin
  set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName+' where '+@strWhere
end
else
begin
  set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName
end
--执行语句
exec sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out

myredhatlinux

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
3
评论
Jquery Easyui插件Datagrid获取后台mssql数据通过存储过程高效分页来获取

<br />前台Html-------Easyui Datagrid<br /> <br /><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><br /><html xmlns="http://www.w3.org/1999/xhtml"><br /><head><br /> <meta http-equiv=
复制链接

扫一扫

专栏目录