Can you get the column names from a SqlDataReader?

Question

After connecting to the database, can I get the name of all the columns that were returned in my SqlDataReader?

Laundry

Answer 1

var reader = cmd.ExecuteReader();

var columns = new List<string>();

for(int i=0;i<reader.FieldCount;i++)
{
   columns.Add(reader.GetName(i));
}

or

var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();

Dew

it's insane that there is no enumerable interface that lets you iterate through the columns. – Machination

A bit shorter: columns = Enumerable.Range(0, reader.FieldCount) .Select(reader.GetName).ToList(); – Evans

This works great. I also found out that my column names were all uppercase unless I used quotes around the column name. SELECT id AS "MyId" FROM table; – Contrition

sir its returning all columnnames in lowercase. Column names in table are all uppercase like OBJECTID and reader is returning lowercase like objectid – Lacking

how does this enumerable.range().select work in vb.net? any ideas? – Southwest

its Dim columns() As String = Enumerable.Range(0, cTab.FieldCount).Select(Function(n) cTab.GetName(n)).ToArray – Southwest

If I remember correctly (which is doubtful), whether column names are returned in all lower case or all upper case is specific to the database you're using. PostgreSQL, which I use most of the time, gives them in lower case, and SQL Server gives them in upper case. Any corrections to this statement will be gratefully appreciated. – Precious

@ROBERTRICHARDSON Unless specified otherwise, SQL Server returns column names as-is. – Perdue

Answer 2

There is a GetName function on the SqlDataReader which accepts the column index and returns the name of the column.

Conversely, there is a GetOrdinal which takes in a column name and returns the column index.

Runofthemill

Two reasons: first, the original poster has not chosen an answer yet, and secondly, there are other answers that give more detailed description of the problem's 'solution' then just the existence of the functionality. Personally, I like Steven Lyons' answer the best as not only does it talk about GetName but also goes into FieldType and DataType. – Runofthemill

GetOrdinal was perfect. I was looking for GetName, but much cleaner solution for my issue with GetOrdinal. – Delvalle

Answer 3

You can get the column names from a DataReader.

Here is the important part:

  for (int col = 0; col < SqlReader.FieldCount; col++)
  {
    Console.Write(SqlReader.GetName(col).ToString());         // Gets the column name
    Console.Write(SqlReader.GetFieldType(col).ToString());    // Gets the column type
    Console.Write(SqlReader.GetDataTypeName(col).ToString()); // Gets the column database type
  }

Dashiell

Answer 4

Already mentioned. Just a LINQ answer:

var columns = reader.GetSchemaTable().Rows
                                     .Cast<DataRow>()
                                     .Select(r => (string)r["ColumnName"])
                                     .ToList();

//Or

var columns = Enumerable.Range(0, reader.FieldCount)
                        .Select(reader.GetName)
                        .ToList();

The second one is cleaner and much faster. Even if you cache GetSchemaTable in the first approach, the querying is going to be very slow.

Goutweed

Is there a way to do this with Values? – Onagraceous

@TravisHeeter I dont get you. Find column names from values of what? – Goutweed

I mean just an east way to get the values in the result set into a list, or perhaps the whole thing to an IEnumerable<dynamic> object. – Onagraceous

@TravisHeeter yes could do reader.Cast<IDataRecord>().ToList(). I believe you could use dynamic keyword there instead of IDataRecord but with no benefit. DataTable was designed to ease onetime loading, so you could use that too but you lose the benefit of loading on demand (with data reader you could stop loading at any point), like var dt = new DataTable(); dt.Load(reader); return dt.AsEnumerable().ToList();. There are many libraries which can automate this for you, find them here stackoverflow.com/questions/11988441 and here stackoverflow.com/questions/1464883 – Goutweed

I tried reader.Cast<IEnumerable<dynamic>> and .Cast<dynamic>, but it says, Cannot convert method group 'Cast' to non-delegate type 'dynamic'. Did you intend to invoke the method? what did I do wrong there? (I looked at your sources, but they required you to know the column name, which I don't) – Onagraceous

@TravisHeeter I dont know what you're missing, I think you're mistyping something. Try using (var reader = command.ExecuteReader()) { return reader.Cast<IDataRecord>().ToList(); } . See this answer for converting generically to expando objects: https://mcmap.net/q/100882/-can-i-create-an-anonymous-type-collection-from-a-datareader – Goutweed

I went ahead and started a question: #31657438 – Onagraceous

Answer 5

If you want the column names only, you can do:

List<string> columns = new List<string>();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
    DataTable dt = reader.GetSchemaTable();
    foreach (DataRow row in dt.Rows)
    {
        columns.Add(row.Field<String>("ColumnName"));
    }
}

