HomeActive Query Builder - Query analysis and modificationProgrammatic modification of SQL queriesHow to add TOP clause to MS SQL Server query

2.9. How to add TOP clause to MS SQL Server query

To get access to the TOP clause of MS SQL Server query, you should use the MSSQLSubQuerySelectExpressionExtender object that could be obtained in the following way:

SQLSubQuerySelectExpression selexpr = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.QueryAST;
MSSQLSubQuerySelectExpressionExtender extender = (MSSQLSubQuerySelectExpressionExtender) selexpr.Extender;

Use the following code to add the top clause programmatically:

// add "TOP n" clause
SQLSubQueryTopRows top = new SQLSubQueryTopRows(queryBuilder1.SQLContext);

// or "TOP n PERCENT" clause
// SQLSubQueryTopPercent top = new SQLSubQueryTopPercent(queryBuilder1.SQLContext);

// add "... WITH TIES" if needed
top.WithTies = new SQLSubQueryTopWithTies(queryBuilder1.SQLContext);

top.Count = queryBuilder1.SQLContext.ParseExpression("10", null);

extender.Top = top;

queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.NotifySQLUpdated();

This page was: Helpful | Not Helpful