-- 获取指定页的数据
CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名
@PrimaryKey varchar(100), --主键
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = "select count(*) as Total from " + @tblName + " where "+@strWhere
else
set @strSQL = "select count(*) as Total from " + @tblName + ""
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType != 0
begin
--set @strTmp = "<(select min"
set @strTmp = " not in "
set @strOrder = " order by [" + @fldName +"] desc"
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
--set @strTmp = ">(select max"
set @strTmp = " not in "
set @strOrder = " order by [" + @fldName +"] asc"
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize,3) +" "+@strGetFields+ " from " + @tblName + " where " + @strWhere + @strOrder
else
set @strSQL = "select top " + str(@PageSize,3) +" "+@strGetFields+ " from "+ @tblName + " "+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = "select top " + str(@PageSize,3) +" "+@strGetFields+ " from "
+ @tblName + " where [" + @PrimaryKey + "]" + @strTmp + " (select top " + str((@PageIndex-1)*@PageSize,3) + " ["+ @PrimaryKey + "] from " + @tblName + "" + @strOrder + ") "+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize,3) +" "+@strGetFields+ " from "
+ @tblName + " where [" + @PrimaryKey + "]" + @strTmp
+ " (select top " + str((@PageIndex-1)*@PageSize,3) + " ["
+ @PrimaryKey + "] from " + @tblName + " where " + @strWhere + " "
+ @strOrder + ") and " + @strWhere + " " + @strOrder
end
end
print @strSQL
exec (@strSQL)
GO
=========================================================
/// <summary>
/// 通过存储过程查询分页信息
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="Primarykeyname"></param>
/// <param name="colName">列名集合</param>
/// <param name="orderCol">排序列名</param>
/// <param name="pageSize">页尺寸</param>
/// <param name="pageIdx">当前页</param>
/// <param name="orderType">升降序，true-0为升序，false-非0为降序</param>
/// <param name="condition">条件</param>
/// <returns></returns>
#region public DataTable GetPageEntitesByStoredProc(string tableName, string Primarykeyname, string colName, string orderCol,int pageSize, int pageIdx, bool orderType, string condition)
public DataTable GetPageEntitesByStoredProc(string tableName, string Primarykeyname, string colName, string orderCol,
int pageSize, int pageIdx, bool orderType, string condition)
{
IList result = new ArrayList();
ISessionFactoryImplementor imp = (ISessionFactoryImplementor)SessionFactory;
IDbConnection conn = imp.ConnectionProvider.GetConnection();
IDbCommand cmd = imp.ConnectionProvider.GetConnection().CreateCommand();
cmd.CommandText = "pagination";
cmd.CommandType = CommandType.StoredProcedure;
IDbDataParameter parameter = cmd.CreateParameter();
parameter.ParameterName = "@tblName";
parameter.Value = tableName;
cmd.Parameters.Add(parameter);
parameter = cmd.CreateParameter();
parameter.ParameterName = "@PrimaryKey";
parameter.Value = Primarykeyname;
cmd.Parameters.Add(parameter);
parameter = cmd.CreateParameter();
parameter.ParameterName = "@strGetFields";
parameter.Value = colName;
cmd.Parameters.Add(parameter);
parameter = cmd.CreateParameter();
parameter.ParameterName = "@fldName";
parameter.Value = orderCol;
cmd.Parameters.Add(parameter);
parameter = cmd.CreateParameter();
parameter.ParameterName = "@PageSize";
parameter.Value = pageSize;
cmd.Parameters.Add(parameter);
parameter = cmd.CreateParameter();
parameter.ParameterName = "@PageIndex";
parameter.Value = pageIdx;
cmd.Parameters.Add(parameter);
parameter = cmd.CreateParameter();
parameter.ParameterName = "@OrderType";
parameter.Value = orderType;
cmd.Parameters.Add(parameter);
parameter = cmd.CreateParameter();
parameter.ParameterName = "@strWhere";
parameter.Value = condition;
cmd.Parameters.Add(parameter);
try
{
cmd.Connection = conn;
IDataReader rs = cmd.ExecuteReader();
// 分割列
string[] cols = SplitsColumnNames(colName, ',');
// 数据表
DataTable dt = new DataTable(tableName);
foreach (string col in cols)
dt.Columns.Add(col);
// 取数据
while (rs.Read())
{
// 创建行
DataRow row = dt.NewRow();
for (int i = 0; i < cols.Length; i++)
row[cols[i]] = rs.GetValue(i);
// 插入行
dt.Rows.Add(row);
}
// 返回结果集
return dt;
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(DaoTemplate));
log.Error(ex.Message, ex);
return null;
}
finally
{
imp.CloseConnection(conn);
}
}
#endregion
/// <summary>
/// 将字符里的列表分解出来
/// </summary>
/// <returns></returns>
#region internal static string[] SplitsColumnNames(string columns, char separator)
internal static string[] SplitsColumnNames(string columns, char separator)
{
return columns.Split(new char[] { separator });
}
#endregion
}
}
/// <summary>
/// 泛型读取
/// </summary>
/// <param name="obj"></param>
/// <param name="id"></param>
#region T LoadFromId<T>(object id)
public T LoadFromId<T>(object id)
{
try
{
T obj =
(T)HibernateTemplate.Load(typeof(T), id);
return obj;
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return default(T);
}
}
#endregion
/// <summary>
/// 泛型存储
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="obj"></param>
#region bool Save<T>(T obj)
public bool Save<T>(T obj)
{
try
{
HibernateTemplate.Save(obj);
return true;
}
catch (DataAccessException ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return false;
}
}
#endregion
/// <summary>
/// 泛型更新
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="obj"></param>
#region bool Update<T>(T obj)
public bool Update<T>(T obj)
{
try
{
HibernateTemplate.Update(obj);
return true;
}
catch (DataAccessException ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return false;
}
}
#endregion
/// <summary>
/// 泛型删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="obj"></param>
#region bool Delete<T>(T obj)
public bool Delete<T>(T obj)
{
try
{
HibernateTemplate.Delete(obj);
return true;
}
catch (DataAccessException ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return false;
}
}
#endregion
/// <summary>
/// 条件删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
#region bool Delete<T>(string where)
public bool Delete<T>(string where)
{
try
{
string sql =string.Format("from {0} {1}",
typeof(T).ToString(),
where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where);
HibernateTemplate.Delete(sql);
return true;
}
catch (DataAccessException ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return false;
}
}
#endregion
/// <summary>
/// 泛型搜索
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
#region IList<T> Search<T>(string where)
public IList<T> Search<T>(string where)
{
try
{
//有意思的模板反射哟~
T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
string hql = string.Format("from {0} {1}",
obj.GetType().ToString(),
where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where);
IList alist = HibernateTemplate.Find(hql);
IList<T> list = new List<T>();
if (alist != null && alist.Count > 0)
{
foreach (T t in alist)
{ list.Add(t); }
return list;
}
else
return null;
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return null;
}
}
#endregion
/// <summary>
/// 泛型搜索 - DISTINCT
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="field">列名，用","分开，不带别名</param>
/// <param name="where"></param>
/// <param name="alias">别名</param>
#region IList<T> SearchDistinct<T>(string where,string field,string alias)
public IList<T> SearchDistinct<T>(string where, string field, string alias)
{
try
{
//有意思的模板反射哟~
T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
// 反射DTO对象的各字段,必须把字段和DB中字段同名
System.Reflection.PropertyInfo[] pps = obj.GetType().GetProperties();
//拆分成别名+列名
string[] cols = field.Split(',');
string columns = string.Empty;
foreach (string col in cols)
columns += string.Format("{0}.{1},", alias, col);
columns = columns.TrimEnd(',');
//hql
string hql = string.Format("select distinct {2} from {0} {3} {1}",
obj.GetType().ToString(),
where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where
, columns
, alias);
IList alist = HibernateTemplate.Find(hql);
IList<T> list = new List<T>();
if (alist != null && alist.Count > 0)
{
//是否为数组
bool isArray = (cols.Length == 1 ? false : true);
foreach (object arr in alist)
{
//产生一个类实例
T t = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
for (int i = 0; i < cols.Length; i++)
{
//逐字段检查名称
foreach (System.Reflection.PropertyInfo pi in pps)
{
if(pi.Name.Equals(cols[i]))
{
//数组与object对象
pi.SetValue(t, (isArray ? (arr as object[])[i] : arr), null);
}
}
}
list.Add(t);
}
return list;
}
else
return null;
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return null;
}
}
#endregion
/// <summary>
/// 基于表达式的排序查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <param name="propertyName"></param>
/// <param name="ascending"></param>
#region IList<T> SearchWithOrder<T>(string where, string propertyName, bool ascending)
public IList<T> SearchWithOrder<T>(string where, string propertyName, bool ascending)
{
try
{
//排序
Order order = new Order(propertyName, ascending);
//排序
ICriteria ic = Session.CreateCriteria(typeof(T));
ic.AddOrder(order);
//表达式
ICriterion exp = Expression.Sql(where);
ic.Add(exp);
return ic.List<T>();
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return null;
}
}
#endregion
/// <summary>
/// 执行存储过程(返回bool)
/// </summary>
/// <param name="spName">名称</param>
/// <param name="paramInfos">参数表</param>
#region bool ExecuteStoredProc2(string spName, ICollection paramInfos)
public bool ExecuteStoredProc2(string spName, ICollection paramInfos)
{
bool result = true;
IDbCommand cmd = Session.Connection.CreateCommand();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
// 加入参数
if (paramInfos != null)
{
foreach (ParamInfo info in paramInfos)
{
IDbDataParameter parameter = cmd.CreateParameter();
parameter.ParameterName = info.Name; // driver.FormatNameForSql( info.Name );
parameter.Value = info.Value;
cmd.Parameters.Add(parameter);
}
}
IDbConnection conn = Session.Connection;
if(conn.State == ConnectionState.Closed)
conn.Open();
try
{
cmd.Connection = conn;
IDataReader rs = cmd.ExecuteReader();
result = true;
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(DaoTemplate));
log.Error(ex.Message, ex);
result = false;
}
finally
{
Session.Connection.Close();
}
return result;
}
#endregion
/// <summary>
/// 执行存储过程(返回ILIST)
/// </summary>
/// <param name="spName">名称</param>
/// <param name="paramInfos">参数表</param>
#region IList ExecuteStoredProc(string spName, ICollection paramInfos)
public IList ExecuteStoredProc(string spName, ICollection paramInfos)
{
IList result = new ArrayList();
IDbCommand cmd = Session.Connection.CreateCommand();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
// 加入参数
if (paramInfos != null)
{
foreach (ParamInfo info in paramInfos)
{
IDbDataParameter parameter = cmd.CreateParameter();
parameter.ParameterName = info.Name; // driver.FormatNameForSql( info.Name );
parameter.Value = info.Value;
cmd.Parameters.Add(parameter);
}
}
IDbConnection conn = Session.Connection;
conn.Open();
try
{
cmd.Connection = conn;
IDataReader rs = cmd.ExecuteReader();
while (rs.Read())
{
int fieldCount = rs.FieldCount;
object[] values = new Object[fieldCount];
for (int i = 0; i < fieldCount; i++)
values[i] = rs.GetValue(i);
result.Add(values);
}
}
finally
{
Session.Connection.Close();
}
return result;
}
#endregion
/// <summary>
/// 获取记录数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
#region int GetRecordCount<T>(string where)
public int GetRecordCount<T>(string where)
{
return GetRecordCount<T>(where, "*");
}
#endregion
/// <summary>
/// 获取记录数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
#region int GetRecordCount<T>(string where,string cols)
public int GetRecordCount<T>(string where,string cols)
{
try
{
//DISTINCT统计
bool distinct = false;
if (cols.ToLower().StartsWith("distinct"))
{
distinct = true;
string[] columns = cols.Replace("distinct", "").Split(',');
StringBuilder sb = new StringBuilder();
sb.Append("distinct ");
for (int i = 0; i < columns.Length; i++)
sb.Append("alia." + columns[i].Trim());
cols = sb.ToString().TrimEnd(',');
}
T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
string hql = "";
if (where.Trim() == String.Empty)
{
hql = string.Format("select count({1}) from {0} {2}",
obj.GetType().ToString(),cols
,(distinct ? "alia":"")
);
}
else
{
hql = string.Format("select count({2}) from {0} {3} {1}",
obj.GetType().ToString(),
where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where
, cols, (distinct ? "alia" : ""));
}
IQuery query = Session.CreateQuery(hql);
object o = query.UniqueResult();
return int.Parse(o.ToString());
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return 0;
}
finally
{
Session.Close();
}
}
#endregion
/// <summary>
/// 获取记录数（全文检索）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
#region int GetRecordCount4Fulltext<T>(string where,string tbName)
public int GetRecordCount4Fulltext<T>(string where, string tbName)
{
try
{
string hql = string.Format("select count(*) as CountNum from {0} {1}",
tbName,
where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where);
IQuery query = Session.CreateSQLQuery(hql)
.AddScalar("CountNum", NHibernateUtil.Int32);
object o = query.UniqueResult();
return int.Parse(o.ToString());
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return 0;
}
finally
{
Session.Close();
}
}
#endregion
/// <summary>
/// 通过where条件查询获取分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <param name="varQuerysort">排序</param>
/// <param name="Start"></param>
/// <param name="Max"></param>
/// <returns></returns>
#region IList<T> GetPageEntites<T>(string where,string varQuerysort, int Start, int Max)
public IList<T> GetPageEntites<T>(string where, string varQuerysort, int Start, int Max)
{
try
{
T obj = (T)System.Reflection.Assembly.GetAssembly(typeof(T)).CreateInstance(typeof(T).ToString());
string hql = "";
if (where.Trim() == String.Empty)
{
hql = string.Format("from {0}",
obj.GetType().ToString());
}
else
{
hql = string.Format("from {0} {1}",
obj.GetType().ToString(),
where.ToUpper().StartsWith("WHERE") ? where : "WHERE " + where);
}
if (varQuerysort != String.Empty) hql += " " + varQuerysort;
IQuery query = Session.CreateQuery(hql);
IList<T> list = query.SetFirstResult(Start).SetMaxResults(Max).List<T>();
return list;
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(T));
log.Error(ex.Message, ex);
return null;
}
finally
{
Session.Close();
}
}
#endregion
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
