Loading a datatable from a SQL stored procedure

Question

I'm trying to call a stored procedure for a vb.net WCF program. For some reason, it's not returning any rows on the vb side. It should be a simple datatable.load(cmd.executereader). When I execute the stored proc in SQL, I get at least two rows. There are NO parameters for the stored proc.

Here's my VB code:

Public Function GetClaimsLetterTypes() As List(Of LetterTypes) Implements ILetterWriter.GetClaimsLetterTypes
    Dim SQLcon As New SqlClient.SqlConnection
    Dim SQLcmd As New SqlClient.SqlCommand
    Dim dtTypes As DataTable
    Dim rw As DataRow

    'Initialize
    GetClaimsLetterTypes = New List(Of LetterTypes)

    'Connect to the database
    SQLcon.ConnectionString = "Data Source=VMSQL08-SRV1;Initial Catalog=Mine;User ID=stupido;Password=opensesame;"
    SQLcon.Open()

    'Grab the stored procedure, which returns the letter types
    SQLcmd.CommandText = "sp_GetTypes"
    SQLcmd.CommandType = CommandType.StoredProcedure
    SQLcmd.Connection = SQLcon


    'Execute the stored procedure, fill the datatable from a data adapter
    dtTypes = New DataTable
    dtTypes.Load(SQLcmd.ExecuteReader)

    'Load the list to be returned
    For Each rw In dtTypes.Rows
        Dim ltrTypes As New LetterTypes
        ltrTypes.ID = rw(0)
        ltrTypes.TypeName = rw(1)

        'Add the variable to the object list
        GetClaimsLetterTypes.Add(ltrTypes)
    Next

    'Shut it down
    SQLcmd.Dispose()
    SQLcon.Close()
    SQLcon.Dispose()


End Function

What am I doing wrong? For whatever reason, it won't send the rows back to my WCF. I have another function that works almost identically, but works just fine.

Am I missing something?

Thanks, Jason

Solution

Try this code, it can pretty much replace everything from your command declaration to the end of your for each loop. Has the added benefit of using the contents of the SqlDataReader while executing Read()

Using command As New SqlClient.SqlCommand("sq_GetTypes", SQLcon)
    command.CommandType = CommandType.StoredProcedure
    command.Connection.Open()
    Dim reader As SqlClient.SqlDataReader = command.ExecuteReader
    Dim ltrTypes As LetterTypes
    While reader.Read
        ltrTypes = New LetterTypes
        ltrTypes.id = reader(0)
        ltrTypes.typename = reader(1)
        GetClaimsLetterTypes.Add(ltrTypes)
    End While
    reader.Close()
End Using