CODEWITHSUNDEEP
c#web-applicationsmultiple-resultsets
Praesagus
Praesagus

Reputation: 2094

Stored Procedure Returns Multiple Recordsets

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>

Upvotes: 1

Views: 2431

Answers (3)

csichar
csichar

Reputation: 154

This question has been viewed a bunch of times and I just wanted to submit my own solution.
I know the original question was c#, but I'm supporting legacy systems and it's easy to convert the code.

This came up for me today. I needed a quick-and-dirty way to display the results from a stored procedure that returned 7 different datasets.

I went about this by filling a System.Data.DataSet with my stored procedure results. Then dynamically creating DataGrids, and adding them to a PlaceHolder.

HTML Code:

<html>
  <body>
  <form id="form1" runat="server">
    <asp:PlaceHolder ID="phMetrics" runat="server"/>
  </form>
  </body>
</html>

VB.NET Code:

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    Dim ds As System.Data.DataSet = Me.GetMetrics()
    If ds IsNot Nothing Then
        For i As Integer = 0 To ds.Tables.Count - 1
            Dim _rpt As New System.Web.UI.WebControls.DataGrid()
            With _rpt
                .AutoGenerateColumns = True
                .Attributes.Add("name", "table_" & i + 1)
                .DataSource = ds.Tables(i)
                .DataBind()
            End With
            Me.phMetrics.Controls.Add(_rpt)
        Next
    End If
End Sub

Private Function GetMetrics() As System.Data.DataSet
    Dim dsMetrics As New System.Data.DataSet

    Using _sqlConn As New System.Data.SqlClient.SqlConnection(_sqlConnString)
        _sqlConn.Open()
        Dim _SqlCommand As New System.Data.SqlClient.SqlCommand("[dbo].[My_Stored_Procedure]", _sqlConn)
        With _SqlCommand
            .CommandType = System.Data.CommandType.StoredProcedure
            .Parameters.Add(New System.Data.SqlClient.SqlParameter("@ClientID", 101))
        End With
        Dim _sqlAdapter As New System.Data.SqlClient.SqlDataAdapter(_SqlCommand)
        With _sqlAdapter
            .Fill(dsMetrics)
            .Dispose()
        End With
        _sqlConn.Close()
        _sqlConn.Dispose()
    End Using

    Return dsMetrics
End Function

Upvotes: 0

Praesagus
Praesagus

Reputation: 2094

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.

Upvotes: 2

Wyatt Barnett
Wyatt Barnett

Reputation: 15673

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.

Upvotes: 9

Related Questions