用存储过程给DataGridView分页入门

 存储过程

 

CREATE PROCEDURE dbo.pagination 
 (
 @PageSize int = 10,
 @CurrentPageIndex int=1 
 )
 
AS
   declare @index int
 if(@CurrentPageIndex=1)
   begin
      EXEC( 'SELECT TOP ' + @PageSize +  '* from Orders ')
   end
 else
   begin
   set @index= (@CurrentPageIndex-1) * @PageSize
        EXEC( 'SELECT TOP ' + @PageSize +  '* from Orders where OrderId not in '
        +'(select top '+ @index +'OrderId from Orders ) ')
      end


 RETURN

 

 

实现分页

private const int pageSize = 10;

        DataTable dtInfo = null;
        protected string ConnectStr()
        {
           
            string _connnectionStr = String.Format("Data Source=localhost;AttachDbFilename={0};Integrated Security=True", @"F:\xyt\MyProject\DataGridView\DataGridViewAndCheckBox\DataGridViewAndCheckBox\bin\Debug\b2bdb_Data.MDF");//连接字符串
          
            return _connnectionStr;
        }

        private DataTable LoadData(int PageIndex)
        {
             SqlConnection con = new SqlConnection(this.ConnectStr());
            SqlCommand cmd = new SqlCommand("pagination", con);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 4));
            cmd.Parameters["@PageSize"].Value =pageSize;
            cmd.Parameters.Add(new SqlParameter("@CurrentPageIndex", SqlDbType.Int, 4));
            cmd.Parameters["@CurrentPageIndex"].Value = PageIndex;

            DataTable dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(dt);

            con.Close();
            return dt;
            
        }


        private void Form1_Load(object sender, EventArgs e)
        {
            dtInfo = LoadData(1);
            this.dataGridView1.DataSource = dtInfo;
            this.label1.Text = Convert.ToString(1);
            
        }

      

    
        private void btnNext_Click(object sender, EventArgs e)
        {
            this.label1.Text = (Convert.ToInt32(this.label1.Text) + 1).ToString();
            dtInfo = LoadData(Convert.ToInt32(this.label1.Text));
            if (dtInfo.Rows.Count==0)//已经是最后一页
            {
                this.label1.Text = (Convert.ToInt32(this.label1.Text) - 1).ToString();
                MessageBox.Show("已经是最后一页！");
            }
            else
            {
               this.dataGridView1.DataSource = null;
               this.dataGridView1.DataSource = dtInfo;
            }
        }

        private void btnPrevious_Click(object sender, EventArgs e)
        {
           
            if (this.label1.Text=="1")
            {
                MessageBox.Show("已经是第一页！");
                return;
            }
            else
            {
                this.label1.Text = (Convert.ToInt32(this.label1.Text) - 1).ToString();
                dtInfo = LoadData(Convert.ToInt32(this.label1.Text));
                this.dataGridView1.DataSource = null;
                this.dataGridView1.DataSource = dtInfo;
            }

        }