ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Sliverlight DataGrid 分页 数据库中分页,速度更快

Sliverlight DataGrid 分页 数据库中分页,速度更快

原创 Linux操作系统 作者:iDotNetSpace 时间:2009-07-29 14:38:46 0 删除 编辑

在数据库执行这段存储过程

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-04

  • 博文量
    2376
  • 访问量
    5297295