C# 如何获取数据库表的字段名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/857942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get the field names of a database table?
提问by Gold
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# 代码来执行此操作？
采纳答案by KM.
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
回答by Nuno Furtado
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 tablesin 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 表，从那里您可以尝试确定该信息的位置，但我不确定如何执行此部分。尝试使用一个例子，但现在得到了
回答by Eoin Campbell
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'
回答by BIBD
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.
然后遍历结果集中的列表字段（和返回值）以获取字段名称。
回答by John M Gant
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# 测试它。
回答by VVS
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:
兴趣点：
- 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 :-).
- Use IDataReader.GetSchemaTable() to get a DataTable with detailed info about the actual table.
- 不要通过给出始终计算为 false 的 where 子句来返回任何数据。当然，这仅适用于您对数据不感兴趣的情况:-)。
- 使用 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>
回答by Vlad
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(); }
}
}
回答by XIVSolutions
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 . . .
虽然我在这个线程中看到其他人提到了 get Schema，但似乎某些实现过于复杂。. .
Hope that helps!
希望有帮助！
回答by Atal Kishore
This Code will print all column name of a table as a class with getterproperty 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"; } }
}
