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.

Vulgarity

You need to provide more information. How are you accessing the database? – Gliwice

@Daniel This is my connection string .. string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Environment.CurrentDirectory + "\\test.mdb"; – Vulgarity

Are you using a SqlConnection to access the database? – Gliwice

@Daniel I am using OleDb to access the database.. Sorry i am new to .net. – Vulgarity

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

Imperturbation

sir its returning column names with lowercase like i have columnname = OBJECTID but it is returning objectid – Flavorous

for me it is returning correct case of the column names. – Blynn

Answer 2

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

Jeanniejeannine

Answer 3

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

Headwind

Answer 4

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.

Jacindajacinta

Answer 5

Here's code to get the column names in the order they appear in the Access table. The examples in the other answers here return the column names in alphabetical order (at least for me... using the Microsoft Access Database Engine 2016 Redistributable and .NET Core 3.1).

Based on qnaninf's code example:

var schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName });
var columnOrdinalForName = schemaTable.Columns["COLUMN_NAME"].Ordinal;
var columnOrdinalForOrdinal = schemaTable.Columns["ORDINAL_POSITION"].Ordinal;
var rows = schemaTable.Rows;
var columns = from DataRow r in schemaTable.Rows
                orderby r.ItemArray[columnOrdinalForOrdinal]
                select new
                {
                    Ordinal = r.ItemArray[columnOrdinalForOrdinal].ToString(),
                    ColumnName = r.ItemArray[columnOrdinalForName].ToString()
                };

Purim

Answer 6

You can get the column names in Vb.net and Oledb from MS access database as follows.

  'In Vb.net with OleDb


    Dim adapter As new OleDb.OleDbDataAdapter
    Dim ds As New DataSet
    cmd.CommandText = "select * from table_name where 1=2"

    adapter.SelectCommand = cmd
    adapter.Fill(ds)
    adapter.Dispose()
    cmd.Dispose()


    For Each dr In ds.Tables(0).Columns
        ComboBox1.Items.Add(dr.ToString) 'The Column name will come in this combobox
    Next

Dionysiac