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;
        }