CREATE OR REPLACE PROCEDURE prc_query
(p_tableName in varchar2, --表名
p_strWhere 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(4000) := ''; --sql语句
v_startRecord Number(10); --开始显示的记录条数
v_endRecord Number(10); --结束显示的记录条数
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 := round(p_totalRecords / p_pageSize,5);
ELSE
p_totalPages := round(p_totalRecords / p_pageSize,5) + 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 * 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;
/// <summary>
/// 通用分页ADO（分页）
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="strWhere">检索条件</param>
/// <param name="sort">排序的列</param>
/// <param name="order">排序方式</param>
/// <param name="page">当前页</param>
/// <param name="rows">每页显示记录条数</param>
/// <returns></returns>
public static ArrayList GetPaging(string tableName, string strWhere, string sort, string order, int page, int rows)
{
OracleParameter[] parameters =
{
new OracleParameter("p_tableName",OracleType.NVarChar),
new OracleParameter("p_strWhere",OracleType.NVarChar),
new OracleParameter("p_orderColumn", OracleType.NVarChar),
new OracleParameter("p_orderStyle", OracleType.NVarChar),
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].Direction = ParameterDirection.Input;
parameters[1].Direction = ParameterDirection.Input;
parameters[2].Direction = ParameterDirection.Input;
parameters[3].Direction = ParameterDirection.Input;
parameters[4].Direction = ParameterDirection.Input;
parameters[5].Direction = ParameterDirection.Input;
parameters[6].Direction = ParameterDirection.Output;
parameters[7].Direction = ParameterDirection.Output;
parameters[8].Direction = ParameterDirection.Output;
parameters[0].Value = tableName;
parameters[1].Value = strWhere;
parameters[2].Value = sort;
parameters[3].Value = order;
parameters[4].Value = page;
parameters[5].Value = rows;
ArrayList arrayList = RunProcedureForPaging("prc_query", parameters);
return arrayList;
}
/// <summary>
/// 执行存储过程,返回数据集
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static ArrayList RunProcedureForPaging(string storedProcName, OracleParameter[] parameters)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
ArrayList arrayList = new ArrayList();
DataSet dataSet = new DataSet();
connection.Open();
OracleDataAdapter sqlDA = new OracleDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, "dt");
connection.Close();
arrayList.Add(dataSet);
arrayList.Add(parameters[6].Value);
arrayList.Add(parameters[7].Value);
return arrayList;
}
}
/// <summary>
/// 数据表分页
/// </summary>
/// <param name="request">输入HttpRequestBase对象</param>
/// <param name="tableName">分页表名</param>
/// <param name="strWhere">检索条件</param>
/// <param name="defautlSort">默认排序字段</param>
/// <param name="orderSort">排序方式（默认降序）</param>
/// <returns></returns>
public ActionResult GetIndexJson(HttpRequestBase request, string tableName, string strWhere, string defautlSort, string orderSort = "desc")
{
//当前页码
var page = int.Parse((request.Form["page"] ?? "1"));
//显示行数
var rows = int.Parse(request.Form["rows"] ?? "10");
//排序字段
var sort = request.Form["sort"] ?? defautlSort;
//排序方式
var order = request.Form["order"] ?? orderSort;
//临时存储
var arrayList = OracleHelper.GetPaging(tableName, strWhere, sort, order, page, rows);
var ds = arrayList[0] as DataSet;
HttpContext.Current.Session["Exprotds"] = ds;
//正常检索
var pTotalRecords = Convert.ToInt32(arrayList[1]);
var jsons = JsonHelper.EasyUIDataTableJson(ds.Tables[0], null, pTotalRecords).Replace("\r", "\\r").Replace("\n", "\\n").Replace("\t", "\\t").Replace("\\", "\\\\");
return new StringResult(jsons);
}
public class JsonHelper
{
/// <summary>
/// Easyui专用Json数据列生成器
/// </summary>
/// <param name="dt">输入数据源</param>
/// <param name="getrows">生成列的长度 null表示生成全部列</param>
/// <param name="totalRecords">总行数</param>
/// <returns></returns>
public static string EasyUIDataTableJson(DataTable dt, int? getrows, int totalRecords)
{
/*
* 源模型
* "{\"total\":30,\"rows\":[{\"dateTB\":\"2014-05-27\",\"PeopleTZ\":\"姜杰\",\"PeopleTJ\":\"邓康\",\"State\":\"1\"}]}"
*/
const decimal decimaltype = 0.01M; //定义decimal数据类型
var stringBuilder = new StringBuilder();
if (dt.Rows.Count > 0)
{
stringBuilder.Append("{\"total\":" + totalRecords + ",\"rows\":[");
for (var i = 0; i < dt.Rows.Count; i++)
{
var dr = dt.Rows[i];
stringBuilder.Append("{");
if (getrows == null) getrows = dt.Columns.Count;
for (var j = 0; j < getrows; j++)
{
if (dr[j].GetType() == decimaltype.GetType())
{
//若为 decimal 类型，则不加单引号
stringBuilder.Append("\"" + dt.Columns[j].ColumnName + "\":" +
dr[j] + ",");
}
else
{
stringBuilder.Append("\"" + dt.Columns[j].ColumnName + "\":\"" +
dr[j].ToString().Trim().Replace("\r", "").Replace("\n", "") + "\",");
}
}
stringBuilder.Remove(stringBuilder.ToString().LastIndexOf(','), 1);
stringBuilder.Append("},");
}
stringBuilder.Remove(stringBuilder.ToString().LastIndexOf(','), 1);
stringBuilder.Append("]}");
}
else
{
stringBuilder.Append("{\"total\":0,\"rows\":[]}");
}
return stringBuilder.ToString();
}
/// <summary>
/// 泛型集合转DataTable
/// </summary>
/// <param name="arrayList"></param>
/// <param name="rowName"></param>
/// <returns></returns>
public static DataTable ArrayToDataTable(ArrayList arrayList, string[] rowName)
{
var dt = new DataTable();
foreach (var name in rowName)
{
dt.Columns.Add(name, typeof(string));
}
foreach (var t in arrayList)
{
var dr = dt.NewRow();
var rowStrings = t as string[];
for (var j = 0; j < rowStrings.Length; j++)
{
dr[rowName[j]] = rowStrings[j];
}
dt.Rows.Add(dr);
}
return dt;
}
/// <summary>
/// 树形菜单Json序列化 定制
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string CreateJsonParameters(DataSet ds)
{
DataTable dt = ds.Tables[0];
StringBuilder JsonString = new StringBuilder();
if (dt != null && dt.Rows.Count > 0)
{
JsonString.Append("[ ");
for (int i = 0; i < dt.Rows.Count; i++)
{
JsonString.Append("{ ");
for (int j = 0; j < dt.Columns.Count; j++)
{
int _result = 0;
if (j < dt.Columns.Count - 1)
{
JsonString.Append("'isParent':true,'" + dt.Columns[j].ColumnName.ToString().ToLower() + "':");
if (int.TryParse(dt.Rows[i][j].ToString(), out _result))
{
JsonString.Append("" + dt.Rows[i][j].ToString() + ",");
}
else
{
JsonString.Append("'" + dt.Rows[i][j].ToString() + "',");
}
}
else if (j == dt.Columns.Count - 1)
{
JsonString.Append("'isParent':true,'" + dt.Columns[j].ColumnName.ToString().ToLower() + "':");
if (int.TryParse(dt.Rows[i][j].ToString(), out _result))
{
JsonString.Append("" + dt.Rows[i][j].ToString());
}
else
{
JsonString.Append("'" + dt.Rows[i][j].ToString() + "'");
}
}
}
if (i == dt.Rows.Count - 1)
{
JsonString.Append("} ");
}
else
{
JsonString.Append("}, ");
}
}
JsonString.Append("]");
return JsonString.ToString();
}
else
{
return null;
}
}
