internal void close()
{
try
{
if (sqlConnection != null)
{
sqlConnection.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Execute(string connection, string sql, object parameters)
{
using (var mysql = new MySqlData.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"EXECUTE: {sql}. Parameters: {JsonConvert.SerializeObject(parameters)}");
mysql.Open();
mysql.Execute(sql, parameters);
mysql.Close();
}
}
public void Delete <T>(string connection, T obj) where T : class, IModel
{
using (var c = new MySqlData.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"DELETE: {nameof(obj)}. Object: {JsonConvert.SerializeObject(obj)}");
c.Open();
c.Delete(obj);
c.Close();
}
}
public void Update <T>(string connection, T obj) where T : class
{
using (var c = new MySqlData.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"UPDATE: {nameof(obj)}. Connection: {connection}. Object: {JsonConvert.SerializeObject(obj)}");
c.Open();
c.Update(obj);
c.Close();
}
}
public void Insert <T>(T obj) where T : class
{
using (var connection = new MySqlData.MySqlConnection(_connection))
{
_log.LogTrace <MySqlQuery>($"INSERT: {nameof(obj)}. Object: {JsonConvert.SerializeObject(obj)}");
connection.Open();
connection.Insert(obj);
connection.Close();
}
}
public async Task DeleteAsync <T>(string connection, T obj) where T : class, IModel
{
await Task.Run(() => {
using (var c = new MySqlData.MySqlConnection(connection))
{
c.Open();
c.Delete(obj);
c.Close();
}
});
}
public async Task DeleteAsync <T>(T obj) where T : class, IModel
{
await Task.Run(() => {
using (var connection = new MySqlData.MySqlConnection(_connection))
{
connection.Open();
connection.Delete(obj);
connection.Close();
}
});
}
public bool closeConnection()
{
try
{
connectify.Close();
return(true);
}
catch (Exception e)
{
return(false);
}
}
public async Task InsertAsync <T>(T obj) where T : class
{
await Task.Run(() => {
using (var connection = new MySqlData.MySqlConnection(_connection))
{
connection.Open();
connection.Insert(obj);
connection.Close();
}
});
}
public async Task InsertAsync <T>(string connection, T obj) where T : class
{
await Task.Run(() => {
using (var c = new MySqlData.MySqlConnection(connection))
{
c.Open();
c.Insert(obj);
c.Close();
}
});
}
public T ExecuteScalar <T>(string connection, string sql, object parameters)
{
T result = default;
using (var c = new MySqlData.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"EXECUTE: {sql}. Parameters: {JsonConvert.SerializeObject(parameters)}");
c.Open();
result = c.ExecuteScalar <T>(sql, parameters);
c.Close();
}
return(result);
}
public List <T> Get <T>(string connection, string sql, object parameters)
{
var result = new List <T>();
//var test = new MySql.Data.MySqlClient()
using (var c = new MySqlData.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"SELECT: {sql}. Connection: {connection}. Parameters: {JsonConvert.SerializeObject(parameters)}");
c.Open();
result = c.Query <T>(sql, parameters).ToList();
c.Close();
}
return(result.ToList());
}
public void Execute(Definition definition)
{
var connection = definition.Connection;
if (string.IsNullOrEmpty(connection))
{
connection = _connection;
}
using (var mysql = new MySqlData.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"EXECUTE: {definition.Sql}. Parameters: {JsonConvert.SerializeObject(definition.Parameters)}");
mysql.Open();
mysql.ExecuteAsync(new CommandDefinition(definition.Sql, definition.Parameters, cancellationToken: definition.Token, commandTimeout: definition.CommandTimeout)).GetAwaiter().GetResult();
mysql.Close();
}
}
public List <T> Get <T>(Definition definition)
{
var result = new List <T>();
var connection = definition.Connection;
if (string.IsNullOrEmpty(connection))
{
connection = _connection;
}
using (var mysql = new MySqlData.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"SELECT: {definition.Sql}. Connection: {connection}. Parameters: {JsonConvert.SerializeObject(definition.Parameters)}");
mysql.Open();
result = mysql.QueryAsync <T>(new CommandDefinition(definition.Sql, definition.Parameters, cancellationToken: definition.Token, commandTimeout: definition.CommandTimeout)).GetAwaiter().GetResult().ToList();
mysql.Close();
}
return(result.ToList());
}
public T ExecuteScalar <T>(Definition definition)
{
var connection = definition.Connection;
if (string.IsNullOrEmpty(connection))
{
connection = _connection;
}
T result = default;
using (var c = new MySqlData.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"EXECUTE: {definition.Sql}. Parameters: {JsonConvert.SerializeObject(definition.Parameters)}");
c.Open();
result = c.ExecuteScalarAsync <T>(new CommandDefinition(definition.Sql, definition.Parameters, cancellationToken: definition.Token, commandTimeout: definition.CommandTimeout)).GetAwaiter().GetResult();
c.Close();
}
return(result);
}
public void InsertBatch <T>(string connection, List <T> models, int batch = 0) where T : class, IModel
{
var mysql = new MySqlData.MySqlConnectionStringBuilder(connection);
var database = mysql.Database;
using (var c = new MySqlData.MySqlConnection(connection))
{
c.Open();
try
{
var map = _sql?.Configuration?.GetMap <T>();
if (map == null)
{
throw new Exception($"Class Map for:{typeof(T).Name} could not be found.");
}
var name = map.TableName;
var table = models.ToDataTable();
if (table.Rows.Count == 0)
{
return;
}
var builder = new StringBuilder();
builder.Append("SELECT TABLE_NAME");
builder.Append(", COLUMN_NAME");
builder.Append(", DATA_TYPE");
builder.Append(", CHARACTER_MAXIMUM_LENGTH");
builder.Append(", CHARACTER_OCTET_LENGTH");
builder.Append(", NUMERIC_PRECISION");
builder.Append(", NUMERIC_SCALE AS SCALE");
builder.Append(", COLUMN_DEFAULT");
builder.Append(", IS_NULLABLE");
builder.Append(" FROM INFORMATION_SCHEMA.COLUMNS");
builder.Append(" WHERE TABLE_NAME = @Table");
builder.Append(" AND TABLE_SCHEMA = @Database");
var schema = new List <Schema>();
//get table schema (e.g. names and datatypes for mapping)
using (var command = new MySqlData.MySqlCommand(builder.ToString(), c))
{
var parameter = new MySqlData.MySqlParameter();
parameter.Value = map.TableName;
parameter.ParameterName = "@Table";
parameter.MySqlDbType = MySqlData.MySqlDbType.String;
var parameter2 = new MySqlData.MySqlParameter();
parameter2.Value = database;
parameter2.ParameterName = "@Database";
parameter2.MySqlDbType = MySqlData.MySqlDbType.String;
command.Parameters.Add(parameter);
command.Parameters.Add(parameter2);
using (var sql = new MySqlData.MySqlDataAdapter(command))
{
var result = new DataTable();
var parameters = map.Properties
.Where(x => x.Ignored == false)
.Where(x => x.IsReadOnly == false)
.Where(x => x.KeyType == KeyType.NotAKey);
sql.Fill(result);
schema = (from p in parameters
join s in result.AsEnumerable() on p.ColumnName equals s.Field <string>("COLUMN_NAME")
select new Schema()
{
ColumnName = s.Field <string>("COLUMN_NAME"),
DataType = s.Field <string>("DATA_TYPE"),
Size = s.Field <object>("CHARACTER_OCTET_LENGTH")
}).ToList();
}
}
using (var command = new MySqlData.MySqlCommand($"INSERT INTO {map.TableName} ({string.Join(",", schema.Select(x => x.ColumnName))}) VALUES ({string.Join(",", schema.Select(x => $"@{x.ColumnName}"))});", c))
{
command.UpdatedRowSource = UpdateRowSource.None;
foreach (var type in schema)
{
var parameter = new MySqlData.MySqlParameter();
parameter.ParameterName = $"@{type.ColumnName}";
parameter.SourceColumn = type.ColumnName;
switch (type.DataType.ToLower())
{
case "varchar":
case "char":
case "text":
parameter.MySqlDbType = MySqlData.MySqlDbType.String;
parameter.Size = Int32.Parse(type.Size.ToString());
break;
case "datetime":
parameter.MySqlDbType = MySqlData.MySqlDbType.DateTime;
break;
case "int":
parameter.MySqlDbType = MySqlData.MySqlDbType.Int32;
break;
case "bigint":
parameter.MySqlDbType = MySqlData.MySqlDbType.Int64;
break;
default:
throw new NotImplementedException();
}
command.Parameters.Add(parameter);
}
using (var adapter = new MySqlData.MySqlDataAdapter())
{
adapter.InsertCommand = command;
var timer = Stopwatch.StartNew();
_log.LogTrace <MySqlQuery>($"Bulk Insert on {name}. {models.Count} rows queued for insert.");
timer.Start();
if (batch > 0)
{
adapter.UpdateBatchSize = 100;
}
adapter.Update(table);
_log.LogTrace <MySqlQuery>($"Bulk Insert on {name} complete in: {timer.Elapsed.ToString(@"hh\:mm\:ss\:fff")}");
}
}
}
finally
{
c.Close();
}
}
}
