330

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

Improve this question
0

12 Answers 12

Reset to default
548 
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();
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

it's insane that there is no enumerable interface that lets you iterate through the columns.
A bit shorter: columns = Enumerable.Range(0, reader.FieldCount) .Select(reader.GetName).ToList();
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;
its Dim columns() As String = Enumerable.Range(0, cTab.FieldCount).Select(Function(n) cTab.GetName(n)).ToArray
@ROBERTRICHARDSON Unless specified otherwise, SQL Server returns column names as-is.
|
89

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.

Improve this answer

2 Comments

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.
GetOrdinal was perfect. I was looking for GetName, but much cleaner solution for my issue with GetOrdinal.
65

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
  }
Improve this answer

Comments

24

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.

Improve this answer

7 Comments

Is there a way to do this with Values?
@TravisHeeter I dont get you. Find column names from values of what?
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.
@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
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)
|
7

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;
}
Improve this answer

3 Comments

throw ex; is a worst practice.
its just an example
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.
5

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;
    }
Improve this answer

Comments

5

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();
}
Improve this answer

Comments

3

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

Improve this answer

1 Comment

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

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

Improve this answer

Comments

2

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();
Improve this answer

Comments

1

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);
Improve this answer

Comments

0

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.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.