从 SQL 存储过程加载数据表

Question

我正在尝试调用 vb.net WCF 程序的存储过程。由于某种原因，它没有在 vb 端返回任何行。它应该是一个简单的 datatable.load(cmd.executereader)。当我在 SQL 中执行存储过程时，我至少得到两行。存储过程没有参数。

这是我的 VB 代码：

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

我做错了什么？无论出于何种原因，它都不会将行发送回我的 WCF。我有另一个功能几乎相同，但工作得很好。

我错过了什么吗？

谢谢， 贾森

Answer 1

尝试这个代码，它几乎可以替换从命令声明到 foreach 循环结束的所有内容。具有在执行 Read() 时使用 SqlDataReader 的内容的额外好处

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