Click here to Skip to main content
Click here to Skip to main content
Go to top

Data from Two Tables in a Single DataGridView

, 22 Dec 2011
Rate this:
Please Sign up or sign in to vote.
Display, Edit and Delete Data from Two Tables in a Single DataGridView

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.

Screenshot of the Multi Table DataGrid Control on a form

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 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ashraf Shah
Software Developer (Senior) Australian Energy Market Operator
Australia Australia
No Biography provided
Follow on   Twitter

Comments and Discussions

 
QuestionHow do we checked a DataGridView and change value of a cell? Pinmemberrochi00114-May-14 21:02 
General:thumbsup: Pinmembermoeinmohebbi26-Jul-13 4:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 22 Dec 2011
Article Copyright 2011 by Ashraf Shah
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid