Sliverlight DataGrid 分頁 資料庫中分頁，速度更快

iDotNetSpace發表於2009-07-29
資料庫

在資料庫執行這段儲存過程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Pager](
@tblName nvarchar(200), ----要顯示的表或多個表的連線
@fldName nvarchar(1000) = '*', ----要顯示的欄位列表
@pageSize int, ----每頁顯示的記錄個數
@pageIndex int, ----要顯示那一頁的記錄
@pageCount int = 1 output, ----查詢結果分頁後的總頁數
@Counts int = 1 output, ----查詢到的記錄數
@strCondition nvarchar(1000), ----查詢條件,不需where
@ID nvarchar(50) ----主表的主鍵
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放動態生成的SQL語句
Declare @strTmp nvarchar(1000) ----存放取得查詢結果總數的查詢語句
Declare @strID nvarchar(1000) ----存放取得查詢開頭或結尾ID的查詢語句
Declare @sqlSort nvarchar(200) ----存放臨時生成的排序條件
Declare @intCounts int ----要移動的記錄數
Declare @BeginID int ----開始的ID
Declare @EndID int ----結束的ID

--------生成查詢語句--------
--此處@strTmp為取得查詢結果數量的語句
if @strCondition is null --沒有設定顯示條件
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName
set @strID = ' From ' + @tblName + ' order by '+ @ID
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where ' + @strCondition
set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName + ' where ' + @strCondition
set @strID = ' From ' + @tblName + ' where ' + @strCondition + ' order by '+ @ID
end

----取得查詢結果總數量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out

--取得分頁總數
if @Counts <= @pageSize
set @pageCount = 1
else
set @pageCount = (@Counts / @pageSize) + 1

--計算要移動的記錄數
if @pageIndex  = 1
set @intCounts = 1
else
begin
set @intCounts = (@pageIndex -1) * @pageSize + 1
end

--如果啟始行記數超過總行數，退出
if @intCounts > @Counts
   return

-----取得分頁後此頁的第一條記錄的ID
set @strID = 'select @BeginID=' + @ID + ' ' + @strID

set rowcount @intCounts
exec sp_executesql @strID,N'@BeginID int out ',@BeginID out

-----取得分頁後此頁的最後一條記錄的ID
set @intCounts = @intCounts + @pageSize -1
print @intCounts
set rowcount @intCounts
exec sp_executesql @strID,N'@BeginID int out ',@EndID out

------恢復系統設定-----
set rowcount 0
SET NOCOUNT OFF

------返回查詢結果-----
if @strCondition is null
set @strTmp = 'select ' + @sqlTmp + ' where ' + @ID + ' between ' + str(@BeginID) + ' and ' + str(@EndID)
else
set @strTmp = 'select ' + @sqlTmp + ' and ' + @ID +' between ' + str(@BeginID) + ' and ' + str(@EndID)

exec sp_executesql @strTmp

 

用法：(後臺服務)

public void GetPageList(int PageIndex, string strWhere, out int PageCount, out int TotalCount)

{

SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.NVarChar, 200),
                    new SqlParameter("@fldName", SqlDbType.NVarChar, 1200),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@pageCount", SqlDbType.Int,4),
                    new SqlParameter("@Counts", SqlDbType.Int),
                    new SqlParameter("@strCondition", SqlDbType.NVarChar,1000),
                    new SqlParameter("@ID", SqlDbType.NVarChar,50)
                    };
            parameters[0].Value = "表名";
            parameters[1].Value = @"a,b,c";
            parameters[2].Value = 8;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[4].Direction = ParameterDirection.Output;
            parameters[5].Value = 0;
            parameters[5].Direction = ParameterDirection.Output;

            parameters[6].Value = strWhere;
            parameters[7].Value = "表ID";

SqlDataReader reader = DbHelperSQL.RunProcedure("Pager", parameters);

if (reader != null)
           {

………………….

reader.Close();
           }
            PageCount = convert.toint32(parameters[4].Value.ToString());
           TotalCount = convert.toint32(parameters[5].Value.ToString());

}

前臺

