Display records from multiple tables with Stored Procedure using C# and VB.Net in ASP.Net

Hi George616,

To get data from multiple table you need to use Join query in the procedure and select the record page wise.

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

SQL

--EXEC Customer_GetCustomerWithPaging 3,10,NULL
CREATE PROCEDURE [dbo].[Customer_GetCustomerWithPaging]
	  @PageIndex int,
	  @PageSize int,
	  @PageCount int OUTPUT
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT ROW_NUMBER() OVER
			(
				ORDER BY [Customers].[CustomerID]
			) AS RowNumber
			,[Customers].[CustomerID]
			,[ContactName]		  
			,[City]
			,[Country]
			,[Orders].ShipCity
			,PostalCode			
	INTO #Results
	FROM [Customers]
	INNER JOIN Orders
	ON [Customers].CustomerId = Orders.[CustomerID]
	GROUP BY [Customers].[CustomerID],[ContactName],[City],[Country],[Orders].ShipCity,PostalCode	

	SELECT @PageCount = COUNT(*) FROM #Results

	SELECT * from #Results 
	WHERE RowNumber BETWEEN ((@PageIndex-1)*@PageSize)+1 AND ((@PageIndex-1)*@PageSize)+@PageSize OR @PageIndex = -1

	DROP TABLE #Results
END

HTML

C#

<div style="width: 450px">
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" CssClass="Item" Width="450px">
        <Columns>
            <asp:BoundField DataField="CustomerID" HeaderText="Id" />
            <asp:BoundField DataField="ContactName" HeaderText="Name" />
            <asp:BoundField DataField="Country" HeaderText="Country" />
            <asp:BoundField DataField="ShipCity" HeaderText="Ship City" />
            <asp:BoundField DataField="PostalCode" HeaderText="Postal Code" />
        </Columns>
    </asp:GridView>
    <br />
    <div style="float: left;">
        Page&nbsp;<asp:Label ID="lblPageIndex" runat="server" Text="Label" />
        &nbsp;of&nbsp;<asp:Label ID="lblTotalPage" runat="server" />
        (<asp:Label ID="lblTotal" runat="server" />items)&nbsp;&nbsp;
        <div class="dvPager">
            <asp:Repeater ID="rptPager" runat="server">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                        CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
                        OnClick="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
                </ItemTemplate>
            </asp:Repeater>
        </div>
    </div>
</div>

VB.Net

<div style="width: 450px">
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" CssClass="Item" Width="450px">
        <Columns>
            <asp:BoundField DataField="CustomerID" HeaderText="Id" />
            <asp:BoundField DataField="ContactName" HeaderText="Name" />
            <asp:BoundField DataField="Country" HeaderText="Country" />
            <asp:BoundField DataField="ShipCity" HeaderText="Ship City" />
            <asp:BoundField DataField="PostalCode" HeaderText="Postal Code" />
        </Columns>
    </asp:GridView>
    <br />
    <div style="float: left;">
        Page&nbsp;<asp:Label ID="lblPageIndex" runat="server" Text="Label" />
        &nbsp;of&nbsp;<asp:Label ID="lblTotalPage" runat="server" />
        (<asp:Label ID="lblTotal" runat="server" />items)&nbsp;&nbsp;
        <div class="dvPager">
            <asp:Repeater ID="rptPager" runat="server">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                        CssClass='<%# If(Convert.ToBoolean(Eval("Enabled")), "page_enabled", "page_disabled")%>'
                        OnClick="Page_Changed" OnClientClick='<%# If(Not Convert.ToBoolean(Eval("Enabled")), "return false;", "") %>'></asp:LinkButton>
                </ItemTemplate>
            </asp:Repeater>
        </div>
    </div>
</div>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

private int PageSize = 10;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.GetCustomersPageWise(1);

    }
}

private void GetCustomersPageWise(int pageIndex)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("Customer_GetCustomerWithPaging", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", PageSize);
            cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4);
            cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                GridView1.DataSource = sdr;
                GridView1.DataBind();
                con.Close();
                int recordCount = Convert.ToInt32(cmd.Parameters["@PageCount"].Value);
                this.PopulatePager(recordCount, pageIndex);
            }
        }
    }
}

