目录
今天给大家分享Winform实现DataGridView 自定义分页的案例,感兴趣的朋友可以一起来学习一下。
采用技术:C#+Winform+Dapper+SQLite。
本次为了避免安装数据库的繁琐,采用了SQLite数据库。
-
安装Dapper依赖
命令如下:
Install-Package Dapper -Version 1.40.0
1、安装SQLite依赖
命令如下:
Install-Package System.Data.SQLite.Core -Version 1.0.116
2、新建SQLite数据库文件
目前数据库文件放在bin/debug 目录下,文件名为user.db ,大家不需要在新建了。
新建表脚本
CREATE TABLE "user" (
"Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"UserName" TEXT NOT NULL,
"Age" integer NOT NULL,
"Address" TEXT NOT NULL,
"Hobby" TEXT,
UNIQUE ("Id" ASC)
);
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (1, '小明1', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (2, '小明2', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (3, '小明3', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (4, '小明4', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (5, '小明5', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (6, '小明6', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (7, '小明7', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (8, '小明8', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (9, '小明9', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (10, '小明10', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (11, '小明11', 35, '苏州', '读书');
INSERT INTO "main"."user" ("Id", "UserName", "Age", "Address", "Hobby") VALUES (12, '小明12', 35, '苏州', '读书');
3、主要代码示例
新建DBUtils.cs 数据库帮助类
public static SQLiteConnection GetSQLiteConnection()
{
//Sqlite数据库地址<br>
string str = AppDomain.CurrentDomain.BaseDirectory;
var con = new SQLiteConnection("Data Source=" + str + "DB\\user.db");
return con;
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static DataTable GetListTable(int pageIndex, int pageSize, ref int recordCount)
{
using (IDbConnection cnn = new SQLiteConnection(GetSQLiteConnection()))
{
pageIndex --;
DataTable dt = new DataTable();
cnn.Open();
StringBuilder sbCount = new StringBuilder();
sbCount.Append(@"SELECT count(id) recordCount from user where 1 = 1");
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT * from user where 1=1 ");
recordCount = cnn.Query<int>(sbCount.ToString()).FirstOrDefault();
sb.Append(" order by id asc ");
sb.AppendFormat(" limit {0} offset {1} ", pageSize, pageSize * pageIndex);
var reader = cnn.ExecuteReader(sb.ToString());
dt.Load(reader);
return dt;
}
}
}
新建Form2窗体文件,主要代码如下:
/// <summary>
/// 每页记录数
/// </summary>
private int pageSize = 3;
/// <summary>
/// 总记录数
/// </summary>
private int recordCount = 0;
/// <summary>
/// 总页数
/// </summary>
private int pageCount = 0;
/// <summary>
/// 当前页
/// </summary>
private int currentPage = 1;
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
txtCurrentPage.Text = "0";
loadData();
}
/// <summary>
/// 加载数据
/// </summary>
private void loadData()
{
dataGridView1.DataSource = DBUtils.GetListTable(currentPage, pageSize, ref recordCount);
pageCount = (recordCount + pageSize - 1) / pageSize;
if (pageCount == 0)
{
lblRecordCount.Text = "0";
txtCurrentPage.Text = "0";
lblTotal.Text = "/0页";
}
else
{
lblRecordCount.Text = "总记录数:" + recordCount;
txtCurrentPage.Text = currentPage.ToString();
lblTotal.Text = "/" + pageCount + "页";
}
}
/// <summary>
/// 首页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnFirst_Click(object sender, EventArgs e)
{
currentPage = 1;
loadData();
}
/// <summary>
/// 下一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnNext_Click(object sender, EventArgs e)
{
if (currentPage < pageCount)
{
currentPage++;
loadData();
}
else
{
MessageBox.Show("当前已经是最后一页");
}
}
/// <summary>
/// 上一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLast_Click(object sender, EventArgs e)
{
if (currentPage == 1)
{
MessageBox.Show("当前已经是第一页");
}
else
{
currentPage--;
loadData();
}
}
/// <summary>
/// 末页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnEnd_Click(object sender, EventArgs e)
{
currentPage = pageCount;
loadData();
}
/// <summary>
/// 跳转页码
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnGoTo_Click(object sender, EventArgs e)
{
if (!isIntergerNonZero(txtCurrentPage.Text.Trim()))
{
MessageBox.Show("跳转页码必须为正整数");
}
else
{
int page = Convert.ToInt32(txtCurrentPage.Text.Trim());
if(page<=pageCount)
{
currentPage = page;
loadData();
}
else
{
MessageBox.Show("跳转页面不能大于总页数");
}
}
}
/// <summary>
/// 校验是否为正整数
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
private static bool isIntergerNonZero(string str)
{
return string.IsNullOrWhiteSpace(str) ? false : Regex.IsMatch(str, @"^[1-9]\d*$");
}
4、运行界面
主要实现了当前记录数、共有多少页、首页、上一页、下一页、默认、指定页跳转的功能。
以上就是Winfrom 实现DataGridView 自定义分页的案例,欢迎互相交流学习!