/// <summary>
/// 功能：查询分页数据
/// 日期：2013-05-15
/// </summary>
/// <param name="sqlStr">查询SQL</param>
/// <param name="pi">页码</param>
/// <param name="pageSize">每页显示条数</param>
/// <param name="parList">参数类型</param>
/// <param name="dbT">执行库</param>
/// <returns></returns>
public static PagedTable GetDataByPage(string sqlStr, int pi, int pageSize, List <ParameterEntity> parList, DataBaseType dbT)
{
string falg = DataPageFalg;
#region 查询数据语句
//String sqlData = @"SELECT * FROM (SELECT A.*, ROWNUM ROW_NUM FROM (" + sqlStr + ") A) WHERE ROW_NUM > :ROW_NUM_OLD AND ROW_NUM <= :ROW_NUM_NEW";
String sqlData = @"SELECT * FROM (SELECT A.*, ROWNUM ROW_NUM FROM (" + sqlStr + ") A WHERE ROWNUM<= :ROW_NUM_NEW) WHERE ROW_NUM > :ROW_NUM_OLD";
#endregion
#region 查询数据总条数语句
String sqlCount = "(SELECT COUNT(1) ROW_TOTAL from (" + sqlStr + ")) ROW_TOTAL ";
#endregion
String SQL = "SELECT * FROM " + sqlCount + " ,(" + sqlData + ")";
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
DbCommand cmd = oraDb.GetSqlStringCommand(SQL);
if (parList != null && parList.Count > 0)
{
foreach (ParameterEntity par in parList)
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
oraDb.AddInParameter(cmd, ":ROW_NUM_OLD", DbType.Int32, (pi - 1) * pageSize);
oraDb.AddInParameter(cmd, ":ROW_NUM_NEW", DbType.Int32, pageSize * pi);
DataSet ds = oraDb.ExecuteDataSet(cmd);
PagedTable pt = new PagedTable(ds.Tables[0], pi, pageSize, BusinessHelper.GetRowTotal(ds.Tables[0]));
return(pt);
}
/// <summary>
/// 将PagedTable的数据转化为JsonResult.
/// </summary>
/// <param name="data">按DataTable的分页数据</param>
/// <returns>JsonResult对象</returns>
public static JsonResult ToJsonResult(this PagedTable data)
{
JsonResult result = new TextJsonResult();
StringBuilder sbJson = new StringBuilder();
StringBuilder sbRows = new StringBuilder();
sbRows.Append("[");
JavaScriptSerializer serializer = new JavaScriptSerializer();
foreach (DataRow row in data.Table.Rows)
{
sbRows.Append("{");
foreach (DataColumn column in data.Table.Columns)
{
sbRows.AppendFormat("\"{0}\":{1},", column.ColumnName, serializer.Serialize(row[column]));
}
sbRows.TrimEnd(',');
sbRows.Append("},");
}
sbRows.TrimEnd(',');
sbRows.Append("]");
sbJson.AppendFormat("{{\"page\":{0},\"total\":{1},\"records\":{2},\"rows\":{3}}}",
data.CurrentPageIndex,
data.TotalPageCount,
data.TotalItemCount,
sbRows);
result.Data = sbJson;
result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
return(result);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="sqlQuery">DataTable的查询语句</param>
/// <param name="sqlCount">总记录数据查询语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="pageIndex">起始页,从1开始</param>
/// <param name="pageSize">页记录数</param>
/// <param name="dbCategory">数据库分类</param>
/// <returns></returns>
public static PagedTable QueryPagedTable(string sqlQuery, string sqlCount, OracleParameter[] parameters, int pageIndex, int pageSize, DBCatetory dbCategory = DBCatetory.Production)
{
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize + 1;
string sqlPagedQuery = String.Format("SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ({0}) A WHERE ROWNUM < {1} ) WHERE RN >= {2}", sqlQuery, endIndex, startIndex);
long count = Convert.ToInt64(ExecuteScalar(sqlCount, parameters, dbCategory));
DataTable table = Query(sqlPagedQuery, parameters, dbCategory).Tables[0];
PagedTable pagedResult = new PagedTable(table, pageIndex, pageSize, count);
return(pagedResult);
}
public ActionResult SUserList(SUserSCondition sus)
{
PagedTable result = ServiceUser.GetSUserList(sus);
return(result.ToJsonResult());
}
public ActionResult JobsList(JobInfo pageQuery)
{
PagedTable result = Jobs.GetJobsByType(pageQuery);
return(result.ToJsonResult());
}