新建 Pager .xaml

    x:Class="Ehope.SLControls.Pager"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d" 
    xmlns:vsm="clr-namespace:System.Windows;assembly=System.Windows"
    Width="Auto" Height="Auto">

   
       
           
           
           
           
           
               
                   
                       
                           
                               
                                   
                                       
                                           
                                               
                                           
                                       
                                   
                                   
                                       
                                           
                                               
                                           
                                       
                                   
                                   
                                       
                                           
                                               
                                           
                                       
                                   
                                   
                                       
                                           
                                               
                                           
                                           
                                               
                                           
                                           
                                               
                                           
                                       
                                   
                               
                               
                                   
                                       
                                           
                                               
                                           
                                       
                                   
                                   
                               
                           
                           
                               
                           
                           
                           
                           
                       
                   
               
           
       
       
           
           
           
           
           
               
                   
                       
                           
                               
                                   
                                       
                                           
                                               
                                           
                                       
                                   
                                   
                                       
                                           
                                               
                                           
                                       
                                   
                                   
                                       
                                           
                                               
                                           
                                       
                                   
                                   
                                       
                                           
                                               
                                           
                                           
                                               
                                           
                                           
                                               
                                           
                                       
                                   
                               
                               
                                   
                                       
                                           
                                               
                                           
                                       
                                   
                                   
                               
                           
                           
                               
                           
                           
                           
                           
                       
                   
               
           
       
   

   

       

   

 

Pager .xaml.cs

 

public partial class Pager : UserControl
    {
        #region Properties
        ///


        /// Total Number of Pages for this Pager Control
        ///
        public int PageCount { get; set; }
        ///
        /// Current Page Index
        ///
        public int PageIndex { get; set; }
        ///
        /// Number of buttons to render before/after current selection
        /// For example, if = 2, buttons appear for a control with 100 pages
        /// /// Button 5 selected:
        ///     Previous 1 2 3 4 (5) 6 7 ... 99 100 Next
        /// Button 6 selected:
        ///     Previous 1 2 ... 4 5 (6) 7 8 ... 99 100 Next
        /// Button 95 selected
        ///     Previous 1 2 ... 93 94 (95) 96 97 ... 99 100 Next
        /// Button 96 selected
        ///     Previous 1 2 ... 94 95 (96) 97 98 99 100 Next
        ///
        public int PageButtonCount { get; set; }
        ///
        /// EVent delegate for a specific Pager Button
        ///
        ///
        ///
        public delegate void PagerButtonClick(object sender, RoutedEventArgs e);
        ///
        /// Event handler for a specific Pager Button
        ///
        public event PagerButtonClick Click;
        #endregion

        public Pager()
        {
            InitializeComponent();

            // Default Settings
            PageCount = 10;
            PageIndex = 1;
            PageButtonCount = 3;

            this.Loaded += new RoutedEventHandler(Pager_Loaded);
        }
        public Pager(int pageCount, int pageButtonCount)
        {
            InitializeComponent();

            PageCount = pageCount;
            PageIndex = 1;
            PageButtonCount = pageButtonCount;

            this.Loaded += new RoutedEventHandler(Pager_Loaded);
        }

        protected void Pager_Loaded(object sender, RoutedEventArgs e)
        {
            BuildPager();
        }

        #region Internal
        ///


        /// Renders a Button control for the Pager
        ///
        /// Text to display
        /// Flag to denote if this refers to the inner buttons or the Previous/Next buttons
        /// Flag to denote if this button is enabled
        /// Button
        private Button BuildButton(string text, bool inner, bool enabled)
        {
            Button b = new Button()
            {
                Content = text,
                Tag = text,
                Style. = inner ? this.Resources["PagerButtonInnerStyle"] as Style. this.Resources["PagerButtonOuterStyle"] as Style,
                Width = 45
            };

            if (inner == false)
                b.Width = 75;

            b.IsEnabled = enabled;

            b.Click += new RoutedEventHandler(PagerButton_Click);

            return b;
        }
        ///


        /// Renders a selected Button or the Hellip (...) TextBlock
        ///
        /// Text to display
        /// Flag to denote if this button is to have a border
        /// UIElement (either a TextBlock or a Border with a TextBlock within)
        private UIElement BuildSpan(string text, bool border)
        {
            if (border)
            {
                TextBlock t = new TextBlock()
                {
                    Text = text,
                    TextAlignment = TextAlignment.Center,
                    VerticalAlignment = VerticalAlignment.Center,
                    Width = 30
                };
                Border b = new Border()
                {
                    Margin = new Thickness(3, 3, 3, 3),
                    BorderThickness = new Thickness(0.5, 0.5, 0.5, 0.5),
                    BorderBrush = new SolidColorBrush(Color.FromArgb(255, 0, 0, 255)),
                    Width = 30,
                    Height = 22,
                    HorizontalAlignment = HorizontalAlignment.Center,
                    VerticalAlignment = VerticalAlignment.Center
                };
                b.Child = t;
                return b;
            }
            else
            {
                return new TextBlock()
                {
                    Text = text,
                    Width = 30,
                    Height = 22,
                    TextAlignment = TextAlignment.Center,
                    VerticalAlignment = VerticalAlignment.Bottom
                };
            }
        }
        ///
        /// Build the Pager Control
        ///
        private void BuildPager()
        {
            this.spPager.Children.Clear();

            if (PageCount > 1)
            {
                int min = PageIndex - PageButtonCount;
                int max = PageIndex + PageButtonCount;

                if (max > PageCount)
                    min -= max - PageCount;
                else if (min < 1)
                    max += 1 - min;

                // Previous Button
                if (PageIndex > 1)
                    this.spPager.Children.Add(BuildButton("上一頁", false, true));
                else // Disabled State
                    this.spPager.Children.Add(BuildButton("上一頁", false, false));

                // Middle Buttons
                bool needDiv = false;
                for (int i = 1; i <= PageCount; i++)
                {
                    if (i <= 2 || i > PageCount - 2 || (min <= i && i <= max))
                    {
                        string text = i.ToString(NumberFormatInfo.InvariantInfo);

                        if (i == PageIndex) // Currently Selected Index
                            this.spPager.Children.Add(BuildSpan(text, false));
                        else
                            this.spPager.Children.Add(BuildButton(text, true, true));

                        needDiv = true;
                    }
                    else if (needDiv)
                    {
                        // This will add the hellip (...) TextBlock
                        this.spPager.Children.Add(BuildSpan("...", false));
                        needDiv = false;
                    }
                }

                // Next Button
                if (PageIndex < PageCount)
                    this.spPager.Children.Add(BuildButton("下一頁", false, true));
                else // Disabled State
                    this.spPager.Children.Add(BuildButton("下一頁", false, false));
            }
        }
        #endregion

        #region Event Handlers
        ///


        /// Handles Pager Button click
        /// Sets proper PageIndex for rendering
        ///
        ///
        ///
        protected void PagerButton_Click(object sender, RoutedEventArgs e)
        {
            Button b = e.OriginalSource as Button;

            if (b.Tag.ToString() == "上一頁")
            {
                PageIndex--;
            }
            else if (b.Tag.ToString() == "下一頁")
            {
                PageIndex++;
            }
            else
            {
                int p = PageIndex;
                int.TryParse(b.Tag.ToString(), out p);
                PageIndex = p;
            }
            BuildPager();
            Click(sender, e);
        }
        #endregion
    }

 

