public override async Task <RowsetBase> ExecuteAsync(MySqlCrudQueryExecutionContext context, Query query, bool oneDoc = false)
{
var target = context.DataStore.TargetName;
using (var cmd = context.Connection.CreateCommand())
{
cmd.CommandText = Source.StatementSource;
cmd.CommandTimeout = CommandTimeoutSec;
PopulateParameters(cmd, query);
cmd.Transaction = context.Transaction;
MySqlDataReader reader = null;
try
{
reader = oneDoc ? await cmd.ExecuteReaderAsync(CommandBehavior.SingleRow).ConfigureAwait(false) :
await cmd.ExecuteReaderAsync().ConfigureAwait(false);
GeneratorUtils.LogCommand(context.DataStore, "queryhandler-ok", cmd, null);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(context.DataStore, "queryhandler-error", cmd, error);
throw;
}
using (reader)
{
return(await DoPopulateRowsetAsync(context, reader, target, query, Source, oneDoc).ConfigureAwait(false));
}
}//using command
}
public override async Task <Schema> GetQuerySchemaAsync(MySqlCrudQueryExecutionContext context, Query query)
{
var target = context.DataStore.TargetName;
using (var cmd = context.Connection.CreateCommand())
{
cmd.CommandText = Source.StatementSource;
cmd.CommandTimeout = CommandTimeoutSec;
PopulateParameters(cmd, query);
cmd.Transaction = context.Transaction;
MySqlDataReader reader = null;
try
{
reader = await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly).ConfigureAwait(false);
GeneratorUtils.LogCommand(context.DataStore, "queryhandler-ok", cmd, null);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(context.DataStore, "queryhandler-error", cmd, error);
throw;
}
using (reader)
{
Schema.FieldDef[] toLoad;
return(GetSchemaForQuery(target, query, reader, Source, out toLoad));
} //using reader
} //using command
}
public override async Task <Doc> ExecuteProcedureAsync(MySqlCrudQueryExecutionContext context, Query query)
{
using (var cmd = context.Connection.CreateCommand())
{
cmd.CommandText = Source.StatementSource;
cmd.CommandTimeout = CommandTimeoutSec;
PopulateParameters(cmd, query);
cmd.Transaction = context.Transaction;
try
{
var affected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
GeneratorUtils.LogCommand(context.DataStore, "queryhandler-ok", cmd, null);
return(MapProcedureResult(affected));
}
catch (Exception error)
{
GeneratorUtils.LogCommand(context.DataStore, "queryhandler-error", cmd, error);
throw;
}
}//using command
}
internal MySqlCursor(MySqlCrudQueryExecutionContext context,
MySqlCommand command,
MySqlDataReader reader,
IEnumerable <Doc> source) : base(source)
{
m_Context = context;
m_Command = command;
m_Reader = reader;
}
/// <summary>
/// Build Sql command and parameters from the shorthand fluent syntax
/// </summary>
/// <remarks>
/// //Example query with multiple blocks
/// cmd.BuildSelect(builder =>
/// {
/// builder.Limit = 100;
/// builder.Select("T1.*").From("dbo", "TBL_USER", "T1");
///
/// builder.WhereBlockBegin(WhereClauseType.Or)
/// .AndWhere("T1.FIRSTNAME = ?FN1", new MySqlParameter("FN1", "Adam"))
/// .AndWhere("T1.FIRSTNAME != ?FN2", new MySqlParameter("FN2", "Amber"))
/// .WhereBlockEnd();
///
/// builder.WhereBlockBegin(WhereClauseType.Or)
/// .AndWhere("T1.LASTNAME = ?LN1", new MySqlParameter("LN1", "Adamson"))
/// .AndWhere("T1.LASTNAME != ?LN2", new MySqlParameter("LN2", "Amberman"))
/// .WhereBlockEnd();
///
/// builder.OrderByAsc("T1.LASTNAME")
/// .OrderByDesc("T1.FIRSTNAME");
/// builder.GroupBy("T1.FIRSTNAME");
/// builder.Having("T1.SCORE > ?SCORE", new MySqlParameter("SCORE", 12_000));
///});
/// </remarks>
public static MySqlCommand BuildSelect(this MySqlCrudQueryExecutionContext ctx,
MySqlCommand command,
Action <MySqlSelectBuilder> body)
{
command.NonNull(nameof(command));
body.NonNull(nameof(body));
using (var builder = MySqlSelectBuilder.For(ctx.DataStore.SchemaName.NonBlank("schema-name")))
{
body(builder);
builder.Build(command);
}
return(command);
}
private IEnumerable <Doc> execEnumerable(MySqlCrudQueryExecutionContext ctx, MySqlCommand cmd, MySqlDataReader reader, Schema schema, Schema.FieldDef[] toLoad, Query query)
{
using (cmd)
{
using (reader)
{
while (reader.Read())
{
var row = DoPopulateDoc(ctx, query.ResultDocType, schema, toLoad, reader);
yield return(row);
}
}
}
}
public override async Task <Cursor> OpenCursorAsync(MySqlCrudQueryExecutionContext context, Query query)
{
var target = context.DataStore.TargetName;
Schema.FieldDef[] toLoad;
Schema schema = null;
MySqlDataReader reader = null;
var cmd = context.Connection.CreateCommand();
try
{
cmd.CommandText = Source.StatementSource;
cmd.CommandTimeout = CommandTimeoutSec;
PopulateParameters(cmd, query);
cmd.Transaction = context.Transaction;
try
{
reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false);
GeneratorUtils.LogCommand(context.DataStore, "queryhandler-ok", cmd, null);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(context.DataStore, "queryhandler-error", cmd, error);
throw;
}
schema = GetSchemaForQuery(target, query, reader, Source, out toLoad);
}
catch
{
if (reader != null)
{
reader.Dispose();
}
cmd.Dispose();
throw;
}
var enumerable = execEnumerable(context, cmd, reader, schema, toLoad, query);
return(new MySqlCursor(context, cmd, reader, enumerable));
}
/// <summary>
/// Performs CRUD load. Override to do custom Query interpretation
/// </summary>
protected internal async virtual Task <Cursor> DoOpenCursorAsync(MySqlConnection cnn, MySqlTransaction transaction, Query query)
{
var context = new MySqlCrudQueryExecutionContext(this, cnn, transaction);
var handler = QueryResolver.Resolve(query);
try
{
return(await handler.OpenCursorAsync(context, query).ConfigureAwait(false));
}
catch (Exception error)
{
throw new MySqlDataAccessException(
StringConsts.OPEN_CURSOR_ERROR + error.ToMessageWithType(),
error,
KeyViolationKind.Unspecified,
CrudGenerator.KeyViolationName(error));
}
}
/// <summary>
/// Executes the specified number of command steps by calling `ExecuteNonQuery` for each under a transaction
/// committing it on success or rolling back on any failure.
/// The timeout is expressed IN SECONDS (MSFT legacy behavior).
/// Returns total rows affected across all call steps
/// </summary>
public static async Task <long> ExecuteCompoundCommand(this MySqlCrudQueryExecutionContext context,
int timeoutSec,
System.Data.IsolationLevel isolation,
params Action <MySqlCommand>[] steps)
{
var result = 0L;
using (var cmd = context.NonNull(nameof(context)).Connection.CreateCommand())
{
cmd.CommandTimeout = timeoutSec;//in Seconds
using (var tx = context.Connection.BeginTransaction(isolation))
{
try
{
foreach (var step in steps.NonNull(nameof(steps)))
{
cmd.CommandText = string.Empty;
cmd.Parameters.Clear();
step(cmd);
cmd.Transaction = tx;
context.ConvertParameters(cmd.Parameters);
var affected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
result += affected;
}
await tx.CommitAsync().ConfigureAwait(false);
GeneratorUtils.LogCommand(context.DataStore, "ExecCompoundProc-ok", cmd, null);
}
catch (Exception error)
{
await tx.RollbackAsync().ConfigureAwait(false);
GeneratorUtils.LogCommand(context.DataStore, "ExecCompoundProc-error", cmd, error);
throw;
}
}
}
return(result);
}
/// <summary> /// Creates new version stamp - an instance of VersionInfo object initialized with callers context /// </summary> /// <param name="context">Operation context</param> /// <param name="gdidScopeName">GDID generation scope name - use EntityIds.ID_NS* constant</param> /// <param name="gdidSeqName">GDID generation sequence name - use EntityIds.ID_SEQ* constant</param> /// <param name="mode">Form model mode</param> /// <returns>New instance of VersionInfo with generated GDID and other fields stamped from context</returns> public static VersionInfo MakeVersionInfo(this MySqlCrudQueryExecutionContext context, string gdidScopeName, string gdidSeqName, FormMode mode) => context.GetApp().MakeVersionInfo(gdidScopeName, gdidSeqName, mode);
/// <summary> /// Returns IGdidProvider /// </summary> public static IGdidProvider GetGdidProvider(this MySqlCrudQueryExecutionContext context) => context.GetApp().GetGdidProvider();
/// <summary> /// Returns app cloud origin /// </summary> public static Atom GetCloudOrigin(this MySqlCrudQueryExecutionContext context) => context.GetApp().GetCloudOrigin();
/// <summary> /// Returns current UTC Now using app precision time source /// </summary> public static DateTime GetUtcNow(this MySqlCrudQueryExecutionContext context) => context.GetApp().GetUtcNow();
/// <summary>
/// Gets App chassis
/// </summary>
public static IApplication GetApp(this MySqlCrudQueryExecutionContext context)
=> context.NonNull(nameof(context)).DataStore.NonNull("ctx.ds").App;
