c# - VB.NET: How to call another stored procedures based on stored procedure result and display all results?

Question

i have a scenario, my stored procedure (sp1) takes the input from the webpage and returns some results. For example, if sp1 returns 10 records, we have "id" column in each record and we need to pass that "id" column value to another two stored procedures(ex: sp2, sp3) and display all the results(from sp1, sp2, sp3) associated to record1at the beginning of page and record2 all details after that and so on..

Output on web page should look like:

Record1 details 

Sp2 Output(record1)

sp3 Output(record1)

Record2 details 

Sp2 Output(record2)

sp3 Output(record2)
Record3 details 

Sp2 Output(record3)

sp3 Output(record3)

...........

Can some one please let me know how to present in this way. Sp1 results are in an arraylist (in code behind) and sp2 and sp3 literally return tables( around 10 recrds with 5-6 columns).

I just need how to display data.. i'm using for each to execute sp2 and sp3 for each record in the results of sp1. Looking to know how to store the data in code behind and how to display on aspx.page..

Answer 1

This would be an ideal candidate for using the Repeater Class. You can nest them to handle your logic of displaying data based on an initial DataSource. Since I do not know your Model definition, I have used generic placeholders that you will need to replace in the example code. Examples of these placeholders include "SomeFieldFromSp2DataTable", "FieldNameOfId", "TheClassOfItemsInYourArrayList", etc.

First, make the markup in the ASPX page. The HeaderTemplate contains markup to use for the header of a DataSource. The ItemTemplate is the markup for the body of each item in your DataSource. FooterTemplate does markup at the end of a DataSource. In this instance I mocked it up so it would create nested HTML Tables.

<asp:Repeater ID="rptSP1" runat="server">
    <HeaderTemplate>    
        <table>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td><asp:Label ID="lblSP1" runat="server"><%# CType(Container.DataItem, TheClassOfItemsInYourArrayList).FieldNameOfId%></asp:Label></td>
        </tr>
        <asp:Repeater ID="rptSP2" runat="server">
            <HeaderTemplate>
                <tr><td><table>
            </HeaderTemplate>
            <ItemTemplate>
                <tr>
                    <td><asp:Label ID="lblSP2" runat="server"><%#DataBinder.Eval(Container.DataItem, "SomeFieldFromSp2DataTable")%></asp:Label></td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                </table></td></tr>
            </FooterTemplate>
        </asp:Repeater>
        <asp:Repeater ID="rptSP3" runat="server">
            <HeaderTemplate>
                <tr><td><table>
            </HeaderTemplate>
            <ItemTemplate>
                <tr>
                    <td><asp:Label ID="lblSP3" runat="server"><%#DataBinder.Eval(Container.DataItem, "SomeFieldFromSp3DataTable")%></asp:Label></td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                </table></td></tr>
            </FooterTemplate>
        </asp:Repeater>         
    </ItemTemplate>
    </FooterTemplate>
        </table>
    </FooterTemplate>
</asp:Repeater>

Note that accessing the ArrayList values is a little different than a DataTable or more typical List(of T).

Next, you will need to wire up the initial DataSource binding for your parent Repeater:

rptSP1.DataSource = YourArrayList
rptSP1.DataBind()

You are not done yet though. You now have to ensure that you get data from the first Repeater in order to get your DataSources for the second and third repeaters. You accomplish this in the Event Handler for the rptSP1's ItemDataBound Event. Since the nested Repeaters are in the ItemTemplate you'll have to use FindControl and Cast them as a Repeater.

Private Sub rptSP1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rptSP1.ItemDataBound

    'Perform this check because you only need the actual data items.
    If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then

        Dim intIdFromSp1 As Integer = CType(e.Item.DataItem, TheClassOfItemsInYourArrayList).IdField

        Dim rptSP2 As Repeater = CType(e.Item.FindControl("rptSP2"), Repeater)
        Dim rptSP3 As Repeater = CType(e.Item.FindControl("rptSP3"), Repeater)

        'Code that gets the data from the other SPs would be in a Function called GetDataTableFromSP2.
        rptSP2.DataSource = GetDataTableFromSP2(intIdFromSp1)
        rptSP2.DataBind()

        rptSP3.DataSource = GetDataTableFromSP3(intIdFromSp1)
        rptSP3.DataBind()

    End If

End Sub