C# ADO.Net：从 SQL 服务器表中获取表定义
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/457485/
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
ADO.Net : Get table definition from SQL server tables
提问by javito
I am using C# to write a method that returns the following information about a table: column names, column types, column sizes, foreign keys.
我正在使用 C# 编写一个方法，该方法返回有关表的以下信息：列名、列类型、列大小、外键。
Can someone point me in the right direction on how to accomplish this ?
有人可以指出我如何实现这一目标的正确方向吗？
采纳答案by cgreeno
To get the FK and Schema you should be able to use:
要获得 FK 和架构，您应该能够使用：
DA.FillSchema()
DS.Table("Name").PrimaryKey
OR calling sp_fkeyusing the method demonstrated below
或使用下面演示的方法调用sp_fkey
Code Snippet fromAND Another Link
private void LoanSchema()
{
private List<String> tablesList = new List<String>();
private Dictionary<String, String> columnsDictionary = new Dictionary<String, String>();
string connectionString = "Integrated Security=SSPI;" +
"Persist Security Info = False;Initial Catalog=Northwind;" +
"Data Source = localhost";
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "exec sp_tables";
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
tablesList.Add(reader["TABLE_NAME"].ToString());
}
reader.Close();
command.CommandText = "exec sp_columns @table_name = '" +
tablesList[0] + "'";
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
columnsDictionary.Add(reader["COLUMN_NAME"].ToString(), reader["TYPE_NAME"].ToString());
}
}
回答by Rune Grimstad
You can use the SqlDataAdapter.FillSchema() method.
您可以使用 SqlDataAdapter.FillSchema() 方法。
Alternatively you can use the SqlDataAdapter.Fill() method after setting the MissingSchemaAction property of the SqlDataAdapter to AddWithKey. But if you only want the schema you must ensure that your query returns no rows. This can be accomplished by adding a statement like WHERE 1=2 to your query.
或者，您可以在将 SqlDataAdapter 的 MissingSchemaAction 属性设置为 AddWithKey 后使用 SqlDataAdapter.Fill() 方法。但是，如果您只需要架构，则必须确保您的查询不返回任何行。这可以通过在查询中添加类似 WHERE 1=2 的语句来实现。
回答by gkrogers
I think you need the System.Data.DataTable class:
http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx
我认为你需要 System.Data.DataTable 类：http:
//msdn.microsoft.com/en-us/library/system.data.datatable.aspx
回答by Chris Simpson
This really depends on how you communicate with your database. If you are using LinqToSQL or another similar ORM this would be pretty easy but if you want to get these values via a query I'd suggest you use the INFORMATION_SCHEMA views as these are fast and easy to query.
这实际上取决于您与数据库的通信方式。如果您使用的是 LinqToSQL 或其他类似的 ORM，这将非常简单，但如果您想通过查询获取这些值，我建议您使用 INFORMATION_SCHEMA 视图，因为这些视图既快速又易于查询。
e.g.
例如
select * from information_schema.columns where table_name = 'mytable'
回答by luckyluke
If you are using MS SQL Server then You should definately have a look at SMO namespace (server management objects).
如果您使用的是 MS SQL Server，那么您肯定应该看看 SMO 命名空间（服务器管理对象）。
There are objects which You can use in .net responsible for all kinds of things in a database (including but not limited to tables, columns, constraints etc.)
您可以在 .net 中使用负责数据库中各种事物的对象（包括但不限于表、列、约束等）
