How can I get the field names of a database table?

问题:

How can I get the field names of an MS Access database table?

Is there an SQL query I can use, or is there C# code to do this?

回答1:

this will work on sql server 2005 and up:

select * from INFORMATION_SCHEMA.COLUMNS 
where TABLE_Name='YourTableName'
order by ORDINAL_POSITION

回答2:

Use IDataReader.GetSchemaTable()

Here's an actual example that accesses the table schema and prints it plain and in XML (just to see what information you get):

class AccessTableSchemaTest
{
    public static DbConnection GetConnection()
    {
        return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\Test.mdb");
    }

    static void Main(string[] args)
    {
        using (DbConnection conn = GetConnection())
        {
            conn.Open();

            DbCommand command = conn.CreateCommand();
            // (1) we're not interested in any data
            command.CommandText = "select * from Test where 1 = 0";
            command.CommandType = CommandType.Text;

            DbDataReader reader = command.ExecuteReader();
            // (2) get the schema of the result set
            DataTable schemaTable = reader.GetSchemaTable();

            conn.Close();
        }

        PrintSchemaPlain(schemaTable);

        Console.WriteLine(new string('-', 80));

        PrintSchemaAsXml(schemaTable);

        Console.Read();
    }

    private static void PrintSchemaPlain(DataTable schemaTable)
    {
        foreach (DataRow row in schemaTable.Rows)
        {
            Console.WriteLine("{0}, {1}, {2}",
                row.Field<string>("ColumnName"),
                row.Field<Type>("DataType"),
                row.Field<int>("ColumnSize"));
        }
    }

    private static void PrintSchemaAsXml(DataTable schemaTable)
    {
        StringWriter stringWriter = new StringWriter();
        schemaTable.WriteXml(stringWriter);
        Console.WriteLine(stringWriter.ToString());
    }
}

Points of interest:

1. Don't return any data by giving a where clause that always evaluates to false. Of course this only applies if you're not interested in the data :-).
2. Use IDataReader.GetSchemaTable() to get a DataTable with detailed info about the actual table.

For my test table the output was:

ID, System.Int32, 4
Field1, System.String, 50
Field2, System.Int32, 4
Field3, System.DateTime, 8
--------------------------------------------------------------------------------
<DocumentElement>
  <SchemaTable>
    <ColumnName>ID</ColumnName>
    <ColumnOrdinal>0</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <DataType>System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <ProviderType>3</ProviderType>
    <IsLong>false</IsLong>
    <AllowDBNull>true</AllowDBNull>
    <IsReadOnly>false</IsReadOnly>
    <IsRowVersion>false</IsRowVersion>
    <IsUnique>false</IsUnique>
    <IsKey>false</IsKey>
    <IsAutoIncrement>false</IsAutoIncrement>
  </SchemaTable>
  [...]
</DocumentElement>

回答3:

Run this query:

select top 1 *
From foo

and then walk the list fields (and returned values) in the result set to get the field names.

回答4:

Are you asking how you can get the column names of a table in a Database?

If so it completely depends on the Database Server you are using.

In SQL 2005 you can select from the INFORMATION_SCHEMA.Columns View

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

IN SQL 2000 you can join SysObjects to SysColumns to get the info

SELECT     
    dbo.sysobjects.name As TableName
    , dbo.syscolumns.name AS FieldName
FROM
    dbo.sysobjects 
    INNER JOIN dbo.syscolumns 
         ON dbo.sysobjects.id = dbo.syscolumns.id
WHERE
    dbo.sysobjects.name = 'MyTable'

回答5:

Use the DAO automation classes. You may already have an interop library for it in your Visual Studio installation. If not, it's easy enough to create one; just add a reference to the DAO COM library.

using dao;
...
DBEngineClass dbengine = new DBEngineClass();
dbengine.OpenDatabase(path, null, null, null);
Database database = dbengine.Workspaces[0].Databases[0];
List<string> fieldnames = new List<string>();
TableDef tdf = database.TableDefs[tableName];
for (int i = 0; i < tdf.Fields.Count; i++)
{
    fieldnames.Add(tdf.Fields[i].Name);
}
database.Close();
dbengine.Workspaces[0].Close();

