研究了两天FlexiGird 和jqgrid。终于实现了jqgrid 。FlexiGird 的数据总是显示不出来,最后放弃,选择了jqgrid。
网上找来很多资料看才弄懂。
来个简单的dome吧:
1,首先得下载相关jqgrid的必须文件:jquery-1.4.4.min.js,grid.locale-en.js,jquery.jqGrid.src.js (我会放到我的下载里面,可以去下)
2,ok,可以开始了。先建立一个jqGird.htm.html文件吧。注意引入上面几个文件的路径,根据实际改下吧。代码:
<!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>
<title>hooi</title>
<link href="css/ui.jqgrid.css" rel="stylesheet" type="text/css" />
<link href="css/17jqgrid.css" rel="stylesheet" type="text/css" />
<!--以下是JS引用部分,这里的顺序不能变变了会显示不出数据-->
<script src="js/jquery-1.4.4.min.js" type="text/javascript"></script>
<script src="js/grid.locale-en.js" type="text/javascript"></script>
<script src="js/jquery.jqGrid.src.js" type="text/javascript"></script>
</head>
<body>
<br />
<br />
<table id="list" cellpadding="0" cellspacing="0">
<!--用于数据显示-->
</table>
<div id="pager" style="text-align: center;">
<!--用于分页的层-->
</div>
<br />
<script type="text/javascript">
$("#list").jqGrid({
url: "GridData.ashx",//地址文件
datatype: "json",
colNames: ['用户ID', '登陆名', '描述','sm'],//这里是显示的数据库表的字段名称
colModel: [
{ name: 'iUSERID', index: 'iUSERID', width: 50 },
{ name: 'sUserName', index: 'sUserName', width: 100 },
{ name: 'sQuestion', index: 'sQuestion', width: 300 },
{ name: 'dtLastLoginTime', index: 'dtLastLoginTime', width: 120 },
],//这里是数据读取到的字段列名
rowNum: 10,
rowList: [10, 20, 30],
pager: jQuery('#pager'),
sortname: 'id',
viewrecords: true,
sortorder: "desc",
caption: "汇总查询",
height:'230px',
})
.navGrid("#pager", { edit: false, add: false, del: false });
</script>
</body>
</html>
2,建立一个GridData.ashx文件,代码:
<%@ WebHandler Language="C#" Class="Handler2" %>
using System;
using System.Web;
using System.Data;
using System.Web.Script.Serialization;
public class Handler2 : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
context.Response.Buffer = true;
context.Response.ExpiresAbsolute = DateTime.Now.AddDays(-1);
context.Response.AddHeader("pragma", "no-cache");
context.Response.AddHeader("cache-control", "");
context.Response.CacheControl = "no-cache";
//定义数据记取的字符串,这里我是用的SQLHELPER直接读的
//数据表的你可以随便建立
string strSql = "SELECT * FROM tree";
//数据存入一个DATATABLE
DataTable dt = DBHelp.GetDataSet(strSql);//读取数据 这个方法在下面3的代码中
JavaScriptSerializer serializer = new JavaScriptSerializer();
//转成json数据,这里是关键的函数此方法我是放入的一个JSON操作类中的
string jsonData = JsonHandle.GetJson(dt);
context.Response.Write(jsonData);//返回json数据
//反正数据读取一块自己定义
}
public bool IsReusable
{
get
{
return false;
}
}
}
3,还有一个获取数据的方法类,就自己写吧。也贴下我的,以后我自己也好找(其实就是一个SQLHELP):
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
public class DBHelp
{
public static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["HNGH"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
#region 增,删,改ExecuteNonQuery
/// <summary>
/// 单个数据增,删,改
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static int ExecuteCommand(string safeSql)
{
try
{
using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
{
int result = cmd.ExecuteNonQuery();
return result;
}
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region 带参数的增,删,改ExecuteNonQuery
/// <summary>
/// 带多个参数的增,删,改
/// </summary>
/// <param name="safeSql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int ExecuteCommand(string safeSql, params SqlParameter[] values)
{
try
{
using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
{
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region 带参数的增,删,改ExecuteNonQuery
/// <summary>
/// 带多个参数的增,删,改
/// </summary>
/// <param name="safeSql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int ExecuteCommand(string safeSql, CommandType type, params SqlParameter[] values)
{
try
{
using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
{
cmd.CommandType = type;
cmd.Parameters.AddRange(values);
cmd.ExecuteNonQuery();
return cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region 带参数的增,删,改ExecuteNonQuery (Special)
/// <summary>
/// 带多个参数的增,删,改
/// </summary>
/// <param name="safeSql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int ExecuteCommand(string safeSql, CommandType type, int index)
{
try
{
using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
{
cmd.CommandType = type;
SqlParameter paramOne = new SqlParameter("@rid", SqlDbType.Int);
paramOne.Value = index;
cmd.Parameters.Add(paramOne);
return cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region 查询语句ExecuteScalar
/// <summary>
/// 查单个值
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static int GetScalar(string safeSql)
{
try
{
using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
{
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region 带参数的查询语句ExecuteScalar
/// <summary>
/// 带参数的查询语句
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int GetScalar(string sql, params SqlParameter[] values)
{
try
{
using (SqlCommand cmd = new SqlCommand(sql, Connection))
{
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region 带执行类型的ExecuteScalar
/// <summary>
/// 带执行类型的ExecuteScalar
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int GetScalar(string sql, CommandType type, params SqlParameter[] values)
{
try
{
using (SqlCommand cmd = new SqlCommand(sql, Connection))
{
cmd.CommandType = type;
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region 返回DataReader
/// <summary>
/// 查询表,获取多个记录
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string safeSql)
{
try
{
using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
{
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region 带参数DataReader
/// <summary>
/// 带参数的-查询表,获取多个记录
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
try
{
using (SqlCommand cmd = new SqlCommand(sql, Connection))
{
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
catch (SqlException)
{
throw;
}
}
#endregion
#region 返回DataReader ,语句,类型,参数
/// <summary>
/// 查询表,获取多个记录---语句,类型,参数
/// </summary>
/// <param name="safeSql"></param>
/// <param name="cmdType"></param>
/// <param name="values"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string safeSql, CommandType cmdType, params SqlParameter[] values)
{
try
{
using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
{
cmd.CommandType = cmdType;
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region 返回dataTable
/// <summary>
/// 返回datatable
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
#endregion
#region 返回dataTable ,带参数
/// <summary>
/// 返回dataTable ,带参数使用
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
#endregion
}
补一个类
JSonHandle类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Collections;
using System.ServiceModel.Web;
namespace JQGridDemo
{
public class JsonHandle
{
public JsonHandle() { }
public static string GetJson(DataTable dt)
{
StringBuilder jsonBuilder = new StringBuilder();
//jsonBuilder.Append("\"");
jsonBuilder.Append("{\"page\":1,\"total\":" + dt.Rows.Count + ",\"records\":" + dt.Rows.Count + ",\"rows\"");
jsonBuilder.Append(":[");
for (int i = 0; i < dt.Rows.Count; i++)
{
jsonBuilder.Append("{\"id\":" + dt.Rows[i]["iUSERID"].ToString() + ",\"cell\":[");
for (int j = 0; j < dt.Columns.Count; j++)
{
//jsonBuilder.Append("\"");
//jsonBuilder.Append(dt.Columns[j].ColumnName);
jsonBuilder.Append("\"");
jsonBuilder.Append(dt.Rows[i][j].ToString());
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return jsonBuilder.ToString();
}
}
}
3个步骤,完了。运行jqGird.htm看效果吧。