Using GetSchemaTable() to retrieve only column names

Question

Is it possible to use GetSchemaTable() to retrieve only column names?

I have been trying to retrieve Column names (only) using this method, is it possible.

  DataTable table = myReader.GetSchemaTable();

  foreach (DataRow myField in table.Rows)
  {
      foreach (DataColumn myProperty in table.Columns)
      {
          fileconnectiongrid.Rows.Add(myProperty.ColumnName + " = " 
                            + myField[myProperty].ToString());
      }
  }

This code retrieves a lot of table data unwanted, I only need a list containing column names!:

Answer 1

You need to use ExecuteReader(CommandBehavior.SchemaOnly)):

DataTable schema = null;
using (var con = new SqlConnection(connection))
{
    using (var schemaCommand = new SqlCommand("SELECT * FROM table", con))
    {
        con.Open();
        using (var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly))
        {
            schema = reader.GetSchemaTable();
        }
    }
}

SchemaOnly:

The query returns column information only. When using SchemaOnly, the .NET Framework Data Provider for SQL Server precedes the statement being executed with SET FMTONLY ON.

The column name is in the first column of every row. I don't think that it's possible to omit the other column informations like ColumnOrdinal,ColumnSize,NumericPrecision and so on since you cannot use reader.GetString but only reader.GetSchemaTable in this case.

But your loop is incorrect if you only want the column names:

foreach (DataRow col in schema.Rows)
{
    Console.WriteLine("ColumnName={0}", col.Field<String>("ColumnName"));
}