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 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 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 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 Connect()
{
connectify = new MySqlConnection();
try
{
connectify.ConnectionString = "server= localhost; User Id = " + "root" + ";" + "database= onlineticket; Password="******"jaied";
connectify.Open();
}
catch (Exception e)
{
}
}
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();
}
}
/// <summary>
/// Dapper ORM Update function
/// </summary>
/// <param name="t"></param>
public void Update(T t, bool emptyStringNull = false)
{
try
{
var updateQuery = GenerateUpdateQuery(t);
if (emptyStringNull)
{
updateQuery = updateQuery.Replace("''", "null");
}
if (!string.IsNullOrEmpty(connString) && sqlConnection != null && sqlConnection.State == System.Data.ConnectionState.Open)
{
log.Debug(updateQuery);
if (transaction == null)
{
sqlConnection.Execute(updateQuery, t);
sqlConnection.Close();
}
else
{
sqlConnection.Execute(updateQuery, t, transaction);
}
}
else
{
sqlConnection = new MySqlConnector.MySqlConnection(connString);
sqlConnection.Open();
//cn.Open();
Update(t, emptyStringNull);
}
}
catch (Exception ex)
{
log.Error(ex);
if (transaction != null)
{
rollbackTransaction();
}
throw ex;
}
}
public void Insert <T>(T obj, Definition definition) where T : class
{
var connection = definition.Connection;
if (string.IsNullOrEmpty(connection))
{
connection = _connection;
}
using (var c = new MySqlData.MySqlConnection(connection))
{
c.Open();
//NOTE: we can not use DapperExtensions here as they do not support cancellation tokens
var sql = _sql.Insert(_sql.Configuration.GetMap <T>());
var command = new CommandDefinition(sql, obj, cancellationToken: definition.Token, commandTimeout: definition.CommandTimeout);
_log.LogTrace <MySqlQuery>($"INSERT: {definition.Sql}. Connection: {connection}. Object: {JsonConvert.SerializeObject(obj)}. Parameters: {JsonConvert.SerializeObject(definition.Parameters)}");
c.ExecuteScalarAsync <T>(command).GetAwaiter().GetResult();
}
}
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());
}
/// <summary>
/// Dapper ORM Insert function
/// </summary>
/// <param name="t"></param>
public void Insert(T t, bool emptyStringNull = false)
{
var insertQuery = GenerateInsertQuery(t).Replace("上午", "").Replace("下午", "");
try
{
if (emptyStringNull)
{
insertQuery = insertQuery.Replace("''", "null");
}
if (!string.IsNullOrEmpty(connString) && sqlConnection != null && sqlConnection.State == System.Data.ConnectionState.Open)
{
log.Debug(insertQuery);
if (transaction == null)
{
sqlConnection.Execute(insertQuery, t);
sqlConnection.Close();
}
else
{
sqlConnection.Execute(insertQuery, t, transaction);
}
}
else
{
sqlConnection = new MySqlConnector.MySqlConnection(connString);
sqlConnection.Open();
//cn.Open();
Insert(t, emptyStringNull);
}
}
catch (Exception ex)
{
log.Error(ex);
throw ex;
}
}
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);
}
/// <summary>
/// connects database
/// </summary>
/// <returns>return code</returns>
public int connect()
{
int retCode = 1;
try
{
if (sqlConnection == null)
{
sqlConnection = new MySqlConnector.MySqlConnection(connString);
sqlConnection.Open();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
switch (ex.Number)
{
case 0:
//Console.WriteLine("無法連線到資料庫.");
log.Error("無法連線到資料庫.");
retCode = 0;
break;
case 1045:
//Console.WriteLine("使用者帳號或密碼錯誤,請再試一次.");
log.Error("使用者帳號或密碼錯誤,請再試一次.");
retCode = 0;
break;
}
}
catch (Exception ex)
{
retCode = 0;
log.Error(ex + ex.StackTrace);
}
return(retCode);
}
public void Delete <T>(T obj, Definition definition) where T : class, IModel
{
var connection = definition.Connection;
if (string.IsNullOrEmpty(connection))
{
connection = _connection;
}
using (var c = new MySqlData.MySqlConnection(connection))
{
c.Open();
//NOTE: we can not use DapperExtensions here as they do not support cancellation tokens
var sql = _sql.Delete(_sql.Configuration.GetMap <T>(), definition.Predicate, definition.Dictionary);
var parameters = new DynamicParameters();
var map = _sql.Configuration.GetMap <T>();
var columns = map.Properties.Where(p => !(p.Ignored || p.IsReadOnly || p.KeyType == KeyType.Identity));
foreach (var property in ReflectionHelper.GetObjectValues(obj).Where(property => columns.Any(c => c.Name == property.Key)))
{
parameters.Add(property.Key, property.Value);
}
foreach (var parameter in definition.Dictionary)
{
parameters.Add(parameter.Key, parameter.Value);
}
var command = new CommandDefinition(sql, parameters, cancellationToken: definition.Token, commandTimeout: definition.CommandTimeout);
_log.LogTrace <MySqlQuery>($"DELETE: {definition.Sql}. Connection: {connection}. Object: {JsonConvert.SerializeObject(obj)}. Parameters: {JsonConvert.SerializeObject(definition.Parameters)}");
var result = c.ExecuteScalarAsync <T>(command).GetAwaiter().GetResult();
}
}
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();
}
}
}