用存储过程给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;            }        }