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

User · Answer

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

User · Answer

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  lt   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

User · Answer

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

User · Answer

You can get the column names from a DataReader   Here is the important part     for  int col   0  col  lt  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

User · Answer

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   gt  reader GetName x   ToArray

User · Answer

var reader   cmd ExecuteReader     var columns   new List lt string gt      for int i 0 i lt reader FieldCount i         columns Add reader GetName i        or   var columns   Enumerable Range 0  reader FieldCount  Select reader GetName  ToList

User · Answer

It is easier to achieve it in SQL  var columnsList   dbContext Database SqlQuery lt string gt   SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA    SCHEMA OF YOUE TABLE  AND TABLE NAME    YOUR TABLE NAME    ToList

User · Answer

If you want the column names only  you can do   List lt string gt  columns   new List lt string gt     using  SqlDataReader reader   cmd ExecuteReader CommandBehavior SchemaOnly         DataTable dt   reader GetSchemaTable        foreach  DataRow row in dt Rows                columns Add row Field lt String gt   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 lt string  string gt  ExecuteCaseInsensitiveDictionary string query  string connectionString  Dictionary lt string  string gt  queryParams   null        Dictionary lt string  string gt  CaseInsensitiveDictionary   new Dictionary lt string  string gt  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

User · Answer

Use an extension method       public static List lt string gt  ColumnList this IDataReader dataReader                var columns   new List lt string gt             for  int i   0  i  lt  dataReader FieldCount  i                          columns Add dataReader GetName i                      return columns

User · Answer

Already mentioned  Just a LINQ answer   var columns   reader GetSchemaTable   Rows                                       Cast lt DataRow gt                                          Select r   gt   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

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

Examples related to c#

Examples related to ado.net

Examples related to sqldatareader