基于BindingList的WinForm数据绑定机制与DataGridView动态刷新技术

前言：

本文以连接SQLite为例进行代码演示

一、首先建立里一个模型类

   public class MyData{public int id { get; set; }public string name { get; set; }public int age { get; set; }public string sex { get; set; }public string address { get; set; }}

二、封装一个连接SQLite方法，并返回BindingList集合用于DataGridView使用

 private static readonly object _lock = new object();//确保线程安全public static BindingList<MyData> GetData(string sql, int offset, int limit){lock (_lock){// 创建一个BindingList来保存查询结果  BindingList<MyData> dataList = new BindingList<MyData>();// 使用SQLiteConnection打开数据库连接  using (SQLiteConnection connection = new SQLiteConnection(connectionString)){connection.Open();using (SQLiteCommand command = new SQLiteCommand(sql, connection)){// 添加分页的查询语句command.CommandText = sql + " LIMIT @limit OFFSET @offset";command.Parameters.AddWithValue("@limit", limit);command.Parameters.AddWithValue("@offset", offset);using (SQLiteDataReader reader = command.ExecuteReader()){while (reader.Read()){// 读取数据库中的每一行数据，并将其添加到BindingListMyData data = new MyData{id = reader.GetInt32(0),name = reader.IsDBNull(1) ? "N/A" : reader.GetString(1),age = reader.IsDBNull(2) ? 0 : reader.GetInt32(2),sex = reader.IsDBNull(3) ? "N/A" : reader.GetString(3),address = reader.IsDBNull(4) ? "N/A" : reader.GetString(4)};dataList.Add(data);}}}}return dataList;}}

三、数据展示（查、改，删、增）

  public static void BindDataGridView(DataGridView dataGridView){// 计算偏移量，偏移量 = (当前页 - 1) * 每页条数int offset = (currentPage - 1) * pageSize;// SQL查询语句string sql = "SELECT * FROM ceshi Where Status!='-1'";// 获取数据并绑定到DataGridViewBindingList<MyData> dataList = GetData(sql, offset, pageSize);}

 public static void SQLUPDATE(MyData data, string sql, DataGridView dataGridView){var bindingList = dataGridView.DataSource as BindingList<MyData>;if (bindingList != null){// 找到要修改的项var dataToEdit = bindingList.FirstOrDefault(d => d.id == data.id);if (dataToEdit != null){// 更新BindingList中的数据dataToEdit.name = data.name;dataToEdit.age = data.age;dataToEdit.sex = data.sex;dataToEdit.address = data.address;using (SQLiteConnection connection = new SQLiteConnection(connectionString)){connection.Open();// 创建SQLite命令对象using (SQLiteCommand command = new SQLiteCommand(sql, connection)){// 执行更新操作command.ExecuteNonQuery();}}dataGridView.Refresh();}}}

 public static void SQLDelete(int id, DataGridView dataGridView){var bindingList = dataGridView.DataSource as BindingList<MyData>;if (bindingList != null){var itemToDelete = bindingList.FirstOrDefault(d => d.id == id);if (itemToDelete != null){bindingList.Remove(itemToDelete); // 从BindingList中移除using (SQLiteConnection connection = new SQLiteConnection(connectionString)){connection.Open();// 删除SQL语句string sql = "UPDATE ceshi set Status='-1'  WHERE id = @id";// 创建SQLite命令对象using (SQLiteCommand command = new SQLiteCommand(sql, connection)){// 添加参数command.Parameters.AddWithValue("@id", id);// 执行删除操作command.ExecuteNonQuery();}}BindDataGridView(dataGridView);}}}

 public static void SQLAdd(MyData data, string sql, DataGridView dataGridView){// 将新数据项添加到BindingList中var bindingList = dataGridView.DataSource as BindingList<MyData>;if (bindingList != null){bindingList.Add(data); // 添加新数据}using (SQLiteConnection connection = new SQLiteConnection(connectionString)){connection.Open();// 创建SQLite命令对象using (SQLiteCommand command = new SQLiteCommand(sql, connection)){// 执行插入操作command.ExecuteNonQuery();// 获取插入数据的自增IDlong newId = connection.LastInsertRowId;data.id = (int)newId; // 返回自增的ID}BindDataGridView(dataGridView);}}

四、上下页切换

我的最新作品，快来一睹为快！