代码之家 › 专栏 › 技术社区 › Navaneeth K N

从查询中获取表架构

metadata ado.net sql .net

Navaneeth K N · 技术社区 · 15 年前

按照 MSDN , SqlDataReader.GetSchemaTable 返回执行的查询的列元数据。我想知道是否有类似的方法可以为给定的查询提供表元数据?我的意思是涉及哪些表以及它有什么别名。

在我的应用程序中,我得到了查询,我需要附加 where 子句程序化。使用 GetSchemaTable() ,我可以获取列元数据及其所属的表。但即使表有别名,它仍然返回真正的表名。有没有方法获得该表的别名?

下面的代码显示如何获取列元数据。

const string connectionString = "your_connection_string";
string sql = "select c.id as s,c.firstname from contact as c";

using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand command = new SqlCommand(sql, connection))
{
    connection.Open();
    SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
    DataTable schema = reader.GetSchemaTable();
    foreach (DataRow row in schema.Rows)
    {
        foreach (DataColumn column in schema.Columns)
        {
            Console.WriteLine(column.ColumnName + " = " + row[column]);
        }
        Console.WriteLine("----------------------------------------");
    }
    Console.Read();
}

这将正确地提供列的详细信息。但当我看到 BaseTableName 立柱 Id 它给予 contact 而不是别名 c . 有没有办法从上面这样的查询中获取表模式和别名?

任何帮助都会很好!

编辑

虽然我可以使用Rob建议的执行计划,但我还是希望使用任何其他简单的方法。

回答tomekszpakowicz提出的问题

你(或你的应用程序)的来源有疑问的问题吗?在那种情况下你应该知道别名。

我不是问题的作者。我们有一个用户可以输入查询的系统。我们使用上面介绍的方法从中构建列。这些详细信息将被持久化,另一个用户可以使用这些信息,例如添加新的条件等,因此我们需要根据我们拥有的信息动态地构建SQL。所以当一个列有别名,而我们没有得到别名名时,那么构造的WHERE子句将是无效的。

谢谢

5 回复 | 直到 11 年前

Tomek Szpakowicz 14 年前

简短回答

这行不通。根据设计,不能从结果架构中获取表别名。并且您不能依赖于能够从查询执行计划中获取它们。

长回答

当您得到SQL查询的结果时,查询已经被分析、验证、优化、编译成一些内部表示并执行。别名是查询“源代码”的一部分,通常会在步骤1和2左右丢失。

执行查询后,只能将a)实际物理表和b)返回的数据视为单个匿名表。两者之间的一切都可以转化或完全优化。

如果需要DBMS来保留别名,那么实际上就不可能优化复杂的查询。

可能的解决方案

我建议重述一个问题:

您(或您的应用程序)是否是有问题的查询源?在这种情况下,您应该知道别名。
如果你得到别人提供的查询…好。。。这取决于你为什么要添加原因。
- 在最坏的情况下,您必须自己分析查询。
- 在最好的情况下,您可以让他们访问视图,而不是真正的表,并将WHERE子句放在视图中。

简单而难看的解决方案

如果我正确理解您的要求:

用户A将查询输入您的程序。
用户B可以运行它(但不能编辑它)并看到返回的数据。此外,她可以使用您提供的某种小部件,根据返回的列添加过滤器。
您不想在应用程序内部应用过滤器,而是将它们添加到查询中,以避免从数据库中获取不必要的数据。

在这种情况下:

当编辑查询尝试运行它并收集返回列的元数据时。如果 ColumnName S不是独一无二的,向作者抱怨。使用查询存储元数据。
当b添加过滤器(基于查询元数据)时,存储两个列的名称和条件。
执行时:
- 检查筛选列是否仍然有效(查询可能已更改)。如果不移除无效的过滤器和/或通知B。
- 以如下方式执行查询:
```
 select *
 from ({query entered by A}) x
 where x.Column1 op1 Value1
     and x.Column2 op2 Value2
```

