Visual Studio VBA Excel加载项从存储过程中获取列数为x的数据

Question

我通常调用SQL存储过程并使用vba在excel中显示数据的方法是：

Dim sqlConnection1 As New SqlConnection("Data Source=[INSTANCE];Initial Catalog=[databasename];User ID=[user];Password=[password];")
         Dim shXL As Excel.Worksheet
        shXL = Globals.ThisAddIn.Application.ActiveSheet
        Dim Str2 As New SqlCommand
        Dim reader As SqlDataReader
        Dim j As Integer
        Str2.CommandText = "[NAME OF PROCEDURE]"
        Str2.Connection = sqlConnection1
        Str2.CommandType = CommandType.StoredProcedure
        reader = Str2.ExecuteReader()
        j = 2
        While reader.Read()
            shXL.Cells(5, j).Value = reader("[NAME OF COLUMN]")
            j = j +1
        End While
        sqlConnection1.Close()

当返回的列数为常量时，它会起作用。

但是我有一个存储过程，它返回x列，所以代码必须找出我的存储过程返回了多少列，并相应地显示数据。

Answer 1

正常情况下；）SqlDataReader具有FieldCount属性，该属性返回当前行中的列数。

另一种方法是使用Data.Datatable，这在您的情况下可能很有用。

Dim dt As Data.DataTable = new Data.DataTable()
dt.Load(reader)
Dim cc As Integer = dt.Columns.Cont 'get the count of columns
Dim rc As Integer = dt.Rows.Count 'get the count of rows
For c = 0 to cc
    For r =0 to rc
        'here extract your data ;)
    Next
Next