blocks|key|1400212|text|快速浏览一下就会发现它包含一个QueryExpression，这可能是一个QuerySpecification，它确实附加了Where子句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1400213|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Quick glance around would indicate that it contains a QueryExpression, which could be a QuerySpecification, which does have the Where clause attached to it.

blocks|key|1184740|text|如果有人想知道如何获取select语句的全部元素，可以使用以下代码进行解释：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1184741|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+"+%2B+binaryexp.BinaryExpressionType);
if+(binaryexp.FirstExpression+is+Column)
++sb.Append("+First+exp+is+"+%2B+((Identifier)((Column)binaryexp.FirstExpression).Identifiers[0]).Value);

if+(binaryexp.SecondExpression+is+Literal)
++sb.Append("+Second+exp+is+"+%2B+((Literal)binaryexp.SecondExpression).Value);|code-block|syntax|javascript|1184742|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

if someone lands here and wants to know how to get the whole elements of a select statement the following code explain that:

<pre><code>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);
</code></pre>

blocks|key|1184806|text|我必须将SELECT语句拆分成多个片段。我的目标是计算一个查询将返回多少条记录。我的第一个解决方案是构建一个子查询，比如|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1184807|SELECT+COUNT(*)+FROM+(select+id,+name+from+T+where+cat='A'+order+by+id)+as+QUERY|code-block|syntax|javascript|1184808|问题在于，在这种情况下，order子句会引发错误“该ORDER+BY子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了TOP或FOR+XML”|1184809|因此，我构建了一个解析器，使用TSql100Parser类将SELECT语句拆分成片段。|1184810|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+%2B=+error.Identifier+%2B+"+-+"+%2B+error.Message+%2B+"+-+position:+"+%2B+error.Offset+%2B+";+";
++++++++++++}

++++++++++++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%2B%2B)
++++++++{
++++++++++++s+%2B=+statement.ScriptTokenStream[i].Text;
++++++++}

++++++++return+s;
++++}
}|1184811|简单地使用这个类：|1184812|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);|1184813|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|W|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I had to split a SELECT statement into pieces. My goal was to COUNT how many record a query will return. My first solution was to build a sub query such as

<pre><code>SELECT COUNT(*) FROM (select id, name from T where cat='A' order by id) as QUERY
</code></pre>

The problem was that in this case the order clause raises the error "The ORDER BY clause is not valid in views, inline functions, derived tables, sub-queries, and common table expressions, unless TOP or FOR XML is also specified"

So I built a parser that split a SELECT statment into fragments using the TSql100Parser class.

<pre><code>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&lt;ParseError&gt; 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 &gt; 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&lt;string&gt;();
 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
{
 /// &lt;summary&gt;
 /// Get a string representing the SQL source fragment
 /// &lt;/summary&gt;
 /// &lt;param name="statement"&gt;The SQL Statement to get the string from, can be any derived class&lt;/param&gt;
 /// &lt;returns&gt;The SQL that represents the object&lt;/returns&gt;
 public static string GetString(this TSqlFragment statement)
 {
 string s = string.Empty;
 if (statement == null) return string.Empty;

 for (int i = statement.FirstTokenIndex; i &lt;= statement.LastTokenIndex; i++)
 {
 s += statement.ScriptTokenStream[i].Text;
 }

 return s;
 }
}
</code></pre>

And to use this class simply:

<pre><code>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);
</code></pre>

I'm attempting to parse SQL using the TSql100Parser provided by microsoft. Right now I'm having a little trouble using it the way it seems to be intended to be used. Also, the lack of documentation doesn't help. (example: <a href="http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.tsql100parser.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.tsql100parser.aspx</a> )

When I run a simple SELECT statement through the parser it returns a collection of TSqlStatements which contains a SELECT statement.
Trouble is, the TSqlSelect statement doesn't contain attributes such as a WHERE clause, even though the clause is implemented as a class. <a href="http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.whereclause.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.whereclause.aspx</a>
The parser does recognise the WHERE clause as such, looking at the token stream.
So, my question is, am I using the parser correctly? Right now the token stream seems to be the most useful feature of the parser...

My Test project: 

<pre><code>public static void Main(string[] args)
{
 var parser = new TSql100Parser(false);

 IList&lt;ParseError&gt; 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);
 }
}
</code></pre>

using the TSqlParser

我正在尝试使用微软提供的TSql100Parser解析SQL。现在，我在使用它时遇到了一些问题，它似乎是想要使用的方式。此外，缺乏文档也无济于事。(示例： )当我通过解析器运行一条简单的SELECT语句时，它返回一个包含SELECT语句的TSqlStatements集合。问题是，TSqlSelect语句不包含WHERE...

问使用TSqlParser
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用TSqlParserEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用TSqlParser
EN