getting data when not know column names 1

[zerkat]

I am using ASP.NET 2.0. We are using a stored procedure w/ a pivot query that returns the names of positions and hours worked per project but depending on the dates passed into the stored procedure the same positions do not get returned - people get promoted, leaving, etc. Since the pivot query only returns positions (columns) that have relevant data that matches the date criteria, the number of columns returned is variable and so are the column names.

I can get the column names using SQLDataReader.GetName but how do I get the rest of the data? I am used to specifying the column names in my functions to return data and completely clueless as to how to do this without them.

Right now code is this:

Public Function GetData(ByVal StartDate As Date, ByVal EndDate As Date) As List(Of String)
Using conn As New SqlConnection(ConnectionVarName)
Using com As New SqlCommand("storedProcName", conn)
com.Parameters.Add(New SqlParameter("@ParamName1", DataSqlDbType.DateTime)).Value = StartDate
com.Parameters.Add(New SqlParameter("@ParamName2", DataSqlDbType.DateTime)).Value = EndDate
com.CommandType = Data.CommandType.StoredProcedure
conn.Open()
Using dr As SqlDataReader = com.ExecuteReader()
Dim ColumnCount As Integer = dr.FieldCount
Dim ColumnName As String

For i As Integer = 0 to ColumnCount - 1
ColumnName = dr.GetName(i).ToString()
Next
End Using
End Using
End Using
End Fuction
End Function

 

[jbenson001]

I am not sure I understand the problem. You say that the datset is returning different amounts of columns from the SP based on your parmertes correct? If that is the case what is the issue? The dataset will have the column names as part of the resultset. Why do you need to loop through to get the col names and data?

 

[zerkat]

how do i return the column names w/ the result set?

usually i tell it what columns i am expecting...

while dr.read()
Dim object As New object
object = New object(dr("ColumnName1"), dr("ColumnName2"), etc.))

 

[jmeckley]

you don't need the column names, you can reference the data by ordinal, although i don't understand why you are loading the data into an object array, that more useless than a data table.

var table = new DataTable();
table.Load(command.ExecuteQuery());
return table;

this will automatically populate the table with the result's schema.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[zerkat]

Thanks for the tip Jason. I hadn't thought of doing it that way (obviously). That worked just fine.