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

FREE 12 month online training for ASP.NET & MS Expression Studio and a Free copy of MS Expression Web with Windows Server Purchase
How to get data from multiple tables using stored procedure with multiple select statements in ASP.NET

Author: Alanna Kremer
Download Source Code : 1377_MultipleSelectInSP.zip

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">
    </asp:GridView>
    <br />
    <asp:GridView ID="GridView2" runat="server">
    </asp:GridView>
    <br />
    <asp:GridView ID="GridView3" runat="server">
    </asp:GridView>
     
  6. Write a stored procedure to get data from multiple tables.

    CREATE PROCEDURE dbo.MultipleSelect
    AS
    BEGIN
    BEGIN TRANSACTION
     
    SELECT * FROM Person
    SELECT * FROM Person WHERE State = 'WA'
    SELECT * FROM Employee
     
    IF(@@error = 0)
    COMMIT TRANSACTION
    ELSE
    ROLLBACK TRANSACTION
    END
    RETURN
     
    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.

    C#
     
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.Data
    Imports System.Data.SqlClient

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

    C#
     
    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();
     
        try
        {
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            conn.Close();
        }
     
        DataTable dt1 = new DataTable();
        dt1 = ds.Tables[0];
        GridView1.DataSource = dt1;
        GridView1.DataBind();
          
        DataTable dt2 = new DataTable();
        dt2 = ds.Tables[1];
        GridView2.DataSource = dt2;
        GridView2.DataBind();
     
        DataTable dt3 = new DataTable();
        dt3 = ds.Tables[2];
        GridView3.DataSource = dt3;
        GridView3.DataBind();
    }
     
    VB.NET
     
    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()
     
        Try
            conn.Open()
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds)
        Catch ex As Exception
        Finally
            conn.Close()
        End Try
     
        Dim dt1 As New DataTable()
        dt1 = ds.Tables(0)
        GridView1.DataSource = dt1
        GridView1.DataBind()
     
        Dim dt2 As New DataTable()
        dt2 = ds.Tables(1)
        GridView2.DataSource = dt2
        GridView2.DataBind()
     
        Dim dt3 As New DataTable()
        dt3 = ds.Tables(2)
        GridView3.DataSource = dt3
        GridView3.DataBind()
    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-2014 DevASP.net