单个 DataGridView 中的两个表的数据
4.71/5 (7投票s)
在一个 DataGridView 中显示、编辑和删除两个表中的数据
引言
可以对两个表之间存在 SQL join 关系的数据进行显示，并在单个 DataGridView 中执行 CRUD 操作。有很多场景可以证明这个概念的用处。
本文讨论了该实现的概念。我参考了微软 Beth Massi 在她的 MSDN 博客上发表的一篇类似的文章，该文章采用了不同的方法。
在本文中，我使用了一个数据传输对象 (DTO) 及其类。该类包含要显示在 DataGridView 上的两个表中所有字段的“union”。
然后，数据传输对象集合将作为相关 DataGridView 的 datasource。可以通过这个数据传输对象实现“编辑”、“更新”和“删除”操作。
背景
有关本文的背景信息，请参考以下链接，其中包含一些关于许多用户提到的问题的详细信息。
- http://www.vbforums.com/archive/index.php/t-253655.html
- http://aspalliance.com/148_Merging_two_Datasets_into_a_single_Datagrid
Using the Code
在本演示中，我使用了 Northwind.mdf 数据库作为示例。然后我使用 Entity Framework 作为 ORM 工具，并创建了一个泛型 List
//
// Composite class for the DTO
//
public class OrdersDetails
{
public int OrderID { get; set; }
public DateTime? OrderDate { get; set; }
public int Quantity { get; set; }
public float Discount { get; set; }
public decimal UnitPrice { get; set; }
public string ShipName { get; set; }
public string ShipAddress { get; set; }
public string ShipCity { get; set; }
public string ShipCountry { get; set; }
public DateTime? ShippedDate { get; set; }
}
public static class Operations
{
public static void FetchOrders()
{
}
}
在 DataGridView 上显示数据的代码
最后，这段代码处理 DataGridView 上的添加、编辑、保存、删除和取消事件
//
// Load, Save, Delete and Cancel events & methods for the DataGridView.
//
//Method which loads data into the DataGridView control using the "OrdersDetails" object.
private void frmMultiTable_Load(object sender, EventArgs e)
{
LoadDataIntoGrid();
toolStripSave.Enabled = false;
toolStripCancel.Enabled = false;
}
private void LoadDataIntoGrid()
{
List<OrdersDetails> ordersCollection = new List<OrdersDetails>();
NORTHWINDEntities context = new NORTHWINDEntities();
context.Connection.Open();
int counter = context.Orders.Count();
for (int i = 0; i < context.Orders.Count() - 1; i++)
{
// AutoMapper library can be used alternatively to perform this mapping.
OrdersDetails item = new OrdersDetails();
item.OrderID = context.Orders.ToList()[i].OrderID;
item.OrderDate = context.Orders.ToList()[i].OrderDate;
item.Quantity = context.Order_Details.ToList()[i].Quantity;
item.Discount = context.Order_Details.ToList()[i].Discount;
item.UnitPrice = context.Order_Details.ToList()[i].UnitPrice;
item.ShipName = context.Orders.ToList()[i].ShipName;
item.ShipAddress = context.Orders.ToList()[i].ShipAddress;
item.ShipCity = context.Orders.ToList()[i].ShipCity;
item.ShipCountry = context.Orders.ToList()[i].ShipCountry;
item.ShippedDate = context.Orders.ToList()[i].ShippedDate;
ordersCollection.Add(item);
item = null;
}
gridbindingSource.DataSource = ordersCollection;
multiTabledataGridView.DataSource = null;
multiTabledataGridView.DataSource = gridbindingSource;
multiTabledataGridView.Refresh();
statusStrip1.Text = counter + " Item(s)";
context.Connection.Close();
context.Connection.Dispose();
context.Dispose();
}
//Save Event for the DataGridView control.
private void toolStripSave_Click(object sender, EventArgs e)
{
multiTabledataGridView.EndEdit();
SaveDatafromGrid();
toolStripSave.Enabled = false;
toolStripCancel.Enabled = false;
}
//Method which saves the data from the DataGridView into the database
//via the "OrdersDetails" object.
private void SaveDatafromGrid()
{
List<OrdersDetails> ordersCollection = new List<OrdersDetails>();
NORTHWINDEntities context = new NORTHWINDEntities();
ordersCollection = (List<OrdersDetails>) gridbindingSource.DataSource;
context.Connection.Open();
for (int i = 0; i < context.Orders.Count() - 1; i++)
{
// AutoMapper library can be used alternatively to perform this mapping.
OrdersDetails item = new OrdersDetails();
context.Orders.ToList()[i].OrderDate =
ordersCollection.ToList()[i].OrderDate;
context.Orders.ToList()[i].ShipName =
ordersCollection.ToList()[i].ShipName;
context.Orders.ToList()[i].ShipAddress =
ordersCollection.ToList()[i].ShipAddress;
context.Orders.ToList()[i].ShipCity =
ordersCollection.ToList()[i].ShipCity;
context.Orders.ToList()[i].ShipCountry =
ordersCollection.ToList()[i].ShipCountry;
context.Orders.ToList()[i].ShippedDate =
ordersCollection.ToList()[i].ShippedDate;
context.Order_Details.ToList()[i].Quantity =
(short)ordersCollection.ToList()[i].Quantity;
context.Order_Details.ToList()[i].Discount =
ordersCollection.ToList()[i].Discount;
context.Order_Details.ToList()[i].UnitPrice =
ordersCollection.ToList()[i].UnitPrice;
}
gridbindingSource.DataSource = ordersCollection;
multiTabledataGridView.DataSource = null;
multiTabledataGridView.DataSource = gridbindingSource;
multiTabledataGridView.Refresh();
context.SaveChanges();
context.Connection.Close();
context.Connection.Dispose();
context.Dispose();
}
//Delete Event for the DataGridView control.
private void toolStripDelete_Click(object sender, EventArgs e)
{
if (multiTabledataGridView.CurrentCell.RowIndex > -1)
{
DeleteDatafromGrid(multiTabledataGridView.CurrentCell.RowIndex);
}
}
//Method which deletes the data from the DataGridView and also from the database.
private void DeleteDatafromGrid(int rowIndex)
{
List<OrdersDetails> ordersCollection; // = new List<OrdersDetails>();
NORTHWINDEntities context = new NORTHWINDEntities();
ordersCollection = (List<OrdersDetails>)gridbindingSource.DataSource;
context.Connection.Open();
int orderId;
//Retrieve the OrderID
orderId = ordersCollection.ElementAt(rowIndex).OrderID;
var order = (from o in context.Orders
where o.OrderID == orderId
select o).First();
//Delete the row from Order_Details child table first &
//then from the Orders table.
if (!order.Order_Details.IsLoaded)
order.Order_Details.Load();
int counter = order.Order_Details.Count();
for (int i = 0; i < counter; i++)
{
context.DeleteObject(order.Order_Details.First());
}
context.SaveChanges();
context.DeleteObject(order);
gridbindingSource.Remove(gridbindingSource.Current);
multiTabledataGridView.Refresh();
context.SaveChanges();
context.Connection.Close();
context.Connection.Dispose();
context.Dispose();
}
//Cancel Event of the DataGridView control.
private void toolStripCancel_Click(object sender, EventArgs e)
{
multiTabledataGridView.CancelEdit();
toolStripSave.Enabled = false;
toolStripCancel.Enabled = false;
}
在上述 Load、Save 和 Delete 方法中，可以使用 AutoMapper 库设置 "OrdersDetails" 对象的字段，从而减少您需要编写的代码量。
好了，这就是全部！一个多表 DataGridView 控件，显示具有 SQL join 关系的两个表中的数据。
关注点
从这段代码中学到的有趣概念是使用数据传输对象 (DTO) 或类似 ViewModel 的类来完成在单个 DataGridView 控件上显示两个表的任务。
历史
- 版本 1.0，包含初始概念