Flexigrid系列使用教程（二）JSON数据生成代码

摘要：上一篇简单的介绍了一下前台页面代码部分，本文主要说一下如何为flexigrid提供前台数据，主要提供了将后台数据库里面的数据转换成Json格式后交由前台显示。

????? 前台页面中调用后台的代码是这样的：/BDeviceApply/GetSysSdandeviceFlex，这是在.NET MVC框架的调用方式，本人接触.net开发时间不长，所以对这点不是很清楚，最近又搞.net framework框架下开发，写法又和这个不一样，由于对.net技术领域了解的甚少，也浪费了很多时间，最后本文会给出在.net framework下面的相关代码。

?????? .NET MVC生成Json数据的代码主要包括数据库存储过程、调用执行存储过程的类库、JSON数据转换方法等几个，数据库存储过程主要实现分页功能。

一、??? 数据库存储过程代码

?????? 本人在开发中常用的Oracle和Sql Server数据库，把两个数据库下的方法都列在下面。

1、 Oracle数据分页存储过程

A、首先建立一个包，用户创建一个游标类型

create or replace package pkg_query as

? type cur_query is ref cursor;

end pkg_query;

B、创建存储过程

create or replace

PROCEDURE prc_query

?????? (p_tableName??????? in? varchar2,?? --表名

??????? p_strWhere???????? in? varchar2,?? --查询条件

??????? p_fileds?????????? in? varchar2,?? --字段列表

??????? p_orderColumn????? in? varchar2,?? --排序的列

??????? p_orderStyle?????? in? varchar2,?? --排序方式

??????? p_curPage????????? in out Number,? --当前页

??????? p_pageSize???????? in out Number,? --每页显示记录条数

??????? p_totalRecords???? out Number,???? --总记录数

??????? p_totalPages?????? out Number,???? --总页数

??????? v_cur????????????? out pkg_query.cur_query)?? --返回的结果集

IS

?? v_sql VARCHAR2(1000) := '';????? --sql语句

?? v_startRecord Number;???????? --开始显示的记录条数

?? v_endRecord Number;?????????? --结束显示的记录条数

BEGIN

?? --记录中总记录条数

?? v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName || ' WHERE 1=1';

?? IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN

?????? v_sql := v_sql || p_strWhere;

?? END IF;

?? EXECUTE IMMEDIATE v_sql INTO p_totalRecords;

??

?? --验证页面记录大小

?? IF p_pageSize < 0 THEN

?????? p_pageSize := 0;

?? END IF;

??

?? --根据页大小计算总页数

?? IF MOD(p_totalRecords,p_pageSize) = 0 THEN

?????? p_totalPages := p_totalRecords / p_pageSize;

?? ELSE

?????? p_totalPages := p_totalRecords / p_pageSize + 1;

?? END IF;

??

?? --验证页号

?? IF p_curPage < 1 THEN

?????? p_curPage := 1;

?? END IF;

?? IF p_curPage > p_totalPages THEN

?????? p_curPage := p_totalPages;

?? END IF;

??

?? --实现分页查询

?? v_startRecord := (p_curPage - 1) * p_pageSize + 1;

?? v_endRecord := p_curPage * p_pageSize;

?? v_sql := 'SELECT ' || p_fileds ||' FROM (SELECT A.*,rownum r FROM ' ||

??????????? '(SELECT * FROM ' || p_tableName;

?? IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN

?????? v_sql := v_sql || ' WHERE 1=1' || p_strWhere;

?? END IF;

?? IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN

?????? v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle;

?? END IF;

?? v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= '

??????????? || v_startRecord;

?? DBMS_OUTPUT.put_line(v_sql);

?? OPEN v_cur FOR v_sql;

END prc_query;

2、 SQL Server数据库下的分页存储过程

CREATE PROCEDURE [dbo].[GetProList] --存储过程名

??? ?@Tablename??? nvarchar(4000),

???? @WhereClause??? nvarchar(4000),

??? ?@Fields??? nvarchar(4000),

???? @SortExpression??? nvarchar(128),

???? @RowIndex??????? int,

???? @NoOfRows??????? int,

??? ?@TotalRecord???? int output??????????? --return recordNum

?

? AS

? BEGIN

?

? DECLARE @SQL nvarchar(4000)

?

? IF (@WhereClause != '')

?BEGIN

???? SET @WhereClause = char(13) + @WhereClause

?END

?

?IF (@SortExpression != '')

?BEGIN

???? SET @SortExpression = 'ORDER BY ' + @SortExpression