如果要优雅地处理数据库模式更改,则需要添加一些附加检查,以确保元数据与查询实际返回的内容一致。

安全票据

您的程序将把用户编写的查询直接传递到数据库。使用具有不超过A的数据库权限的数据库连接来完成此操作是至关重要的。否则,您将要求使用基于SQL注入的漏洞。

推论

如果出于安全原因,用户A不能直接访问数据库,则不能使用上述解决方案。

在这种情况下,确保安全的唯一方法是确保应用程序100%理解查询,这意味着在程序中解析查询,并且只允许您认为安全的操作。

Rob Farley 15 年前

您可以获取查询的执行计划,然后分析返回的XML。这类似于在ManagementStudio中使用“显示估计计划”选项。

Community CDub 8 年前

您几乎需要一个解析器来解析SQL,然后从解析的查询中生成一个包含别名及其引用的表的符号表。然后将其与getSchemaTable()的结果结合起来,以便将列映射到适当的别名。

不管怎样,看看这个问题 Parsing SQL code in C# 对于一些解析器。我没有详细看过,但也许其中一个就是你需要的。如果只执行select语句,请检查antlr链接和的语法 http://www.antlr.org/grammar/1062280680642/MS_SQL_SELECT.html .

如果查询很简单,则可以使用正则表达式或自己的自定义语法从查询中解析别名和表名。这可能是最简单的解决方案。

最健壮的解决方案可能是为其他人的解析器付费,这些解析器处理完整的SQL,并将其分解成一个解析树,或者在其他地方查询它。我不确定每一个的优点和价格/稳健性比率。但其中一些是非常昂贵的……我想说,如果你不能自己去做,就去探索antlr语法(因为它是免费的),假设你只需要select语句。否则,您可能需要支付……

实际上,假设您的用户不是疯狂的SQL天才,而是使用子查询等。我不明白为什么您不能使用模式视图中的表名,因为您说必须在查询中找到它们,然后将别名作为表名别名或表名作为别名。这可能适用于许多情况……但是对于完整的一般情况,您需要一个完整的解析器…..

Cervo 14 年前

我认为RobFarley的ShowPlanXML可以为您工作(假设您运行的SQL Server已经足够晚了,具有此功能)。

每列似乎都有 <ColumnReference Server="" Database="" Schema="" Table="" Alias="" Column=""/> 对于每个选定列。假设每个表中至少有一列,那么在别名和表之间进行映射就很简单了。

Community CDub 8 年前

实际上,你可以。请看我的答案: https://stackoverflow.com/a/19537164/88409

您需要做的是使用 set showplan_xml on ,解析返回的XML,第一个 <OutputList> 您将找到顶级输出列。只要在查询中的表第一次被引用时为其指定别名,这些别名将一直传递到输出列。

为了更进一步,我必须推测这样的别名不能被优化,因为引擎必须使用它们来区分同一列与同一表的不同实例。

实际上,如果您运行这样的查询: select * from Lessons, Lessons ,引擎基本上会告诉您以下信息:

FROM条款中的“对象”和“经验”相同暴露的名字。 使用相关名称来区分它们。 “

例如,如果运行类似“set showplan”的XML; 从功课A、功课B、功课C中选择*(从功课D中选择*)子查询\u别名\u不会像\u表\u别名那样粘贴\u`

您将得到如下输出:

<OutputList>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="ID"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="Name"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="Description"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="Enabled"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[a]" Column="LessonTypeID"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="ID"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="Name"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="Description"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="Enabled"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[b]" Column="LessonTypeID"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="ID"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="Name"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="Description"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="Enabled"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[c]" Column="LessonTypeID"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[d]" Column="ID"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[d]" Column="Name"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[d]" Column="Description"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[d]" Column="Enabled"/>
  <ColumnReference Database="[sensitive]" Schema="[dbo]" Table="[Lessons]" Alias="[d]" Column="LessonTypeID"/>
</OutputList>