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;
static
int
start = 0;
#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;
dgv.DataSource = gettb(selectsql ,start,count,
"table"
)‘;
}
#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"
)‘;
}
}
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"
)‘;
}
}
/// 下一页
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"
)‘;
}
}
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"
)‘;
}
}
上边红色部分获取总行数是调用的数据操作层里的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
}