?END

?

?IF (@Fields != '')

?BEGIN

???? SET @Fields = @Fields

?END

?

SET @SQL = 'WITH ProjectRows AS (

???????????????????????? SELECT ROW_NUMBER() OVER ('+ @SortExpression +')AS Row,'+@Fields+???????????????????????????????????????????

???????????????? ' FROM '+@Tablename+ ' where '+ @WhereClause +') SELECT '+@Fields+ ' FROM ProjectRows WHERE Row between '

+ CONVERT(nvarchar(10),@RowIndex) +'And ('+ CONVERT(nvarchar(10),@RowIndex) +' + '+ CONVERT(nvarchar(10),@NoOfRows) +')'

?EXEC sp_executesql @SQL

?

?SET @SQL = 'SELECT @iRet=COUNT([ID])

???????????????? FROM '+@Tablename+'

???????????????? ' + ' where '+@WhereClause

?declare @Ret? int

?EXEC sp_executesql @SQL,N'@iRet int OUTPUT',@TotalRecord? OUTPUT

?

? return @TotalRecord

?-- EXEC sp_executesql @SQL67

?END

二、??? 调用数据库存储过程方法

?????? 本文主要采用NET MVC框架讲解，调用存储过程的方法都写得了M层，M层在本文中主要用动软.Net代码生成器实现的，然后再次基础上自己改写。调用执行Sql Server和Oracle存储过程的方法分别如下：

A．? Oracle存储过程调用执行方法如下：

??????? public DataSet GetListByPage(string tablename,string whereCondition,string fields,string sortname,string sortorder,int startRowIndex,int numberOfRows,out int count)

