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