时间：2021-07-01 10:21:17 帮助过：4人阅读
概要:
1.改动BaseModel
2.补充一些重载方法,稍微改动了一下原有代码:GetAllToList()改为GetAllToListBySql();
3.存储过程分页实现
4.客户端拼接sql分页实现
在BaseModel中添加一下几个属性字段用于分页:
/// <summary>
/// 分页查询条件
/// </summary>
private string where="";
public string Where
{
get { return where; }
set { where = value; }
}
/// <summary>
/// 分页查询字段列
/// </summary>
private string fileds = "*";
public string Fileds
{
get { return fileds; }
set { fileds = value; }
}
private int pageIndex = 1;
public int PageIndex
{
get { return pageIndex; }
set { pageIndex = value; }
}
private int pageSize = 2;
public int PageSize
{
get { return pageSize; }
set { pageSize = value; }
}
private string orderField = "";
public string OrderField
{
get { return orderField; }
set { orderField = value; }
}
/// <summary>
/// 升序为0,降序为1
/// </summary>
private int orderFlag = 0;
public int OrderFlag
{
get { return orderFlag; }
set { orderFlag = value; }
}
/// <summary>
/// 总条数
/// </summary>
private int totalCount;
public int TotalCount
{
get { return totalCount; }
set { totalCount=value ; }
}
/// <summary>
/// 总页数
/// </summary>
private int totalPage;
public int TotalPage
{
get { return totalPage; }
set { totalPage=value; }
}
/// <summary>
/// 返回的数据
/// </summary>
private DataTable result;
public DataTable Result
{
get { return result; }
set { result=value; }
}
2.重载部分:
GetAllListToList()的重载方法签名如下:
public virtual List<T> GetAllToList(IDataReader dr)
public virtual List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName)
......
3.存储过程实现分页
存储过程为:
--创建包
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
--存储过程
create or replace procedure fenye
(
tableName in varchar2, --表名
fields in varchar2, --查询结果显示字段
wherecase in varchar2, --查询条件
pageSize in number, --一页显示记录数
pageNow in number, --当前页
orderField varchar2, --排序字段，为空表示不排序
orderFlag number, --排序标识 0：正序 1：倒序
myrows out number, --总记录数
myPageCount out number, --总分页
p_cursor out testpackage.test_cursor --返回的记录集
) is
--定义部分
--定义sql语句字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pagesize+1; --开始记录
v_end number:=pageNow*pageSize; --结束记录
--排序sql
v_orderSql varchar2(100):=‘‘;
v_wherecase varchar2(100):=‘‘;
begin
--执行部分
--如果orderField不为空，则进行排序，如果orderFlag=0为升序，1为降序
if orderField is not null then
if orderFlag=0 then
v_orderSql:=‘ order by ‘||orderField;
elsif orderFlag=1 then
v_orderSql:=‘ order by ‘||orderField||‘ desc‘;
else
null;
end if;
end if;
--条件判断语句
if wherecase is not null then
v_wherecase:=‘ where ‘||wherecase;
end if;
v_sql:=‘select * from
(select t1.* ,rownum rn from(select ‘|| fields ||‘ from ‘|| tableName|| v_wherecase ||‘ ‘||v_orderSql ||‘) t1 where rownum<=‘|| v_end ||‘)
where rn>=‘|| v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和myPageCount
--组织一个sql
v_sql:=‘select count(*) from ‘|| tableName || v_wherecase || ‘ ‘ ||v_orderSql;
--执行sql,并把返回的值赋给myrows;
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount:=myrows/pagesize+1;
end if;
--关闭游标
--close p_cursor;
end;
在BaseDal的调用代码如下:
public virtual List<T> GetDataByProcedure(T model, out int totalCount)
{
OracleConnection conn = DbAction.getConn();
OracleCommand com = new OracleCommand();
com.CommandText = "fenye";
com.Connection = conn;
com.CommandType = CommandType.StoredProcedure;
OracleParameter[] ps = {
new OracleParameter("tableName",OracleType.VarChar),
new OracleParameter("fields",OracleType.VarChar),
new OracleParameter("wherecase",OracleType.VarChar),
new OracleParameter("pageSize",OracleType.Number),
new OracleParameter("pageNow",OracleType.Number),
new OracleParameter("orderField",OracleType.VarChar),
new OracleParameter("orderFlag",OracleType.Number),
new OracleParameter("myrows",OracleType.Number),
new OracleParameter("myPageCount",OracleType.Number),
new OracleParameter("p_cursor",OracleType.Cursor)
};
ps[0].Value = TableName;//注意这里两种情况1.在实体层手动输入tableName,然后调用model.TableName;2.直接在数据访问层中反射类名TableName
ps[1].Value = model.Fileds;
ps[2].Value = model.Where;
ps[3].Value = model.PageSize;
ps[4].Value = model.PageIndex;
ps[5].Value = model.OrderField;
ps[6].Value = model.OrderFlag;
ps[7].Direction = ParameterDirection.Output;
ps[8].Direction = ParameterDirection.Output;
ps[9].Direction = ParameterDirection.Output;
foreach (OracleParameter item in ps)
{
com.Parameters.Add(item);
}
OracleDataAdapter da = new OracleDataAdapter(com);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception)
{
throw;
}
model.Result = ds.Tables[0];
model.TotalCount = int.Parse(ps[7].Value.ToString());
model.TotalPage = Convert.ToInt32(Math.Ceiling(Double.Parse(ps[8].Value.ToString())));
totalCount = int.Parse(ps[7].Value.ToString());
List<T> list = new List<T>();
foreach (DataRow item in ds.Tables[0].Rows)
{
list.Add(GetModel(ds.Tables[0], item));
}
return list;
}
Note:需要注意的几个问题是,
1.参数变量名必须和储存过程的变量名保持一样,
2.记得CommandType设置为存储过程,
3TableName如果取BaseModel则必须在调用之前手动指定表名;
4.客户端拼接sql实现分页
拼接sql的方法代码如下:
public string BuildSql(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName)
{
//查询字段
string sColList = "";
if (string.IsNullOrEmpty(colList) || colList == "*")
{
PropertyInfo[] pis = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
foreach (PropertyInfo pi in pis)
{
sColList += "[" + pi.Name + "],";
}
sColList = sColList.Substring(0, sColList.Length - 1);
}
else
{
sColList = SqlAction.GetSQLFildList(colList);
}
StringBuilder strSql = new StringBuilder();
string strOrder; //排序字段
if (string.IsNullOrEmpty(fldOrder))
{
fldOrder = PrimaryKey;
}//排序类型
if (OrderFlag == 1)
{
strOrder = string.Format(" order by {0} desc", fldOrder);
}
else
{
strOrder = string.Format(" order by {0} asc", fldOrder);
}
//没有where 条件
if (string.IsNullOrEmpty(strWhere))
{
strSql.Append(string.Format("select {0} from(select {1}, rownum as id from {2} {3}",
sColList, sColList, tblName, strOrder));
strSql.Append(string.Format(") a where id between {0} and {1}", (PageIndex - 1) * PageSize + 1,
PageIndex * PageSize));
}
else
{
strSql.Append(string.Format("select {0} from(select {1}, rownum as id from {2} {3} ", sColList, sColList, tblName, strOrder));
strSql.Append(string.Format(" where {0}", strWhere));
strSql.Append(string.Format(") a where id between {0} and {1}", (PageIndex - 1) * PageSize + 1,
PageIndex * PageSize));
}
return strSql.ToString().Replace("[","").Replace("]","");
}
public IDataReader GetDataReaderByPage(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName)
{
string strSql = BuildSql(strWhere, PageSize, PageIndex, OrderFlag, colList, fldOrder, tblName);
OracleConnection conn = DbAction.getConn();
OracleCommand com = new OracleCommand(strSql, conn);
conn.Open();
IDataReader dr= com.ExecuteReader(CommandBehavior.CloseConnection);//使用using也可以
return dr;
}
public virtual List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName)
{
//自托管
using (IDataReader dr = GetDataReaderByPage(strWhere, PageSize, PageIndex, OrderFlag, colList, fldOrder, tblName))
{
return GetAllToList(dr);
}
}
至此,已经完成分页功能.
Oracle+Ado.Net(四)
标签：