.NET+MVC+ORACLE存储分页查询一后端实现

MemberController:
public ActionResult UserList()
        {
             UserBll userBll = new UserBll();
            string keyWords = Request.GetString("keyWords").ToString("filtersql");
            int userStatus = Request.GetInt("userStatus", -1);
            int pageIndex = Request.GetInt("pageIndex", 1);
            int pageSize = Request.GetInt("pageSize", 20);
            string sTime = Request.GetDateTime("Stime").ToString("filtersql");
            string eTime = Request.GetDateTime("Etime").ToString("filtersql");
            string mobileNum = "";
           int totalCount = 0;
            DataTable userList = userBll.GetUser(keyWords, userStatus,  pageIndex, pageSize, ref totalCount, sTime, eTime, mobileNum );

            ViewBag.userList = userList;
            ViewBag.TotalCount = totalCount;

            //是否是Ajax提交过来的数据
            if (Request.IsAjaxRequest())
                return PartialView("PartialPageUserList", userList);
            return View(userList);
        }

 

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="KeyWords">关键字（手机号码、身份证号）</param>
        /// <param name="MebStatus">用户状态</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="totalCount">总记录数</param>
        /// <returns>用户列表</returns>
        public DataTable GetUser(string KeyWords, int MebStatus,  int pageIndex, int pageSize, ref int totalCount, string Stime, string Etime, string mobileNumCity, string mobileOperation)
        {
            StringBuilder sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" 1=1 and u.UserStatus !=9");
            if (KeyWords != "") 
            {
                sbSqlWhere.AppendFormat(" and u.MobileNo like '%{0}%'", KeyWords);
            }
            if (MebStatus >= 0)
            {
                sbSqlWhere.Append(" and u.UserStatus =" + MebStatus);
            }
            if (Stime != "")
            {
                sbSqlWhere.Append(" and u.AddTime>=to_date('"+Convert.ToDateTime(Stime).ToString("yyyy-MM-dd HH:mm:ss")+"','yyyy-mm-dd hh24:mi:ss')");
            }
            if (Etime != "")
            {
                sbSqlWhere.Append(" and u.AddTime<to_date('"+Convert.ToDateTime(Etime).AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")+"','yyyy-mm-dd hh24:mi:ss')");
            }
            string tableName = @" Meb_User u INNER JOIN Meb_Blance o ON u.UserId=o.MeberUserId ";
            string fields = @" u.UserId,u.MobileNo,u.UserType,u.AddTime,u.UserStatus,o.BlancePrice"      
string orderField = " u.AddTime desc ";
            return commonDll.GetListByFenye(tableName, fields, pageIndex, pageSize, ref totalCount, sbSqlWhere.ToString(), orderField);
        }

 

 public interface ICommon
    {
        /// <summary>
        /// 查询tableName信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fields">字段</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageSize">大小</param>
        /// <param name="count">返回个数</param>
        /// <param name="strWhere">条件</param>
        /// <param name="orderField">排序</param>
        /// <returns>返回tableName集合</returns>
        DataTable GetListByFenye(string tableName, string fields, int pageIndex, int pageSize, ref int count, string strWhere, string orderField);
}

 

        /// <summary>
        /// 查询tableName信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fields">字段</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageSize">大小</param>
        /// <param name="count">返回个数</param>
        /// <param name="strWhere">条件</param>
        /// <param name="orderField">排序</param>
        /// <returns>返回tableName集合</returns>
        public DataTable GetListByFenye(string tableName, string fields, int pageIndex, int pageSize, ref int count, string strWhere, string orderField)
        {
            OracleParameter[] parameter ={
              new OracleParameter("TableName",OracleType.VarChar),
               new OracleParameter("SelectFields",OracleType.VarChar),
               new OracleParameter("OrderField",OracleType.VarChar),
               new OracleParameter("sqlWhere",OracleType.VarChar),
               new OracleParameter("pageSize",OracleType.Int32),
               new OracleParameter("pageIndex",OracleType.Int32),
               new OracleParameter("totalRecord",OracleType.Int32),
               new OracleParameter("v_cur",OracleType.Cursor)
           };
            parameter[0].Value = tableName;
            parameter[1].Value = fields;
            parameter[2].Value = orderField;
            parameter[3].Value = strWhere;
            parameter[4].Value = pageSize;
            parameter[5].Value = pageIndex;
            parameter[6].Direction = ParameterDirection.Output;
            parameter[7].Direction = ParameterDirection.Output;
            DataTable dt = OracleHelper.ExecuteDataTable(CommandType.StoredProcedure, "FenYePagePro", parameter);
            count = (int)parameter[6].Value;
            return dt;
        }

 存储：

CREATE OR REPLACE PROCEDURE "FENYEPAGEPRO"
(
  TABLENAME IN VARCHAR2 
, SELECTFIELDS IN VARCHAR2 
, ORDERFIELD IN VARCHAR2 
, SQLWHERE IN VARCHAR2 
, PAGESIZE IN NUMBER 
, PAGEINDEX IN NUMBER 
, TOTALRECORD OUT NUMBER 
, v_cur OUT SYS_REFCURSOR
) is 

strSql varchar2(20000);--获取数据的sql语句
pageCount number;--该条件下记录页数
startIndex number;--开始记录
endIndex number;--结束记录

BEGIN
  strSql:='select count(1) from '||TABLENAME;
  if SQLWHERE is not null or SQLWHERE<>'' then 
     strSql:=strSql||' where '||SQLWHERE;
  end if;  
  
  EXECUTE IMMEDIATE strSql INTO TOTALRECORD;
  --计算数据记录开始和结束
  pageCount:=TOTALRECORD/PAGESIZE+1;
  startIndex:=(PAGEINDEX-1)*PAGESIZE+1;
  endIndex:=PAGEINDEX*PAGESIZE;
  
  strSql:='select rownum ro,A.* from ( select '||SELECTFIELDS||' from '||TABLENAME||' ';  
  
  if SQLWHERE is not null or SQLWHERE<>'' then 
     strSql:=strSql||' where '||SQLWHERE;
  end if;
  
  if  ORDERFIELD is not null or ORDERFIELD<>'' then 
     strSql:=strSql||' order by '||ORDERFIELD;
  end if;
  
  strSql:=strSql||') A where rownum<='||endIndex;
  
  strSql:='select * from ('||strSql||') where ro >='||startIndex;  
  --DBMS_OUTPUT.put_line(strSql);打印SQL语句

  OPEN v_cur FOR strSql;
END FENYEPAGEPRO;