public Task OutputResultsAsync(IQueryRunner runner)
{
return Task.Factory.StartNew(() =>
{
try
{
runner.OutputMessage("Query Started");
var start = DateTime.Now;
var dq = runner.QueryText;
var res = runner.ExecuteQuery(dq);
var end = DateTime.Now;
var durationMs = (end - start).TotalMilliseconds;
// TODO write results to Excel
runner.Host.OutputStaticResult(res, runner.SelectedWorksheet);
//runner.ResultsTable = res;
runner.OutputMessage(
string.Format("Query Completed ({0} row{1} returned)", res.Rows.Count,
res.Rows.Count == 1 ? "" : "s"), durationMs);
runner.ActivateResults();
runner.QueryCompleted();
}
catch (Exception ex)
{
runner.ActivateOutput();
runner.OutputError(ex.Message);
}
});
}
public Task OutputResultsAsync(IQueryRunner runner)
{
return(Task.Factory.StartNew(() =>
{
try
{
runner.OutputMessage("Query Started");
var start = DateTime.Now;
var dq = runner.QueryText;
var res = runner.ExecuteQuery(dq);
var end = DateTime.Now;
var durationMs = (end - start).TotalMilliseconds;
// TODO write results to Excel
runner.Host.OutputStaticResult(res, runner.SelectedWorksheet);
//runner.ResultsTable = res;
runner.OutputMessage(
string.Format("Query Completed ({0} row{1} returned)", res.Rows.Count,
res.Rows.Count == 1 ? "" : "s"), durationMs);
runner.ActivateResults();
runner.QueryCompleted();
}
catch (Exception ex)
{
runner.ActivateOutput();
runner.OutputError(ex.Message);
}
}));
}
/// <summary>
/// SELECT ... (without FROM)
/// </summary>
public static IEnumerable <T> Select <T>(this IQueryRunner runner, Expression <Func <SelectorContext, T> > selectExpr)
{
var stmtList = new StatementList();
var select = stmtList.Select(selectExpr);
return(runner.ExecuteQuery(select));
}
/// <summary>
/// SELECT ... FROM ...
/// </summary>
public static IEnumerable <T> Select <TFrom, T>(this IQueryRunner runner, Query <TFrom, T> query)
{
var stmtList = new StatementList();
var select = stmtList.Select(query);
return(runner.ExecuteQuery(select));
}
/// <summary>
/// INSERT INTO (...) VALUES ( ...); SELECT LAST_IDENTITY()
/// </summary>
public static TIdentity Insert <T, TIdentity>(this IQueryRunner runner, FromQuery <T> query, Expression <Action <InsertBuilder <T> > > insertExpr) where T : new()
{
var stmtList = new StatementList();
stmtList.Insert(query, insertExpr);
var select = stmtList.Select(ctx => Function.LastInsertIdentity <TIdentity>(ctx));
return(runner.ExecuteQuery(select).FirstOrDefault());
}
private void OutputResults(IQueryRunner runner)
{
try
{
runner.OutputMessage("Query Started");
var sw = Stopwatch.StartNew();
var dq = runner.QueryText;
var res = runner.ExecuteQuery(dq);
sw.Stop();
var durationMs = sw.ElapsedMilliseconds;
runner.OutputMessage(string.Format("Query Completed ({0:N0} row{1} returned)", res.Rows.Count, res.Rows.Count == 1 ? "" : "s"), durationMs);
runner.RowCount = res.Rows.Count;
runner.SetResultsMessage("Query timings sent to output tab", OutputTargets.Timer);
//runner.QueryCompleted();
runner.ActivateOutput();
}
catch (Exception ex)
{
runner.ActivateOutput();
runner.OutputError(ex.Message);
}
}
private static void TestRunQuery(TestData testData, StructuredQuery query)
{
query.TimeZoneName = TimeZoneHelper.SydneyTimeZoneName;
QuerySettings settings = new QuerySettings();
QueryResult result;
IQueryRunner queryRunner = Factory.Current.ResolveNamed <IQueryRunner>("Test"); // test instance does not cache runner or builder
// Run query
result = queryRunner.ExecuteQuery(query, settings);
if (result.DataTable.Rows.Count == 0)
{
throw new Exception("No rows returned.");
}
object actual;
if (result.DataTable.Rows.Count == 1)
{
actual = result.DataTable.Rows[0][0];
}
else
{
actual = result.DataTable.Rows.OfType <DataRow>().Select(row => row[0]).ToArray();
}
// Check result
if (!testData.Expected.NoValue)
{
object expected = testData.Expected.Value;
if (testData.Expected.ExprType.Type == DataType.Bool)
{
if (expected == null)
{
expected = false;
}
}
if (actual is DBNull)
{
actual = null;
}
if (actual is double)
{
actual = (decimal)(double)actual;
}
if (actual is float)
{
actual = (decimal)(float)actual;
}
if (actual is decimal && expected is int)
{
expected = (decimal)(int)expected;
}
if (actual is int && expected is decimal)
{
actual = (decimal)(int)actual;
}
if (expected is IEntity)
{
// SQL returns names for now
expected = ((IEntity)expected).As <Resource>().Name;
}
object expected2 = ReformatResult(expected, testData);
object actual2 = ReformatResult(actual, testData);
// Note: reporting engine will cast int to bigint for sum aggregates - see #24609
if (actual2 is long)
{
actual2 = ( int )( long )actual2;
}
if (!expected2.Equals(actual2))
{
throw new Exception(string.Format("Expected SQL result {0} but actual result was {1}.", expected2, actual2));
}
}
}
public Task OutputResultsAsync(IQueryRunner runner)
{
return Task.Factory.StartNew(() =>
{
try
{
runner.OutputMessage("Query Started");
var sw = Stopwatch.StartNew();
var dq = runner.QueryText;
var res = runner.ExecuteQuery(dq);
if (res != null)
{
sw.Stop();
var durationMs =sw.ElapsedMilliseconds;
runner.ResultsTable = res;
runner.OutputMessage(
string.Format("Query Completed ({0:N0} row{1} returned)", res.Rows.Count,
res.Rows.Count == 1 ? "" : "s"), durationMs);
runner.RowCount = res.Rows.Count;
// activate the result only when Counters are not selected...
runner.ActivateResults();
//runner.QueryCompleted();
}
}
catch (Exception ex)
{
runner.ActivateOutput();
runner.OutputError(ex.Message);
}
});
}