C# sql存儲過程(Stored Procedure)分页查詢功能

       前几天工作，用到了存储过程的分页，觉得存储过程还挺好用的,工作了这么久,也没做啥笔记,整天就写写代码，这段时间我会慢慢整理一些简单的知识点,之前一直只是看着别人的文章，也没想说自己写一个,现在发觉写写还能当笔记用,顺便也给自己做一下笔记,

      

sql server 存储过程代码

USE [數據表]
GO
/****** Object:  StoredProcedure [dbo].[sp_EmpInfo_Date]    Script Date: 04/18/2017 09:50:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc  [dbo].[sp_EmpInfo_Date]
@pageSize int, --一页显示几笔数据
@pageIndex int, -- 第几页
@dataCount int output, --总数据
@pageCount int output, --总页数
@EmpName varchar(30)  --輸入名字
AS
BEGIN
	SET NOCOUNT ON;
	
	declare @sqlMain varchar(max) = ' '
	declare @sqlFilter varchar(max)=' '
    declare @sqlMaster varchar(max)=' '
    declare @dataCountSql nvarchar(max)=' '
    
    if(@pageIndex < 2)
        set @pageIndex = 1
    declare @mmin int = @pageSize*(@pageIndex -1)+1
    declare @mmax int = @pageIndex*@pageSize
    
set @sqlMain = ' select row_number() over(order by tmpid) as num,UserName,CreateTime
                 from MYW..SysAccount where 1=1'
if(@EmpName<> '')  set   @sqlFilter =  ' and @EmpName = ' + @EmpName 


set @sqlMaster = ' select * from (' +@sqlMain + @sqlFilter +' ) as temp where num between '+ cast(@mmin as varchar)+' and ' + cast(@mmax as varchar)
print(@sqlMaster)
set @dataCountSql =  'select @dataCount=count(*) from  ( ' +@sqlMain + @sqlFilter +' ) as temp '
exec sp_executesql @dataCountSql,N'@dataCount int out',@dataCount out
set @pageCount = ceiling(1.0*@dataCount/@pageSize)
print(@sqlMaster)
exec (@sqlMaster)
end 
set nocount off;

C# 代码调用存储过程

界面代码

<Window x:Class="Ceishi.Test"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Test" Height="678" Width="665">
    <Grid Height="427" Name="grid1" Width="549">
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="268*" />
            <ColumnDefinition Width="281*" />
        </Grid.ColumnDefinitions>
        <DataGrid AutoGenerateColumns="False" Height="392" HorizontalAlignment="Left" Margin="21,12,0,0" Name="dgDetail" VerticalAlignment="Top" Width="516" BorderBrush="#FF2B2B2B" FontSize="14" FontWeight="Normal" ForceCursor="False" VerticalContentAlignment="Stretch" Grid.ColumnSpan="2">
            <DataGrid.ColumnHeaderStyle>
                <Style TargetType="DataGridColumnHeader">
                    <Setter Property="Control.Background" Value="{StaticResource {x:Static SystemColors.GrayTextBrushKey}}" />
                    <Setter Property="Control.Foreground" Value="White" />
                    <Setter Property="Control.FontSize" Value="13" />
                    <Setter Property="FrameworkElement.Height" Value="30" />
                    <Setter Property="Control.HorizontalContentAlignment" Value="Center" />
                    <Setter Property="DataGridColumnHeader.SeparatorBrush" Value="White" />
                    <Setter Property="Control.BorderThickness" Value="0.5" />
                    <Setter Property="Control.BorderBrush" Value="Black" />
                </Style>
            </DataGrid.ColumnHeaderStyle>
            <DataGrid.Columns>
                <DataGridTextColumn Header="序號" Binding="{Binding Path=num}"/>
                <DataGridTextColumn Binding="{Binding Path=EmpNo}" Header="工號"></DataGridTextColumn>
                <DataGridTextColumn Binding="{Binding Path=UserName}" Header="姓名"></DataGridTextColumn>
            </DataGrid.Columns>
            
           
        </DataGrid>
        <Label Content="共 0 筆" FontSize="14" Foreground="#FF2020E2" Height="28" Margin="-15,-51,0,0" Name="lblDataCount" VerticalAlignment="Top" HorizontalAlignment="Left" Width="87" />
        <Label Content="第" Height="28" HorizontalAlignment="Left" Margin="103,-49,0,0" VerticalAlignment="Top" />
        <TextBox Height="23" HorizontalAlignment="Left" HorizontalContentAlignment="Right" IsReadOnly="True" Margin="131,-47,0,0" Name="txtPage" Text="0" VerticalAlignment="Top" Width="40" />
        <Label Content="/" HorizontalAlignment="Left" HorizontalContentAlignment="Right" Margin="168,-50,0,0" VerticalAlignment="Top" Width="23" />
        <Label Content="0" FontSize="14" Foreground="#FF2020E2" Height="28" HorizontalAlignment="Left" Margin="197,-51,0,0" Name="lblPageCount" VerticalAlignment="Top" />
        <Label Content="頁" Height="28" HorizontalAlignment="Left" Margin="0,-50,0,0" VerticalAlignment="Top" Grid.Column="1" />
        <Button Content="GO" FontWeight="Bold" Grid.Column="1" Height="28" HorizontalAlignment="Left" IsEnabled="False" Margin="53,-48,0,0" Name="btnDump" VerticalAlignment="Top" Width="36"  Click="btnDump_Click"/>
        <Image Grid.Column="1" Height="30" HorizontalAlignment="Left" Margin="110,-48,0,0" Name="tbFirst" Source="/Ceishi;component/Image/first.ico" VerticalAlignment="Top" MouseUp="tbFirst__MouseUp" />
        <Image Grid.Column="1" Height="30" HorizontalAlignment="Left" Margin="150,-48,0,0" Name="tbUp" Source="/Ceishi;component/Image/previous.ico" VerticalAlignment="Top" MouseUp="tbUp__MouseUp" />
        <Image Grid.Column="1" Height="30" HorizontalAlignment="Right" Margin="0,-48,61,0" Name="tbDown" Source="/Ceishi;component/Image/next.ico" VerticalAlignment="Top"  MouseUp="tbDown__MouseUp"/>
        <Image Grid.Column="1" Height="30" HorizontalAlignment="Left" Margin="230,-48,0,0" Name="tbLast" Source="/Ceishi;component/Image/last.ico" VerticalAlignment="Top"  MouseUp="tbLast__MouseUp"/>
        <TextBox Height="34" HorizontalAlignment="Left" Margin="21,-91,0,0" Name="txtEmpno" VerticalAlignment="Top" Width="183" FontSize="18"/>
        <Button Content="Button" Grid.ColumnSpan="2" Height="33" HorizontalAlignment="Left" Margin="231,-91,0,0" Name="button1" VerticalAlignment="Top" Width="75" Click="btnSearch_Click" />
    </Grid>
</Window>

// 定义    
        public int pageNum = 20;  // 每一页显示多少数据
        //当前是第几页
        public int pIndex = 1;
        //最大页数
        private int MaxIndex = 1;
        //一共多少条
        private int allNum = 0;

访问数据库

public DataTable ExecQuery(string cmdText, CommandType ct, SqlParameter[] para, string[] returnValue)
        {
            DataTable lo_Dtb = new DataTable();
            SqlDataReader lo_DBSdr = null;
            SqlCommand lo_DBCmd = null;
            SqlConnection lo_DBConn = null;
            string ConnStr = "server= 數據庫地址;uid=用戶名;pwd= 密碼;database=數據表";
            try
            {
                lo_DBConn = new SqlConnection(ConnStr);
                lo_DBConn.Open();
                lo_DBCmd = new SqlCommand(cmdText, lo_DBConn);
                lo_DBCmd.CommandType = ct;
                lo_DBCmd.Parameters.AddRange(para);
                lo_DBCmd.Parameters["@dataCount"].Direction = ParameterDirection.Output;
                lo_DBCmd.Parameters["@pageCount"].Direction = ParameterDirection.Output;
                using (lo_DBSdr = lo_DBCmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    lo_Dtb.Load(lo_DBSdr);
                }

                for (int i = 0; i < lo_DBCmd.Parameters.Count; i++)
                {
                    returnValue[i] = lo_DBCmd.Parameters[i].Value.ToString();
                }
            }
            catch (Exception lo_Ex)
            {
                throw new Exception(lo_Ex.Message);
            }
            finally
            {
                if (lo_DBCmd != null) lo_DBCmd.Dispose();
                lo_DBCmd = null;
                lo_DBConn.Close();
            }
            return lo_Dtb;

        }

接下來查询数据

public void QueryData()
        {
            try
            {
                string ls_EmpNo = txtEmpno.Text.ToString().Trim();
                SqlParameter[] para =   
                {
                    new SqlParameter("@pageSize",pageNum),  //一頁顯示幾筆數據
                    new SqlParameter("@pageIndex",pIndex),  // 第幾頁
                    new SqlParameter("@dataCount",allNum),  //總數據
                    new SqlParameter("@pageCount",MaxIndex), //總頁數
                    new SqlParameter("@EmpNo",ls_EmpNo),
                  };
                string[] ls_Arry = new string[9];
                dgDetail.ItemsSource = ExecQuery("sp_EmpNum", CommandType.StoredProcedure, para, ls_Arry).DefaultView; // 存儲過程名
                lblDataCount.Content = "共 " + ls_Arry[2] + " 筆";
                lblPageCount.Content = ls_Arry[3];

                allNum = int.Parse(ls_Arry[2].ToString());
                MaxIndex = int.Parse(ls_Arry[3].ToString());

                if (this.MaxIndex > 1)
                {
                    this.txtPage.IsReadOnly = false;
                    this.btnDump.IsEnabled = true;
                }
            }
            catch (Exception lo_Ex)
            {
                MessageBox.Show(lo_Ex.Message, "錯誤信息", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (this.pIndex == 1) // 按钮状态
                {
                    this.tbUp.IsEnabled = false;
                    this.tbFirst.IsEnabled = false;
                }
                else
                {
                    this.tbUp.IsEnabled = true;
                    this.tbFirst.IsEnabled = true;
                }
                if (this.pIndex == this.MaxIndex)
                {
                    this.tbDown.IsEnabled = false;
                    this.tbLast.IsEnabled = false;
                }
                else
                {
                    this.tbDown.IsEnabled = true;
                    this.tbLast.IsEnabled = true;
                }
            }
        }

点击首頁按鈕

 private void tbFirst__MouseUp(object sender, MouseButtonEventArgs e)
        {
            this.pIndex = 1;
            txtPage.Text = pIndex.ToString();
            QueryData();
        }

点击上一頁按鈕

private void tbUp__MouseUp(object sender, MouseButtonEventArgs e)
        {
            if (this.pIndex <= 1)
                return;
            this.pIndex--;
            txtPage.Text = pIndex.ToString();
            QueryData();
        }

点击下一頁按鈕

 private void tbDown__MouseUp(object sender, MouseButtonEventArgs e)
        {
            if (this.pIndex >= this.MaxIndex)
                return;
            this.pIndex++;
            txtPage.Text = pIndex.ToString();
            QueryData();

点击尾页按钮

private void tbLast__MouseUp(object sender, MouseButtonEventArgs e)
        {
            this.pIndex = this.MaxIndex;
            txtPage.Text = pIndex.ToString();
            QueryData();
        }

点击GO按钮

private void btnDump_Click(object sender, RoutedEventArgs e)
        {
            string nummber = txtPage.Text.ToString();
            if (!IsNumber(nummber))
            {
                MessageBox.Show("請輸入正確的數字", "錯誤信息", MessageBoxButton.OK, MessageBoxImage.Asterisk);
                return;
            }
            else
            {
                this.pIndex = Convert.ToInt32(nummber);
            }
            QueryData();
        }

下载完整代码：http://download.youkuaiyun.com/detail/why_n/9817567