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

/// <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 ONGOSET QUOTED_IDENTIFIER ONGOCreate 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                          */asbegin--    Begin Tran --开始事务    Declare @sql nvarchar(4000);    Declare @totalRecord int;    Declare @TotalPage int;Declare @MastFiledNoTableName varchar(100);set @MastFiledNoTableName = @MastFieldif(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)beginset @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 Listwhere 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 ONGOSET QUOTED_IDENTIFIER ONGOALTER 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                          */asbegin    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


	
				
	
					   
Linq To SQL通用分页存储过程二个,支持多表联合.
	  	   
[MSSQL]SQL通用存储过程分页,支持多表联合
	  	   
又一个通用分页存储过程，支持表别名，多表联合查询SQL语句
	  	   
又一个通用分页存储过程，支持表别名，多表联合查询SQL语句--转载
	  	   
分页存储过程支持多表联合
	  	   
数据库分页存储过程，支持多表联合查询
	  	   
多表联合查询sql存储过程带分页
	  	   
SQL 通用分页存储过程
	  	   
SQL 通用分页存储过程
	  	   
sql通用分页存储过程
	  	   
sql 通用分页存储过程
	  	   
sql 通用分页存储过程
	  	   
SQL分页通用存储过程
	  	   
通用sql分页存储过程
	  	   
通用SQL分页存储过程
	  	   
linq to sql 存储过程
	  	   
linq to sql 存储过程
	  	   
联合表 分页存储过程
	     		  
	  	   
linux 管理小命令9—— 定时执行任务命令at和crontab
	  	   
emacs tips vim
	  	   
iPhone开发--警告Receiver 'ClassName' is a forward class and corresponding @interface may not exist
	  	   
精通JAVA的25个标准
	  	   
架构那点事系列三 - 由EAI到ESB
	  	   
Linq To SQL通用分页存储过程二个,支持多表联合.
	  	   
标号Label的使用
	  	   
Row_Num()
	  	   
正则替换字符串的全角、半角标点符号
	  	   
sys_connect_by_path
	  	   
Mac OS Evolution
	  	   
算法学习【1】 - 1027. MJ, Nowhere to Hide
	  	   
Linux进入系统时自动设置环境变量的方法
	  	   
vmware  vpshere esxi 建立共享磁盘