Fetch data from multiple tables using single SqlCommand object

Sourav.Kayal
Posted by in C# category on for Beginner level | Points: 250 | Views : 6688 red flag

How to fetch record 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

 


Page copy protected against web site content infringement by Copyscape

About the Author

Sourav.Kayal
Full Name: Sourav Kayal
Member Level: Silver
Member Status: Member,MVP
Member Since: 6/20/2013 2:09:01 AM
Country: India
Read my blog here http://ctrlcvprogrammer.blogspot.in/
http://www.dotnetfunda.com
I am .NET developer working for HelixDNA Technologies,Bangalore in healthcare domain. Like to learn new technology and programming language. Currently working in ASP.NET ,C# and other microsoft technologies.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)