/// <summary>
/// 数据库查询会话基类
/// </summary>
public class DataContextBase : DataContext, IPagingDataContext
{
private static readonly MappingSource MappingSource = new AttributeMappingSource();
public DataContextBase(string conectionString)
: base(conectionString, MappingSource)
{
ConnectionString = conectionString;
}
public string ConnectionString { get; set; }
/// <summary>
/// 返回分页数据
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="tableName"></param>
/// <param name="outTableName"></param>
/// <param name="fields"></param>
/// <param name="orderField"></param>
/// <param name="sqlWhere"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public PagedList<TEntity> GetPaging<TEntity>([Parameter(Name = "TableName", DbType = "VarChar(500)")] string tableName, [ParameterAttribute(Name = "OutTableName", DbType = "VarChar(500)")] string outTableName, [ParameterAttribute(Name = "Fields", DbType = "VarChar(5000)")] string fields, [ParameterAttribute(Name = "OrderField", DbType = "VarChar(5000)")] string orderField, [ParameterAttribute(DbType = "VarChar(5000)")] string sqlWhere, [ParameterAttribute(DbType = "Int")] int? pageSize, [ParameterAttribute(DbType = "Int")] int? pageIndex)
where TEntity : class
{
if (string.IsNullOrEmpty(orderField)) throw new ArgumentNullException("orderField");
var re = new PagedList<TEntity>();
IMultipleResults m = GetPagingData(tableName, outTableName, fields, orderField, sqlWhere, pageSize, pageIndex);
List<TEntity> list = m.GetResult<TEntity>().ToList();
re.Page = m.GetResult<PageInfo>().FirstOrDefault();
re.ListData = list;
return re;
}
/// <summary>
/// 返回分页数据
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="mastField"> </param>
/// <param name="tableName"></param>
/// <param name="outTableName"></param>
/// <param name="fields"></param>
/// <param name="orderField"></param>
/// <param name="sqlWhere"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public PagedList<TEntity> GetPaging1<TEntity>(string mastField, string tableName, string outTableName, string fields, string orderField, string sqlWhere, int? pageSize, int? pageIndex) where TEntity : class
{
if (string.IsNullOrEmpty(orderField)) throw new ArgumentNullException("orderField");
var re = new PagedList<TEntity>();
IMultipleResults m = GetPagingData1(mastField,tableName, outTableName, fields, orderField, sqlWhere, pageSize, pageIndex);
List<TEntity> list = m.GetResult<TEntity>().ToList();
re.Page = m.GetResult<PageInfo>().FirstOrDefault();
re.ListData = list;
return re;
}
/// <summary>
/// 多表多数据,建议使用此方法(数据量在10W以上,50W以下)
///
/// 执行分页存储过程,第二种
/// 支持多表连接查询,
/// 无RowID
/// 需要提供mastField字段,该字段在返回的结果是必须是唯一的,
/// 性能参考,三个表,一个15万条记录,一个260,一个30,,(CPU E5700,2G,SQL2005)
/// 每页10条,检索最后第15000页用时140毫秒,
/// 其中第一个查询中关联查找占用90%的84%,
/// 第二个查询中两个表的键关联查找占用10%的40%
/// 多表联合查询中,表的关联非常损耗性能
/// 索引字段的搜索:
/// 性能参考,三个表,一个15万条记录,一个260,一个30,
/// 每页10条,检索最后第15000页用时473毫秒,
/// 其中第一个查询中关联查找占用6%的40%,
/// 第二个查询中两个表的键关联查找占用94%的94%
/// </summary>
/// <param name="mastField"></param>
/// <param name="tableName"></param>
/// <param name="outTableName"></param>
/// <param name="fields"></param>
/// <param name="orderField"></param>
/// <param name="sqlWhere"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
[FunctionAttribute(Name = "dbo.Paging1")]
[ResultType(typeof(PagedList<>))]
public IMultipleResults GetPagingData1(
[ParameterAttribute(Name = "MastField", DbType = "VarChar(500)")] string mastField,
[ParameterAttribute(Name = "TableName", DbType = "VarChar(500)")] string tableName,
[ParameterAttribute(Name = "OutTableName", DbType = "VarChar(500)")] string outTableName,
[ParameterAttribute(Name = "Fields", DbType = "VarChar(5000)")] string fields,
[ParameterAttribute(Name = "OrderField", DbType = "VarChar(5000)")] string orderField,
[ParameterAttribute(DbType = "VarChar(5000)")] string sqlWhere,
[ParameterAttribute(DbType = "Int")] int? pageSize,
[ParameterAttribute(DbType = "Int")] int? pageIndex)
{
IExecuteResult result = ExecuteMethodCall(this, ((MethodInfo) (MethodBase.GetCurrentMethod())),mastField, tableName,
outTableName, fields, orderField, sqlWhere, pageSize, pageIndex);
return ((IMultipleResults) (result.ReturnValue));
}
/// <summary>
/// 单表多数据或,多表少数据建议使用此方法,多表10W以下,或单表100W以上
///
/// 分页存储过程
/// 支持多表连接
/// 但在字段比较多的性能低下,
/// 查询结果数据量大时,性能下降严重,如果有条件,且条件字段有索引,则性能提高
/// 有RowID
/// 性能参考,三个表,一个15万条记录,一个260,一个30,(CPU E5700,2G,SQL2005)
/// 每页10条,检索最后第15000页用时2200毫秒,
/// 其中第一个查询中关联查找占用57%的84%,
/// 第二个查询中两个表的键关联查找占用43%的86%
/// 多表联合查询中,表的关联占用了85%的性能损耗
///
/// 单表200W条记录,查找最后一页1080毫秒
/// </summary>
/// <param name="tableName"></param>
/// <param name="outTableName"></param>
/// <param name="fields"></param>
/// <param name="orderField"></param>
/// <param name="sqlWhere"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
[FunctionAttribute(Name = "dbo.Paging")]
[ResultType(typeof(PagedList<>))]
public IMultipleResults GetPagingData([ParameterAttribute(Name = "TableName", DbType = "VarChar(500)")] string tableName, [ParameterAttribute(Name = "OutTableName", DbType = "VarChar(500)")] string outTableName, [ParameterAttribute(Name = "Fields", DbType = "VarChar(5000)")] string fields, [ParameterAttribute(Name = "OrderField", DbType = "VarChar(5000)")] string orderField, [ParameterAttribute(DbType = "VarChar(5000)")] string sqlWhere, [ParameterAttribute(DbType = "Int")] int? pageSize, [ParameterAttribute(DbType = "Int")] int? pageIndex)
{
IExecuteResult result = ExecuteMethodCall(this, ((MethodInfo)(MethodBase.GetCurrentMethod())), tableName, outTableName, fields, orderField, sqlWhere, pageSize, pageIndex);
return ((IMultipleResults)(result.ReturnValue));
}
}
/// <summary>
/// 表数据基类
/// </summary>
public abstract class RepositoryBase<TEntityTable,T> : IPagingRepository
where TEntityTable : class
where T : class, IPagingDataContext, IDisposable, new()
{
#region SQL日志
public static TextWriter sw;
private static StringBuilder sb;
private void WriteLog()
{
while (true)
{
var error = false;
try
{
using (
var sw1 =
new StreamWriter(string.Format("D:\\1\\11.log"),true))
{
string dw = sb.ToString();
sb.Clear();
sw1.Write(dw);
sw1.Flush();
}
}
catch
{
error = true;
}
Thread.Sleep(error ? 500 : 5000);
}
}
#endregion
public RepositoryBase()
{
//if (sw == null)
//{
// sb = new StringBuilder();
// sw = new StringWriter(sb);
// Thread t = new Thread(WriteLog);
// t.IsBackground = true;
// t.Start();
//}
//DataContext.Log = sw;
}
private T _innerDataContext;
private Table<TEntityTable> _innerEntityTable;
/// <summary>
/// 返回分页后的数据表,及分页信息
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="tableName"></param>
/// <param name="fields"></param>
/// <param name="orderField"></param>
/// <param name="sqlWhere"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public PagedList<TEntity> GetPaging<TEntity>(string tableName, string fields, string orderField, string sqlWhere, int? pageSize, int? pageIndex)
where TEntity : class
{
return DataContext.GetPaging<TEntity>(tableName, "List", fields, orderField, sqlWhere, pageSize, pageIndex);
}
/// <summary>
/// 返回表名
/// </summary>
/// <typeparam name="T1">实体类</typeparam>
/// <returns></returns>
private string GetTableName<T1>()
{
var t = typeof(T1);
if (t.IsDefined(typeof(LevcnTableAttribute), false))
{
object[] attributes = t.GetCustomAttributes(typeof(LevcnTableAttribute), false);
var studentAttr = (LevcnTableAttribute)attributes[0];
return studentAttr.Name;
}
return typeof(T1).Name;
}
/// <summary>
/// 获取字段列表
/// </summary>
/// <returns></returns>
private string GetField<T1>()
{
var fieldList = new StringBuilder();
PropertyInfo[] properites = typeof(T1).GetProperties();//得到实体类属性的集合
for (int k = 0; k < properites.Length; k++)//遍历数组
{
PropertyInfo propertyInfo = properites[k];
var fieldName = GetPropertyField(propertyInfo);
if (!string.Equals(fieldName, "rowID", StringComparison.CurrentCultureIgnoreCase))
{
fieldList.Append(fieldName);
if (k < properites.Length - 1)
{
fieldList.Append(",");
}
}
}
return fieldList.ToString();
}
/// <summary>
/// 返回属性所对应的字段名
/// </summary>
/// <param name="propertyInfo"></param>
/// <returns></returns>
private string GetPropertyField(PropertyInfo propertyInfo)
{
if (propertyInfo.IsDefined(typeof(ColumnAttribute), false))
{
var attributes = propertyInfo.GetCustomAttributes(typeof(ColumnAttribute), false);
var studentAttr = (ColumnAttribute)attributes[0];
return studentAttr.Name;
}
return propertyInfo.Name;
}
/// <summary>
/// 数据库查询会话
/// </summary>
protected T DataContext
{
get { return _innerDataContext ?? (_innerDataContext = GetBaseDataContext()); }
}
protected abstract T GetBaseDataContext();
/// <summary>
/// 重置数据库链接
/// </summary>
protected void ReNewDataContext()
{
DataContext.Dispose();
_innerDataContext = GetBaseDataContext();
}
/// <summary>
/// 返回当前表
/// </summary>
protected Table<TEntityTable> EntityTable
{
get { return _innerEntityTable ?? (_innerEntityTable = DataContext.GetTable<TEntityTable>()); }
}
/// <summary>
/// 保存更改
/// </summary>
public void CommitChanges()
{
DataContext.SubmitChanges();
}
/// <summary>
/// 返回分页过的数据列表
/// </summary>
/// <typeparam name="TReturnType">实体类</typeparam>
/// <param name="orderField">排序的字段</param>
/// <param name="condition">条件()</param>
/// <param name="pageSize">每页面显示数据</param>
/// <param name="pageIdx">第几页</param>
/// <param name="pageInfo">分页信息</param>
/// <returns></returns>
public List<TReturnType> FindDataList<TReturnType>(string orderField, string condition, int pageSize, int pageIdx, out PageInfo pageInfo) where TReturnType : class
{
string tableName = GetTableName<TReturnType>();
string fields = GetField<TReturnType>();
var data = GetPaging<TReturnType>(tableName, fields, orderField, condition, pageSize, pageIdx);
pageInfo = data.Page;
return data.ListData;
}
}
/// <summary>
/// 分页表接口
/// </summary>
public interface IPagingRepository
{
List<TReturnType> FindDataList<TReturnType>(string orderField, string condition, int pageSize, int pageIdx,
out PageInfo pageInfo) where TReturnType : class;
//List<TReturnType> FindDataList<TReturnType>(string orderField, string condition, int pageSize, int pageIdx,
// out PageInfo pageInfo) where TReturnType : class;
}
public class PageInfo
{
/// <summary>
/// 总记录数
/// </summary>
public int TotalRecord { get; set; }
/// <summary>
/// 总页
/// </summary>
public int TotalPage { get; set; }
/// <summary>
/// 当前页
/// </summary>
public int PageIndex { get; set; }
public int StartRecord { get; set; }
public int EndRecord { get; set; }
/// <summary>
/// 页面记录数
/// </summary>
public int PageSize { get; set; }
}
public class PagedList<T>
{
public List<T> ListData { get; set; }
public PageInfo Page { get; set; }
}
GO
/****** Object: StoredProcedure [dbo].[Paging] Script Date: 02/09/2012 09:57:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[Paging1]
@TableName varchar(500), --表名
@OutTableName varchar(500) = 'List', --输出的表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000) = '', --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = '', --条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1, --指定当前为第几页
@MastField varchar(500) --可以做为主键列的列名
--返回总页数
/*
--多表联合
exec [Paging]
@tableName = ' T_User as u left join T_Group as g on u.GID = G.GID'
,@OrderField = 'age'
,@Fields = 'name,age'
,@OutTableName = 'DataList' --可选 （输出的表名）
,@pageSize = 5
,@sqlWhere = ' age>4 '
,@PageIndex = 4
--单表
exec [Paging]
@tableName = ' T_User'
,@OrderField = 'age'
,@Fields = 'name,age'
,@OutTableName = 'DataList' --可选 （输出的表名）
,@pageSize = 5
,@sqlWhere = ' age>4 '
,@PageIndex = 1
*/
as
begin
-- Begin Tran --开始事务
Declare @sql nvarchar(4000);
Declare @totalRecord int;
Declare @TotalPage int;
Declare @MastFiledNoTableName varchar(100);
set @MastFiledNoTableName = @MastField
if(CharIndex('.',@MastField)>1)set @MastFiledNoTableName = SUBSTRING(@MastField,CharIndex('.',@MastField)+1,1000)
--计算总记录数
if (@SqlWhere='' or @sqlWhere = NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
--select @sql
--return
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
--计算总页数
select @TotalPage =CEILING((@totalRecord+0.0)/@PageSize)
if (@SqlWhere='' or @sqlWhere=NULL)
begin
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
set @sqlWhere = '1=1'
end
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1
--if @pageIndex>@TotalPage
--Set @pageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
--set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
--set @Sql = @Sql + ') as ' + @OutTableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
set @sql = 'Select ' + @Fields + '
from ' + @TableName + '
where
' + @MastField + ' in(
select ' + @MastFiledNoTableName + '
from(
select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @MastField + '
from ' + @TableName + '
where ' + @sqlWhere + '
) as List
where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) +
')
order by '+@OrderField
--select @sql
--return
Exec(@Sql)
select @totalRecord as TotalRecord,
@TotalPage as TotalPage,
@PageIndex as PageIndex,
@PageSize AS PageSize,
@StartRecord as StartRecord,
@EndRecord as EndRecord
---------------------------------------------------
If @@Error <> 0
Begin
Return -1
End
Else
Begin
Return @totalRecord ---返回记录总数
End
end
GO
/****** Object: StoredProcedure [dbo].[Paging] Script Date: 03/15/2012 12:32:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Paging]
@TableName varchar(500), --表名
@OutTableName varchar(500) = 'List', --输出的表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000) = '', --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = '', --条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 --指定当前为第几页
--返回总页数
/*
--多表联合
exec [Paging]
@tableName = ' T_User as u left join T_Group as g on u.GID = G.GID'
,@OrderField = 'age'
,@Fields = 'name,age'
,@OutTableName = 'DataList' --可选 （输出的表名）
,@pageSize = 5
,@sqlWhere = ' age>4 '
,@PageIndex = 4
--单表
exec [Paging]
@tableName = ' T_User'
,@OrderField = 'age'
,@Fields = 'name,age'
,@OutTableName = 'DataList' --可选 （输出的表名）
,@pageSize = 5
,@sqlWhere = ' age>4 '
,@PageIndex = 1
*/
as
begin
Begin Tran --开始事务
Declare @sql nvarchar(4000);
Declare @totalRecord int;
Declare @TotalPage int;
--计算总记录数
if (@SqlWhere='' or @sqlWhere = NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
--select @sql
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
--计算总页数
select @TotalPage =CEILING((@totalRecord+0.0)/@PageSize)
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1
--if @pageIndex>@TotalPage
--Set @pageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
--set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
set @Sql = @Sql + ') as ' + @OutTableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
--select @sql
Exec(@Sql)
select @totalRecord as TotalRecord,
@TotalPage as TotalPage,
@PageIndex as PageIndex,
@PageSize AS PageSize,
@StartRecord as StartRecord,
@EndRecord as EndRecord
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
end
7000
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
