How to add the "with (nolock)" hint to all datasources in the query?
Last modified:
Here is the code sample which iterates through all datasources in the query and adds the "with (nolock)" hint to them.
private void button1_Click(object sender, EventArgs e) { // process entire query var queryRoot = queryBuilder1.SQLQuery.QueryRoot; using (new UpdateRegion(queryRoot)) { // get all dataSourceObjects which are not CTE references var dataSources = queryRoot.GetChildrenRecursive(true) .Where(ds => ds.SubQueryCTE == null); // process all dataSources foreach (var dataSource in dataSources) { // get syntax-specific extender var extender = (MSSQLFromSourceExtender) dataSource.DataSourceAST.Extender; // remove old hint var oldHint = extender.ObjectJoinHint; if (oldHint != null) oldHint.Dispose(); // create new hint var newHint = new AstNodeWithList(dataSource.SQLContext); newHint.Keyword("With").Symbol("(").Keyword("NoLock").Symbol(")"); // set new hint extender.ObjectJoinHint = newHint; // fire "dataSource inner AST updated" dataSource.NotifyUpdated(); } } }