fetch column names for specific table

Question

I want to fetch all the column names for specific table..

I am using msaccess and C# .net 2008.

Answer 1

You can fetch schema information for a given query through OleDb using the SchemaOnly CommandBehavior and the GetSchemaTable method, as follows:

var conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.mdb";
using (var con = new OleDbConnection(conStr))
{
    con.Open();
    using (var cmd = new OleDbCommand("select * from Suppliers", con))
    using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
    {
        var table = reader.GetSchemaTable();
        var nameCol = table.Columns["ColumnName"];
        foreach (DataRow row in table.Rows)
        {
            Console.WriteLine(row[nameCol]);
        }
    }
}

Answer 2

I found this article while trying to build a C# application to migrate an Access database. The database I'm migrating is an Access 2007/2010 file with .accdb extension.

If you use this code on a table that has Memo or Attachment columns (available in accdb files), it will return the type of these columns as string (wchar).

I had trouble finding much information about how to deal with these types of columns, so I wanted to provide a link to the article that helped me figure out how to handle them:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/d15606f9-f38d-4a1b-8ce3-000c558e79c5

I took the bottom example in that thread and converted it to C#. I did have to add this using statement to the module to avoid having to edit all of the references to "AccessDao":

using AccessDao = Microsoft.Office.Interop.Access.Dao;

My apologies for tacking onto an old thread, but I used this thread as a starting point for writing my code and didn't realize this gotcha right away.

Answer 3

A variant of bubi's method for a specific table:

public List<string> GetTableColumnNames(string tableName)
{
  var conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.mdb";
  using (var connection = new OleDbConnection(conStr))
  {
    connection.Open();
    var schemaTable = connection.GetOleDbSchemaTable(
      OleDbSchemaGuid.Columns,
      new Object[] { null, null, tableName });
    if (schemaTable == null)
      return null;

    var columnOrdinalForName = schemaTable.Columns["COLUMN_NAME"].Ordinal;

    return (from DataRow r in schemaTable.Rows select r.ItemArray[columnOrdinalForName].ToString()).ToList();
  }
}

Of course first you might want to check if the table actually exists before getting its column names:

public bool TableExists(string tableName)
{
  var conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.mdb";      
  using (var connection = new OleDbConnection(conStr))
  {
    connection.Open();
    var tables = connection.GetSchema("Tables");
    var tableExists = false;
    for (var i = 0; i < tables.Rows.Count; i++)
    {
      tableExists = String.Equals(tables.Rows[i][2].ToString(),
                           tableName,
                           StringComparison.CurrentCultureIgnoreCase);
      if (tableExists)
        break;
    }
    return tableExists;
  }
}

Answer 4

This retrieves all the columns of all tables and views

        DataTable schemaTable = ((OleDbConnection)jetConnection).GetOleDbSchemaTable(
          System.Data.OleDb.OleDbSchemaGuid.Columns,
          new object[] { null, null, null, null });