如何获取数据库表的字段名称？

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

如何获取MS Access数据库表的字段名称？

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

我可以使用SQL查询，还是有C＃代码来执行此操作？

9 个解决方案

#1

this will work on sql server 2005 and up:

这将适用于sql server 2005及以上版本：

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

#2

Use IDataReader.GetSchemaTable()

使用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):

这是一个访问表模式并以XML格式打印它的实际示例（只是为了查看您获得的信息）：

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. 不要通过给出总是求值为false的where子句来返回任何数据。当然，只有在您对数据不感兴趣时​​才适用:-)。
3. Use IDataReader.GetSchemaTable() to get a DataTable with detailed info about the actual table.
4. 使用IDataReader.GetSchemaTable（）获取DataTable，其中包含有关实际表的详细信息。

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

在SQL 2005中，您可以从INFORMATION_SCHEMA.Columns视图中进行选择

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

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

在SQL 2000中，您可以将SysObjects连接到SysColumns以获取信息

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.

使用DAO自动化类。您可能已在Visual Studio安装中为其创建了一个互操作库。如果没有，那么创建一个就很容易了;只需添加对DAO COM库的引用。

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.

这就像查询系统表一样简单（我发现在Access中有问题），您可以通过这种方式获得大量其他信息。

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.

编辑：我已经修改了我昨天发布的代码，我刚刚从VB.NET翻译过，而且缺少了几个部分。我已经重写了它并在VS2008中用C＃进行了测试。

#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

此代码将打印表的所有列名称作为具有所有列名称的getter属性的类，然后可以在c＃代码中使用

    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

对于访问我无法找到答案，我知道你可以看到访问中的sys表，从那里你可以尝试确定信息的位置，但我不确定如何做这部分。尝试使用一个例子，但现在得到了

#8

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

对于c＃中的microsoft SQL，您可以执行以下操作：

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:

我对OleDb.Connection的GetSchema属性运气不错：

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):

提供列数据的类。这将返回数据库中的所有列。然后可以通过列名过滤生成的DataTable，这些列名称（大部分）对应于标准INFORMATION_SCHEMA中找到的列名（MS Access不为我们提供）：

    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:

然后，在一个相当粗略但不那么优雅的示例中使用该类，并对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.

请注意，我并不假装这些代码都很好。这只是一个例子。但是我在很多场合都使用过类似的东西，事实上甚至创建了一个应用程序来使用类似的方法编写整个MS Access数据库（约束和所有）的脚本。

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

虽然我已经看到其他人在这个帖子中提到了获取Schema，但似乎有些实现过于复杂了。 。 。

Hope that helps!

希望有所帮助！