Is it possible to extract the names of the columns from a SqlDataReader?

Problem

Can I retrieve the names of all the columns returned in my SqlDataReader after connecting to the database?

Asked by Blankman

Solution #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();

Answered by Rob Stevenson-Leggett

Solution #2

The SqlDataReader has a GetName function that takes the column index and returns the name of the column.

GetOrdinal, on the other hand, accepts a column name and returns the column index.

Answered by Stephen Wrighton

Solution #3

A DataReaper can provide you with the column names.

Here’s the crucial 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
  }

Answered by Steven Lyons

Solution #4

It has already been mentioned. Just a LINQ response:

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 significantly faster and cleaner. Even if GetSchemaTable is cached in the first approach, querying will be exceedingly sluggish.

Answered by nawfal

Solution #5

If you only want the column names, you can do it by typing:

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"));
    }
}

If you simply require one row, though, I recommend my AdoHelper addon. If you only have an one line query and don’t want to deal with data tables in your code, this feature is ideal. It returns a dictionary of column names and values that is case insensitive.

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;
}

Answered by Yakir Manor

Post is based on https://stackoverflow.com/questions/681653/can-you-get-the-column-names-from-a-sqldatareader