public void BatchCompleted(QueryTrace trace)
{
var newtable = new Dictionary <TEntity, TableEntry>(CqlEqualityComparer <TEntity> .Default);
foreach (KeyValuePair <TEntity, TableEntry> kv in _table)
{
if (!CqlEqualityComparer <TEntity> .Default.Equals(kv.Key, kv.Value.Entity))
{
throw new InvalidOperationException();
}
if (kv.Value.QueryTracingEnabled)
{
_traces.TryAdd(kv.Key, trace);
}
if (kv.Value.MutationType != MutationType.Delete && kv.Value.CqlEntityTrackingMode != EntityTrackingMode.DetachAfterSave)
{
newtable.Add(Clone(kv.Value.Entity),
new TableEntry
{
Entity = kv.Value.Entity,
MutationType = MutationType.None,
CqlEntityUpdateMode = kv.Value.CqlEntityUpdateMode,
CqlEntityTrackingMode = kv.Value.CqlEntityTrackingMode
});
}
}
_table = newtable;
}
/// <summary>
/// Provides a general-purpose way to save QueryTraces. Use this in <see cref="Query{TResult}.TraceCallback"/> or
/// <see cref="SqlDb{TKey}.TraceCallback"/> handlers
/// </summary>
public static void SaveTrace(IDbConnection connection, QueryTrace trace, SqlDb <int> db)
{
if (!db.Syntax.TableExists(connection, typeof(QueryTrace)))
{
var tableInfo = db.Syntax.GetTableInfoFromType(typeof(QueryTrace));
if (!db.Syntax.SchemaExists(connection, tableInfo.Schema))
{
CreateSchema cs = new CreateSchema(db.Syntax, tableInfo.Schema);
foreach (var cmd in cs.SqlCommands(connection))
{
connection.Execute(cmd);
}
}
CreateTable ct = new CreateTable(db.Syntax, db.Syntax.GetTableInfoFromType(typeof(QueryTrace)));
foreach (var cmd in ct.SqlCommands(connection))
{
connection.Execute(cmd);
}
}
var callback = db.TraceCallback;
// prevent infinite loop
db.TraceCallback = null;
db.Save(connection, trace);
// restore original callback
db.TraceCallback = callback;
}
public void BatchCompleted(QueryTrace trace)
{
var newtable = new Dictionary <TEntity, TableEntry>(CqlEqualityComparer <TEntity> .Default);
foreach (var kv in _table)
{
if (!CqlEqualityComparer <TEntity> .Default.Equals(kv.Key, kv.Value.Entity))
{
throw new InvalidOperationException();
}
if (kv.Value.QueryTracingEnabled)
{
lock (_traces)
_traces[kv.Key] = trace;
}
if (kv.Value.MutationType != MutationType.Delete)
{
newtable.Add(Clone(kv.Value.Entity), new TableEntry()
{
Entity = kv.Value.Entity, MutationType = MutationType.None, CqlEntityUpdateMode = kv.Value.CqlEntityUpdateMode
});
}
}
_table = newtable;
}
/// <summary>
/// Executes a Query that does not return results
/// </summary>
/// <param name="Command">MySQLCommand</param>
internal static void ExecuteNonQuery(MySqlCommand Command, bool LogStats, int RetryCount = 0)
{
if (string.IsNullOrWhiteSpace(Command.CommandText))
{
return;
}
MySqlConnection Connection = null;
try
{
Connection = OpenConnection(LogStats);
Command.Connection = Connection;
QueryTrace.AddQuery(Command.CommandText, Command.Parameters);
Command.ExecuteNonQuery();
}
catch (InvalidOperationException e)
{
QueryTrace.RemoveQuery(Command.CommandText);
if (HandleException(Connection, e, RetryCount))
{
ExecuteNonQuery(Command, LogStats, ++RetryCount);
}
else
{
throw e;
}
}
catch (MySqlException e)
{
if (e.Message.StartsWith("Duplicate entry"))
{
throw e;
}
QueryTrace.RemoveQuery(Command.CommandText);
if (HandleException(Connection, e, RetryCount))
{
ExecuteNonQuery(Command, LogStats, ++RetryCount);
}
else
{
throw e;
}
}
catch (CouldNotConnectException e)
{
throw new Exception("Database connection not valid. \n" + e.ToString());
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(Connection);
QueryTrace.RemoveQuery(Command.CommandText);
}
}
protected RowSet InternalEndExecute(IAsyncResult ar)
{
var tag = (CqlQueryTag)Session.GetTag(ar);
var ctx = tag.Session;
RowSet rowSet = ctx.EndExecute(ar);
QueryTrace = rowSet.Info.QueryTrace;
return(rowSet);
}
private RowSet InternalEndExecute(IAsyncResult ar)
{
var tag = (CqlQueryTag)Session.GetTag(ar);
var ctx = tag.Session;
var outp = ctx.EndExecute(ar);
QueryTrace = outp.Info.QueryTrace;
return(outp);
}
private void InvokeTraceCallback(IDbConnection connection, List <QueryTrace.Parameter> parameters, Stopwatch sw)
{
var trace = new QueryTrace(GetType().FullName, _db.UserName, _resolvedSql, parameters, sw.ElapsedMilliseconds, TraceContext);
_db?.TraceCallback?.Invoke(connection, trace);
if (_db.TraceQueries)
{
_db.QueryTraces.Add(trace);
}
}
/// <summary>
/// Returns a single result
/// </summary>
public object Scalar(DynamicCommand command)
{
var queryTraceEventArgs = new QueryTraceEventArgs(command.Sql, command.Arguments, command.Context);
QueryTrace.InvokeQueryBegin(queryTraceEventArgs);
using (var conn = this.OpenConnection())
{
var scalar = this.CreateDbCommand(command, connection: conn).ExecuteScalar();
QueryTrace.InvokeQueryEnd(queryTraceEventArgs);
return(scalar);
}
}
public List <QueryTrace> RetriveAllQueryTraces()
{
var ret = new List <QueryTrace>();
while (!_traces.IsEmpty)
{
KeyValuePair <TEntity, QueryTrace> kv = _traces.FirstOrDefault();
QueryTrace trace = RetriveQueryTrace(kv.Key);
if (trace != null)
{
ret.Add(trace);
}
}
return(ret);
}
/// <summary>
/// Executes a query the returns a datareader
/// </summary>
/// <param name="Command">MysqlCommand, Add params and query before using this method.</param>
/// <returns>MysqlDataReader</returns>
internal static MySqlDataReader ExecuteQuery(MySqlCommand Command, bool LogStats, int RetryCount = 0)
{
MySqlConnection Connection = null;
MySqlDataReader Reader = null;
try
{
Connection = OpenConnection(LogStats);
Command.Connection = Connection;
QueryTrace.AddQuery(Command.CommandText, Command.Parameters);
Reader = Command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (InvalidOperationException e)
{
if (HandleException(Connection, e, RetryCount))
{
return(ExecuteQuery(Command, LogStats, ++RetryCount));
}
else
{
throw e;
}
}
catch (CouldNotConnectException e)
{
throw new CouldNotConnectException("Database connection not valid. \n" + e.ToString());
}
catch (MySqlException e)
{
if (HandleException(Connection, e, RetryCount))
{
return(ExecuteQuery(Command, LogStats, ++RetryCount));
}
else
{
throw e;
}
}
catch (Exception e)
{
throw e;
}
return(Reader);
}
private async Task <DapperResult <T> > ExecuteInnerAsync <T>(IDbConnection connection, Func <string, object, Task <DapperResult <T> > > dapperMethod, List <QueryTrace> traces = null, Action <DynamicParameters> setParams = null, int newPageSize = 0)
{
ResolveSql(out DynamicParameters queryParams, setParams, newPageSize);
var macros = RegexHelper.ParseMacros(ResolvedSql);
var macroInserts = await ResolveMacrosAsync(connection, macros);
if (macroInserts.inserts.Any())
{
queryParams.AddDynamicParams(macroInserts.parameters);
foreach (var macro in macroInserts.inserts)
{
ResolvedSql = ResolvedSql.Replace(macro.Key, macro.Value);
DebugSql = DebugSql.Replace(macro.Key, macro.Value);
}
}
try
{
Debug.Print(DebugSql);
var stopwatch = Stopwatch.StartNew();
await OnExecutingAsync(connection, queryParams);
var result = await dapperMethod.Invoke(ResolvedSql, queryParams);
stopwatch.Stop();
var qt = new QueryTrace(GetType().Name, ResolvedSql, DebugSql, queryParams, stopwatch.Elapsed);
OnQueryExecuted(qt);
await OnQueryExecutedAsync(connection, qt);
traces?.Add(qt);
return(result);
}
catch (Exception exc)
{
throw new QueryException(exc, ResolvedSql, DebugSql, queryParams);
}
}
/// <summary>
/// Executes a series of DBCommands optionally in a transaction
/// </summary>
public int Execute(IEnumerable <DynamicCommand> commands, bool transaction = false)
{
using (var connection = this.OpenConnection())
using (var tx = (transaction) ? connection.BeginTransaction() : null)
{
var result = commands
.Select(cmd => new { Args = new QueryTraceEventArgs(cmd.Sql, cmd.Arguments, cmd.Context), Command = this.CreateDbCommand(cmd, tx, connection) })
.Aggregate(0, (a, x) =>
{
QueryTrace.InvokeQueryBegin(x.Args);
var r = a + x.Command.ExecuteNonQuery();
QueryTrace.InvokeQueryEnd(x.Args);
return(r);
});
if (tx != null)
{
tx.Commit();
}
return(result);
}
}
public async Task <IEnumerable <TResult> > ExecuteAsync(IDbConnection connection)
{
ResolvedSql = QueryUtil.ResolveSql(Sql, this, out DynamicParameters queryParams);
Parameters = queryParams;
var stopwatch = Stopwatch.StartNew();
try
{
return(await connection.QueryAsync <TResult>(ResolvedSql, queryParams));
}
catch (Exception exc)
{
throw new QueryException(exc, ResolvedSql, queryParams);
}
finally
{
stopwatch.Stop();
var qt = new QueryTrace(ResolvedSql, queryParams, stopwatch.Elapsed);
OnQueryExecuted(qt);
await OnQueryExecutedAsync(qt);
}
}
private DapperResult <T> ExecuteInner <T>(Func <string, object, DapperResult <T> > dapperMethod, List <QueryTrace> traces = null, Action <DynamicParameters> setParams = null, int newPageSize = 0)
{
ResolveSql(out DynamicParameters queryParams, setParams, newPageSize, removeMacros: true);
try
{
Debug.Print(DebugSql);
var stopwatch = Stopwatch.StartNew();
var result = dapperMethod.Invoke(ResolvedSql, queryParams);
stopwatch.Stop();
var qt = new QueryTrace(GetType().Name, ResolvedSql, DebugSql, queryParams, stopwatch.Elapsed);
OnQueryExecuted(qt);
traces?.Add(qt);
return(result);
}
catch (Exception exc)
{
throw new QueryException(exc, ResolvedSql, DebugSql, queryParams);
}
}
/// <summary>
/// Enumerates the reader yielding the results
/// </summary>
public IEnumerable <object> Query(DynamicCommand command)
{
var queryTraceEventArgs = new QueryTraceEventArgs(command.Sql, command.Arguments, command.Context);
QueryTrace.InvokeQueryBegin(queryTraceEventArgs);
using (var conn = this.OpenConnection())
{
var dbCommand = this.CreateDbCommand(command, connection: conn);
using (var rdr = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
QueryTrace.InvokeQueryEnd(queryTraceEventArgs);
while (rdr.Read())
{
var d = (IDictionary <string, object>) new ExpandoObject();
for (var i = 0; i < rdr.FieldCount; i++)
{
var value = rdr[i];
d.Add(rdr.GetName(i), DBNull.Value.Equals(value) ? null : value);
}
yield return(d);
}
}
}
}
public void SetQueryTrace(QueryTrace trace)
{
QueryTrace = trace;
}
public void SetQueryTrace(QueryTrace trace)
{
QueryTrace = trace;
}
/*
* [TestMethod]
* public void QueryArrayParams()
* {
* var qry = new ProductAttributesDownload();
* qry.AttributeId = new int[] { 1844, 1843, 3, 18 };
*
* using (var cn = _db.GetConnection())
* {
* qry.CommandTimeout = 120;
* var results = qry.Execute(cn);
* Assert.IsTrue(results.Any());
* }
* }
*/
private void TestSaveTrace(IDbConnection cn, QueryTrace trace)
{
QueryUtil.SaveTrace(cn, trace, _sqlDb);
}
public void SaveChangesOneByOne(Context context, string tablename, ConsistencyLevel consistencyLevel)
{
var commitActions = new List <Action>();
try
{
foreach (KeyValuePair <TEntity, TableEntry> kv in _table)
{
if (!CqlEqualityComparer <TEntity> .Default.Equals(kv.Key, kv.Value.Entity))
{
throw new InvalidOperationException();
}
string cql = "";
object[] values;
if (kv.Value.MutationType == MutationType.Add)
{
cql = CqlQueryTools.GetInsertCQLAndValues(kv.Value.Entity, tablename, out values, null, null, false);
}
else if (kv.Value.MutationType == MutationType.Delete)
{
cql = CqlQueryTools.GetDeleteCQLAndValues(kv.Value.Entity, tablename, out values);
}
else if (kv.Value.MutationType == MutationType.None)
{
cql = CqlQueryTools.GetUpdateCQLAndValues(kv.Key, kv.Value.Entity, tablename, out values,
kv.Value.CqlEntityUpdateMode == EntityUpdateMode.AllOrNone);
}
else
{
continue;
}
QueryTrace trace = null;
if (cql != null) // null if nothing to update
{
RowSet res = context.ExecuteWriteQuery(cql, values, consistencyLevel, kv.Value.QueryTracingEnabled);
if (kv.Value.QueryTracingEnabled)
{
trace = res.Info.QueryTrace;
}
}
KeyValuePair <TEntity, TableEntry> nkv = kv;
commitActions.Add(() =>
{
if (nkv.Value.QueryTracingEnabled)
{
if (trace != null)
{
_traces.TryAdd(nkv.Key, trace);
}
}
_table.Remove(nkv.Key);
if (nkv.Value.MutationType != MutationType.Delete && nkv.Value.CqlEntityTrackingMode != EntityTrackingMode.DetachAfterSave)
{
_table.Add(Clone(nkv.Value.Entity),
new TableEntry
{
Entity = nkv.Value.Entity,
MutationType = MutationType.None,
CqlEntityUpdateMode = nkv.Value.CqlEntityUpdateMode,
CqlEntityTrackingMode = nkv.Value.CqlEntityTrackingMode
});
}
});
}
}
finally
{
foreach (Action act in commitActions)
{
act();
}
}
}
private void ShowQueryInfo(IDbConnection cn, QueryTrace trace)
{
Debug.WriteLine($"{trace.QueryClass}\n{trace.UserName}\n{trace.Sql}\n{trace.GetParameterValueString()}\n{trace.Duration}ms");
}
protected override async Task OnQueryExecutedAsync(QueryTrace queryTrace)
{
_traces?.Add(queryTrace);
await Task.CompletedTask;
}
/// <summary>
/// Override this to capture information about a query execution in your application
/// </summary>
protected virtual void OnQueryExecuted(QueryTrace queryTrace)
{
}
/// <summary>
/// Override this to capture information about a query execution in your application
/// </summary>
protected virtual async Task OnQueryExecutedAsync(QueryTrace queryTrace)
{
await Task.CompletedTask;
}
protected override void OnQueryExecuted(QueryTrace queryTrace)
{
_traces?.Add(queryTrace);
}
/// <summary>
/// Override this to capture information about a query execution in your application
/// </summary>
protected virtual async Task OnQueryExecutedAsync(IDbConnection connection, QueryTrace queryTrace)
{
await Task.CompletedTask;
}
private static MySqlConnection OpenConnection(bool LogStats)
{
MySqlConnection Connection = null;
try
{
Connection = new MySqlConnection(Settings.ConnectionString);
}
catch
{
throw new InvalidOperationException("No connection string");
}
if (!semaphore.WaitOne(10000))
{
new EasyMySqlException("DatabaseHandler", new Exception("Maximum connections reached \n" + QueryTrace.GetTrace()));
return(Connection);
}
try
{
Connection.Open();
if (LogStats)
{
try
{
DatabaseStats.CurrentStats.AddDatabaseHit();
}
catch (Exception)
{
}
}
}
catch (Exception e)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("Database connection Failed\n" + e.ToString());
Console.WriteLine("Database connection Failed\n" + e.ToString());
#else
throw new CouldNotConnectException("Could not open the connection to the SQL server, Connectionstring correct? /n" + e.ToString());
#endif
}
return(Connection);
}