protected void Page_Changed(object sender, EventArgs e)
{
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    this.GetCustomersPageWise(pageIndex);
}

private void PopulatePager(int recordCount, int currentPage)
{
    double dblPageCount = (double)((decimal)recordCount / (decimal)PageSize);
    int pageCount = (int)Math.Ceiling(dblPageCount);
    List<ListItem> pages = new List<ListItem>();
    if (pageCount > 0)
    {
        if (currentPage != 1)
        {
            pages.Add(new ListItem("Prev", (currentPage - 1).ToString()));
        }
        if (pageCount < 4)
        {
            for (int i = 1; i <= pageCount; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
        }
        else if (currentPage < 4)
        {
            for (int i = 1; i <= 4; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
        }
        else if (currentPage > pageCount - 4)
        {
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
            for (int i = currentPage - 1; i <= pageCount; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
        }
        else
        {
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
            for (int i = currentPage - 2; i <= currentPage + 2; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
        }
        if (currentPage != pageCount)
        {
            pages.Add(new ListItem("Next", (currentPage + 1).ToString()));
        }
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();

    lblPageIndex.Text = currentPage.ToString();
    lblTotalPage.Text = ((recordCount / PageSize) + ((recordCount % PageSize) > 0 ? 1 : 0)).ToString();
    lblTotal.Text = recordCount.ToString();
}

VB.Net

Private PageSize As Integer = 10

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Me.GetCustomersPageWise(1)
    End If
End Sub

Private Sub GetCustomersPageWise(ByVal pageIndex As Integer)
    Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand("Customer_GetCustomerWithPaging", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
            cmd.Parameters.AddWithValue("@PageSize", PageSize)
            cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4)
            cmd.Parameters("@PageCount").Direction = ParameterDirection.Output
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                GridView1.DataSource = sdr
                GridView1.DataBind()
                con.Close()
                Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@PageCount").Value)
                Me.PopulatePager(recordCount, pageIndex)
            End Using
        End Using
    End Using
End Sub

Protected Sub Page_Changed(ByVal sender As Object, ByVal e As EventArgs)
    Dim pageIndex As Integer = Integer.Parse((TryCast(sender, LinkButton)).CommandArgument)
    Me.GetCustomersPageWise(pageIndex)
End Sub

Private Sub PopulatePager(ByVal recordCount As Integer, ByVal currentPage As Integer)
    Dim dblPageCount As Double = CDbl((CDec(recordCount) / CDec(PageSize)))
    Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
    Dim pages As List(Of ListItem) = New List(Of ListItem)()
    If pageCount > 0 Then
        If currentPage <> 1 Then
            pages.Add(New ListItem("Prev", (currentPage - 1).ToString()))
        End If
        If pageCount < 4 Then
            For i As Integer = 1 To pageCount
                pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
            Next
        ElseIf currentPage < 4 Then
            For i As Integer = 1 To 4
                pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
            Next
            pages.Add(New ListItem("...", (currentPage).ToString(), False))
        ElseIf currentPage > pageCount - 4 Then
            pages.Add(New ListItem("...", (currentPage).ToString(), False))
            For i As Integer = currentPage - 1 To pageCount
                pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
            Next
        Else
            pages.Add(New ListItem("...", (currentPage).ToString(), False))
            For i As Integer = currentPage - 2 To currentPage + 2
                pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
            Next
            pages.Add(New ListItem("...", (currentPage).ToString(), False))
        End If
        If currentPage <> pageCount Then
            pages.Add(New ListItem("Next", (currentPage + 1).ToString()))
        End If
    End If

    rptPager.DataSource = pages
    rptPager.DataBind()
    lblPageIndex.Text = currentPage.ToString()
    lblTotalPage.Text = (Math.Round(recordCount / PageSize) + (If((recordCount Mod PageSize) > 0, 1, 0))).ToString()
    lblTotal.Text = recordCount.ToString()
End Sub

Screenshot

Yes, you need to create separate stored procedure for table and call in respective procedure in respective page got get the data.