主页 / coding / 问题 / 79041632
Accepted
Raheel Khan
Asked: 2024-10-01 12:23:26 +0800 CST

使用 SqlConnection 架构查找 RDBMS 表关系

  • 772

我正在用 .NET 8 编写一个应用程序，并使用它Northwind database来生成存储过程、实体 POCO 类、存储过程包装器、控制器等。

数据库关系图： 在此处输入图片描述

为了确定表之间的关系，我查询了以下模式：

using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
var schemaTables = await connection.GetSchemaAsync("Tables");
var schemaColumns = await connection.GetSchemaAsync("Columns");
var schemaForeignKeys = await connection.GetSchemaAsync("ForeignKeys");
var schemaIndexes = await connection.GetSchemaAsync("Indexes");
var schemaXXX = await connection.GetSchemaAsync("XXX");
/* Process [schemaXXX]... */

虽然我能够创建表、列等，但ForeignKeys架构似乎没有关系的两端。考虑以下结果：

架构：ForeignKeys 外键

架构：索引 索引

我怎样才能得到如下结果：

TYPE        PRINCIPLE       DEPENDENT
OneToMany   Department.Id   Employee.DepartmentId
OneToMany   Employee.Id     Employee.ReportsTo

请注意，我对数据库优先方法、EntityFramework 或其他 ORM 不感兴趣。我需要自己使用 生成实体 POCO 类SqlConnection.GetSchema。

我也在考虑SQL Server Management Objects最终使用，但目前还是使用 ADO.NET。

c#

1 个回答

  1. Best Answer

    从SQL Server Schema Collections 文档中可以看出，没有您要查找的信息。

    相反，您可以使用原始查询来获取该信息。有一篇SO 文章指导如何做到这一点，基于SQL Essentials：外键元数据：改进 INFORMATION_SCHEMA 视图

    SELECT 
     KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
   , KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
   , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
   , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
   , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
   , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
   , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
   , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG 
   AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
   AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG = 
RC.UNIQUE_CONSTRAINT_CATALOG 
   AND KCU2.CONSTRAINT_SCHEMA = 
RC.UNIQUE_CONSTRAINT_SCHEMA
   AND KCU2.CONSTRAINT_NAME = 
RC.UNIQUE_CONSTRAINT_NAME
   AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION

    C# 代码片段：

    using var connection = new SqlConnection(connectionString);
connection.Open();

var query = @"SELECT 
     KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
   , KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
   , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
   , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
   , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
   , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
   , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
   , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG 
   AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
   AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG = 
RC.UNIQUE_CONSTRAINT_CATALOG 
   AND KCU2.CONSTRAINT_SCHEMA = 
RC.UNIQUE_CONSTRAINT_SCHEMA
   AND KCU2.CONSTRAINT_NAME = 
RC.UNIQUE_CONSTRAINT_NAME
   AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION";

using var command = connection.CreateCommand();

command.CommandText = query;
var reader = command.ExecuteReader();

while(reader.Read())
{
    
}
    • 2

相关问题