public override async Task<bool> updateAsync(object table)
{
var cliente = this.getCastCliente(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = @"UPDATE hotelcolonia.cliente SET numpersonas=?numpersonas,nombre=?nombre,
lugar_procedencia=?lugar_procedencia,correo=?correo,numtarjeta=?numtarjeta,telefono=?telefono
WHERE id_cliente=?id_cliente"
};
MidbConexion.Open();
miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
miComando.Parameters.AddWithValue("?numpersonas", cliente.acompaniantes);
miComando.Parameters.AddWithValue("?nombre", cliente.nombre);
miComando.Parameters.AddWithValue("?lugar_procedencia", cliente.origen);
miComando.Parameters.AddWithValue("?correo", cliente.email);
miComando.Parameters.AddWithValue("?numtarjeta", cliente.tarjeta);
miComando.Parameters.AddWithValue("?telefono", cliente.telefono);
await miComando.ExecuteNonQueryAsync();
await new CrudHabitacion().updateAsync(cliente.selectedHabitacion.numero, true);
}
return true;
}
public override async Task<bool> addAsync(object table)
{
var cliente = this.getCastCliente(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = @"INSERT INTO cliente(id_cliente,numpersonas,nombre,lugar_procedencia,correo,numtarjeta,telefono)
VALUES (?id_cliente,?numpersonas,?nombre,?lugar_procedencia,?correo,?numtarjeta,?telefono)"
};
MidbConexion.Open();
miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
miComando.Parameters.AddWithValue("?numpersonas", cliente.acompaniantes);
miComando.Parameters.AddWithValue("?nombre", cliente.nombre);
miComando.Parameters.AddWithValue("?telefono", cliente.telefono);
miComando.Parameters.AddWithValue("?lugar_procedencia", cliente.origen);
miComando.Parameters.AddWithValue("?correo", cliente.email);
miComando.Parameters.AddWithValue("?numtarjeta", cliente.tarjeta);
await miComando.ExecuteNonQueryAsync();
miComando.Parameters.Clear();
miComando.CommandText = @"INSERT INTO control_reservacion(clv_reservacion,dia_entrada,dia_salida,hora_entrada,hora_salida,id_cliente)
VALUES (?clv_reservacion,?dia_entrada,?dia_salida,?hora_entrada,?hora_salida,?id_cliente)";
int claveReservacion = await this.getMaxAsync("control_reservacion", "clv_reservacion");
miComando.Parameters.AddWithValue("?clv_reservacion", claveReservacion);
miComando.Parameters.AddWithValue("?dia_entrada", cliente.dateEntrada);
miComando.Parameters.AddWithValue("?dia_salida", cliente.dateSalida);
miComando.Parameters.AddWithValue("?hora_entrada", cliente.dateEntrada.ToShortTimeString());
miComando.Parameters.AddWithValue("?hora_salida", cliente.dateSalida.ToShortTimeString());
miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
await miComando.ExecuteNonQueryAsync();
miComando.Parameters.Clear();
miComando.CommandText = @"INSERT INTO detalle_reservacion(clv_reservacion,num_habitacion)
VALUES(?clv_reservacion,?num_habitacion)";
miComando.Parameters.AddWithValue("?clv_reservacion", claveReservacion);
miComando.Parameters.AddWithValue("?num_habitacion", cliente.selectedHabitacion.numero);
await miComando.ExecuteNonQueryAsync();
miComando.Parameters.Clear();
int claveGasto = await this.getMaxAsync("gastos","clv_gasto");
miComando.CommandText = "INSERT INTO gastos(clv_gasto,id_cliente) VALUES (?clv_gasto,?id_cliente)";
miComando.Parameters.AddWithValue("clv_gasto", claveGasto);
miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
await miComando.ExecuteNonQueryAsync();
await new CrudHabitacion().updateAsync(cliente.selectedHabitacion.numero, true);
return true;
}
}
public async Task ExecutarComandoSQLAsync(string comandoSql)
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(commStr);
await conn.OpenAsync();
MySql.Data.MySqlClient.MySqlCommand comando = new MySql.Data.MySqlClient.MySqlCommand(comandoSql, conn);
await comando.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
public override async Task<bool> addAsync(object table)
{
var orden = this.getTable(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MidbConexion.Open();
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = "INSERT INTO orden(clv_orden,nummesa,fecha,id_cliente) VALUES (?clv_orden,?nummesa,?fecha,?id_cliente)"
};
miComando.Parameters.AddWithValue("?clv_orden", orden.claveOrden);
miComando.Parameters.AddWithValue("?nummesa", orden.numMesa);
miComando.Parameters.AddWithValue("?fecha", orden.fechaOrden);
miComando.Parameters.AddWithValue("?id_cliente", orden.idCliente);
await miComando.ExecuteNonQueryAsync();
miComando.Parameters.Clear();
foreach (var producto in listProducto)
{
for (int i = producto.cantidad;i>0;i--)
{
miComando.CommandText = "INSERT INTO detalle_orden(clv_orden,clv_producto) VALUES(?clv_orden,?clv_producto)";
miComando.Parameters.AddWithValue("?clv_orden", orden.claveOrden);
miComando.Parameters.AddWithValue("?clv_producto", producto.clvProducto);
await miComando.ExecuteNonQueryAsync();
miComando.Parameters.Clear();
}
}
miComando.CommandText = "INSERT INTO detalle_gasto(clv_gasto,clv_orden) VALUES(?clv_gasto,?clv_orden)";
miComando.Parameters.AddWithValue("?clv_gasto",await new CrudCliente().getClienteGasto(orden.idCliente));
miComando.Parameters.AddWithValue("?clv_orden", orden.claveOrden);
await miComando.ExecuteNonQueryAsync();
return true;
}
}
public override async Task<bool> addAsync(object table)
{
var usuario = getCastUsuario(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MidbConexion.Open();//Abrimos la conexion para realizar los siguientes pasos
// 'Declaro comando para poder realizar mis instrucciones sql
MySqlCommand micomando = new MySqlCommand { Connection = MidbConexion, CommandText = "INSERT INTO usuario(nombre,password) VALUES (?Nom,?Pass)" };
micomando.Parameters.AddWithValue("?Nom", usuario.nombre);
micomando.Parameters.AddWithValue("?Pass", usuario.password);
await micomando.ExecuteNonQueryAsync();
return true;
}
}
public async void add_app_to_store(string title, string description, double cost, int dev_id, bool in_app, string package_loc)
{
int retryCount = 3;
bool success = false;
MySqlCommand cmd = new MySqlCommand();
while (retryCount > 0 && !success)
{
try
{
conn.Open();
//->insert relevant data
cmd.Connection = conn;
cmd.CommandText = "COMMIT; ";
cmd.CommandText += "INSERT INTO software(title, description, dev_id, cost, in_app_purchases, package_location)" +
"VALUES(@title, @description, @dev_id, @cost, @in_app_purchases, @package_location);";
cmd.CommandText += "COMMIT; ";
cmd.Prepare();
//->add parameters to query
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@dev_id", dev_id); //->Datetime now in current timezone
cmd.Parameters.AddWithValue("@cost", cost); //-> we are not actually using a bank, so its always a valid payment
cmd.Parameters.AddWithValue("@in_app_purchases", in_app);
cmd.Parameters.AddWithValue("@package_location", package_loc);
await cmd.ExecuteNonQueryAsync();
success = true;
if (conn != null)
conn.Close();
return;
}
catch (MySqlException ex)
{
rollback(cmd);
Thread.Sleep(500);
if (ex.Number != 1205)
{
// a sql exception that is not a deadlock
throw;
}
// Add delay here if you wish.
retryCount--;
if (retryCount == 0) throw;
}
}
}
public override async Task<bool> deleteAsync(object table)
{
var producto = getCastProducto(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MidbConexion.Open();
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = @"DELETE FROM PRODUCTO WHERE clv_producto=?clv_producto"
};
miComando.Parameters.AddWithValue("?clv_producto", producto.clvProducto);
await miComando.ExecuteNonQueryAsync();
return true;
}
}
public override async Task<bool> deleteAsync(object table)
{
var cliente = this.getCastCliente(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
//http://blog.openalfa.com/como-trabajar-con-restricciones-de-clave-externa-en-mysql
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = "DELETE FROM cliente WHERE id_cliente =?id_cliente"
};
MidbConexion.Open();
miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
await miComando.ExecuteNonQueryAsync();
}
return true;
}
public override async Task<bool> updateAsync(object table)
{
var habitacion = getCastHabitacion(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MidbConexion.Open();
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = "UPDATE habitacion SET estado=?estado,tamanio=?tamanio,precio=?precio,telefono=?telefono WHERE num_habitacion=?habitacion"
};
miComando.Parameters.AddWithValue("?estado", habitacion.estado == true ? "Ocupado" : "Disponible");
miComando.Parameters.AddWithValue("?tamanio", habitacion.tamanio);
miComando.Parameters.AddWithValue("?precio", habitacion.precio);
miComando.Parameters.AddWithValue("?telefono", habitacion.telefono);
miComando.Parameters.AddWithValue("?habitacion", habitacion.numero);
await miComando.ExecuteNonQueryAsync();
return true;
}
}
public override async Task<bool> addAsync(object table)
{
var habitacion = getCastHabitacion(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MidbConexion.Open();
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = "INSERT INTO habitacion(num_habitacion,estado,tamanio,precio,telefono) VALUES (?habitacion,?estado,?tamanio,?precio,?telefono)"
};
miComando.Parameters.AddWithValue("?estado", habitacion.estado == true ? "Ocupado" : "Disponible");
miComando.Parameters.AddWithValue("?tamanio", habitacion.tamanio);
miComando.Parameters.AddWithValue("?precio", Convert.ToDouble(habitacion.precio));
miComando.Parameters.AddWithValue("?telefono", habitacion.telefono);
miComando.Parameters.AddWithValue("?habitacion", habitacion.numero);
await miComando.ExecuteNonQueryAsync();
return true;
}
}
public override async Task<bool> updateAsync(object table)
{
var producto = getCastProducto(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MidbConexion.Open();
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = @"UPDATE PRODUCTO SET descripcion=?descripcion, precio=?precio, cantidad=?cantidad, img_producto=?img_producto,categoria=?categoria
WHERE clv_producto=?clv_producto"
};
miComando.Parameters.AddWithValue("?clv_producto", producto.clvProducto);
miComando.Parameters.AddWithValue("?descripcion", producto.descripcion);
miComando.Parameters.AddWithValue("?precio", producto.precio);
miComando.Parameters.AddWithValue("?cantidad", producto.cantidad);
miComando.Parameters.AddWithValue("?categoria", producto.categoria);
miComando.Parameters.AddWithValue("?img_producto", producto.imgProducto);
await miComando.ExecuteNonQueryAsync();
return true;
}
}
private async ValueTask <MySqlTransaction> BeginDbTransactionAsync(IsolationLevel isolationLevel, IOBehavior ioBehavior, CancellationToken cancellationToken)
{
if (State != ConnectionState.Open)
{
throw new InvalidOperationException("Connection is not open.");
}
if (CurrentTransaction is object)
{
throw new InvalidOperationException("Transactions may not be nested.");
}
#if !NETSTANDARD1_3
if (m_enlistedTransaction is object)
{
throw new InvalidOperationException("Cannot begin a transaction when already enlisted in a transaction.");
}
#endif
string isolationLevelValue = isolationLevel switch
{
IsolationLevel.ReadUncommitted => "read uncommitted",
IsolationLevel.ReadCommitted => "read committed",
IsolationLevel.RepeatableRead => "repeatable read",
IsolationLevel.Serializable => "serializable",
// "In terms of the SQL:1992 transaction isolation levels, the default InnoDB level is REPEATABLE READ." - http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-model.html
IsolationLevel.Unspecified => "repeatable read",
_ => throw new NotSupportedException("IsolationLevel.{0} is not supported.".FormatInvariant(isolationLevel))
};
using (var cmd = new MySqlCommand("set transaction isolation level " + isolationLevelValue + "; start transaction;", this))
await cmd.ExecuteNonQueryAsync(ioBehavior, cancellationToken).ConfigureAwait(false);
var transaction = new MySqlTransaction(this, isolationLevel);
CurrentTransaction = transaction;
return(transaction);
}
/// <summary>
/// 在【主库】执行
/// </summary>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
async public Task <int> ExecuteNonQueryAsync(CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms)
{
DateTime dt = DateTime.Now;
string logtxt = "";
Object <MySqlConnection> conn = null;
MySqlCommand cmd = PrepareCommandAsync(cmdType, cmdText, cmdParms, ref logtxt);
DateTime logtxt_dt = DateTime.Now;
int val = 0;
Exception ex = null;
try {
if (cmd.Connection == null)
{
cmd.Connection = (conn = await this.MasterPool.GetAsync()).Value;
}
val = await cmd.ExecuteNonQueryAsync();
} catch (Exception ex2) {
ex = ex2;
}
if (conn != null)
{
if (IsTracePerformance)
{
logtxt_dt = DateTime.Now;
}
this.MasterPool.Return(conn, ex);
if (IsTracePerformance)
{
logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms";
}
}
LoggerException(this.MasterPool, cmd, ex, dt, logtxt);
cmd.Parameters.Clear();
return(val);
}
internal async Task RollbackAsync(IOBehavior ioBehavior, CancellationToken cancellationToken)
{
VerifyNotDisposed();
if (m_isFinished)
{
throw new InvalidOperationException("Already committed or rolled back.");
}
if (m_connection.CurrentTransaction == this)
{
using (var cmd = new MySqlCommand("rollback", m_connection, this))
await cmd.ExecuteNonQueryAsync(ioBehavior, cancellationToken).ConfigureAwait(false);
m_connection.CurrentTransaction = null;
m_isFinished = true;
}
else if (m_connection.CurrentTransaction != null)
{
throw new InvalidOperationException("This is not the active transaction.");
}
else if (m_connection.CurrentTransaction == null)
{
throw new InvalidOperationException("There is no active transaction.");
}
}
private async Task CommitAsync(IOBehavior ioBehavior, CancellationToken cancellationToken)
{
VerifyNotDisposed();
if (Connection is null)
{
throw new InvalidOperationException("Already committed or rolled back.");
}
if (Connection.CurrentTransaction == this)
{
using (var cmd = new MySqlCommand("commit", Connection, this))
await cmd.ExecuteNonQueryAsync(ioBehavior, cancellationToken).ConfigureAwait(false);
Connection.CurrentTransaction = null;
Connection = null;
}
else if (Connection.CurrentTransaction is object)
{
throw new InvalidOperationException("This is not the active transaction.");
}
else if (Connection.CurrentTransaction is null)
{
throw new InvalidOperationException("There is no active transaction.");
}
}
// MySql
private static void InsertIntoMySqlDatabase(MySqlConnection con, QueryLanguage ql)
{
try
{
con.Open();
}
catch (MySqlException)
{
MessageBox.Show("Unable to open connection to MySQL Database while attempting to insert data. Please recheck your credentials and try again", "Potential User Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
mw.ResetButtons();
}
List<BibleCollection> LanguageCollection = new List<BibleCollection>();
LanguageCollection = MemoryStorage.FullDataCollection[(int)ql].BibleCollection;
StringBuilder sb = new StringBuilder();
bool firstStringFinished = false;
foreach (var fc in LanguageCollection)
{
if (!firstStringFinished)
{
sb.Append("INSERT INTO " + ql.ToString() + "(Book, Chapter, Verse, Word) " + Environment.NewLine + " VALUES('" + fc.CurrentBook + "', " + fc.Chapter + ", " + fc.Verse + ", \"" + fc.Word + "\")" + Environment.NewLine);
firstStringFinished = true;
}
else
{
sb.Append(", ('" + fc.CurrentBook + "', " + fc.Chapter + ", " + fc.Verse + ", \"" + fc.Word + "\")" + Environment.NewLine);
}
MemoryStorage.CurrentQuery++;
mw.UpdateQueryProgress();
}
sb.Append(";" + Environment.NewLine + Environment.NewLine);
using (StreamWriter w = new StreamWriter("debug-me.sql", true))
{
w.Write(sb.ToString());
}
using (MySqlCommand cmd = new MySqlCommand(sb.ToString()))
{
cmd.Connection = con;
cmd.CommandTimeout = 999999;
try
{
cmd.ExecuteNonQueryAsync();
}
catch (MySqlException mse)
{
MessageBox.Show("Unable to insert data into MySQL database:" + Environment.NewLine + Environment.NewLine + mse, "Potential User Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
mw.ResetButtons();
}
}
con.Close();
}
public override async Task<bool>deleteAsync(object table)
{
var habitacion = getCastHabitacion(table);
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MidbConexion.Open();
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = "DELETE FROM habitacion WHERE num_habitacion=?habitacion"
};
miComando.Parameters.AddWithValue("?habitacion", habitacion.numero);
await miComando.ExecuteNonQueryAsync();
return true;
}
}
/// <param name="checkTable">Checks the datatables existence, creates if it does not exist</param>
/// <param name="parsedValues">The sound sensor values</param>
/// <param name="parsedInfo">The information from the sensor</param>
/// <param name="dateTime">The datetime of data arrival</param>
public static void storeDataExternal(byte[] parsedValues, byte[] parsedInfo, DateTime dateTime)
{
if (checkTable)
{
MySqlCommand cmd1 = new MySqlCommand("CREATE TABLE IF NOT EXISTS node" + parsedInfo[0] + " LIKE sensorTable", mySQLDB.connection);
cmd1.ExecuteNonQuery();
checkTable = false;
}
MySqlCommand cmd2 = new MySqlCommand("INSERT INTO node" + parsedInfo[0] + "(dateTime, positionX, positionY, sensOne, sensTwo, sensThr, sensFou, sensFiv, sensSix) VALUES(@dateTime, @positionX, @positionY, @sensOne, @sensTwo, @sensThr, @sensFou, @sensFiv, @sensSix)", mySQLDB.connection);
cmd2.Parameters.AddWithValue("@dateTime", dateTime.ToString("yyyy-MM-dd HH:mm:ss"));
cmd2.Parameters.AddWithValue("@positionX", parsedValues[0] * parsedValues[1]);
cmd2.Parameters.AddWithValue("@positionY", parsedValues[2] * parsedValues[3]);
cmd2.Parameters.AddWithValue("@sensOne", parsedValues[0]);
cmd2.Parameters.AddWithValue("@sensTwo", parsedValues[1]);
cmd2.Parameters.AddWithValue("@sensThr", parsedValues[2]);
cmd2.Parameters.AddWithValue("@sensFou", parsedValues[3]);
cmd2.Parameters.AddWithValue("@sensFiv", parsedValues[4]);
cmd2.Parameters.AddWithValue("@sensSix", parsedValues[5]);
cmd2.ExecuteNonQueryAsync();
}
public static void storeStringExternal(int node, string input)
{
if (checkTable)
{
MySqlCommand cmd1 = new MySqlCommand("CREATE TABLE IF NOT EXISTS node" + node + " LIKE sensorTable2", mySQLDB.connection);
cmd1.ExecuteNonQuery();
checkTable = false;
}
MySqlCommand cmd2 = new MySqlCommand("INSERT INTO node" + node + "(string) VALUES(@string)", mySQLDB.connection);
cmd2.Parameters.AddWithValue("@string", input);
cmd2.ExecuteNonQueryAsync();
}
public async Task<int> ExecuteNonQuery(MySqlCommand stmt)
{
int result = 0;
if (connState == ConnectionState.Open)
{
try
{
result = await stmt.ExecuteNonQueryAsync();
}
catch (MySqlException e)
{
Logger.LOG_DATABASE.Error("Could not execute prepared statement " + stmt.ToString() + " as NonQuery. " + e.Message);
}
}
return result;
}
public async Task ClearStateAsync(string grainType, GrainReference grainReference, GrainState grainState)
{
var con = await GetFreeConnection();
var table = GetTableName(grainState);
var key = GetKey(grainReference);
string query;
if (CustomTable)
query = string.Format("delete from `{0}` where `guid` = \"{1}\";", table, MySqlHelper.EscapeString(key));
else
query = string.Format("delete from `{0}` where `guid` = \"{1}\" AND `type` = \"{2}\";", table,
MySqlHelper.EscapeString(key), MySqlHelper.EscapeString(grainType));
MySqlCommand com = new MySqlCommand(query, con);
Log.Verbose(query);
await com.ExecuteNonQueryAsync();
com.Dispose();
await AddFreeConnection(con);
}
public async void create_account(string email, string password, DateTime dob, char gender, string phone, string name, int dev)
{ //-> insert user account into database
int retryCount = 3;
bool success = false;
MySqlCommand cmd = new MySqlCommand();
while (retryCount > 0 && !success)
{
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "START TRANSACTION; ";
cmd.CommandText += "INSERT INTO users(user_name, password, name, dob, gender, phone, dev_id)" +
"VALUES(@user_name, @password, @name, @dob, @gender, @phone, @dev_id);";
cmd.CommandText += "COMMIT; ";
cmd.Prepare();
//-> add parameters of this function to insert statement
cmd.Parameters.AddWithValue("@user_name", email);
cmd.Parameters.AddWithValue("@password", md5_encrypt(password));
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@dob", dob);
cmd.Parameters.AddWithValue("@gender", gender);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@dev_id", dev);
await cmd.ExecuteNonQueryAsync();
success = true;
}
catch (MySqlException ex)
{ //-> error handline
rollback(cmd);
if (ex.Number != 1205)
{
// a sql exception that is not a deadlock
throw;
}
// Add delay here if you wish.
Thread.Sleep(500);
retryCount--;
if (retryCount == 0) throw;
}
finally
{
if (conn != null)
conn.Close();
}
}
}
public async Task<bool>updateAsync(int num_habitacion,bool estado)
{
using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
{
MidbConexion.Open();
MySqlCommand miComando = new MySqlCommand()
{
Connection = MidbConexion,
CommandText = "UPDATE habitacion SET estado=?estado WHERE num_habitacion=?habitacion"
};
miComando.Parameters.AddWithValue("?estado", estado == true ? "Ocupado" : "Disponible");
miComando.Parameters.AddWithValue("?habitacion", num_habitacion);
await miComando.ExecuteNonQueryAsync();
return true;
}
}
private async void PasswordChange(string newPW)
{
string agentid = Convert.ToString(ICResponse.Properties.Settings.Default.AgentID);
string prequerystring = "UPDATE agents SET password='******' WHERE agentID='" + agentid + "'";
using (MySqlConnection dbConn = new MySqlConnection("Server=" + ICResponse.Properties.Settings.Default.DBServer +
";Database=" + ICResponse.Properties.Settings.Default.DBName +
";Uid=" + ICResponse.Properties.Settings.Default.DBUser +
";Pwd=" + ICResponse.Properties.Settings.Default.DBPass + ";"))
{
dbConn.Open();
using (MySqlCommand cmd = new MySqlCommand(prequerystring, dbConn))
{
await cmd.ExecuteNonQueryAsync();
}
dbConn.Close();
}
}
public async Task WriteStateAsync(string grainType, GrainReference grainReference, GrainState grainState)
{
var con = await GetFreeConnection();
var table = GetTableName(grainState);
var key = GetKey(grainReference);
var data = Newtonsoft.Json.JsonConvert.SerializeObject(grainState, Newtonsoft.Json.Formatting.Indented);
List<string> queries = new List<string>();
if (CustomTable)
{
queries.Add(string.Format("replace into `{0}` (`guid`, `data`) VALUE (\"{1}\", \"{2}\");", table, key,
MySqlHelper.EscapeString(data)));
}
else
{
queries.Add(string.Format("delete from `{0}` where `guid` =\"{1}\" and `type` = \"{2}\";", table, key,
MySqlHelper.EscapeString(grainType)));
queries.Add(
string.Format("insert into `{0}` (`guid`, `type`, `data`) VALUE (\"{1}\", \"{2}\", \"{3}\");", table,
key, MySqlHelper.EscapeString(grainType), MySqlHelper.EscapeString(data)));
}
foreach (var q in queries)
{
MySqlCommand com = new MySqlCommand(q, con);
Log.Verbose(q);
await com.ExecuteNonQueryAsync();
com.Dispose();
}
await AddFreeConnection(con);
}
public void Suggest(String filename, String mcversion, String modversion, String md5, String modid, String modname, String author = "")
{
try
{
if (!IsModSuggested(md5))
{
const string sql =
"INSERT INTO solderhelper.new(filename, mcversion, modversion, md5, modid, modname, author) VALUES(@filename, @mcversion, @modversion, @md5, @modid, @modname, @author);";
using (MySqlConnection connection = new MySqlConnection(_connectionStringSuggest))
{
connection.OpenAsync();
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@filename", filename);
command.Parameters.AddWithValue("@mcversion", mcversion);
command.Parameters.AddWithValue("@modversion", modversion);
command.Parameters.AddWithValue("@md5", md5);
command.Parameters.AddWithValue("@modid", modid);
command.Parameters.AddWithValue("@modname", modname);
command.Parameters.AddWithValue("@author", author);
command.ExecuteNonQueryAsync();
}
}
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.StackTrace);
if (e.InnerException != null)
{
Debug.WriteLine(e.InnerException.Message);
Debug.WriteLine(e.InnerException.StackTrace);
}
}
}
public async void update_software_tuple(int index, int dev_id, string tuple, string value)
{
int retryCount = 3;
bool success = false;
MySqlCommand cmd = new MySqlCommand();
while (retryCount > 0 && !success)
{
try
{
// your sql here
success = true;
conn.Open();
//->insert relevant data
cmd.Connection = conn;
cmd.CommandText = "START TRANSACTION; ";
cmd.CommandText += "Update software SET "+tuple+" = @value "+
"WHERE software_id = @index and dev_id = @dev_id;";
cmd.CommandText += "COMMIT; ";
cmd.Prepare();
//->add parameters to query
cmd.Parameters.AddWithValue("@dev_id", dev_id);
cmd.Parameters.AddWithValue("@index", index);
cmd.Parameters.AddWithValue("@value", value);
await cmd.ExecuteNonQueryAsync();
if (conn != null)
conn.Close();
}
catch (MySqlException ex)
{
rollback(cmd);
if (ex.Number != 1205)
{
// a sql exception that is not a deadlock
throw;
}
// Add delay here if you wish.
Thread.Sleep(500);
retryCount--;
if (retryCount == 0) throw;
}
}
}
private async Task <int> LoadAsync(IOBehavior ioBehavior, CancellationToken cancellationToken)
{
if (Connection == null)
{
throw new InvalidOperationException("Connection not set");
}
if (!string.IsNullOrWhiteSpace(FileName) && SourceStream != null)
{
throw new InvalidOperationException("Cannot set both FileName and SourceStream");
}
// LOCAL INFILE case
if (!string.IsNullOrWhiteSpace(FileName) && Local)
{
SourceStream = CreateFileStream(FileName);
FileName = null;
}
if (string.IsNullOrWhiteSpace(FileName) && SourceStream != null)
{
if (!Local)
{
throw new InvalidOperationException("Cannot use SourceStream when Local is not true.");
}
FileName = GenerateSourceStreamName();
lock (s_lock)
s_streams.Add(FileName, SourceStream);
}
if (string.IsNullOrWhiteSpace(FileName) || string.IsNullOrWhiteSpace(TableName))
{
// This is intentionally a different exception to what is thrown by MySql.Data because
// it does not handle null or empty FileName and TableName.
// The baseline client simply tries to use the given values, resulting in a NullReferenceException for
// a null FileName, a MySqlException with an inner FileStream exception for an empty FileName,
// and a MySqlException with a syntax error if the TableName is null or empty.
throw new InvalidOperationException("FileName or SourceStream, and TableName are required.");
}
bool closeConnection = false;
if (Connection.State != ConnectionState.Open)
{
closeConnection = true;
Connection.Open();
}
try
{
var commandString = BuildSqlCommand();
var cmd = new MySqlCommand(commandString, Connection, Connection.CurrentTransaction)
{
CommandTimeout = Timeout,
};
return(await cmd.ExecuteNonQueryAsync(ioBehavior, cancellationToken).ConfigureAwait(false));
}
finally
{
if (closeConnection)
{
Connection.Close();
}
}
}
public bool set_user_account_details(int uid, string email, string password, string name, DateTime dob, string phone)
{ //-> update user account details
int retryCount = 3;
bool success = false;
MySqlCommand cmd = new MySqlCommand();
while (retryCount > 0 && !success)
{
try
{
if (login(email.ToString(), password.ToString()))
{ //-> check login
cmd.Connection = conn;
cmd.CommandText = "START TRANSACTION; ";
cmd.CommandText += "UPDATE users SET user_name = @user_name, password = @password," +
"name = @name, dob = @dob, phone= @phone WHERE user_id = " + uid + ";";
cmd.CommandText += "COMMIT; ";
conn.Open();
cmd.Prepare();
//->add parameters to query
cmd.Parameters.AddWithValue("@user_name", email);
cmd.Parameters.AddWithValue("@password", md5_encrypt(password.ToString()));
cmd.Parameters.AddWithValue("@name", name); //-> Paypal, direct deposit, etc.
cmd.Parameters.AddWithValue("@dob", dob); //->Datetime now in current timezone
cmd.Parameters.AddWithValue("@phone", phone); //-> we are not actually using a bank, so its always a valid payment
cmd.ExecuteNonQueryAsync();
success = true;
if (conn != null)
conn.Close();
return true;
}
}
catch (MySqlException ex)
{
rollback(cmd);
if (ex.Number != 1205)
{
if (ex.Number != 1205)
{
// a sql exception that is not a deadlock
return false;
}
// Add delay here if you wish.
Thread.Sleep(500);
retryCount--;
if (retryCount == 0) throw;
}
else
{
return false;
}
}
}
return false;
}
public async void process_transaction(int sid, int uid, string p_method)
{ //->record users transaction details upon app purchase
int retryCount = 3;
bool success = false;
MySqlCommand cmd = new MySqlCommand();
while (retryCount > 0 && !success)
{
try
{
// your sql here
success = true;
conn.Open();
//->insert relevant data
cmd.Connection = conn;
cmd.CommandText = "START TRANSACTION; ";
cmd.CommandText += "INSERT INTO transaction(sid, uid, payment_method, date, confirmed)" +
"VALUES(@sid, @uid, @payment_method, @date, @confirmed);";
cmd.Prepare();
//->add parameters to query
cmd.Parameters.AddWithValue("@sid", sid);
cmd.Parameters.AddWithValue("@uid", uid);
cmd.Parameters.AddWithValue("@payment_method", p_method); //-> Paypal, direct deposit, etc.
cmd.Parameters.AddWithValue("@date", DateTime.UtcNow.ToString()); //->Datetime now in current timezone
cmd.Parameters.AddWithValue("@confirmed", 1); //-> we are not actually using a bank, so its always a valid payment
await cmd.ExecuteNonQueryAsync();
string stm = "SELECT payment_method from transaction where trans_id = last_insert_id();";
stm += "COMMIT;";
cmd = new MySqlCommand(stm, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
if (rdr.GetString(0) == "error")
Console.WriteLine("trigger activated upon bad value");
else
Console.WriteLine("succesful commit, trigger not activated");
Console.WriteLine("rolling back test");
rollback(cmd);
if (conn != null)
conn.Close();
}
catch (MySqlException ex)
{
rollback(cmd);
if (ex.Number != 1205)
{
// a sql exception that is not a deadlock
Console.WriteLine("generic error");
throw;
}
// Add delay here if you wish.
Console.WriteLine("deadlock detected");
Thread.Sleep(500);
retryCount--;
if (retryCount == 0) throw;
}
}
}
public async Task SaveToMysql(MySqlConnection conn, Res res)
{
if (await ExistsOnMysql(conn, res))
return;
byte[] image = null;
if (res.Oekaki != null)
{
var imagePath = cacheDir + ryu_s.MyCommon.Tool.SanitizeForFilename(res.Oekaki);
using (var fs = new FileStream(imagePath, FileMode.Open, FileAccess.Read))
{
using (var br = new BinaryReader(fs))
{
image = br.ReadBytes((int)fs.Length);
}
}
}
var query = $"INSERT INTO {communityId} ({col_resnum},{col_name},{col_trip},{col_id},{col_date},{col_body},{col_oekaki_url},{col_oekaki_image})"
+ $"values(@{col_resnum},@{col_name},@{col_trip},@{col_id},@{col_date},@{col_body},@{col_oekaki_url},@{col_oekaki_image})";
var cmd = new MySqlCommand(query, conn);
cmd.Parameters.Add($"@{col_resnum}", resnum_type);
cmd.Parameters.Add($"@{col_name}", name_type, name_size);
cmd.Parameters.Add($"@{col_trip}", trip_type, trip_size);
cmd.Parameters.Add($"@{col_id}", id_type, id_size);
cmd.Parameters.Add($"@{col_date}", date_type);
cmd.Parameters.Add($"@{col_body}", body_type);
cmd.Parameters.Add($"@{col_oekaki_url}", oekaki_url_type, oekaki_url_size);
cmd.Parameters.Add($"@{col_oekaki_image}", oekaki_image_type);
cmd.Parameters[$"@{col_resnum}"].Value = res.Resnum;
cmd.Parameters[$"@{col_name}"].Value = (res.Name != null) ? MySqlHelper.EscapeString(res.Name) : res.Name;
cmd.Parameters[$"@{col_trip}"].Value = res.Trip;
cmd.Parameters[$"@{col_id}"].Value = res.Id;
cmd.Parameters[$"@{col_date}"].Value = res.Date;
cmd.Parameters[$"@{col_body}"].Value = (res.Body != null) ? MySqlHelper.EscapeString(res.Body) : res.Body;
cmd.Parameters[$"@{col_oekaki_url}"].Value = res.Oekaki;
cmd.Parameters[$"@{col_oekaki_image}"].Value = image;
try
{
var rowsAffected = await cmd.ExecuteNonQueryAsync();
}
catch (MySqlException ex)
{
ryu_s.MyCommon.Logging.LogException(ryu_s.MyCommon.LogLevel.error, ex);
}
return;
}
