正文

jquery.datatable插件从数据库读取数据

IP云
IP云V管理员/5 阅读
08/21
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一、分页

分页的基本思想是根据datatable的页码及每页显示的行数，将数据从数据库分段提出，然后再填充到表格中，以达到分页的效果。

这里需要用到datatable插件的几个属性：

"sEcho"：这个属性需要原封不动地传回给datatable，具体的作用我也不清楚，但是根据它值的变化情况来看，好像是一个操作次数的计数（之前我一直把它当做是pageindex来用，结果发现，不论我在datatable中是翻下一页还是翻上一页，它一直在增加。）

"iDisplayStart"：这个属性，根据字面意思理解，就是每段数据开始的行数，比如第一页的数据就是从0开始计，那么它就是0，每页显示的行数是10，那么第二页的第一行的iDisplayStart就是10。

"iDisplayLength"：这个属性就是每页显示的行数。

然后是数据库操作，只需要从数据库查询其中一段数据，然后输出出来，转成JSON格式，让datatable插件获取。在网上可以找到很多分页的方法，我选择了其中一种，使用row_number()的分页的存储过程。具体代码如下(根据sql创建存储过程模板）：

USE T_LOG
GO
/****** 对象: StoredProcedure [dbo].[pagination] 脚本日期: 07/30/2013 09:37:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[pagination]
(
@pageIndex int, --页索引
@pageSize int --每页记录数
)
as
begin
set nocount on;
declare @sql nvarchar()
set @sql='SELECT LOG_ID,USER_ID,TABLE_NAME
FROM (
SELECT t.*,ROW_NUMBER()OVER(ORDER BY t.LOG_ID) AS rownum
FROM T_LOG t
) AS a
WHERE rownum BETWEEN ('+str(@pageSize)+' * ('+str(@pageIndex)+' - )) + AND '+str(@pageSize)+' * '+str(@pageIndex)+'' execute(@sql)
set nocount off;
end

存储过程的两个参数，pageindex表示页索引即当前页码，我不懂datatable有没有这项属性，所以我是用计算的方法得来的，就是iDisplayStart/iDisplayLength+1。

pagesize可以直接从datatable获得。

服务端的代码，我创建了一个datasource.ashx文件，代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;namespace WebApplication1
{
/// <summary>
/// Handler1 的摘要说明
/// </summary>
public class Handler1 : IHttpHandler
{ public void ProcessRequest(HttpContext context)
{
string sEcho = context.Request.Form["sEcho"];
int iDisplayStart;
int iDisplayLength;
int.TryParse(context.Request.Form["iDisplayStart"], out iDisplayStart);
int.TryParse(context.Request.Form["iDisplayLength"], out iDisplayLength);
int pageindex = iDisplayStart / iDisplayLength + ;
int count = getcount();
//DataTableToObjects类中的DataTableToJson()方法用来将数据转成json数据格式
string json = DataTableToObjects.DataTableToJson(int.Parse(sEcho),count,getJson(pageindex.ToString(),iDisplayLength.ToString()),false);
context.Response.Write(json); } public bool IsReusable
{
get
{
return false;
}
} public static DataTable getJson(string pageindex, string iDisplayLength) //执行存储过程，提出数据
{
string constr = "此处填写数据库连接字段";
using (SqlConnection conn = new SqlConnection(constr))
{
string sqlstr = "DECLARE @pageIndex int " +
"DECLARE @pageSize int " +
"EXECUTE pagination " + pageindex + "," + iDisplayLength;
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sqlstr, conn);
da.Fill(ds);
DataTable dt = ds.Tables[];
return dt;
} }
public int getcount() //获取数据总行数，iTotalRecords参数需要
{
string constr = "此处填写数据库连接字段";
string countstr = "select count(1) from T_LOG";
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
SqlCommand com = new SqlCommand(countstr, conn);
object obj = com.ExecuteScalar();
conn.Close();
return int.Parse(obj.ToString()); }
}
}
}

DataTableToObjects类的代码如下：

using System;
using System.Collections.Generic;
using System.Web;
using System.Text;
using System.Data;namespace WebApplication1
{
public class DataTableToObjects
{
public static string DataTableToJson(int sEcho, int totalRow, DataTable dt, bool dt_dispose)
{
StringBuilder json = new StringBuilder();
json.Append("{\"sEcho\":" + sEcho.ToString() + ",");
json.Append("\"iTotalRecords\":" + totalRow.ToString() + ",");
json.Append("\"iTotalDisplayRecords\":" + totalRow.ToString() + ",");
json.Append("\"aaData\":[");
//json.AppendFormat("{\"sEcho\":{0},\n \"iTotalRecords\":{1},\n \"iTotalDisplayRecords\": {2},\n \"aaData\":[", sEcho, totalRow, totalRow); for (int i = ; i < dt.Rows.Count; i++)
{
json.Append("{");
for (int j = ; j < dt.Columns.Count; j++)
{
json.Append("\"");
json.Append(dt.Columns[j].ColumnName);
json.Append("\":\"");
json.Append(dt.Rows[i][j].ToString());
json.Append("\",");
}
json.Remove(json.Length - , );
json.Append("},");
}
json.Remove(json.Length - , );
json.Append("]}"); return json.ToString();
} } }

需要注意的一点：iTotalRecords与iTotalDisplayRecords是两个不同的值，是改变分页栏显示用的参数，这里因为没有考虑数据过滤功能，所以都设置成数据的总的行数。

然后是客户端的代码，与之前的差不多：

<%@ Page Language="C#" %><!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 id="Head1" runat="server">
<title></title>
<link rel="stylesheet" type="text/css" href="Styles/jquery.dataTables.css"/>
<script type="text/javascript" charset="utf8" src="Scripts/jquery.js"></script>
<script type="text/javascript" charset="utf8" src="Scripts/jquery.dataTables.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#example").dataTable({
"bLengthChange": false,
"bFilter": false,
"bSort": false,
"iDisplayLength": ,
//"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "Handler1.ashx",
"sServerMethod": "POST",
"aoColumns": [
{ "mData": "LOG_ID" },
{ "mData": "USER_ID" },
{ "mData": "TABLE_NAME" }
]
//"fnServerParams": function (aoData) {
// aoData.push({ "name": "name1", "value": "value1" });
// aoData.push({ "name": "name2", "value": "value2" });
//}
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<table id="example" width="100%" border="" cellspacing="" cellpadding="">
<thead>
<tr>
<td>LOG_ID</td>
<td>USER_ID</td>
<td>TABLE_NAME</td>
</tr>
</thead>
</table>
</form></body>
</html>