Loading relational tables into DataGridView in C#.NET and Winforms

In this post we can see how to show relational tables in the datagridview.

For this sample I have taken Nortwind as my database and I have selected Customers and Orders Tables.

o    Let’s take a new windows application.

o    Add one form to it.

o    Copy and paste the below code in form.cs file

 private DataGridView customerDataGridView = new DataGridView();

 private BindingSource customerBindingSource = new BindingSource();

 private DataGridView ordersDataGridView = new DataGridView();

 private BindingSource ordersBindingSource = new BindingSource();

 public Form1()

   {

 InitializeComponent();

 customerDataGridView.Dock = DockStyle.Fill;

 customerDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

 customerDataGridView.AllowUserToAddRows = false;

 customerDataGridView.AllowUserToDeleteRows = false;

 ordersDataGridView.Dock = DockStyle.Fill;

 ordersDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

 customerDataGridView.AllowUserToAddRows = false;

 customerDataGridView.AllowUserToDeleteRows = false;

 SplitContainer splitContainer1 = new SplitContainer();

 splitContainer1.Dock = DockStyle.Fill;

 splitContainer1.Orientation = Orientation.Horizontal;

 splitContainer1.Panel1.Controls.Add(customerDataGridView);

 splitContainer1.Panel2.Controls.Add(ordersDataGridView);

 this.Controls.Add(splitContainer1);

 this.Load += new System.EventHandler(Form1_Load);

 this.Text = "Loading relational tables into DataGridView";

   }

 private void Form1_Load(object sender, EventArgs e)

        {

 // Bind the DataGridView controls to the BindingSource

 // components and load the data from the database.

 customerDataGridView.DataSource = customerBindingSource;

 ordersDataGridView.DataSource = ordersBindingSource;

GetData();

 // Resize the master DataGridView columns to fit the newly loaded data.

 customerDataGridView.AutoResizeColumns();

 // Configure the details DataGridView so that its columns automatically

 // adjust their widths when the data changes.

 ordersDataGridView.AutoSizeColumnsMode =

 DataGridViewAutoSizeColumnsMode.AllCells;

        }

private void GetData()

    {

 try

  {

    // Specify a connection string. Replace the given value with a

// valid connection string for a Northwind SQL Server sample

// database accessible to your system.

String connectionString = "Server=localhost; uid=sa;pwd=sa@123;       database=Northwind";

SqlConnection connection = new SqlConnection(connectionString);

// Create a DataSet.

DataSet data = new DataSet();

data.Locale = System.Globalization.CultureInfo.InvariantCulture;

// Add data from the Customers table to the DataSet.

SqlDataAdapter masterDataAdapter = new

SqlDataAdapter("select * from customers", connection);

masterDataAdapter.Fill(data, "Customers");

 // Add data from the Orders table to the DataSet.

 SqlDataAdapter detailsDataAdapter = new

 SqlDataAdapter("select * from Orders", connection);

 detailsDataAdapter.Fill(data, "Orders");

 // Establish a relationship between the two tables.

 DataRelation relation = new DataRelation("CustomersOrders",

 data.Tables["Customers"].Columns["customerID"],

 data.Tables["Orders"].Columns["customerID"]);

 data.Relations.Add(relation);

 // Bind the master data connector to the Customers table.

 customerBindingSource.DataSource = data;

 customerBindingSource.DataMember = "Customers";

  // Bind the details data connector to the master data connector,

  // using the DataRelation name to filter the information in the

  // details table based on the current row in the master table.

  ordersBindingSource.DataSource = customerBindingSource;

  ordersBindingSource.DataMember = "CustomersOrders";

    }

  catch (SqlException ex)

   {

                MessageBox.Show("sql connection has not been established”);

    }

 }