Linq To SQL通用分页存储过程二个,支持多表联合.

5 篇文章 0 订阅
4 篇文章 0 订阅
/// <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



  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值