-
安装Dapper依赖
Install-Package Dapper -Version 1.40.0
-
安装SQLite依赖
-
新建SQLite数据库文件
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, '苏州', '读书');-
主要代码示例
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;
}
}
}/// <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*$");
}-
运行界面
