Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
SQL Server

How to get data from multiple tables using stored procedure with multiple select statements in ASP.NET

Author: Alanna Kremer
Download Source Code :

Data from multiple tables can be retrieved using multiple select statements in a stored procedure and this data from multiple tables can be handled in ADO.NET code to display or use.


This article will explain that how we can write multiple select statements in a SQL Server stored procedure and how we can handle this data from multiple tables in our ADO.NET code. You can read my article on DevASP.NET on the topic of multiple queries in a stored procedure to insert data to multiple tables. I will use same database and tables which I have used in my previous article.
When you need to write multiple queries, you have to use transaction. Transaction makes sure that if any of queries fails, any other queries those are executed will not show its effect on database. Transaction rolled back means database will come to its previous stable condition.
Getting data from multiple tables and handle it in your code is easy. You just need to get data in a DataSet object and can use DataTable objects to get individual tables from DataSet. You can also use ExecuteReader() and SqlDataReader object to read data row by row from DataSet.

Let’s start our example. You can also download source code.

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Add three GridView controls your Web Form

    <asp:GridView ID="GridView1" runat="server">
    <br />
    <asp:GridView ID="GridView2" runat="server">
    <br />
    <asp:GridView ID="GridView3" runat="server">
  6. Write a stored procedure to get data from multiple tables.

    CREATE PROCEDURE dbo.MultipleSelect
    SELECT * FROM Person
    SELECT * FROM Person WHERE State = 'WA'
    SELECT * FROM Employee
    IF(@@error = 0)
    Write multiple select statements for different tables and check error. If there is any error then roll back transaction otherwise commit transaction. You can write select statements with parameters and you can add parameters in your code file when you call stored procedure. 
  7. Open code behind file and include following namespace.

    using System.Data;
    using System.Data.SqlClient;
    Imports System.Data
    Imports System.Data.SqlClient

  8. Write code below in Page Load event of code file

    protected void Page_Load(object sender, EventArgs e)
        string constring = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True";
        SqlConnection conn = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand("MultipleSelect", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
        catch (Exception ex)
        DataTable dt1 = new DataTable();
        dt1 = ds.Tables[0];
        GridView1.DataSource = dt1;
        DataTable dt2 = new DataTable();
        dt2 = ds.Tables[1];
        GridView2.DataSource = dt2;
        DataTable dt3 = new DataTable();
        dt3 = ds.Tables[2];
        GridView3.DataSource = dt3;
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim constring As String = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True"
        Dim conn As New SqlConnection(constring)
        Dim cmd As New SqlCommand("MultipleSelect", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim ds As New DataSet()
            Dim da As New SqlDataAdapter(cmd)
        Catch ex As Exception
        End Try
        Dim dt1 As New DataTable()
        dt1 = ds.Tables(0)
        GridView1.DataSource = dt1
        Dim dt2 As New DataTable()
        dt2 = ds.Tables(1)
        GridView2.DataSource = dt2
        Dim dt3 As New DataTable()
        dt3 = ds.Tables(2)
        GridView3.DataSource = dt3
    End Sub
    Connect to your server and set connection using SqlConnection class. Pass stored procedure name and connection object in SqlCommand constructor and command type as stored procedure. Create an instance of DataSet class and fill DataSet using SqlDataAdapter instance. Get DataTable by using table index or table name.
  9. Press F5 and see result. 
Add Article Comment:
Name :
Email Address :
Comments :
<< How to merge GridView Cells to display same value in ASP.NET

Disclaimer - Privacy
© 2002-2017