/// <summary>
/// Returns a ResultSet from a select command
/// </summary>
/// <param name="cmd">MySqlCommand to use for the interaction</param>
/// <returns>ResultSet containing the rows</returns>
protected override ResultSet Execute(MySqlCommand cmd)
{
ResultSet result = new ResultSet();
cmd.CommandText = CommandText;
cmd.AddParameters(Parameters);
try
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result.Add(Enumerable.Range(0, reader.FieldCount).ToDictionary(reader.GetName,
i => (reader.IsDBNull(i)) ? null : reader.GetValue(i)));
}
}
}
catch (MySqlException mysqlEx)
{
Utility.PrintErrorInformation(mysqlEx, Debug);
}
if (Debug)
{
CommandText = cmd.Stringify();
}
return(result);
}
/// <summary>
/// Executes a scalar
/// </summary>
/// <param name="cmd">MySqlCommand to be used to get the Scalar value</param>
/// <returns>Scalar</returns>
protected override object Execute(MySqlCommand cmd)
{
object result = null;
cmd.CommandText = CommandText;
cmd.AddParameters(Parameters);
try
{
result = cmd.ExecuteScalar();
}
catch (MySqlException mysqlEx)
{
Utility.PrintErrorInformation(mysqlEx, Debug);
}
if (result != null && result.GetType() == typeof(DBNull))
{
result = null;
}
if (Debug)
{
CommandText = cmd.Stringify();
}
return(result);
}
/// <summary>
/// Execute the MySqlCommand as a NonQuery
/// </summary>
/// <param name="cmd">MySqlCommand to be executed</param>
/// <returns></returns>
protected override long Execute(MySqlCommand cmd)
{
long result = -1;
cmd.CommandText = CommandText;
cmd.AddParameters(Parameters);
try
{
result = cmd.ExecuteNonQuery();
}
catch (MySqlException mySqlEx)
{
Utility.PrintErrorInformation(mySqlEx, Debug);
}
if (IsInsert)
{
result = cmd.LastInsertedId;
}
if (Debug)
{
CommandText = cmd.Stringify();
}
return(result);
}
public Task <int> Query(string query, IDictionary <string, dynamic> parameters = null) => Task.Factory.StartNew(() => {
int result = -1;
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
long connectionTime = 0, queryTime = 0;
using (Connection db = new Connection(connectionString))
{
timer.Start();
db.connection.Open();
connectionTime = timer.ElapsedMilliseconds;
using (MySqlCommand cmd = db.connection.CreateCommand())
{
cmd.CommandText = query;
cmd.AddParameters(parameters);
timer.Restart();
result = cmd.ExecuteNonQuery();
queryTime = timer.ElapsedMilliseconds;
}
}
timer.Stop();
PrintDebugInformation(connectionTime, queryTime, 0, query);
return(result);
}, CancellationToken.None, TaskCreationOptions.None, queryScheduler);
/// <summary>
/// wrapper for transactions
/// </summary>
/// <param name="querys">List of query strings</param>
/// <param name="parameters">Dictionary of parameters which count for all transactions</param>
/// <returns>true or false depending on whether the transaction succeeded or not</returns>
public Task <bool> Transaction(IList <string> querys, IDictionary <string, dynamic> parameters = null) => Task.Factory.StartNew(() =>
{
bool result = false;
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
long connectionTime = 0, queryTime = 0;
using (Connection db = new Connection(settings.ConnectionString))
{
timer.Start();
db.connection.Open();
connectionTime = timer.ElapsedMilliseconds;
using (MySqlCommand cmd = db.connection.CreateCommand())
{
using (MySqlTransaction transaction = db.connection.BeginTransaction())
{
cmd.AddParameters(parameters);
cmd.Transaction = transaction;
timer.Restart();
try
{
foreach (string query in querys)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
transaction.Commit();
result = true;
}
catch (Exception exception)
{
if (settings.Debug)
{
CitizenFX.Core.Debug.Write(String.Format("[GHMattiMySQL] [Failed Transaction] {0}\n{1}\n", exception.Message, exception.StackTrace));
}
else
{
CitizenFX.Core.Debug.Write(String.Format("[GHMattiMySQL] [Failed Transaction] {0}\n", exception.Message));
}
// Don't try it, throw on failure
transaction.Rollback();
}
}
queryTime = timer.ElapsedMilliseconds;
}
}
timer.Stop();
PrintDebugInformation(connectionTime, queryTime, 0, "Transaction");
return(result);
}, CancellationToken.None, TaskCreationOptions.None, queryScheduler);
/// <summary>
/// This is the actual query wrapper where you read from the database more than a singular value
/// </summary>
/// <param name="query">Query string</param>
/// <param name="parameters">Parameters in dictionary form</param>
/// <returns>Result of the Query, List of rows containing dictionarys representing each row</returns>
public Task <MySQLResult> QueryResult(string query, IDictionary <string, dynamic> parameters = null) => Task.Factory.StartNew(() =>
{
MySQLResult result = new MySQLResult();
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
long connectionTime = 0, queryTime = 0, readTime = 0;
using (Connection db = new Connection(settings.ConnectionString))
{
timer.Start();
db.connection.Open();
connectionTime = timer.ElapsedMilliseconds;
using (MySqlCommand cmd = db.connection.CreateCommand())
{
cmd.CommandText = query;
cmd.AddParameters(parameters);
try
{
timer.Restart();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
queryTime = timer.ElapsedMilliseconds;
timer.Restart();
while (reader.Read())
{
result.Add(Enumerable.Range(0, reader.FieldCount).ToDictionary(reader.GetName,
i => (reader.IsDBNull(i)) ? null : reader.GetValue(i)));
}
if (settings.Debug)
{
query = cmd.Stringify();
}
}
readTime = timer.ElapsedMilliseconds;
}
catch (MySqlException mysqlEx)
{
PrintErrorInformation(mysqlEx);
}
// I don't think I want to catch the other exceptions. Just throw for now.
}
}
timer.Stop();
PrintDebugInformation(connectionTime, queryTime, readTime, query);
return(result);
}, CancellationToken.None, TaskCreationOptions.None, queryScheduler);
/// <summary>
/// This is the ExecuteNonQuery command wrapper
/// </summary>
/// <param name="query">Query string</param>
/// <param name="parameters">Parameters in dictionary form</param>
/// <param name="isInsert">If true, then the return value will be the last inserted id</param>
/// <returns>rows affected</returns>
public Task <long> Query(string query, IDictionary <string, dynamic> parameters = null, bool isInsert = false) => Task.Factory.StartNew(() =>
{
long result = -1;
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
long connectionTime = 0, queryTime = 0;
using (Connection db = new Connection(settings.ConnectionString))
{
timer.Start();
db.connection.Open();
connectionTime = timer.ElapsedMilliseconds;
try
{
using (MySqlCommand cmd = db.connection.CreateCommand())
{
cmd.CommandText = query;
cmd.AddParameters(parameters);
timer.Restart();
result = cmd.ExecuteNonQuery();
queryTime = timer.ElapsedMilliseconds;
if (isInsert)
{
result = cmd.LastInsertedId;
}
if (settings.Debug)
{
query = cmd.Stringify();
}
}
}
catch (MySqlException mysqlEx)
{
PrintErrorInformation(mysqlEx);
}
// I don't think I want to catch the other exceptions. Just throw for now.
}
timer.Stop();
PrintDebugInformation(connectionTime, queryTime, 0, query);
return(result);
}, CancellationToken.None, TaskCreationOptions.None, queryScheduler);
/// <summary>
/// This is the ExecuteScalar wrapper
/// </summary>
/// <param name="query">Query string</param>
/// <param name="parameters">Parameters in dictionary form</param>
/// <returns>A singular value selected, like SELECT 1; => 1</returns>
public Task <object> QueryScalar(string query, IDictionary <string, dynamic> parameters = null) => Task.Factory.StartNew(() =>
{
object result = null;
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
long connectionTime = 0, queryTime = 0;
using (Connection db = new Connection(settings.ConnectionString))
{
timer.Start();
db.connection.Open();
connectionTime = timer.ElapsedMilliseconds;
using (MySqlCommand cmd = db.connection.CreateCommand())
{
cmd.CommandText = query;
cmd.AddParameters(parameters);
try
{
timer.Restart();
result = cmd.ExecuteScalar();
queryTime = timer.ElapsedMilliseconds;
if (result != null && result.GetType() == typeof(DBNull))
{
result = null;
}
if (settings.Debug)
{
query = cmd.Stringify();
}
}
catch (MySqlException mysqlEx)
{
PrintErrorInformation(mysqlEx);
}
// I don't think I want to catch the other exceptions. Just throw for now.
}
}
timer.Stop();
PrintDebugInformation(connectionTime, queryTime, 0, query);
return(result);
}, CancellationToken.None, TaskCreationOptions.None, queryScheduler);
/// <summary>
/// Execute a transaction
/// </summary>
/// <param name="cmd">MySqlCommand to be used for the transaction</param>
/// <returns>bool transactionSucceeded</returns>
protected override bool Execute(MySqlCommand cmd)
{
bool result = false;
CommandText = "Transaction";
using (MySqlTransaction transaction = connection.BeginTransaction())
{
cmd.AddParameters(Parameters);
cmd.Transaction = transaction;
try
{
foreach (string query in Commands)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
transaction.Commit();
result = true;
}
catch (Exception exception)
{
transaction.Rollback();
if (Debug)
{
CitizenFX.Core.Debug.Write(String.Format("[GHMattiMySQL] [Failed Transaction] {0}\n{1}\n", exception.Message, exception.StackTrace));
}
else
{
CitizenFX.Core.Debug.Write(String.Format("[GHMattiMySQL] [Failed Transaction] {0}\n", exception.Message));
}
}
}
return(result);
}
public Task <MySQLResult> QueryResult(string query, IDictionary <string, dynamic> parameters = null) => Task.Factory.StartNew(() => {
MySQLResult result = new MySQLResult();
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
long connectionTime = 0, queryTime = 0, readTime = 0;
using (Connection db = new Connection(connectionString))
{
timer.Start();
db.connection.Open();
connectionTime = timer.ElapsedMilliseconds;
using (MySqlCommand cmd = db.connection.CreateCommand())
{
cmd.CommandText = query;
cmd.AddParameters(parameters);
timer.Restart();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
queryTime = timer.ElapsedMilliseconds;
timer.Restart();
while (reader.Read())
{
result.Add(Enumerable.Range(0, reader.FieldCount).ToDictionary(reader.GetName, reader.GetValue));
}
}
readTime = timer.ElapsedMilliseconds;
}
}
timer.Stop();
PrintDebugInformation(connectionTime, queryTime, readTime, query);
return(result);
}, CancellationToken.None, TaskCreationOptions.None, queryScheduler);