Stored Procedure Returns Multiple Recordsets

Question

I am pretty new to c#. I have a page that requires multiple recordsets, and a single sproc that returns them. I am using a repeater control for the main recordset. How do I get to the next returned recordset?

---

OK so the datasource is in the aspx page. I would have move it to the code behind page to use NextResult right? Here is my code now. How do I move the datasource to the codebehind, implement a datareader so I can use nextresult?

<asp:SqlDataSource ID="AssetMgtSearch" runat="server" 
                ConnectionString="<%$ ConnectionStrings:OperationConnectionString %>" 
                SelectCommand="spAssetMgtItemList" SelectCommandType="StoredProcedure">
            </asp:SqlDataSource>
            <div class="contentListFullHeight">
                <table cellspacing="0" cellpadding="0" border="0" class="contentList">
                    <tr>
                        <th>ShipmentID/</td>
                        <th>MaterialID/</td>
                        <th>ItemID/</td>
                    </tr>
                    <asp:Repeater ID="Repeater1" runat="server" DataSourceID="AssetMgtSearch">
                        <ItemTemplate> 
                            <tr>
                                <td colspan="3" style="border-top:solid thin blue">&nbsp;</td>
                            </tr>
                            <tr>
                                <td><%#Container.DataItem(0)%></td>
                                <td><%#Container.DataItem(1)%></td>
                                <td><%#Container.DataItem(2)%></td>
                            </tr>
                        </ItemTemplate>                           
                    </asp:Repeater>
                </table>

Answer 1

Call the NextResult() method on your reader to move to the next result.

No, you can't do this using SqlDataSource, you need to use codebehind or break up the procedure into separate queries.

Answer 2

Thanks for your answers everyone. NextResult() works well provided you make quite a few changes going from the drag and drop control creation. Here they are.

1. Remove the datasource from the aspx page
2. Remove the DataSourceID property from the Repeater control
3. Create a function in your codebehind that returns a datareader object. eg AstMgtDr()

4. On your page load set the datasource and databind properties for the Repeater control

   Repeater1.DataSource = AstMgtDr();

   Repeater1.DataBind();

5. At the top of your aspx page, add a page level directive to use the "System.Data.Common" namespace <%@ Import namespace="System.Data.Common" %>

6. To display your data:

this is the method with the best performance but it requires explicit typing

`<%#((DbDataRecord)Container.DataItem).GetInt32(0)%>`

this is another method using field names - more expensive than the previous method but faster than the default Eval.

`<%# ((DbDataRecord)Container.DataItem)["ShipmentID"] %>`

Hope this saves somebody else some time.