/// <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()); }