摘要：上一篇简单的介绍了一下前台页面代码部分，本文主要说一下如何为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", OracleType.VarChar, 2000),
new OracleParameter("p_fileds", OracleType.VarChar, 2000),
new OracleParameter("p_orderColumn", OracleType.VarChar, 2000),
new OracleParameter("p_orderStyle", OracleType.VarChar, 2000),
new OracleParameter("p_curPage", OracleType.Number),
new OracleParameter("p_pageSize", OracleType.Number),
new OracleParameter("p_totalRecords", OracleType.Number),
new OracleParameter("p_totalPages", OracleType.Number),
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, string whereCondition, string fields, string sortname, int startRowIndex, int numberOfRows, out int count)
{
DataSet ds = new DataSet();
const string SP = "GetProList";
SqlParameter[] parameters = {
new SqlParameter("Tablename", SqlDbType.VarChar, 255),
new SqlParameter("WhereClause", SqlDbType.VarChar, 255),
new SqlParameter("Fields", SqlDbType.VarChar, 255),
new SqlParameter("SortExpression", SqlDbType.VarChar, 255),
new SqlParameter("RowIndex", SqlDbType.Int),
new SqlParameter("NoOfRows", SqlDbType.Int),
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, parameters, "Baseinfo", out count);
}
/// <summary>
/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, out int count)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
//sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, 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], page, 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> <a href='Delete/" + tools.JsonStringFormat(dt.Rows[i][j].ToString().Trim()) + "'>删除</a> <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("\r\n", "").Replace("\r", "").Replace("\r\r", "").Replace("\n", "").Replace("\n\r", "");
}
至此本文已结束，希望对各位有帮助。