This is just as easy as querying a system table (which I've found to be problematic in Access), and you can get a lot of additional information this way.

EDIT: I've modified the code from what I posted yesterday, which I had just translated from VB.NET, and which was missing a couple of pieces. I've rewritten it and tested it in C# in VS2008.

回答6:

This Code will print all column name of a table as a class with getter property of all column names which can be then used in c# code

    declare @TableName sysname = '<EnterTableName>'
    declare @Result varchar(max) = 'public class ' + @TableName + '
    {'

    select @Result = @Result + '
        public static string ' + ColumnName + ' { get { return "'+ColumnName+'"; } }
    '
    from
    (
        select
            replace(col.name, ' ', '_') ColumnName,
            column_id ColumnId
        from sys.columns col
            join sys.types typ on
                col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
        where object_id = object_id(@TableName)
    ) t
    order by ColumnId

    set @Result = @Result  + '
    }'

    print @Result

Output:

 public class tblPracticeTestSections
 {
   public static string column1 { get { return "column1"; } }

   public static string column2{ get { return "column2"; } }

   public static string column3{ get { return "column3"; } }

   public static string column4{ get { return "column4"; } }

 }

回答7:

Depending on the DB engine your using you can easily query the DB system tables for that information

For access i can't find the answer i know you can see the sys tables in access and from there you could try and determine where that information is but im not really sure how to do this part. tried using an example but got nowwhere

回答8:

for microsoft SQL in c# you can do the following:

Dictionary<string, int> map = 
(from DataRow row in Schema.Rows
 let columnName = (string)row["ColumnName"]
  select columnName)
 .Distinct(StringComparer.InvariantCulture)
 .Select((columnName, index) => new { Key = columnName, Value = index })
 .ToDictionary(pair => pair.Key, pair => pair.Value);

thus creates a map of column name into its index which can be used as follows:

internal sealed class ColumnToIndexMap
{
    private const string NameOfColumn = "ColumnName";
    private DataTable Schema { get; set; }
    private Dictionary<string, int> Map { get; set; }

    public ColumnToIndexMap(DataTable schema)
    {
        if (schema == null) throw new ArgumentNullException("schema");
        Schema = schema;

        Map = (from DataRow row in Schema.Rows
               let columnName = (string)row[NameOfColumn]
               select columnName)
              .Distinct(StringComparer.InvariantCulture)
              .Select((columnName, index) => new { Key = columnName, Value = index })
              .ToDictionary(pair => pair.Key, pair => pair.Value);
    }

    int this[string name]
    {
        get { return Map[name]; }
    }

    string this[int index]
    {
        get { return Schema.Rows[index][NameOfColumn].ToString(); }
    }
}

回答9:

I have had good luck with the GetSchema property of the OleDb.Connection:

A class to provide column data. This returns ALL columns in the database. The resulting DataTable can then be filtered by column names which correspond (mostly) to those found in a standard INFORMATION_SCHEMA (which MS Access does NOT provide for us):

    class JetMetaData
    {
        /// <summary>
        /// Returns a datatable containing MetaData for all user-columns
        /// in the current JET Database. 
        /// </summary>
        /// <returns></returns>
        public static DataTable AllColumns(String ConnectionString)
        {
            DataTable dt;

            using (OleDbConnection cn = new OleDbConnection(ConnectionString))
            {
                cn.Open();
                dt = cn.GetSchema("Columns");
                cn.Close();
            }
            return dt;
        }

    }

Then, Consuming the class in a rather crude and not-so-elegant example, and filtering on TABLE_NAME:

    private void Form1_Load(object sender, EventArgs e)
    {
        DataTable dt = JetMetaData.AllColumns("", Properties.Settings.Default.JetConnection);
        String RowFilter = "TABLE_NAME = 'YourTableName'";
        DataView drv = dt.DefaultView;
        drv.RowFilter = RowFilter;

        DataGridView dgv = this.dataGridView1;

        dgv.DataSource = drv;

    }

Note that I do not pretend that this is all well-though out code. It is only an example. But I have used something like this on a number of occasions, and in fact even created an application to script out an entire MS Access database (contraints and all) using similar methods.

While I have seen others in this thread mention the get Schema, it seem slike some of the implementation was overly complicated . . .

Hope that helps!