C# Database.Page方法代码示例

        protected override void PagedEntities(int entityCount)
        {
            using (var database = new Database("SQLiteTest"))
            {
                using (var transaction = database.GetTransaction())
                {
                    var results = database.Page<Entity>(1, entityCount, new Sql("SELECT * FROM Entity"));

                    transaction.Complete();
                }
            }
        }

        /// <summary>
        /// 执行耗时的语句 
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="connName"></param>
        /// <param name="totalElapsedTime"> 完成此计划的执行所占用的总时间 </param>
        /// <param name="beginDt"> 最后一次执行时间 </param>
        /// <param name="endDt"> 最后一次执行时间 </param>
        /// <returns></returns>
        public static PetaPoco.Page<dynamic> CastLongTimeSqlGetList(int pageSize, int pageIndex, string connName, DateTime beginDt, DateTime endDt)
        {
            var db = new PetaPoco.Database(connName);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@" select *  from ( SELECT top 10000
total_worker_time/execution_count/1000000 AS 'AvgCpu',
execution_count       as '执行次数',
total_worker_time/1000     as '总共占用CPU',
creation_time         as '创建时间',
last_execution_time   as '最后执行时间',
min_worker_time       as '最低每次占用CPU',
max_worker_time       as '最高每次占用cpu',
total_physical_reads  as '总共io物理读取次数',
total_logical_reads   as '总共逻辑读取次数',
total_logical_writes  as '总共逻辑写次数',
total_elapsed_time/1000000    as '完成此计划的执行所占用的总时间秒',
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)

) AS 'SQL内容'
FROM sys.dm_exec_query_stats");

            if (beginDt.Year > 2000)
            {
                sb.AppendFormat("  WHERE last_execution_time>='{0}' ", beginDt);
            }

            if (endDt.Year > 2000)
            {
                sb.AppendFormat("  WHERE last_execution_time<='{0}' ", endDt);
            }

            sb.AppendFormat(@"   ORDER BY AvgCpu DESC)  a");
            PetaPoco.Page<dynamic> result = new Page<dynamic>();
            try
            {
                result = db.Page<dynamic>(pageIndex, pageSize, sb.ToString());
            }
            catch (Exception ex)
            {
                var mm = ex.Message;
            }

            return result;
        }

        public IEnumerable<MixERP.Net.Entities.Core.Account> GetPagedResult(long page=1)
        {
            ApiAccessPolicy policy = new ApiAccessPolicy(typeof(MixERP.Net.Entities.Core.Account), "GET");
            policy.Authorize();

            if (!policy.IsAuthorized)
            {
                throw new HttpResponseException(HttpStatusCode.Forbidden);
            }

            try
            {
                using (Database db = new Database(Factory.GetConnectionString(), "Npgsql"))
                {
                    return db.Page<MixERP.Net.Entities.Core.Account>(page, 10, "SELECT * FROM core.accounts ORDER BY account_id").Items;
                }
            }
            catch
            {
                throw new HttpResponseException(HttpStatusCode.InternalServerError);
            }
        }

 private StringBuilder Aflist(int pageindex)
 {
     StringBuilder list = new StringBuilder();
     string str = string.Empty;
     var objs = new List<object>();
     List<T_ERP_Aftersales> sales = new List<T_ERP_Aftersales>();
     using (var db = new Database(SQLCONN.Conn))
     {
         var page = db.Page<T_ERP_Aftersales>(pageindex, PageSize, "select * from T_ERP_Aftersales where IsDel = 1 Order by Createdate desc", objs.ToArray());
         total.Value = (((int)page.TotalItems % PageSize == 0) ? ((int)page.TotalItems / PageSize) : ((int)page.TotalItems / PageSize + 1)).ToString();//总页数
         totalRecords.Value = ((int)page.TotalItems).ToString();
         sales = page.Items;
     }
     if (sales == null || sales.Count < 1) return list;
     foreach (var st in sales)
     {
         str += " <div class='comment_box'><div class='info'><span class='c_info fr'>" + st.CreateDate.ToString("yyyy-MM-dd") + "</span><div><a title='查看详细' target='_blank' href='SalesDetal.aspx?SGD=" + st.Guid + "'><span class='mr40'>姓名：" + st.Applicanter + "</span>&nbsp;&nbsp;运单号：" + st.CheckNo + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;状态：" + (st.IsRefuse == false ? "已处理" : "已驳回") + "</a></div></div>";
         str += "<div class='c_cont'>" + st.Problemdescription + "</div><div class='reply_box'>";
         str += "<div class='reply' style='width:767px;'>" +"回复："+ st.Solution + "</div><div class='date'>" + st.ProcessingTime.Value.ToString("yyyy-MM-dd") + "</div></div></div>";
     }
     list.Append(str);
     return list;
 }

        /// <summary>
        /// 一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈 
        /// </summary>
        /// <returns></returns>
        public static PetaPoco.Page<dynamic> GetWaitTimeForResource(string connName)
        {
            var db = new PetaPoco.Database(connName);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 , SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,");
            sb.AppendFormat("SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [signal_wait_percent信号等待％],");
            sb.AppendFormat("SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent资源等待％]");
            sb.AppendFormat("FROM sys.dm_os_wait_stats");
            PetaPoco.Page<dynamic> result = new Page<dynamic>();
            try
            {
                result = db.Page<dynamic>(1, 100, sb.ToString());
            }
            catch (Exception ex)
            {
                var mm = ex.Message;
            }

            return result;
        }

        /// <summary>
        /// 表空间大小查询 
        /// </summary>
        /// <returns></returns>
        public static PetaPoco.Page<dynamic> GetTableSpaceSize(string connName)
        {
            var db = new PetaPoco.Database(connName);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("   create table #tb(表名 sysname,记录数 int,保留空间 varchar(100),使用空间 varchar(100),索引使用空间 varchar(100),未用空间 varchar(100)) insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''  select * from #tb order by 记录数 desc ;DROP TABLE #tb; ");
            PetaPoco.Page<dynamic> result = new Page<dynamic>();
            try
            {
                result = db.Page<dynamic>(1, 500, sb.ToString());
            }
            catch (Exception ex)
            {
                var mm = ex.Message;
            }

            return result;
        }

        /// <summary>
        /// 查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU) 
        /// </summary>
        /// <param name="topNum"> 100 </param>
        /// <returns></returns>
        public static PetaPoco.Page<dynamic> GetSessionOfCpuHighAndSqlText(string connName)
        {
            var db = new PetaPoco.Database(connName);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" select *  from ( select spid,cmd,cpu,physical_io,memusage,(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text  from master.dbo.sysprocesses  )a  where [sql_text] is not null  order by cpu desc,physical_io desc ");
            PetaPoco.Page<dynamic> result = new Page<dynamic>();
            try
            {
                result = db.Page<dynamic>(1, 100, sb.ToString());
            }
            catch (Exception ex)
            {
                var mm = ex.Message;
            }

            return result;
        }

        public static PetaPoco.Page<dynamic> GetSessionCount(string connName)
        {
            var db = new PetaPoco.Database(connName);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("  exec   sp_who   'active' ;print @@rowcount ");
            PetaPoco.Page<dynamic> result = new Page<dynamic>();
            try
            {
                result = db.Page<dynamic>(1, 100, sb.ToString());
            }
            catch (Exception ex)
            {
                var mm = ex.Message;
            }

            return result;
        }

        private static void TestPaging(Database db, int herd, DateTime? date)
        {
            Console.WriteLine("sivutettu haku dynaamisella sql:llä...");
            Console.WriteLine();

            var sql2 = PetaPoco.Sql.Builder
                .Append("SELECT * FROM idkarbovine with(nolock)")
                .Append("WHERE [email protected]", herd)
                .Append("AND ValidFromDate<[email protected]", date.Value)
                .Append("AND ValidDueDate>[email protected]", date.Value)
                .Append("ORDER BY EarNr");

            var page1 = db.Page<Bovine>(1, 10, sql2);
            Console.WriteLine("Total items:" + page1.TotalItems);
            Console.WriteLine("Total pages:" + page1.TotalPages);
            Console.WriteLine("Current page:" + page1.CurrentPage);
            foreach (var a in page1.Items)
            {
                Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
            }

            Console.WriteLine();
            var page2 = db.Page<Bovine>(2, 10, sql2);
            Console.WriteLine("Total items:" + page2.TotalItems);
            Console.WriteLine("Total pages:" + page2.TotalPages);
            Console.WriteLine("Current page:" + page2.CurrentPage);
            foreach (var a in page2.Items)
            {
                Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
            }
            Console.WriteLine();

            var page3 = db.Page<Bovine>(3, 10, sql2);
            Console.WriteLine("Total items:" + page3.TotalItems);
            Console.WriteLine("Total pages:" + page3.TotalPages);
            Console.WriteLine("Current page:" + page3.CurrentPage);
            foreach (var a in page3.Items)
            {
                Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
            }

            Console.WriteLine();
            var page4 = db.Page<Bovine>(4, 10, sql2);
            Console.WriteLine("Total items:" + page4.TotalItems);
            Console.WriteLine("Total pages:" + page4.TotalPages);
            Console.WriteLine("Current page:" + page4.CurrentPage);
            foreach (var a in page4.Items)
            {
                Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
            }
        }

 private StringBuilder Aflist(int pageindex)
 {
     StringBuilder list = new StringBuilder();
     string str = string.Empty;
     var objs = new List<object>();
     List<T_ERP_Message> message = new List<T_ERP_Message>();
     using (var db = new Database(SQLCONN.Conn))
     {
         var page = db.Page<T_ERP_Message>(pageindex, PageSize, "select * from T_ERP_Message where IsDel = 1 Order by Createdate desc", objs.ToArray());
         total.Value = (((int)page.TotalItems % PageSize == 0) ? ((int)page.TotalItems / PageSize) : ((int)page.TotalItems / PageSize + 1)).ToString();//总页数
         totalRecords.Value = ((int)page.TotalItems).ToString();
         message = page.Items;
     }
     if (message == null || message.Count < 1) return list;
     foreach (var st in message)
     {
         str += " <div class='comment_box'><div class='info'><span class='c_info fr'>" + st.CreateDate.ToString("yyyy-MM-dd") + "</span><div><a><span class='mr40'>姓名：" + st.Applicanter + "</span>&nbsp;&nbsp;标题：" + st.Title + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;状态：" + (st.IsCancel == false ? "已处理" : "已驳回") + "</a></div></div>";
         str += "<div class='c_cont'>" + st.Description + "</div><div class='reply_box'>";
         str += "<div class='reply' style='width:767px;'>" + "评论：" + st.Comment + "</div><div class='date'>" + st.CommentTime.Value.ToString("yyyy-MM-dd") + "</div></div></div>";
     }
     list.Append(str);
     return list;
 }

        /// <summary> 查询是否由于连接没有释放引起CPU过高 spid<=50的是系统的会话，所以平时查询，最好加>50 </summary> <param
        /// name="topNum">100</param> <returns></returns>
        public static PetaPoco.Page<dynamic> GetCpuHighForConnectionNoRelease(string connName)
        {
            var db = new PetaPoco.Database(connName);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select  * from master.dbo.sysprocesses where spid> 50 and waittype = 0x0000 and waittime = 0 and status = 'sleeping ' and last_batch < dateadd(minute, -10, getdate()) and login_time < dateadd(minute, -10, getdate()) ");
            PetaPoco.Page<dynamic> result = new Page<dynamic>();
            try
            {
                result = db.Page<dynamic>(1, 100, sb.ToString());
            }
            catch (Exception ex)
            {
                var mm = ex.Message;
            }

            return result;
        }

        public static PetaPoco.Page<dynamic> GetOpSystemMemory(string connName)
        {
            StringBuilder sb = new StringBuilder();
            var db = new PetaPoco.Database(connName);
            sb.AppendFormat("select * from sys.dm_os_sys_memory  ");
            PetaPoco.Page<dynamic> result = new Page<dynamic>();
            try
            {
                result = db.Page<dynamic>(1, 100, sb.ToString());
            }
            catch (Exception ex)
            {
                var mm = ex.Message;
            }

            return result;
        }

        /// <summary>
        /// 搜索sql根据逻辑读写次数等 
        /// </summary>
        /// <param name="dateBegin"></param>
        /// <param name="dateEnd"></param>
        /// <param name="PhysicalReadsTimes"></param>
        /// <param name="ExecutionCount"></param>
        /// <param name="LogicalReadsTimes"></param>
        /// <param name="LogicalWritesTimes"></param>
        /// <param name="ElapsedTime"></param>
        /// <param name="keyword"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="connName"></param>
        /// <returns></returns>
        public static PetaPoco.Page<dynamic> GetSqlByReadWrite(DateTime dateBegin, DateTime dateEnd, int PhysicalReadsTimes, int ExecutionCount, int LogicalReadsTimes, int LogicalWritesTimes, int ElapsedTime, string keyword, int pageSize, int pageIndex, string connName)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT   creation_time  N'语句编译时间' ");
            sb.AppendFormat("  ,last_execution_time  N'上次执行时间'");
            sb.AppendFormat("   ,total_physical_reads N'物理读取总次数'");
            sb.AppendFormat("   ,total_logical_reads/execution_count N'每次逻辑读次数'");
            sb.AppendFormat("   ,total_logical_reads  N'逻辑读取总次数'");
            sb.AppendFormat("   ,total_logical_writes N'逻辑写入总次数'");
            sb.AppendFormat("  , execution_count  N'执行次数'");
            sb.AppendFormat("   , total_worker_time/1000 N'所用的CPU总时间ms'");
            sb.AppendFormat("  , total_elapsed_time/1000  N'总花费时间ms'");
            sb.AppendFormat("   , (total_elapsed_time / execution_count)/1000  N'平均时间ms'");
            sb.AppendFormat("  ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,");
            sb.AppendFormat("   ((CASE statement_end_offset ");
            sb.AppendFormat("    WHEN -1 THEN DATALENGTH(st.text)");
            sb.AppendFormat("   ELSE qs.statement_end_offset END ");
            sb.AppendFormat("    - qs.statement_start_offset)/2) + 1) N'执行语句'");
            sb.AppendFormat("  FROM sys.dm_exec_query_stats AS qs");
            sb.AppendFormat("  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st");
            sb.AppendFormat("  where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,");
            sb.AppendFormat("   ((CASE statement_end_offset ");
            sb.AppendFormat("   WHEN -1 THEN DATALENGTH(st.text)");
            sb.AppendFormat("  ELSE qs.statement_end_offset END ");
            sb.AppendFormat("   - qs.statement_start_offset)/2) + 1) not like '%fetch%'");

            if (dateBegin > Convert.ToDateTime("2015-01-01"))
            {
                sb.AppendFormat(" and last_execution_time>='{0}'", dateBegin);
            }

            if (dateEnd > Convert.ToDateTime("2015-01-01"))
            {
                sb.AppendFormat(" and last_execution_time<='{0}'", dateEnd);
            }

            if (PhysicalReadsTimes > 0)
            {
                sb.AppendFormat("  and total_physical_reads>={0} ", PhysicalReadsTimes);
            }

            if (ExecutionCount > 0)
            {
                sb.AppendFormat(" and execution_count>={0}", ExecutionCount);
            }

            if (LogicalReadsTimes > 0)
            {
                sb.AppendFormat(" and total_logical_reads>={0}", LogicalReadsTimes);
            }

            if (LogicalWritesTimes > 0)
            {
                sb.AppendFormat(" and total_logical_writes>={0}", LogicalWritesTimes);
            }

            if (ElapsedTime > 0)
            {
                sb.AppendFormat(" and total_elapsed_time>={0} ", Convert.ToInt32(ElapsedTime / 1000));
            }

            if (!string.IsNullOrEmpty(keyword))
            {
                sb.AppendFormat("  and   st.text like '%{0}%'", keyword);
            }

            sb.AppendFormat("  ORDER BY  total_elapsed_time / execution_count DESC;");

            PetaPoco.Page<dynamic> result = new Page<dynamic>();
            var db = new PetaPoco.Database(connName);
            try
            {
                result = db.Page<dynamic>(pageIndex, pageSize, sb.ToString());
            }
            catch (Exception ex)
            {
                var mm = ex.Message;
            }

            return result;
        }

 private void BindGrid(string sortfield, string sort)
 {
     if (Users == null)
     {
         SetSession();
         return;
     }
     int pageIndex = 0;
     if (list.PageIndex == 0)
     {
         pageIndex += 1;
     }
     else
     {
         pageIndex = list.PageIndex + 1;
     }
     int pageSize = list.PageSize;
     string whr = string.Empty;
     if (Users.SuppName != "admin")
     {
         whr = string.Format(" where SuppName = '{0}' ", Users.SuppName);
     }
     else
     {
         whr = " where 1=1";
     }
     var objs = new List<object>();
     var idx = 0;
     if (!string.IsNullOrEmpty(this.txtOuterIid.Text))
     {
         whr += " and OuterIid  like @" + (idx++).ToString();
         objs.Add("%" + this.txtOuterIid.Text.Trim() + "%");
     }
     if (!string.IsNullOrEmpty(this.txtOuterSkuId.Text))
     {
         whr += " and OuterSkuId  like @" + (idx++).ToString();
         objs.Add("%" + this.txtOuterSkuId.Text.Trim() + "%");
     }
     if (!string.IsNullOrEmpty(this.txtModel.Text))
     {
         whr += " and Model  like @" + (idx++).ToString();
         objs.Add("%" + this.txtModel.Text.Trim() + "%");
     }
     whr += " order by " + sortfield + " " + sort;
     using (var db = new Database(SQLCONN.Conn))
     {
         var pages = db.Page<V_ERP_Inventory>(pageIndex, pageSize, "select * from V_ERP_Inventory " + whr, objs.ToArray());
         list.RecordCount = (int)pages.TotalItems;
         this.list.DataSource = pages.Items;
         list.DataBind();
     }
 }

        /// <summary> 查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局 bucketid 
        //int
        //存储桶 ID。该值指示从 0 到 1 的范围（目录大小）。目录大小是哈希表的大小。
        //refcounts
        //int
        //引用该缓存对象的其他缓存对象数。计数 1 为基数。
        //usecounts
        //int
        //自开始以来使用该缓存对象的次数。
        //pagesused
        //int
        //缓存对象消耗的内存页数。
        //cacheobjtype
        //nvarchar(34)
        //缓存中的对象类型。以下类型之一：
        //编译计划
        //可执行计划
        //分析树
        //扩展存储过程
        //memory_object_address
        //varbinary(8)
        //计划的内存地址。
        //objtype
        //nvarchar(16)
        //对象的类型。可以是下列类型之一：
        //Proc--存储过程
        //Prepared--预定义语句
        //Adhoc--即席查询
        //ReplProc--复制筛选过程
        //Trigger--触发器
        //View--视图
        //Default--默认值
        //UsrTab--用户表
        //SysTab--系统表
        //CheckCHECK--约束 Rule规则
        //plan_handle
        //varbinary(64)
        //内存中计划的标识符。该标识符是瞬态的
        //仅当计划保留在缓存中时，它才保持不变。
        //该值可以与 sys.dm_exec_query_plan 动态管理函数以及sys.dm_exec_plan_attributes 动态管理函数一同使用
        /// </summary> <param name="pageIndex"></param> <param name="pageSize"></param> <param
        /// name="orderby"> 排序 , usecounts或者 size </param> <returns></returns>
        public PetaPoco.Page<object> GetSqlOfCacheReusedFewAndMemeryMany(int pageIndex, int pageSize, string connName, string orderField = "", string order = "asc")
        {
            var db = new PetaPoco.Database(connName);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"SELECT  usecounts,
case cacheobjtype
when 'Compiled Plan' then '编译计划'
when 'Parse Tree' then '解析树'
when 'Extended Proc' then '扩展存储过程'
when 'CLR Compiled Func' then 'clr编译函数'
when 'CLR Compiled Proc' then 'clr编译过程'
ELSE '未知' END
cacheobjtype,  refcounts,bucketid,
case objtype
when 'proc' then '存储过程'
when 'Prepared' then '预定义语句'
when 'Adhoc' then '即席查询'
when 'ReplProc' then '复制筛选过程'
when 'Trigger' then '触发器'
when 'View' then '视图'
when 'Default' then '默认值'
when 'UsrTab' then '用户表'
when 'SysTab' then '系统表'
when 'Check' then '约束'
when 'Rule' then '规则'
ELSE '未知' END
objtype, p.size_in_bytes,[sql].[text] FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql ORDER BY ");
            order = order == "asc" ? "asc" : "desc";

            switch (orderField)
            {
                case "usecounts":
                    sb.AppendFormat("  usecounts {0} ,p.size_in_bytes desc ", order);
                    break;

                case "size":
                    sb.AppendFormat(" p.size_in_bytes {0}, usecounts desc ", order);
                    break;

                default:
                    sb.AppendFormat("  usecounts {0},p.size_in_bytes desc ", order);
                    break;
            }

            PetaPoco.Page<dynamic> result = new Page<dynamic>();
            try
            {
                result = db.Page<dynamic>(pageIndex, pageSize, sb.ToString());
            }
            catch (Exception ex)
            {
                var mm = ex.Message;
            }

            return result;
        }