cs代码
/// <summary>
/// 处理分页检索存储过程(SQL2005)
/// </summary>
/// <param name="fields">需要查询的字段</param>
/// <param name="tables">表名</param>
/// <param name="filters">sql条件</param>
/// <param name="sortStr">排序字符串</param>
/// <param name="currentPage">当前页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="total">总记录数</param>
/// <param name="totalPage">总页数</param>
/// <returns>DataTable</returns>
protected IList<T> ExecutePaginationQueryListSP<T>(string fields, string tables, string filters, string sortFields, string sortType,
int currentPage, int pageSize, out int total) where T : new()
{
IList<T> result=default(List<T>);
string spName = "ceb_Pagination";
//参数赋值
SqlParameter pFields = new SqlParameter("@fields", SqlDbType.NVarChar, 1000);
pFields.Value = fields;
SqlParameter pTables = new SqlParameter("@tables", SqlDbType.NVarChar, 400);
pTables.Value = tables;
SqlParameter pFilters = new SqlParameter("@filters", SqlDbType.NVarChar, 1000);
pFilters.Value = filters;
SqlParameter pSortFields = new SqlParameter("@sortfields", SqlDbType.NVarChar, 100);
pSortFields.Value = sortFields;
SqlParameter pSortType = new SqlParameter("@sorttype", SqlDbType.NVarChar, 4);
pSortType.Value = sortType;
SqlParameter pCurrentPage = new SqlParameter("@currentpage", SqlDbType.Int);
pCurrentPage.Value = currentPage;
SqlParameter pPageSize = new SqlParameter("@pagesize", SqlDbType.Int);
pPageSize.Value = pageSize;
SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int);
pTotal.Direction = ParameterDirection.Output;
//添加参数
SqlParameter[] param = new SqlParameter[]{
pFields,
pTables,
pFilters,
pSortFields,
pSortType,
pCurrentPage,
pPageSize,
pTotal
};
try
{
total = Convert.ToInt32(pTotal.Value);
result=ExecuteQueryListSP<T>(spName, param);
}
catch (Exception e)
{
total = 0;
}
finally
{
Close();
}
return result;
}
///<summary>
///利用反射将SqlDataReader转换成List模型
///</summary>
///<param name="spName">存储过程名称</param>
///<returns></returns>
public virtual IList<T> ExecuteQueryListSP<T>(string spName, params SqlParameter[] listParams) where T : new()
{
IList<T> list = new List<T>();
Type type = typeof(T);
string tempName = string.Empty;
using (SqlDataReader reader = ExecuteReaderSP(spName, new ArrayList(listParams)))
{
if (reader.HasRows)
{
//list = new List<T>();
while (reader.Read())
{
T t = new T();
PropertyInfo[] propertys = t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
//for (int intField = 0; intField < reader.FieldCount; intField++)
//{//遍历该列名是否存在
//}
if (readerExists(reader, tempName))
{
if (!pi.CanWrite)
{
continue;
}
var value = reader[tempName];
if (value != DBNull.Value)
{
pi.SetValue(t, value, null);
}
}
}
list.Add(t);
}
}
if (reader != null && (!reader.IsClosed))
{
reader.Close();
}
}
return list;
}
/// <summary>
/// 处理存储过程
/// </summary>
/// <param name="spName">存储过程名</param>
/// <param name="parameters">参数数组</param>
/// <returns>sql数据流</returns>
public virtual SqlDataReader ExecuteReaderSP(string spName, ArrayList parameters)
{
SqlDataReader result = null;
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
if (parameters != null)
{
foreach (SqlParameter param in parameters)
{
cmd.Parameters.Add(param);
}
}
try
{
Open();
result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
if (result != null && (!result.IsClosed))
{
result.Close();
}
//LogHelper.WriteLog("\r\n方法异常【ExecuteReaderSP(string spName, ArrayList parameters)】" + spName, e);
// LogStackTrace.WriteError(e);
throw new Exception(e.Message);
}
//finally
//{
// Close();
//}
return result;
}
//存储过程
Create proc [dbo].[ceb_Pagination2005]
@strFields nvarchar(2000), --字段名
@strTableName nvarchar(2000), --表名
@strWhere nvarchar(4000), --条件 无需加where
@strOrderBy nvarchar(200), --排序 必添 无需加order by
@PageSize int, --分页大小
@CurrentPage int, --当前页，1为起始页
@PageCount int output, --返回总页数
@RecordCount int output --返回记录总数
as
begin
declare @StartIndex int --定义起始位置
set @StartIndex = (@currentPage - 1) * @PageSize + 1
declare @strSql1 nvarchar (Max) --数据查询
declare @strSql2 nvarchar (Max) --统计记录总数
declare @ParmDefinition nvarchar (Max)
set @ParmDefinition = N'@tmp int output'
set @strSql1 = N'select row_number() over (order by ' + @strOrderBy + ' ) as RowID, '
set @strSql2 = 'select @tmp = count(*) '
if @strFields <> ''
set @strSql1 = @strSql1 + @strFields
else
set @strSql1 = @strSql1 + ' * '
if @strTableName <> ''
begin
set @strSql1 = @strSql1 + ' from ' + @strTableName
set @strSql2 = @strSql2 + ' from ' + @strTableName
end
if @strWhere <> ''
begin
set @strSql1 = @strSql1 + ' where ' + @strWhere
set @strSql2 = @strSql2 + ' where ' + @strWhere
end
exec sp_executesql @strSql2,@ParmDefinition,@tmp = @RecordCount output --执行统计记录总数SQL语句
if @RecordCount % @PageSize = 0 --计算总页数
set @PageCount = @RecordCount / @PageSize
else
set @PageCount = @RecordCount / @PageSize + 1
set @strSql1 = 'with TempTable as ( ' + @strSql1 + ' ) select * from TempTable where RowID between '
+ Convert(varchar(10),@StartIndex) + ' and ' + Convert(varchar(10),@StartIndex + @PageSize - 1)
exec(@strSql1)
end