Data from Two Tables in a Single DataGridView

• Download demo project - 593.69 KB
• Download source - 1.14 MB

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.

• http://www.vbforums.com/archive/index.php/t-253655.html
• http://aspalliance.com/148_Merging_two_Datasets_into_a_single_Datagrid

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.

//
// 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()
        {

        }
  }

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:

//
// 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;
        }

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