获取表架构从查询 [英] Getting table schema from a query
问题描述
按<一个href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable.aspx"相对=nofollow> MSDN ， SqlDataReader.GetSchemaTable 返回列元数据执行查询。我想知道有没有这将使表中的元数据给定的查询类似的方法？我的意思是表涉及什么别名它已得到。
As per MSDN, SqlDataReader.GetSchemaTable returns column metadata for the query executed. I am wondering is there a similar method that will give table metadata for the given query? I mean what tables are involved and what aliases it has got.
在我的应用程序，我得到的查询，我需要追加其中，子句programically。使用的GetSchemaTable（），我能得到列元数据和表属于。但是，即使表有别名，它仍然会返回一个真正的表名。有没有办法让该表的aliase叫什么名字？
In my application, I get the query and I need to append the where clause programically. Using GetSchemaTable(), I can get the column metadata and the table it belongs to. But even though table has aliases, it still return the real table name. Is there a way to get the aliase name for that table?
随着code显示获得列元数据。
Following code shows getting the column metadata.
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 列编号，这是给联系人而不是别名 C 。有没有什么办法可以得到像上面的查询？
This will give me details of columns correctly. But when I see BaseTableName for column Id, it is giving contact rather than the alias name c. Is there any way to get the table schema and aliases from a query like the above?
任何帮助将是巨大的！
修改
虽然我可以使用由罗布提出的执行计划，我AP preciate任何其他简单的方法。
While I could use the execution plan suggested by Rob, I'd appreciate any alternative simple approaches.
通过回答问题tomekszpakowicz
您（或您的应用程序）的源 对有问题的查询？在这种情况下 你应该知道的别名。
Are you (or your application) source of the query in question? In that case you should know the aliases.
我不查询的作者。我们有一个系统，用户可以输入查询。我们建立用我上面的解释方法列出来。这些细节将依然存在，其他用户可以使用此如添加新的标准等，因此，我们需要从我们掌握的信息动态生成的SQL。因此，当一个列别名，我们没有得到别名，然后在那里建造条款将是无效的。
I am not the author of queries. We have a system where users can enter the query. We build columns out of it using the method I explained above. These details will be persisted and another user can use this like adding new criteria etc. So we need to build the SQL dynamically from the information we have. So when a column is aliased and we are not getting alias name, then the where clause constructed will be invalid.
感谢
推荐答案
简短的回答
这是行不通的。你不能按照设计，得到表的别名从结果模式。你可以不依赖于能够从查询执行计划得到它们。
This won't work. You cannot, by design, get table aliases from result schema. And you cannot rely on being able to get them from query execution plan.
龙的答案
当你得到一个SQL查询的结果，查询已经被解析，验证，优化，编译成一些内部重新presentation和执行。别名是查询的源$ C $ C的一部分，通常在某处失去了周围的步骤1和2。
When you get result of a SQL query, the query has already been parsed, validated, optimized, compiled into some internal representation and executed. Aliases are part of query's "source code" and are usually lost somewhere around step 1 and 2.
在查询被执行，可以被看作是表中的唯一的东西是a）真实物理表和b）返回视为单个匿名表数据。可以转化或完全的一切都优化掉了。
After query is executed the only things that can be seen as tables are a) real physical tables and b) returned data seen as single anonymous table. Everything between can be transformed or completely optimized out.
如果被要求的DBMS保留别名这将是几乎不可能的优化复杂的查询。
If DBMSes were required to retain aliases it would be practically impossible to optimize complex queries.
可能的解决方案
我建议重申一个问题：
-
您（或您的应用程序）所涉及的查询源？在这种情况下，你应该知道的别名。
Are you (or your application) source of the query in question? In that case you should know the aliases.
如果你得到别人提供的查询嘛......那要看你为什么要添加的地方的原因。
If you get queries provided by someone else... Well... That depends on why are you adding where causes.
-
在最坏的情况下，你必须自己解析查询。
In the worst case, you'll have to parse queries yourself.
在最好的情况下，你可以给他们访问的看法，而不是真实的表，并把where子句中的观点。
In the best case, you could give them access to views instead of real tables and put where clauses in the views.
简单和丑陋的解决方案
如果我正确地理解您的要求：
If I understand your requirements correctly:
-
用户A进入查询到你的程序。
User A enters query into your program.
用户B可以运行它（但不能编辑），并认为返回的数据。 此外，她可以根据使用某种你所提供的widget的返回的列添加过滤器。
User B can run it (but cannot edit it) and sees returned data. Additionally she can add filters based on returned columns using some kind of widget provided by you.
您不想应用过滤器内部的应用，而是将它们添加到查询，以避免从数据库中获取不必要的数据。
You don't want to apply filter inside application but instead add them to the query, in order to avoid fetching unnecessary data from database.
在这种情况下：
-
在编辑查询尝试运行它，并收集元数据返回的列。 如果
的ColumnName是不是唯一的，抱怨的作者。 存储元数据查询。
When A edits query try to run it and gather metadata for returned columns. If
ColumnNames are not unique, complain to the author. Store metadata with query.
在B加上过滤器（基于查询的元数据），同时存储列名 和条件。
When B adds filter (based on query metadata), store both columns names and conditions.
在执行：
-
检查过滤列仍然是有效的（A可能已经改变了查询）。 如果不删除无效的过滤器和/或通知的B。
Check if filter columns are still valid (A might have changed query). If not remove invalid filters and/or inform B.
执行的查询，是这样的：
Execute query as something like:
select *
from ({query entered by A}) x
where x.Column1 op1 Value1
and x.Column2 op2 Value2
如果你想妥善地处理数据库模式的改变，你需要添加一些额外的检查，以确保元数据是什么真正的查询返回一致的。
If you want to gracefully handle database schema changes you need to add some additional checks to make sure metadata is consistent with what query really returns.
安全说明
您的计划是要通过写用户直到数据库中的查询。 至关重要的是，你使用与不超过A的数据库权限权限的数据库连接它。 否则，你所要求的SQL注入漏洞基础。
Your program is going to pass a query written by user A straight to database. It is crucial that you do it using database connection with permissions which do not exceed A's database permissions. Otherwise you are asking for SQL injection based exploits.
推论
如果用户A不能直接访问数据库出于安全方面的原因，你不能用上面的解决方案。
If user A doesn't have direct access to the database out of security reasons, you cannot use above solution.
在这种情况下，使之安全的唯一方法是确保您的应用程序理解查询的100％，这意味着你认为安全解析它在你的程序，并只允许操作。
In that case the only way to make it safe is to make sure your application understands 100% of the query which means parsing it in your program and allowing only operations you consider safe.
这篇关于获取表架构从查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
