ASP.NET 2.0: Nested Repeater Using Stored Procedure

[kwilliams]

I found a great tutorial on how to use the nested Repeater control to display hierarchical data using ASP.NET 2.0 and VB.NET at http://www.aspnettutorials.com/tutor...peater-vb.aspx. But it uses SELECT statements within the codefile, while I want to use stored procedures instead. So if someone could give me a basic example of how to modify its code using stored procedures instead, it would be greatly appreciated. I've included the code to reference that I've already created that uses stored procedures. Thanks.

spElectionResults
CREATE PROCEDURE [dbo].[spElectionResults] AS

SET NOCOUNT OFF

SELECT ContestID, ContestTitle
FROM tblElectionResults
WHERE (VoteFor <> '00')
GROUP BY ContestID, ContestTitle
HAVING (COUNT(ContestID) >= 1)
ORDER BY ContestTitle ASC, ContestID ASC

SET NOCOUNT ON
GO

contests.aspx.vb

'Declare global variables
    Dim sqlConn As SqlConnection
    Dim strConnection As String
    Public dr As SqlDataReader

    'Declare the parameters for stored procedures
    Private cmd_electionresults As New SqlCommand()

    Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)

        Dim ds As New DataSet

        'Assign connection string
        strConnection = System.Configuration.ConfigurationManager.AppSettings("strConn")
        sqlConn = New SqlConnection(strConnection)

        'Open DB connection
        sqlConn.Open()

        'Declare variables
        Dim strContestID As String = Nothing, strContestTitle As String = Nothing

        'Declare stored procedure
        cmd_electionresults = New SqlCommand("spElectionResults", sqlConn)
        cmd_electionresults.CommandType = CommandType.StoredProcedure

        'Execute stored procedure and data reader
        cmd_electionresults.ExecuteNonQuery()
        dr = cmd_electionresults.ExecuteReader()

        While dr.Read()
            'Assign variables from DB table
            strContestID = dr("ContestID").ToString()
            strContestTitle = dr("ContestTitle").ToString()

            'Trim trailing whitespace from address variables
            strContestID = strContestID.Trim
            strContestTitle = strContestTitle.Trim

            'Test variables
            'Response.Write("strContestID: " & strContestID & "<br />")
            'Response.Write("strContestTitle: " & strContestTitle & "<br />")

            'Assign labels
            lblContestID.Text = strContestID
            lblContestTitle.Text = strContestTitle
        End While

        dr.Close()

        'Close DB connection
        sqlConn.Close()

    End Sub

[Cstick]

Based on your procedure, I don't see how your data is hierarchic. But I did notice some room for improvement in your code, so here are some modifications with notes.

I haven't tested this code, so it might have errors but it is close enough. Also, I haven't written long hand data access code in a while but I think it is right. One thing I wanted to note is the fact that in your code you call ExecuteNonQuery and then ExecuteReader on the command, the ExecuteNonQuery is completely unnecessary.

'More times than not, class scoped variables are not necessary.  In your code, they are not saving any memory and they could bite your arse down the road.
'You really don't want to worry about whether a connection is closed/created, a data reader is still open, etc.  Just declare them when you need them and get rid of them when you're done.
    
Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
    Dim electionResultSet As New DataSet

    'You should really considering moving data access and most logic into their own layers.
    Dim connString As String = System.Configuration.ConfigurationManager.AppSettings("strConn")
            
    Using conn As New SqlConnection(connString)
        conn.Open()
        
        Using da As New SqlDataAdapter("spElectionResults", conn)
            da.Fill(electionResultSet)
        End Using
        
        conn.Close()
    End Using
    
    'Assuming you have an ASP Repeater with an Id of "ElectionResultsRepeater"
    Me.ElectionResultsRepeater.DataSource = electionResultSet.Tables(0)
    Me.ElectionResultsRepeater.DataBind()
    
    'After some refactoring, your code might look like this and your concerns have been separated.
    'Me.ElectionResultsRepeater.DataSource = ElectionResults.GetElectionResults()
    'Me.ElectionResultsRepeater.DataBind()
End Sub