Home → Active Query Builder → Programmatic modification of SQL queries → How to add TOP clause to MS SQL Server query

7.8. 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();