呼叫頁面

a.xaml

a.xaml.cs

Pager Pager = null;

        ///


        /// 分頁查詢的頁索引初始值
        ///
        private int PageIndex = 1;

 

 

if (datagrid.ItemsSource != null)
            {
                datagrid.ItemsSource = null;
            }

            //第一次得到資料後載入分頁控制元件
            if (Pager == null)
            {
                Pager = new Pager(e.PageCount, 2);

                Pager.Click += new Pager.PagerButtonClick(Pager_Click);

                PagerPanel.Children.Clear();

                PagerPanel.Children.Add(Pager);
            }

//strWhere 是查詢條件

            if (GetPageList(PageIndex,strWhere）!= null)
            {
                datagrid.ItemsSource = GetPageList(PageIndex,strWhere）
            }

 

private void Pager_Click(object sender, RoutedEventArgs e)
        {
            Button btn = e.OriginalSource as Button;
            if (btn.Content.ToString() == "上一頁")
                PageIndex -= 1;
            else if (btn.Content.ToString() == "下一頁")
                PageIndex += 1;
            else
            {
                int.TryParse(btn.Content.ToString(), out PageIndex);

            }

            if (PageIndex <= 0)
                PageIndex = 1;

datagrid.ItemsSource = GetPageList(PageIndex,strWhere）;
        }

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-610734/，如需轉載，請註明出處，否則將追究法律責任。

相關文章