//导入命名空间部分省略sql
DBClass.DBExecute dbexecute = new DBExecute(); 数据库
string connectionString = @"Data Source=ServerName;Database=DatabaseName;integrated security=true";this
#region 初始化分页显示的参数
static int rowsall = 0;//总行数
static int pageall = 0;//总页数
static int page = 0;//第几页
static int count = 20;//返回20行
static int start = 0;//从第start行开始返回
#endregion 对象
#region 创建数据库连接
/// <summary>
/// 创建数据库链接
/// </summary>
/// <returns>返回SqlConnection对象</returns>
public SqlConnection getcon()
{
SqlConnection myCon = new SqlConnection(connectionString);
return myCon;
} blog
#region 设置DataGridView分页显示的参数,和初次绑定
/// <summary>
/// 设置DataGridView分页显示的参数,和初次绑定
/// </summary>
/// <param name="sqlstr">设置查询的sql语句</param>
/// <param name="table">设置返回绑定的DataSet中的表的名称</param>
/// <param name="dgv">要绑定的DataGridView</param>
public void upPage(string sqlstr,string table,DataGridView dgv)
{
rowsall = dbexecute.getds(sqlstr, table).Tables[table].Rows.Count;//总行数
if (rowsall == 0)
{
//若是没有数据则将第一页、上一页、下一页、最后一页设置为不可用;并设置其余参数
toolStripButton2.Enabled = false;
toolStripButton3.Enabled = false;
toolStripButton4.Enabled = false;
toolStripButton5.Enabled = false;
page = 0;
pageall = 0;
rowsall = 0;
dgv.DataSource = null;
tslRowsall.Text = rowsall.ToString();
tslPageAll.Text = pageall.ToString();
tslPage.Text = page.ToString();
return;
}
if (rowsall > 0)//判断是否有内容
{
page = 1;//若是有内容,设置为第一页
start = 0;
}
int yushu = rowsall % count;//是否存在余行
if (yushu == 0)//不存在余行时设置总页数
{
if (rowsall > 0 && rowsall <= count)
{
pageall = 1;
}
else
{
pageall = rowsall / count;
}
}
else//存在余行时设置总页数
{
pageall = rowsall / count + 1;
}
{//设置显示数据,
tslRowsall.Text = rowsall.ToString();
tslPageAll.Text = pageall.ToString();
tslPage.Text = page.ToString();
if (pageall > 0)
{ //设置跳转到第几页
tscbPage.Items.Clear();
for (int i = 1; i <= pageall; i++)
tscbPage.Items.Add(i);
}
}
selectsql = sqlstr; //设置sql语句
dgv.DataSource = gettb(selectsql ,start,count,"table")‘;//绑定DataGridView ip
}
#endregionget
#region
/// <summary>
/// 分页返回DataTable
/// </summary>
/// <param name="sql">查询的sql语句</param>
/// <param name="i">从第i行开始返回</param>
/// <param name="j">共返回j行记录</param>
/// <param name="tablename">返回DataSet中的代表</param>
/// <returns>返回DataTable</returns>
public DataTable gettb(string sql, int start, int count, string tablename)
{
SqlConnection con = this.getcon();
DataSet myds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sql, con);string
sda.Fill(myds, start, count, tablename);
return myds.Tables[tablename];
}
#endregion it
/// 第一页
private void toolStripButton2_Click(object sender, EventArgs e)
{
if (pageall > 1)
{
start = 0;
page = 1;
tslPage.Text = page.ToString();
this.dataGridView1.DataSource = gettb(selectsql ,start,count,"table")‘;//绑定DataGridView
}
} io
// 上一页
private void toolStripButton3_Click(object sender, EventArgs e)
{
if (page >1)
{
page--;
start -= 20;
tslPage.Text = page.ToString();
this.dataGridView1.DataSource = gettb(selectsql ,start,count,"table")‘; //绑定DataGridView
}
}
/// 下一页
private void toolStripButton4_Click(object sender, EventArgs e)
{
if (page < pageall)
{
page++;
start += 20;
tslPage.Text = page.ToString();
this.dataGridView1.DataSource = gettb(selectsql ,start,count,"table")‘;//绑定DataGridView
}
}
//最后一页
private void toolStripButton5_Click(object sender, EventArgs e)
{
if (pageall > 0)
{
start = (pageall - 1) * count;
page = pageall;
tslPage.Text = page.ToString();
this.dataGridView1.DataSource = gettb(selectsql ,start,count,"table")‘;//绑定DataGridView
}
}
上边红色部分获取总行数是调用的数据操做层里的getds方法返回一个数据集,类和方法以下:
class DBExecute { string G_str_connectionString = @"Data Source=70AB360C9ABA49E/SQLEXPRESS;Database=db_CRM;integrated security=true";//这里设置成你本身的链接 public DBExecute(){} public DBExecute(string M_str_connectionString) { G_str_connectionString = M_str_connectionString; } #region 创建数据库连接 /// <summary> /// 创建数据库链接 /// </summary> /// <returns>返回SqlConnection对象</returns> public SqlConnection getcon() { string M_str_sqlcon = G_str_connectionString; SqlConnection myCon = new SqlConnection(M_str_sqlcon); return myCon; } #endregion #region 查询数据库返回一个DataSet对象 /// <summary> /// 查询数据库返回一个DataSet对象 /// </summary> /// <param name="M_str_sqlstr">SQL语句</param> /// <param name="M_str_table">表名</param> /// <returns>返回DataSet对象</returns> public DataSet getds(string M_str_sqlstr, string M_str_table) { DataSet myds = new DataSet(); SqlConnection sqlcon = this.getcon(); SqlDataAdapter sqlda = new SqlDataAdapter(M_str_sqlstr, sqlcon); sqlda.Fill(myds, M_str_table); return myds; } #endregion }