CODEWITHSUNDEEP
c#ado.netsqldatareader
Blankman
Blankman

Reputation: 267020

Can you get the column names from a SqlDataReader?

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

Upvotes: 327

Views: 307018

Answers (12)

Ed Gibbs
Ed Gibbs

Reputation: 26343

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.

Upvotes: 0

rory.ap
rory.ap

Reputation: 35270

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

Upvotes: 1

Kinin Roza
Kinin Roza

Reputation: 2338

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

Upvotes: 5

Almett
Almett

Reputation: 906

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

Upvotes: 2

Steven Lyons
Steven Lyons

Reputation: 8218

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
  }

Upvotes: 63

Rob Stevenson-Leggett
Rob Stevenson-Leggett

Reputation: 35679

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

Upvotes: 545

Rob Sedgwick
Rob Sedgwick

Reputation: 4514

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

Upvotes: 5

nawfal
nawfal

Reputation: 73183

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.

Upvotes: 23

keith
keith

Reputation: 3110

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

Upvotes: 3

Yakir Manor
Yakir Manor

Reputation: 4829

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

Upvotes: 7

Stephen Wrighton
Stephen Wrighton

Reputation: 37819

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.

Upvotes: 88

anon
anon

Reputation:

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

Upvotes: 2

Related Questions