Fetch data from multiple tables using single SqlCommand object

Fetch data from multiple tables using single SqlCommand object

In this article we will see how to fetch data from more than one table using single command object in C#. There are two procedures to do same. First one is by combining two query and second one is using store procedure.

Fetch data from multiple tables using query

Here we will combine two SQL queries within single command and using dataset we can store data separately into two DataTable

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
 
namespace WindowsForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
           
	InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
          
        }
        private void button1_Click_1(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection();
           
	con.ConnectionString = "Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial 				Catalog=test;Integrated	Security=True";
           
	  con.Open();
 
          SqlCommand cmd = new SqlCommand();
           
	  cmd.CommandText = "select * from name;select * from friend";
           
	 cmd.Connection = con;
         SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter();
           
	 ad.SelectCommand = cmd;
         DataSet ds = new DataSet();
           
	 ad.Fill(ds);
 
            this.dataGridView1.DataSource = ds.Tables[0];
            this.dataGridView2.DataSource = ds.Tables[1];
 
        }
        
    }
}
 

Here is sample output.

Fetch data from multiple tables using Store procedure

It is another technique to fetch data from multiple tables. At first we have to create one store procedure like below.

create procedure fetchdata
as
      begin
            select * from name
            select * from friend
      end

Then we will all this store procedure using ADO.NET code. In below code we are calling store procedure using single SqlCommand object.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
 
namespace WindowsForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
           
	InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
          
        }
        private void button1_Click_1(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection();
           
	con.ConnectionString = "Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial 		Catalog=test;Integrated
Security=True";
            con.Open();
 
            SqlCommand cmd = new SqlCommand();
           
	   cmd.CommandText = "fetchdata";
           
	   cmd.CommandType = CommandType.StoredProcedure;
           
           cmd.Connection = con;
 
           SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter();
           
	   ad.SelectCommand = cmd;
           DataSet ds = new DataSet();
           
	   ad.Fill(ds);
 
           this.dataGridView1.DataSource = ds.Tables[0];
           this.dataGridView2.DataSource = ds.Tables[1];
 
        }
        
    }
}

Here is sample output