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();
}
}
}