- //导入命名空间部分省略
- DBClass.DBExecute dbexecute = new DBExecute();
- string connectionString = @"Data Source=ServerName;Database=DatabaseName;integrated security=true";
- #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;
- }
- #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
- }
- #endregion
- #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);
- sda.Fill(myds, start, count, tablename);
- return myds.Tables[tablename];
- }
- #endregion
- /// 第一页
- 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
- }
- }
- // 上一页
- 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
- }
C# WinForm dataGridview 分页实现
最新推荐文章于 2023-11-20 17:38:50 发布