Get field names returned from any sql statement

Question

I want to get a list of the field names returned from a sql statement. This can be done using a sql statement or some c# parsing code to parse the statement as a string. Can this be done easily without writing a complex parser?

For example I may want to return

name, field2, field3

from

SELECT a.field1 as name, a.field2, b.field3 FROM A INNER JOIN B ON A.Id = B.FkId

Answer 1

If you are asking about using SqlClient components, such as the SqlDataReader Class, you can use the

Reader.GetName

Method to return the column name.

Answer 2

If you have a DataSet or DataTable, you can access its Columns property, which is a collection of DataColumn objects. The column name can then be accessed like so:

// assume dt is DataTable
string colname = dt.Columns[0].ColumnName;

Answer 3

If you are using a SqlDataReader, which most people are, you can retrieve the field names using the following code

Private Shared Function GetDataRecordColumns(ByVal dr As SqlClient.SqlDataReader) As List(Of String)

    '' list to contain the columns
    Dim ls As New List(Of String)

    For x As Integer = 0 To dr.FieldCount - 1
        ls.Add(dr.GetName(x))
    Next

    Return ls

End Function

In case you are using a dataset or datatable, the following function can be used (Simply pass in your dataset.Table(0) if you are working with a dataset)

Private Shared Function GetDataRecordcolumns(ByVal dt As DataTable) As List(Of String)

        Dim ls As New List(Of String)

        For Each col As DataColumn In dt.Columns
            ls.Add(col.ColumnName)
        Next

        Return ls

 End Function

Hope this helps

G