一个分页的dataGridViewDEMO 用sqlite数据库实现
/*
* 分页参考：https://www.cnblogs.com/swjian/p/9889789.html
* 填充数据参考：https://blog.csdn.net/qiuyu6958334/article/details/104929265
* 更新2021年1月4日 11:22:00：https://www.cnblogs.com/xe2011/p/14228899.html
*/
直接粘贴源码 不解释 数据库用的是SQLITE
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SQLite; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace dgv分页 { /* * 分页参考：https://www.cnblogs.com/swjian/p/9889789.html * 填充数据参考：https://blog.csdn.net/qiuyu6958334/article/details/104929265 * 更新2021年1月4日 11:22:00：https://www.cnblogs.com/xe2011/p/14228899.html */ public partial class Form1 : Form { public Form1() { InitializeComponent(); } /// <summary> /// 每页记录数 /// </summary> public int pageSize = 11; /// <summary> /// 总记录数 /// </summary> public int recordCount = 0; /// <summary> /// 总页数 /// </summary> public int pageCount = 0; /// <summary> /// 当前页 /// </summary> public int currentPage = 0; /// <summary> /// dt = new DataTable(); 用于过滤数据用 dgv1.DataSource = dt; /// </summary> DataTable dt = new DataTable(); /// <summary> /// 相当于重新绑定数据了 刷新数据了 /// </summary> public void getData() { SQLiteHelper sqlite = new SQLiteHelper("Data Source = test.db; Version=3;"); dt = sqlite.DataAdapter($"Select * From Data","Data").Tables[0]; //dgv1.DataSource = dt;// currentPage = 1; LoadPage();//调用加载数据的方法 HideColumns(); } private void HideColumns() { //全部隐藏 for (int i = 0; i < dgv1.ColumnCount; i++) { dgv1.Columns[i].Visible = false; //dgv1.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; } dgv1.RowHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft; //列头中对齐 //dgv1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; dgv1.Columns["单词"].Visible = true; dgv1.Columns["单词"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft; dgv1.Columns["单词"].MinimumWidth = 160; dgv1.Columns["单词"].Width = 160; dgv1.Columns["解释"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft; dgv1.Columns["解释"].MinimumWidth = 200; dgv1.Columns["解释"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; //显示不隐藏的部分 //dgv1.Columns["单词"].DefaultCellStyle.Font = new Font("Arial",9,FontStyle.Regular); dgv1.Columns["音标"].Visible = true; dgv1.Columns["音标"].DefaultCellStyle.ForeColor = Color.Gray; dgv1.Columns["音标"].MinimumWidth = 60; dgv1.Columns["音标"].Width = 60; dgv1.Columns["解释"].Visible = true; } private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e) {//行号 DataGridView dgv1 = sender as DataGridView; Rectangle rectangle = new Rectangle(e.RowBounds.Location.X, e.RowBounds.Location.Y, dgv1.RowHeadersWidth - 4, e.RowBounds.Height); TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(), dgv1.RowHeadersDefaultCellStyle.Font, rectangle, dgv1.RowHeadersDefaultCellStyle.ForeColor, TextFormatFlags.VerticalCenter | TextFormatFlags.Right); } private void Form1_Load(object sender,EventArgs e) { getData(); dgv1.ScrollBars = ScrollBars.Vertical;//绑定数据 、然后隐藏列会闪现一次水平滚动条 dgv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; dgv1.AllowUserToAddRows = false; dgv1.AllowUserToDeleteRows = false; dgv1.ReadOnly = true; dgv1.AllowUserToResizeRows = false; dgv1.AllowUserToResizeColumns = true; //禁止用户改变列宽 } private void LoadPage( ) { recordCount = dt.Rows.Count; //记录总行数 pageCount = (recordCount / pageSize); if ((recordCount % pageSize) > 0) { pageCount++; } if (currentPage < 1) currentPage = 1; if (currentPage > pageCount) currentPage = pageCount; int beginRecord; //开始指针 int endRecord; //结束指针 DataTable dtTemp; dtTemp = dt.Clone(); beginRecord = pageSize * (currentPage - 1); if (currentPage == 1) beginRecord = 0; endRecord = pageSize * currentPage; if (currentPage == pageCount) endRecord = recordCount; for (int i = beginRecord; i < endRecord; i++) { dtTemp.ImportRow(dt.Rows[i]); } dgv1.DataSource = null; dgv1.DataSource = dtTemp; HideColumns(); labRecordCount.Text = $"总行数: {recordCount} 行";//总记录数 labPageIndex.Text = $"当前页: {currentPage} / {pageCount}";//当前页 } //首页 private void btnFirst_Click(object sender, EventArgs e) { if (currentPage == 1) return; currentPage = 1; LoadPage(); } //上一页 private void btnPrev_Click(object sender, EventArgs e) { if (currentPage == 1) return; currentPage--; LoadPage(); } //下一页 private void btnNext_Click(object sender, EventArgs e) { if (currentPage == pageCount) return; currentPage++; LoadPage(); } //尾页 private void btnLast_Click(object sender, EventArgs e) { if (currentPage == pageCount) return; currentPage = pageCount; LoadPage(); } private void button1_Click(object sender,EventArgs e) { pageSize = (int)numericUpDown1.Value; LoadPage(); } private void numericUpDown1_ValueChanged(object sender,EventArgs e) { //label1.Text = $"每页显示：{(int)numericUpDown1.Value}"; pageSize = (int)numericUpDown1.Value; LoadPage(); } private void dgv1_SelectionChanged(object sender,EventArgs e) { richTextBox1.Text = id+ "\n"+ _dc_单词 + "\n" + _yb_音标 + "\n" + _js_解释; btn_第一页.Enabled = currentPage != 1; btn_最后一页.Enabled = currentPage != pageCount; btn_上一页.Enabled = currentPage != 1; btn_下一页.Enabled = currentPage != pageCount; } #region 表格字段属性 /// <summary> /// dgv1.CurrentCell.RowIndex /// </summary> public int SeletedIndex { get { //dgv1.CurrentCell.RowIndex return dgv1.CurrentCell != null ? dgv1.CurrentRow.Index : -1; } set { //dgv1.Focus(); //dgv1.MultiSelect = false; //dgv1.ClearSelection(); //dgv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; if (dgv1.CurrentCell == null) return; if (value < 0) return; if (value < dgv1.RowCount) { //dgv1.Rows[value].Selected = true; dgv1.CurrentCell = dgv1.Rows[value].Cells["单词"]; //dgv1.CurrentRow.Cells[value].Value = true; } //dgv1_SelectionChanged(null,null); } } /// <summary> /// 当前ID dgv1.CurrentRow.Cells["id"].Value.ToString() /// </summary> public string id { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["id"].Value.ToString() : ""; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["id"].Value = value; } } /// <summary> /// 当前的单词 dgv1.CurrentRow.Cells["单词"].FormattedValue.ToString() /// </summary> public string _dc_单词 { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["单词"].FormattedValue.ToString() : ""; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["单词"].Value = SqlString.Transform(value); } } /// <summary> /// 当前解释 dgv1.CurrentRow.Cells["解释"].FormattedValue.ToString() /// </summary> public string _js_解释 { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["解释"].FormattedValue.ToString() : ""; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["解释"].Value = SqlString.Transform(value); } } /// <summary> /// 当前例句 dgv1.CurrentRow.Cells["例句"].FormattedValue.ToString() /// </summary> public string _lj_例句 { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["例句"].FormattedValue.ToString() : ""; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["例句"].Value = SqlString.Transform(value); } } /// <summary> /// 当前的音标 dgv1.CurrentRow.Cells["音标"].FormattedValue.ToString() /// </summary> public string _yb_音标 { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["音标"].FormattedValue.ToString() : ""; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["音标"].Value = SqlString.Transform(value); } } /// <summary> /// 图片路径 dgv1.CurrentRow.Cells["图片"].Value.ToString() 12321.jpg 图片放在collection_files目录下 /// </summary> public string _imagefile { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["图片"].Value.ToString() : ""; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["图片"].Value = value; } } ///// <summary> ///// 图片的完整路径 db.Collection_files + "\\" + _imagePath ///// </summary> //public string _image_FullFileName => db.Collection_files + "\\" + _image_相对图片; /// <summary> /// 正确次数 dgv1.CurrentRow.Cells["正确次数"].Value /// </summary> public int zqCount { get { return dgv1.CurrentCell != null ? Convert.ToInt32(dgv1.CurrentRow.Cells["正确次数"].Value) : 0; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["正确次数"].Value = value; } } /// <summary> /// 练习次数 dgv1.CurrentRow.Cells["练习次数"].Value /// </summary> public int lxCount { get { return dgv1.CurrentCell != null ? Convert.ToInt32(dgv1.CurrentRow.Cells["练习次数"].Value) : 0; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["练习次数"].Value = value; } } ///// <summary> ///// 创建日期 dgv1.CurrentRow.Cells["创建日期"].Value.ToString() ///// </summary> //public string _createTime { // get { // return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["创建日期"].Value.ToString() : db.Now; // } // set { // if (dgv1.CurrentCell != null) // dgv1.CurrentRow.Cells["创建日期"].Value = value; // } //} ///// <summary> ///// 修改日期 dgv1.CurrentRow.Cells["修改日期"].Value.ToString() ///// </summary> //public string _modifyTime { // get { // return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["修改日期"].Value.ToString() : db.Now; // } // set { // if (dgv1.CurrentCell != null) // dgv1.CurrentRow.Cells["修改日期"].Value = value; // } //} ////todo:: //public string _nextLearn { // get { // return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["练习日期"].Value.ToString() : db.Now; // } // set { // if (dgv1.CurrentCell != null) // dgv1.CurrentRow.Cells["练习日期"].Value = value; // } //} /// <summary> /// 表格中的难度等级 dgv1.CurrentRow.Cells["难度"].Value.ToString() /// </summary> public string HardLevel { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["难度"].Value.ToString() : "未学"; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["难度"].Value = value; } } public string _zu { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["组"].Value.ToString() : ""; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["组"].Value = value; } } /// <summary> /// _wav_相对声音 相对 collection_files 目录下的声音 12321.wav /// </summary> public string _wavfile { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["声音"].Value.ToString() : ""; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["声音"].Value = value; } } ///// <summary> ///// wav的完整路径 db.Collection_files + "\\" + _wav_相对声音; ///// </summary> //public string _wav_FullFileName => db.Collection_files + "\\" + _wav_相对声音; /// <summary> /// 备注 /// </summary> public string _note { get { return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["备注"].Value.ToString() : ""; } set { if (dgv1.CurrentCell != null) dgv1.CurrentRow.Cells["备注"].Value = value; } } #endregion } }
SQLITE HELPER
using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.SQLite; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Windows.Forms; namespace System.Data.SQLite { class SqlString { /* * [UPDATE 2019年10月9日 22:53:51] * , * 编码解码可以使用 HttpUtility * //编码 textBox4.Text = HttpUtility.HtmlEncode(textBox4.Text); //解码 textBox4.Text = HttpUtility.HtmlDecode(textBox4.Text); */ /*string.Replace 方法莫名其妙会多个 \n*/ public static string Transform(string s) { if (s == null) return ""; //普通字符变换成转义字符 s = Regex.Replace(s,"&","&",RegexOptions.IgnoreCase); s = Regex.Replace(s,"<","<",RegexOptions.IgnoreCase); s = Regex.Replace(s,">",">",RegexOptions.IgnoreCase); s = Regex.Replace(s,"'","'",RegexOptions.IgnoreCase); s = Regex.Replace(s,"\"",""",RegexOptions.IgnoreCase); //new 2019年10月23日 20:47:40 //我的单词是以逗号区分的所以不能出现逗号 //s = Regex.Replace(s, ",", ",", RegexOptions.IgnoreCase); return s; } //还原原来字符 public static string Restore(string s) { //转义字符变换成普通字符 s = Regex.Replace(s,"<","<",RegexOptions.IgnoreCase); s = Regex.Replace(s,">",">",RegexOptions.IgnoreCase); s = Regex.Replace(s,"'","'",RegexOptions.IgnoreCase); s = Regex.Replace(s,""","\"",RegexOptions.IgnoreCase); s = Regex.Replace(s,"&","&",RegexOptions.IgnoreCase); s = Regex.Replace(s,",",",",RegexOptions.IgnoreCase); return s; } } public class SQLiteHelper { public SQLiteHelper(string conStr) { ConnectionString = conStr; } /// <summary> /// 数组库文件的名称 /// </summary> public string db = ""; /// <summary> /// SQLITE连接字符串 /// </summary> public string ConnectionString { get; set; } /* * SQLiteParameter * DataSet * ExecuteScalar * https://www.cnblogs.com/yukaizhao/archive/2008/12/01/sqlitehelper.html * */ ///<summary> /// 压缩体积、优化体积 ，使用此方法可以减小数据库的体积[如果文件进行了删除操作] /// </summary> public void Vacuum() { /* * https://blog.csdn.net/HardWorkingAnt/article/details/70667104 */ ExecuteNoneQuery("vacuum"); } /// <summary> /// string cmdText = "SELECT * FROM [单词本] "; /// private DataSet dataSet1 = DataAdapter(cmdText, "单词本"); //单词本 是数据库的 表 名 /// </summary> /// <param name="commandText"></param> /// <param name="tablename">数据库表的名字</param> /// <returns></returns> public DataSet DataAdapter(string commandText,string tablename) { DataSet ds = new DataSet(); using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { conn.Open(); using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(commandText,ConnectionString)) { if (string.Empty.Equals(tablename)) { adapter.Fill(ds); } else { adapter.Fill(ds,tablename); } } } return ds; } /* 示例 using (SQLiteConnection conn = new SQLiteConnection(_sqlite.connectionString)) { using (SQLiteCommand cmd = new SQLiteCommand(conn)) { conn.Open(); cmd.CommandText = "SELECT* FROM [单词本] ORDER BY random()"; SQLiteDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Application.DoEvents(); string word = (reader["单词"] != DBNull.Value) ? reader["单词"].ToString() : "";//单词 //string uk = (dataReader["uk"] != DBNull.Value) ? dataReader["uk"].ToString() : "";//英标 英式 //uk = SqlString.Restore(uk); string js = (reader["解释"] != DBNull.Value) ? reader["解释"].ToString() : "";//解释 string tag = (reader["Tag"] != DBNull.Value) ? reader["Tag"].ToString() : "";//单词分类 int progress = (reader["熟练度"] != DBNull.Value) ? (int)reader["熟练度"] : 0;//掌握度 //TODO: 500个卡的要死闪的厉害 ClassListView.AddItem(listView1, word, js, Convert.ToString(progress)); } } } */ public SQLiteDataReader ExecuteReader(string sql) { SQLiteConnection conn = new SQLiteConnection(ConnectionString); SQLiteCommand cmd = new SQLiteCommand(conn); conn.Open(); cmd.CommandText = sql; SQLiteDataReader reader = cmd.ExecuteReader(); return reader; // try { // /* //* 此处语句不要使用using 否则会错 //*/ // } // catch { // return null; // } } /// <summary> /// string cmdText = "SELECT * FROM [单词本] "; /// private DataSet dataSet1 = DataAdapter(cmdText, "单词本"); //单词本 是数据库的 表 名 /// </summary> /// <param name="commandText"></param> /// <param name="tablename">数据库表的名字</param> /// <returns></returns> public DataSet GetDataSet(string commandText, string tablename) { DataSet ds = new DataSet(); ////con = new SQLiteConnection(ConnectionString); ////da = new SQLiteDataAdapter("select * from [单词本]", con); ////da.Fill(ds, "单词本"); ////bs1.DataSource = ds.Tables["单词本"]; //SQLiteConnection con = new SQLiteConnection(ConnectionString); //con.Open(); //SQLiteDataAdapter da = new SQLiteDataAdapter(commandText, con); //da.Fill(ds, tablename); using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(commandText, conn)) { conn.Open(); //if (string.Empty.Equals(tablename)) //{ // adapter.Fill(ds); //} //else { adapter.Fill(ds, tablename); } } } return ds; } /// <summary> /// 通过查询SQL语句得到指定字段的值 /// string sql = "SELECT * FROM 单词本 WHERE 单词 = '" + word + "'"; /// string yb = GetSectionValue(sql, "音标"); /// </summary> /// <param name="sql"></param> /// <param name="sectionName"></param> /// <returns></returns> public string GetSectionValue(string sql, string sectionName) { DataTable dt = GetDataTable(sql); return (dt.Rows.Count > 0) ? dt.Rows[0][sectionName].ToString() : "0"; } /* * 判断值是不是存在 * string sql = $"select * from [配置] where 组名= '12'"; * DataTable dt = sqlite.GetDataTable(sql); * if (dt.Rows.Count == 0) 不存在 else 存在 */ public bool exists(string sql) { DataTable dt = GetDataTable(sql); return (dt.Rows.Count > 0) ? true : false; } //public void SetSectionValue(string sql, string sectionName,object value) //{ // sql = $""; // ExecuteNoneQuery(sql); //} public DataTable GetDataTable(string sql) { using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn)) { DataTable dt = new DataTable(); adapter.Fill(dt); return dt; } } } /// <summary> ///0=执行失败，大于0表示执行成功了 /// </summary> /// <param name="sql"></param> /// <returns></returns> public int ExecuteNoneQuery(string sql) { try { using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand cmd = new SQLiteCommand(sql, conn)) { conn.Open(); return cmd.ExecuteNonQuery(); } } } catch { return -1; } } public int ExecuteNoneQuery(string sqlStr, params SQLiteParameter[] p) { using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand command = new SQLiteCommand()) { try { PrepareCommand(command, conn, sqlStr, p); return command.ExecuteNonQuery(); } catch /*(Exception ex)*/ { return -99; } } } } /// <summary> /// 参数设置 /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="sqlStr"></param> /// <param name="p"></param> private void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string sqlStr, params SQLiteParameter[] p) { try { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Parameters.Clear(); cmd.Connection = conn; cmd.CommandText = sqlStr; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 30; if (p != null) { foreach (SQLiteParameter parm in p) { cmd.Parameters.AddWithValue(parm.ParameterName, parm.Value); } } } catch /*(Exception ex)*/ { return; } } //public int RecordLength //{ // get // { // //https://www.w3school.com.cn/sql/sql_func_count.asp // string sql = "select count(单词) from [单词本]"; // return (int)SqlQuery(sql); // } //} /// <summary> /// query 直接返回sql查询语句的值 执行只返回一个值的SQL命令 /// ----------------------------------------------------------- /// 注意： cmd.ExecuteScalar() 返回可能是非int 类型的object类型 /// 使用下面这种方法要出现异常的 /// int i =(int)sqlite.ExecuteScalar(sql); 出现异常：System.InvalidCastException: 指定的转换无效。 /// 正确的写法是 /// int i = Convert.ToInt32(sqlite.ExecuteScalar(sql)); /// </summary> /// <param name="sql">SQLCommandText</param> /// <param name="i"> > 0 执行成功，i=0语句执行失败</param> public object ExecuteScalar(string sql) { using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand cmd = new SQLiteCommand(sql, conn)) { conn.Open(); return cmd.ExecuteScalar(); } } } /// <summary> /// 转换后的SQL格式语句，事物 执行语句语句 这个语句是瞬间完成的即使是1万条也是这样 /// 一句一句转换成SQL语句是相当的慢的 /// INSERT INTO 表2 (Keyword,Trans,Phrase) VALUES ('undoubtedly','解释','短语'); /// </summary> /// <param name="sqlArr">每一行都是完整的 INSERT INTO语句，每插入一条</param> /// <returns>返回异常的SQL语句</returns> public string BatchExecute(string[] sqlArr) { StringBuilder sb = new StringBuilder(); using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) { SQLiteCommand cmd = new SQLiteCommand(conn); { conn.Open(); using (DbTransaction trans = conn.BeginTransaction()) { for (int i = 0; i < sqlArr.Length; i++) { try { cmd.CommandText = sqlArr[i]; cmd.ExecuteNonQuery(); } catch { //下面是插入失败的内容 sb.Append(sqlArr[i] + "\n"); //trans.Rollback();//回滚事务 //MessageBox.Show(EX.Message); } } trans.Commit(); //richTextBox2.Text = sb.ToString(); } } } return sb.ToString(); } } }
下载地址：https://download.csdn.net/download/u012663700/14020674
百度网盘：
链接：https://pan.baidu.com/s/1d3bzVfU-JmHq667we6bK7Q
提取码：6e9t
