CODEWITHSUNDEEP
c#databasems-accessschemaoledb
krunal shah
krunal shah

Reputation: 16339

fetch column names for specific table

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

I am using msaccess and C# .net 2008.

Upvotes: 11

Views: 21797

Answers (6)

R George
R George

Reputation: 1

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

Upvotes: 0

jspinella
jspinella

Reputation: 2423

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

Upvotes: 0

TechSavvySam
TechSavvySam

Reputation: 1442

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.

Upvotes: 0

qnaninf
qnaninf

Reputation: 67

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

Upvotes: 4

bubi
bubi

Reputation: 6501

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

Upvotes: 2

Nathan Baulch
Nathan Baulch

Reputation: 20703

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

Upvotes: 32

Related Questions