offset代表从第几条记录“之后“开始查询,也就是定义的nCurrent; limit表明查询多少条结果,也就是定义的pageSize。
offset关键字设置偏移量,跳过n条数据后,从下一条数据开始返回。
select * from EXPRESSITEM where itemNo like @itemno limit @pageSize offset @nCurrent
//button按钮 点击查询
private void btnSelect_Click(object sender, EventArgs e)
{
getAllInfo();
getInfo();
//解决查看所有信息以后继续按条件查询分页页数不改变的问题
txtCurrent.Text = "1";
this.btnGo_Click(null,null);
}
int pageSize = 5; //每页显示行数
int nMax = 0; //总记录数
int pageCount = 0; //总页数
int pageCurrent = 1; //当前页
int nCurrent = 0; //当前记录行
DataSet ds = new DataSet();
DataTable dtInfo = new DataTable();
//一页显示五条,按条件查询出来分页
public void getInfo() {
SQLiteHelper slh = new SQLiteHelper("E:\\openises.db");
slh.OpenConnection();
String sql = "select * from EXPRESSITEM where itemNo like @itemno limit @pageSize offset @nCurrent";
string num = txtNo.Text + "%";
SQLiteParameter[] param = new SQLiteParameter[] { new SQLiteParameter("@itemno", num),new SQLiteParameter("@pageSize",pageSize),new SQLiteParameter("@nCurrent",nCurrent) };
DataTable dt = slh.ExecuteDataTable(sql, param);
dataGridView1.DataSource = dt;
txtCurrent.Text = pageCurrent.ToString();
}
//获取所有的信息
public int getAllInfo() {
SQLiteHelper slh = new SQLiteHelper("E:\\openises.db");
slh.OpenConnection();
String sql = "select * from EXPRESSITEM where itemNo like @itemno";
string num = txtNo.Text + "%";
SQLiteParameter[] param = new SQLiteParameter[] {new SQLiteParameter("@itemno",num)};
DataTable dt = slh.ExecuteDataTable(sql,param);
/*
* MessageBox.Show(dt.Rows.Count.ToString()); //nMax
*/
//获取一共多少页,麻烦了,可以是((总条数-1)/每页显示数)+1
int a;
if (dt.Rows.Count % pageSize == 0)
{
a = dt.Rows.Count / pageSize;
lblnMax.Text = (dt.Rows.Count / pageSize).ToString();
}
else {
a = (dt.Rows.Count / pageSize) + 1;
lblnMax.Text = ((dt.Rows.Count / pageSize) + 1).ToString();
}
return a;
}
//加了个皮肤
private void Form1_Load(object sender, EventArgs e)
{
this.skinEngine1.SkinFile="MP10.ssk";
}
//上一页
private void lblBack_Click(object sender, EventArgs e)
{
if(pageCurrent>1){
nCurrent -= 5;
pageCurrent -= 1;
txtCurrent.Text = pageCurrent.ToString();
this.getInfo();
}
}
//下一页
private void lblNext_Click(object sender, EventArgs e)
{
if(pageCurrent<this.getAllInfo()){
nCurrent += 5;
pageCurrent += 1;
txtCurrent.Text = pageCurrent.ToString();
this.getInfo();
}
}
//跳转至第几页
private void btnGo_Click(object sender, EventArgs e)
{
if(this.getAllInfo()>=int.Parse(txtCurrent.Text)){
nCurrent = (int.Parse(txtCurrent.Text) - 1) * 5;
pageCurrent = int.Parse(txtCurrent.Text);
this.getInfo();
}
}