使用TSqlParser
使用TSqlParser
提问于 2009-11-23 03:44:12
我正在尝试使用微软提供的TSql100Parser解析SQL。现在，我在使用它时遇到了一些问题，它似乎是想要使用的方式。此外，缺乏文档也无济于事。(示例：http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.tsql100parser.aspx )
当我通过解析器运行一条简单的SELECT语句时，它返回一个包含SELECT语句的TSqlStatements集合。问题是，TSqlSelect语句不包含WHERE子句等属性，即使该子句是作为类实现的。http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.whereclause.aspx解析器通过查看令牌流来识别WHERE子句。所以，我的问题是，我是否正确地使用了解析器？现在，令牌流似乎是解析器最有用的功能……
我的测试项目：
public static void Main(string[] args)
{
var parser = new TSql100Parser(false);
IList<ParseError> Errors;
IScriptFragment result = parser.Parse(
new StringReader("Select col from T1 where 1 = 1 group by 1;" +
"select col2 from T2;" +
"select col1 from tbl1 where id in (select id from tbl);"),
out Errors);
var Script = result as TSqlScript;
foreach (var ts in Script.Batches)
{
Console.WriteLine("new batch");
foreach (var st in ts.Statements)
{
IterateStatement(st);
}
}
}
static void IterateStatement(TSqlStatement statement)
{
Console.WriteLine("New Statement");
if (statement is SelectStatement)
{
PrintStatement(sstmnt);
}
}回答 3
Stack Overflow用户
发布于 2009-11-24 23:23:40
快速浏览一下就会发现它包含一个QueryExpression，这可能是一个QuerySpecification，它确实附加了Where子句。
Stack Overflow用户
发布于 2011-04-08 00:44:59
如果有人想知道如何获取select语句的全部元素，可以使用以下代码进行解释：
QuerySpecification spec = (QuerySpecification)(((SelectStatement)st).QueryExpression);
StringBuilder sb = new StringBuilder();
sb.AppendLine("Select Elements");
foreach (var elm in spec.SelectElements)
sb.Append(((Identifier)((Column)((SelectColumn)elm).Expression).Identifiers[0]).Value);
sb.AppendLine();
sb.AppendLine("From Elements");
foreach (var elm in spec.FromClauses)
sb.Append(((SchemaObjectTableSource)elm).SchemaObject.BaseIdentifier.Value);
sb.AppendLine();
sb.AppendLine("Where Elements");
BinaryExpression binaryexp = (BinaryExpression)spec.WhereClause.SearchCondition;
sb.Append("operator is " + binaryexp.BinaryExpressionType);
if (binaryexp.FirstExpression is Column)
sb.Append(" First exp is " + ((Identifier)((Column)binaryexp.FirstExpression).Identifiers[0]).Value);
if (binaryexp.SecondExpression is Literal)
sb.Append(" Second exp is " + ((Literal)binaryexp.SecondExpression).Value);Stack Overflow用户
发布于 2012-06-25 03:43:09
我必须将SELECT语句拆分成多个片段。我的目标是计算一个查询将返回多少条记录。我的第一个解决方案是构建一个子查询，比如
SELECT COUNT(*) FROM (select id, name from T where cat='A' order by id) as QUERY问题在于，在这种情况下，order子句会引发错误“该ORDER BY子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了TOP或FOR XML”
因此，我构建了一个解析器，使用TSql100Parser类将SELECT语句拆分成片段。
using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.ScriptDom;
using System.IO;
...
public class SelectParser
{
public string Parse(string sqlSelect, out string fields, out string from, out string groupby, out string where, out string having, out string orderby)
{
TSql100Parser parser = new TSql100Parser(false);
TextReader rd = new StringReader(sqlSelect);
IList<ParseError> errors;
var fragments = parser.Parse(rd, out errors);
fields = string.Empty;
from = string.Empty;
groupby = string.Empty;
where = string.Empty;
orderby = string.Empty;
having = string.Empty;
if (errors.Count > 0)
{
var retMessage = string.Empty;
foreach (var error in errors)
{
retMessage += error.Identifier + " - " + error.Message + " - position: " + error.Offset + "; ";
}
return retMessage;
}
try
{
// Extract the query assuming it is a SelectStatement
var query = ((fragments as TSqlScript).Batches[0].Statements[0] as SelectStatement).QueryExpression;
// Constructs the From clause with the optional joins
from = (query as QuerySpecification).FromClauses[0].GetString();
// Extract the where clause
where = (query as QuerySpecification).WhereClause.GetString();
// Get the field list
var fieldList = new List<string>();
foreach (var f in (query as QuerySpecification).SelectElements)
fieldList.Add((f as SelectColumn).GetString());
fields = string.Join(", ", fieldList.ToArray());
// Get The group by clause
groupby = (query as QuerySpecification).GroupByClause.GetString();
// Get the having clause of the query
having = (query as QuerySpecification).HavingClause.GetString();
// Get the order by clause
orderby = ((fragments as TSqlScript).Batches[0].Statements[0] as SelectStatement).OrderByClause.GetString();
}
catch (Exception ex)
{
return ex.ToString();
}
return string.Empty;
}
}
public static class Extension
{
/// <summary>
/// Get a string representing the SQL source fragment
/// </summary>
/// <param name="statement">The SQL Statement to get the string from, can be any derived class</param>
/// <returns>The SQL that represents the object</returns>
public static string GetString(this TSqlFragment statement)
{
string s = string.Empty;
if (statement == null) return string.Empty;
for (int i = statement.FirstTokenIndex; i <= statement.LastTokenIndex; i++)
{
s += statement.ScriptTokenStream[i].Text;
}
return s;
}
}简单地使用这个类：
string fields, from, groupby, where, having, orderby;
SelectParser selectParser = new SelectParser();
var retMessage = selectParser.Parse("SELECT * FROM T where cat='A' Order by Id desc",
out fields, out from, out groupby, out where, out having, out orderby);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1779737
复制相关文章
相似问题