public SearchResult Search(IReportsDataSource dataSource, SearchRequest request, bool doNotExecute = false)
{
var result = new SearchResult();
var searchResultSummary = new SearchResultSummary();
var query = new SqlModeller.Model.Query();
CompiledQuery compiledQuery = null;
string sql = null;
if (request.SelectedColumns == null || !request.SelectedColumns.Any())
{
throw new Exception("No Columns Selected");
}
try
{
using (new QuickTimer(x => searchResultSummary.BuildQueryElapsedMilliseconds = x))
{
long mapTime;
query = dataSource.BuildQuery(request, out mapTime);
searchResultSummary.MapRequestElapsedMilliseconds = mapTime;
}
long countResult = 0;
using (new QuickTimer(x => searchResultSummary.CompileQueryElapsedMilliseconds = x))
{
compiledQuery = query.Compile();
}
// add OPTION (RECOMPILE) to combat parameter sniffing slowness
sql = compiledQuery.Sql + "\n OPTION (RECOMPILE) ";
DynamicParameters sqlParams = null;
if (compiledQuery.Parameters != null)
{
sqlParams = new DynamicParameters();
foreach (var p in compiledQuery.Parameters)
{
sqlParams.Add(p.ParameterName, p.Value, p.DataType);
}
}
if (!doNotExecute)
{
using (var connection = ConnectionFactory.GetOpenConnectionUsingConnectionStringName(dataSource.ConnectionStringName))
{
Debug.WriteLine(compiledQuery.ToString());
IEnumerable <dynamic> queryResult;
using (new QuickTimer(x => searchResultSummary.QueryElapsedMilliseconds = x))
{
using (var tran = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
queryResult = connection.Query(sql, sqlParams, tran, commandTimeout: sqlCommandTimeoutSeconds);
tran.Commit();
}
}
using (new QuickTimer(x => searchResultSummary.ParseResultElapsedMilliseconds = x))
{
result = ParseQueryResult(dataSource, queryResult, request, out countResult);
}
}
}
searchResultSummary.TotalRows = countResult;
result.Summary = searchResultSummary;
}
catch (Exception ex)
{
result = new SearchResponse()
{
Error = new ResponseError().Load(ex)
};
}
try
{
result.DebugInfo.SqlQuery = compiledQuery.ParameterSql + "\n\n" + sql;
}
catch
{
}
return(result);
}