Question

我需要获取列名、主键、外键和其他模式信息。DataTable类似乎包含了所有这些。

以下是我目前得到的代码。有了它，我可以检索除外键之外的所有信息。我希望它们在DataTable.Constraints中定义，但它们不是。这是我当前的代码：

    private static DataTable LoadSchemaInfo(string tableName, SqlConnection connection)
    {
        string cmdText = "SELECT * FROM [" + tableName + "] WHERE 1 = 0";

        // Create a SqlDataAdapter to get the results as DataTable
        var sqlDataAdapter = new SqlDataAdapter(cmdText, connection);

        // Create a new DataTable
        var dataTable = new DataTable(tableName);

        // Fill the DataTable with the result of the SQL statement
        sqlDataAdapter.FillSchema(dataTable, SchemaType.Source);

        return dataTable;
    }

知道如何检索所有信息或如何获取FK（最好不使用纯SQL语法，因为这样我就缺乏一些编译时检查）吗？

Answer 1

使用SMO可以做到这一点。。。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;


// Add references: (in c:Program FilesMicrosoft SQL Server90SDKAssemblies)
// Microsoft SqlServer.ConnectionInfo
// Microsoft SqlServer.Management.Sdk.Sfc
// Microsoft SqlServer.Smo

namespace SMO
{
    class Program
    {
        static Database db;

        static void Main(string[] args)
        {
            Microsoft.SqlServer.Management.Smo.Server server;

            SqlConnection sqlConnection = new SqlConnection(@"Integrated Security=SSPI; Data Source=LOCAL");
            //build a "serverConnection" with the information of the "sqlConnection"
            Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
              new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);

            //The "serverConnection is used in the ctor of the Server.
            server = new Server(serverConnection);

            db = server.Databases["TestDB"];

            Table tbl;
            tbl = db.Tables["Sales"];
            foreach (ForeignKey fk in tbl.ForeignKeys)
            {
                Console.WriteLine("Foreign key {0} references table {1} and key {2}", fk.Name, fk.ReferencedTable, fk.ReferencedKey);
            } 
        }
    }
}

Answer 2

您可以始终使用简单的ADO.NET查询来检查数据库中的sys目录视图，这些视图如下：

sys.columns with information about your columns
sys.foreign_keys which stores information about foreign keys
sys.tables for tables

等等。只需从sys.foreign_keys中执行SELECT（字段列表），看看你会得到什么！



请参阅：联机丛书查询SQL Server系统目录以获取更多详细信息。

Answer 3

如果您正在寻找数据库架构的约束，那么ADO.net DataTable并不是您的最佳选择。您可以使用SMO是一个更合适的选项。

Answer 4

您可以使用SqlConnection来获取信息。

string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    DataTable dtAllForeignKeys = conn.GetSchema("ForeignKeys");

    string[] restrictionValues = { "Northwind", "dbo", "Orders" };
    DataTable dtForeignKeysForJustTheOrderTable = conn.GetSchema("ForeignKeys", restrictionValues);
    conn.Close();
}

Answer 5

DataTable对象与数据库表不同。它们只是碰巧有相同的结构，因为你创造它们的方式。例如，您可以有一个DataTable，它包含多个数据库表之间联接的结果。一个DataTable可以有另一个DataTable的外键，但不能有数据库表的外键。

To get the information about foreign keys in the database, you need to read metadata for the tables (when you call SELECT, you get only metadata for the query, that s why you get no information on keys).
You can either query this information directly from Information Schema Views or have SMO classes do that for you. In the latter case you start with Server object, then get Database, Tables and so on.

友情链接