c# - Can you get the column names from a SqlDataReader?

ID : 10333

viewed : 11

Tags : c#ado.netsqldatareaderc#

Top 5 Answer for c# - Can you get the column names from a SqlDataReader?

vote vote

100

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

80

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.

vote vote

79

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

68

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.

vote vote

51

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

Top 3 video Explaining c# - Can you get the column names from a SqlDataReader?

Related QUESTION?