Introduction
The data from two tables which can have SQL join between them can be displayed on a single DataGridView and CRUD operations can be performed on it. There can be a lot of scenarios where this concept can prove to be useful.
This article discusses the concept for this implementation. I came across a similar article by Beth Massi from Microsoft on her MSDN blog which had a different approach to it.
In this article, I am using a data transfer object (DTO) and a class for it. This class contains a 'union' of all the fields from both the tables which are to be displayed on the DataGridView.
Then a collection of Data transfer objects will act as the datasource for the DataGridView in question. The ‘edit’, ‘update’ & ‘delete’ operations can be implemented through this data transfer object.
Background
For the background information to this article, please refer to these links which contain some details about the problem which many users have mentioned.
Using the Code
In this demo, I have used the Northwind.mdf database as an example. Then I used the Entity Framework as the ORM tool and made a generic List<t> of the DTOs to be used for the data transfer.
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()
{
}
}
Code to Display the Data on the DataGridView
Finally, this is the code which handles the Add, Edit, Save, Delete and Cancel events on the DataGridView:
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++)
{
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();
}
private void toolStripSave_Click(object sender, EventArgs e)
{
multiTabledataGridView.EndEdit();
SaveDatafromGrid();
toolStripSave.Enabled = false;
toolStripCancel.Enabled = false;
}
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++)
{
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();
}
private void toolStripDelete_Click(object sender, EventArgs e)
{
if (multiTabledataGridView.CurrentCell.RowIndex > -1)
{
DeleteDatafromGrid(multiTabledataGridView.CurrentCell.RowIndex);
}
}
private void DeleteDatafromGrid(int rowIndex)
{
List<OrdersDetails> ordersCollection;
NORTHWINDEntities context = new NORTHWINDEntities();
ordersCollection = (List<OrdersDetails>)gridbindingSource.DataSource;
context.Connection.Open();
int orderId;
orderId = ordersCollection.ElementAt(rowIndex).OrderID;
var order = (from o in context.Orders
where o.OrderID == orderId
select o).First();
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();
}
private void toolStripCancel_Click(object sender, EventArgs e)
{
multiTabledataGridView.CancelEdit();
toolStripSave.Enabled = false;
toolStripCancel.Enabled = false;
}
In the above Load, Save, & Delete methods, the data fields for the "OrdersDetails" object can be set using AutoMapper library which minimizes the lines of code you will need to write.
So, there you have it! A Multi-Table DataGridView control displaying data from two tables which have an SQL-join on them.
Points of Interest
The interesting concept learned from this code is the use of a Data transfer object (DTO) or a ViewModel like class for accomplishing the task of displaying two tables on a single DataGridView control.
History
- Version 1.0 with the initial concept