But if you only need one row, I like my AdoHelper addition. This addition is great if you have a single line query and you don't want to deal with data table in you code. It's returning a case insensitive dictionary of column names and values.

public static Dictionary<string, string> ExecuteCaseInsensitiveDictionary(string query, string connectionString, Dictionary<string, string> queryParams = null)
{
    Dictionary<string, string> CaseInsensitiveDictionary = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
    try
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;

                // Add the parameters for the SelectCommand.
                if (queryParams != null)
                    foreach (var param in queryParams)
                        cmd.Parameters.AddWithValue(param.Key, param.Value);

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    foreach (DataRow row in dt.Rows)
                    {
                        foreach (DataColumn column in dt.Columns)
                        {
                            CaseInsensitiveDictionary.Add(column.ColumnName, row[column].ToString());
                        }
                    }
                }
            }
            conn.Close();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    return CaseInsensitiveDictionary;
}

Ventose

throw ex; is a worst practice. – Roquelaure

its just an example – Ventose

Asawyer, you should at least say why. I assume you're going to say you should use "throw;" instead so that you don't lose the original strack trace details. – Grammar

Answer 6

Use an extension method:

    public static List<string> ColumnList(this IDataReader dataReader)
    {
        var columns = new List<string>();
        for (int i = 0; i < dataReader.FieldCount; i++)
        {
            columns.Add(dataReader.GetName(i));
        }
        return columns;
    }

Wilsonwilt

Answer 7

For me, I would write an extension method like this:

public static string[] GetFieldNames(this SqlDataReader reader)
{
     return Enumerable.Range(0, reader.FieldCount).Select(x => reader.GetName(x)).ToArray();
}

Chaetognath

Answer 8

I use the GetSchemaTable method, which is exposed via the IDataReader interface.

Loathe

yes here is an article about it: Getting schema info from the datareader msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx – Sergias

Answer 9

You sure can.

protected void GetColumNames_DataReader()
{
  System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection("server=localhost;database=northwind;trusted_connection=true");
  System.Data.SqlClient.SqlCommand SqlCmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM Products", SqlCon);

  SqlCon.Open();

  System.Data.SqlClient.SqlDataReader SqlReader = SqlCmd.ExecuteReader();
  System.Int32 _columncount = SqlReader.FieldCount;

  System.Web.HttpContext.Current.Response.Write("SqlDataReader Columns");
  System.Web.HttpContext.Current.Response.Write(" ");

  for ( System.Int32 iCol = 0; iCol < _columncount; iCol ++ )
  {
    System.Web.HttpContext.Current.Response.Write("Column " + iCol.ToString() + ": ");
    System.Web.HttpContext.Current.Response.Write(SqlReader.GetName( iCol ).ToString());
    System.Web.HttpContext.Current.Response.Write(" ");
  }

}

This is originally from: http://www.dotnetjunkies.ddj.com/Article/B82A22D1-8437-4C7A-B6AA-C6C9BE9DB8A6.dcik

Ambo

Answer 10

It is easier to achieve it in SQL

var columnsList = dbContext.Database.SqlQuery<string>("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'SCHEMA_OF_YOUE_TABLE' AND TABLE_NAME = 'YOUR_TABLE_NAME'").ToList();

Eberly

Answer 11

Odd that for such an old question no one proposed using the DbDataReaderExtensions.GetColumnSchema(DbDataReader) Method:

var colNames = reader.GetColumnSchema().Select((dbCol) => dbCol.ColumnName);

or its async equivalent:

var colNames = (await reader.GetColumnSchemaAsync()).Select((col) => col.ColumnName);

Covey

Answer 12

This is a tweak of the more popular answers, with arguably a couple minor improvements:

var columnNames = new HashSet<string>(
    Enumerable.Range(0, reader.FieldCount).Select(reader.GetName),
    StringComparer.OrdinalIgnoreCase);

To check for TaskId (or taskid, taskID, TaskID, etc. -- works for all of them):

if (columnNames.Contains("TaskId")) {
    // yes, the reader has this column name...
}

Contrast to other answers:

• Column check is not case sensitive.
• Column check syntax is no less readable.
• Using a HashSet is faster, at least on paper: O(log n) as opposed to O(n). This probably won't matter if there are just a handful of columns.

Arlina