??????? {

??????????? DataSet ds = new DataSet();

?

??????????? const string SP = "prc_query";

?

????? ??????OracleParameter[] parameters = {

???????????????????????????? new OracleParameter("p_tableName",OracleType.VarChar,2000),

???????????????????????????? new OracleParameter("p_strWhere",

???????????????????????????? new OracleParameter("p_fileds",

???????????????????????????? new OracleParameter("p_orderColumn",

???????????????????????????? new OracleParameter("p_orderStyle",

???????????????? ????????????new OracleParameter("p_curPage",OracleType.Number),

???????????????????????????? new OracleParameter("p_pageSize",

???????????????????????????? new OracleParameter("p_totalRecords",

???????????????????? ????????new OracleParameter("p_totalPages",

???????????????????????????? new OracleParameter("v_cur",OracleType.Cursor)

???????????????????????????? };

??????????? parameters[0].Value = tablename;

??????????? parameters[1].Value = whereCondition;

??????????? parameters[2].Value = fields;

??????????? parameters[3].Value = sortname;

??????????? parameters[4].Value = sortorder;

??????????? parameters[5].Value = startRowIndex;

??????????? parameters[6].Value = numberOfRows;

??????????? parameters[7].Direction = ParameterDirection.Output;

??????????? parameters[8].Direction = ParameterDirection.Output;

??????????? parameters[9].Direction = ParameterDirection.Output;

??????????? for (int i = 0; i < parameters.Length; i++)

??????????? {

?????? ?????????if (parameters[i].Value == null)

??????????????? {

??????????????????? parameters[i].Value = DBNull.Value;

??????????????? }

??????????? }

??????????? return DbHelperOra.RunProcedure(SP,parameters,"Baseinfo",out count);

??????? }

?//执行存储过程代码

?????????????????? /// <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,out int count)

?????????????????? {

??????????????????????????? using (OracleConnection connection = new OracleConnection(connectionString))

??????????????????????????? {

???????????????????????????????????? DataSet dataSet = new DataSet();

? ???????????????????????????????? connection.Open();

???????????????????????????????????? OracleDataAdapter sqlDA = new OracleDataAdapter();

???????????????????????????????????? //sqlDA.SelectCommand = BuildQueryCommand(connection,storedProcName,parameters);

?

??????????????? OracleCommand command = new OracleCommand(storedProcName,connection);

??????????????? command.CommandType = CommandType.StoredProcedure;

??????????????? foreach (OracleParameter parameter in parameters)

??????????????? {

??????????????????? command.Parameters.Add(parameter);

??????????????? }

??????????????? sqlDA.SelectCommand = command;

???????????????????????????????????? sqlDA.Fill( dataSet,tableName );

??????????????? count = Int32.Parse(parameters[7].Value.ToString());

???????????????????????????????????? connection.Close();

???????????????????????????????????? return dataSet;

??????????????????????????? }

?????????????????? }

?

B．Sql Server存储过程调用执行方法如下：

public DataSet GetListByPage(string tablename,out int count)

??????? {

??????????? DataSet ds = new DataSet();

?

??????????? const string SP = "GetProList";

?

??????????? SqlParameter[] parameters = {

???????????????????????????? new SqlParameter("Tablename",SqlDbType.VarChar,255),

???????????????????????????? new SqlParameter("WhereClause",

??????????????????????? ?????new SqlParameter("Fields",

???????????????????????????? new SqlParameter("SortExpression",

???????????????????????????? new SqlParameter("RowIndex",SqlDbType.Int),

???????????????????????????? new SqlParameter("NoOfRows",

???????????????????????????? new SqlParameter("TotalRecord",SqlDbType.Int)

???????????????????????????? };

??????????? parameters[0].Value = tablename;

??????????? parameters[1].Value = whereCondition;

??????????? parameters[2].Value = fields;

??????????? parameters[3].Value = sortname;

??????????? parameters[4].Value = startRowIndex;

??????????? parameters[5].Value = numberOfRows;

??????????? parameters[6].Direction = ParameterDirection.Output;

?

??????????? for (int i = 0; i < parameters.Length; i++)

??????????? {

??????????????? if (parameters[i].Value == null)

??????????????? {

??????????????????? parameters[i].Value = DBNull.Value;

??????????????? }

??????????? }

??????????? return DbHelperSQL.RunProcedure(SP,out count);

??????? }

??????? /// <summary>

??????? /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

??????? /// </summary>

??????? /// <param name="storedProcName">存储过程名</param>

??????? /// <param name="parameters">存储过程参数</param>

??????? /// <returns>SqlDataReader</returns>

public static DataSet RunProcedure(string storedProcName,out int count)

??????? {

??????????? using (SqlConnection connection = new SqlConnection(connectionString))

??????????? {

??????????????? DataSet dataSet = new DataSet();

??????????????? connection.Open();

??????????????? SqlDataAdapter sqlDA = new SqlDataAdapter();

??????????????? //sqlDA.SelectCommand = BuildQueryCommand(connection,parameters);

?

??????????????? SqlCommand command = new SqlCommand(storedProcName,connection);

??????????????? command.CommandType = CommandType.StoredProcedure;

??????????????? foreach (SqlParameter parameter in parameters)

??????????????? {

??????????????????? command.Parameters.Add(parameter);

??????????????? }

???????????? ???sqlDA.SelectCommand = command;

??????????????? sqlDA.Fill(dataSet,tableName);

??????????????? count = Int32.Parse(parameters[6].Value.ToString());

??????????????? //count = 100;

??????????????? connection.Close();

??????????????? return dataSet;

????? ??????}

??????? }

?

三、??? JSON数据格式转换方法

//前台页面调用的方法? ??

public String GetSysSdandeviceFlex ()

??????? {

??????????? int page = 1;

??????????? if (HttpContext.Request.Form["page"] != null)

??????????? {

??????????????? page = int.Parse(HttpContext.Request.Form["page"].ToString());

??????????? }

??????????? int rp = 1;

??????????? if (HttpContext.Request.Form["rp"] != null)

??????????? {

??????????????? rp = int.Parse(HttpContext.Request.Form["rp"].ToString());

??????????? }

??????????? string sortname = "";

??????? ????if (HttpContext.Request.Form["sortname"] != null)

??????????? {

??????????????? sortname = HttpContext.Request.Form["sortname"].ToString();

??????????? }

??????????? string whereCondition = " 1=1";

??????????? if (HttpContext.Request.Form["qtype"] != null && HttpContext.Request.Form["query"] != null && HttpContext.Request.Form["query"].ToString() != string.Empty)

??????????? {

??????????????? whereCondition += " and " + HttpContext.Request.Form["qtype"] + " = '" + HttpContext.Request.Form["query"].ToString() + "'";

??????????? }

??????????? string sortorder = "";

??????????? if (HttpContext.Request.Form["sortorder"] != null)

??????????? {

??????????????? sortorder = HttpContext.Request.Form["sortorder"].ToString();

??????????? }

??????????? string tablename = "B_APPLY_DE";

??????????? if (HttpContext.Request.Form["tablename"] != null)

??????????? {

??????????????? tablename = HttpContext.Request.Form["tablename"].ToString();

??????????? }

??????????? string sortExp = sortname + " " + sortorder;

????????? ??int start = ((page - 1) * rp) + 1;

??????????? DataSet ds = new DataSet(); int total = 0;

??????????? B_DEVICE_BASEINFODAL FlexDal = new B_DEVICE_BASEINFODAL();

??????????? string fields = "ID,APID,DE_COMPUTER,DE_PERIPHER,DE_NETDEVICE,DE_SDISK,APPLY_REASON,SQDWYJ,BGSYJ,FGLDYJ,JYLDYJ,BZ";

??????????? ds = FlexDal.GetListByPage(tablename,whereCondition,fields,sortExp,page,rp,out total);

??????????? return JsonForApplygrid(ds.Tables[0],total);

??????? }

??????? //生成json数据

??????? public string JsonForApplygrid(DataTable dt,int page,int total)

??????? {

??????????? StringBuilder jsonBuilder = new StringBuilder();

??????????? jsonBuilder.Append("{");

?

??????????? jsonBuilder.Append(""page":" + page.ToString() + ","total":" + total.ToString() + ","rows":[");

??????????? for (int i = 0; i < dt.Rows.Count; i++)

??????????? {

??????????????? jsonBuilder.Append("{");

??????????????? for (int j = 0; j < dt.Columns.Count; j++)

??????????????? {

??????????????????? switch (j)

??????????????? ????{

??????????????????????? case 0:

??????????????????????????? jsonBuilder.Append(""id":"");

??????????????????????????? jsonBuilder.Append(tools.JsonStringFormat(dt.Rows[i][j].ToString().Trim()));

??????????????????????????? jsonBuilder.Append("","cell":[");

??????????????????????????? jsonBuilder.Append(""");

??????????????????????????? jsonBuilder.Append("<a href='Edit/");

??????????????????????????? jsonBuilder.Append(tools.JsonStringFormat(dt.Rows[i][j].ToString()));

???????????????????????? ???jsonBuilder.Append("'>修改</a>&nbsp;<a href='Delete/" + tools.JsonStringFormat(dt.Rows[i][j].ToString().Trim()) + "'>删除</a>&nbsp;<a onclick="JqueryDialog.Open('选择设备','../BDeviceApply/SelectStandDevice?applyid=" + tools.JsonStringFormat(dt.Rows[i]["ID"].ToString().Trim()) + "',1100,580)"? target='_blank'><font color=blue>选择设备</font></a>");

??????????????????????????? jsonBuilder.Append("",");

??????????????????????????? break;

??????????????????????? case 2:

??????????????????????? case 3:

?????? ?????????????????case 4:

??????????????????????? case 5:

??????????????????????????? jsonBuilder.Append(""");

??????????????????????????? jsonBuilder.Append("<input type='checkbox' value='true' " + tools.IsCeckboxSelected(tools.JsonStringFormat(dt.Rows[i][j].ToString().Trim())) + "/>");

??????????????????????????? jsonBuilder.Append("",");

??????????????????????????? break;

??????????????????????? default:

??????????????????????????? jsonBuilder.Append(""");

??????????????????????????? jsonBuilder.Append(tools.JsonStringFormat(dt.Rows[i][j].ToString().Trim()));

??????????????????????????? jsonBuilder.Append("",");

??????????????????????????? break;

??????????????????? }

?

??????????????? }

??????????????? jsonBuilder.Remove(jsonBuilder.Length - 1,1);

?? ?????????????jsonBuilder.Append("],");

??????????????? jsonBuilder.Remove(jsonBuilder.Length - 1,1);

??????????????? jsonBuilder.Append("},");

??????????? }

??????????? if (total > 0)

??????????? {

?????????? ?????jsonBuilder.Remove(jsonBuilder.Length - 1,1);

??????????? }

??????????? jsonBuilder.Append("]");

??????????? jsonBuilder.Append("}");

??????????? return jsonBuilder.ToString();

??????? }

?????? 以上有个需要注意的地方，也是很值得注意的地方就是在读取数据库值时用到一个JsonStringFormat方法，由于Json数据格式限制，必须对数据中的特殊字符进行处理一下，否前台数据将不会被显示，以下是作者在开发时发现到的特殊字符的处理方法：

??????? //Json数据源字符串格式过滤

??????? public string JsonStringFormat(string JsonString)

??????? {

??????????? return JsonString.Trim().Replace(""",""").Replace("","/").Replace("rn","").Replace("r","").Replace("rr","").Replace("n","").Replace("nr","");

??????? }

出处：http://www.kwstu.com/ArticleView/lydia_2012103120844585