/// <summary>
/// Registers a user into the PRPG.
/// </summary>
/// <param name="connection"></param>
/// <param name="uuid">The users ID</param>
/// <param name="starterXML">The starter pokemon (in a team) in XML form.</param>
/// <returns></returns>
public static async Task <bool> RegisterUser(this MySqlConnection connection, ulong uuid, string starterXML)
{
bool success;
try
{
await connection.OpenAsync();
string cmdString = $"INSERT INTO Trainers (UUID, Team, Money) VALUES ('{uuid}',@starterXML,'0')";
MySqlCommand cmd = new MySqlCommand(cmdString, connection);
cmd.Parameters.Add("@starterXML", MySqlDbType.Text).Value = starterXML;
await cmd.ExecuteNonQueryAsync();
success = true;
if (cachedRegistry.ContainsKey(uuid))
{
cachedRegistry[uuid] = true;
}
else
{
cachedRegistry.Add(uuid, true);
}
}
catch (Exception e)
{
await Program.Log(e.ToString(), "Database -> Register User", LogSeverity.Error);
success = false;
}
finally
{
await connection.CloseAsync();
}
return(success);
}
public async Task Create(long questionId, Answer answer)
{
var con = await MysqlConnector.GetConnection();
var command = new MySqlCommand("INSERT INTO `answers`(`author`, `question`, `message`) VALUES (@author, @question, @message)", con);
if (command == null)
{
throw new Exception();
}
command.Parameters.AddWithValue("@author", answer.User.Id);
command.Parameters.AddWithValue("@question", questionId);
command.Parameters.AddWithValue("@message", answer.Message);
var result = await command.ExecuteNonQueryAsync();
if (result < 1)
{
throw new Exception("failed to create answer");
}
}
public async Task AddEntry(Entry entry, uint idPatient)
{
if (connect.IsConnect())
{
string insertEntryQuery = "INSERT INTO entry(id_registrator, date_registration, id_patient, id_doctor, id_service) " +
"VALUES( @idReg, @dateReg, @idPati, @idDoc, @idServ );";
using (MySqlCommand command = new MySqlCommand(insertEntryQuery, connect.GetConnect))
{
DateTime t = entry.DateRegistration;
command.Parameters.AddWithValue("@idReg", entry.RegistratorEntry.Id);
command.Parameters.AddWithValue("@dateReg", t);
command.Parameters.AddWithValue("@idPati", idPatient);
command.Parameters.AddWithValue("@idDoc", entry.DoctorEntry.Id);
command.Parameters.AddWithValue("@idServ", entry.ServiceEntry.Id);
await command.ExecuteNonQueryAsync();
}
connect.Close();
}
await UploadEntry();
await UploadPatient();
}
/// <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 async static Task <Boolean> Remove(Int32 id)
{
try
{
String sql = "DELETE FROM `product` WHERE `id` = @id";
MySqlCommand cmd = new MySqlCommand(sql, Program.conn);
cmd.Parameters.AddWithValue("@id", id);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
CUtils.LastLogMsg = null;
}
catch (Exception ex)
{
#if DEBUG
Console.WriteLine(ex.Message + " " + ex.StackTrace);
#endif
CUtils.LastLogMsg = "Unahandled Exception!";
return(false);
}
return(true);
}
public async Task <bool> Crear(Cliente cliente)
{
string peticion = "INSERT INTO clientes " +
"VALUES (@dni,@nombre,@telefono,@domicilio)";
var conexion = ContextoBD.GetInstancia().GetConexion();
conexion.Open();
MySqlCommand command = new MySqlCommand(peticion, conexion);
command.Parameters.AddWithValue("@dni", cliente.Dni);
command.Parameters.AddWithValue("@nombre", cliente.Nombre);
command.Parameters.AddWithValue("@telefono", cliente.Telefono);
command.Parameters.AddWithValue("@domicilio", cliente.Domilicio);
command.Prepare();
try
{
int result = await command.ExecuteNonQueryAsync();
}
catch (DbException ex)
{
if (ex.Message.Contains("Duplicate entry"))
{
throw new DniYaExisteException();
}
else
{
throw ex;
}
}
finally
{
conexion.Close();
}
return(true);
}
//INSERT PERSONAL CONTACTS
public async void InsertPersonal(PersonalContact personalContact)
{
using (var conn = new MySqlConnection(connString))
{
await conn.OpenAsync();//open database connection
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "CALL insertPersonal(@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8);";//parameters where data would be inserted using command text
cmd.Parameters.AddWithValue("p1", personalContact.ContactFname);
cmd.Parameters.AddWithValue("p2", personalContact.ContactLname);
cmd.Parameters.AddWithValue("p3", personalContact.ContactEmail);
cmd.Parameters.AddWithValue("p4", personalContact.HomeTel);
cmd.Parameters.AddWithValue("p5", personalContact.ContactAddr1);
cmd.Parameters.AddWithValue("p6", personalContact.ContactAddr2);
cmd.Parameters.AddWithValue("p7", personalContact.ContactCity);
cmd.Parameters.AddWithValue("p8", personalContact.ContactPostcode);
await cmd.ExecuteNonQueryAsync();
}
}
}
public async Task SaveAsync <TEvent>(TEvent @event) where TEvent : IEvent
{
var paras = new List <MySqlParameter>
{
new MySqlParameter("@Version", @event.Version),
new MySqlParameter("@Id", @event.Id),
new MySqlParameter("@Seq", @event.Seq),
new MySqlParameter("@Timestamp", @event.OccurredOn),
};
using (var myConn = new MySqlConnection(_connectionString))
{
await myConn.OpenAsync();
string strSql = null;
using (var cmd = new MySqlCommand(strSql, myConn))
{
cmd.Parameters.AddRange(paras.ToArray());
await cmd.ExecuteNonQueryAsync();
}
}
}
public async void InsertBusiness(BusinessContact businessContact) //INSERT BUSINESS CONTACT
{
using (var conn = new MySqlConnection(connString)) //accessing private class with connString
{
await conn.OpenAsync();
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "CALL insertBusiness(@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8);";
cmd.Parameters.AddWithValue("p1", businessContact.ContactFname);//inserting the value for ContactFname from business contacts in parameter 1
cmd.Parameters.AddWithValue("p2", businessContact.ContactLname);
cmd.Parameters.AddWithValue("p3", businessContact.ContactEmail);
cmd.Parameters.AddWithValue("p4", businessContact.BusinessTel);
cmd.Parameters.AddWithValue("p5", businessContact.ContactAddr1);
cmd.Parameters.AddWithValue("p6", businessContact.ContactAddr2);
cmd.Parameters.AddWithValue("p7", businessContact.ContactCity);
cmd.Parameters.AddWithValue("p8", businessContact.ContactPostcode);
await cmd.ExecuteNonQueryAsync();
}
}
}
public async Task LastInsertedIdUlong()
{
await m_database.Connection.ExecuteAsync(@"drop table if exists insert_ai;
create table insert_ai(rowid bigint unsigned not null primary key auto_increment);");
try
{
await m_database.Connection.OpenAsync();
using (var command = new MySqlCommand("INSERT INTO insert_ai(rowid) VALUES (@rowid);", m_database.Connection))
{
command.Parameters.AddWithValue("@rowid", ((ulong)long.MaxValue) + 1);
await command.ExecuteNonQueryAsync();
Assert.Equal(long.MinValue, command.LastInsertedId);
}
}
finally
{
m_database.Connection.Close();
}
}
public async Task ExecuteQuery([Remainder] string query)
{
var server = Environment.GetEnvironmentVariable("Server");
var db = Environment.GetEnvironmentVariable("Database");
var uid = Environment.GetEnvironmentVariable("Uid");
var pwd = Environment.GetEnvironmentVariable("Pwd");
if (server == null || db == null || uid == null || pwd == null)
{
throw new Exception("Cannot find MySQL connection string in EnvVar.");
}
var connStr = new MySqlConnectionStringBuilder
{
Server = server,
Database = db,
UserID = uid,
Password = pwd
};
try
{
using (var connection = new MySqlConnection())
{
connection.ConnectionString = connStr.ToString();
connection.Open();
using (var sql = new MySqlCommand(query, connection))
{
await sql.ExecuteNonQueryAsync();
}
}
await Context.Message.AddReactionAsync(Emote.Parse("<:success:499567039451758603>"));
}
catch
{
await Context.Message.AddReactionAsync(new Emoji("\u274C"));
}
}
public async static void UpdateRoom(Room room)
{
MySqlConnection con = InitConnection();
try
{
await con.OpenAsync();
String query = $"UPDATE room SET room_number = {room.RoomNumber}, type = '{room.GetType()}' " +
$"WHERE room_id = '{room.ID}'";
MySqlCommand command = new MySqlCommand(query, con);
await command.ExecuteNonQueryAsync();
}
catch
{
Console.WriteLine("Error Updating Room.");
}
finally
{
con.Close();
}
}
public async UniTask <int> ExecuteNonQuery(MySqlConnection connection, MySqlTransaction transaction, string sql, params MySqlParameter[] args)
{
bool createLocalConnection = false;
if (connection == null)
{
connection = NewConnection();
transaction = null;
await OpenConnection(connection);
createLocalConnection = true;
}
int numRows = 0;
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
{
if (transaction != null)
{
cmd.Transaction = transaction;
}
foreach (MySqlParameter arg in args)
{
cmd.Parameters.Add(arg);
}
try
{
numRows = await cmd.ExecuteNonQueryAsync();
}
catch (MySqlException ex)
{
Logging.LogException(ex);
}
}
if (createLocalConnection)
{
await connection.CloseAsync();
}
return(numRows);
}
public static async Task Ban(uint userId, List <string> ips)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string banuser = "******";
var m = new MySqlCommand(banuser, conn.Connection);
m.Parameters.AddWithValue("user", userId);
await m.ExecuteNonQueryAsync();
const string banips = "UPDATE ips SET ipban=1 WHERE user_id = @user";
var n = new MySqlCommand(banips, conn.Connection);
n.Parameters.AddWithValue("user", userId);
await n.ExecuteNonQueryAsync();
await conn.Close();
}
public async Task UpdateArticle(int?id, string title, string name, string articletext)
{
string sqlCommand = id.HasValue ?
"update Articles set title=@title, name=@name, Articletext=@articletext where id=@id;"
:
"insert into Articles(Title,Name,ArticleText) values(@title,@name,@articletext);";
using var conn = new MySqlConnection(_connectionWriterString);
await conn.OpenAsync();
using var comm = new MySqlCommand(sqlCommand, conn);
comm.Parameters.AddRange(
new MySqlParameter[]
{
new MySqlParameter("@id", MySqlDbType.Int32)
{
Value = id
},
new MySqlParameter("@title", MySqlDbType.VarChar, 50)
{
Value = title
},
new MySqlParameter("@name", MySqlDbType.VarChar, 50)
{
Value = name
},
new MySqlParameter("@articletext", MySqlDbType.VarChar, 10000)
{
Value = articletext
},
});
int values = await comm.ExecuteNonQueryAsync();
if (values != 1)
{
throw new Exception("Error in insert/update data in table");
}
}
private async Task Inserts(OperationLog log)
{
StringBuilder tmp = new StringBuilder();
var i = 0;
List <MySqlParameter> paramters = new List <MySqlParameter>();
tmp.Append(",(");
tmp.Append("?uid");
tmp.Append(i);
tmp.Append(",?content");
tmp.Append(i);
tmp.Append(",?ctime");
tmp.Append(i);
tmp.Append(')');
paramters.Add(new MySqlParameter(string.Format("?uid{0}", i), MySqlDbType.Int64)
{
Value = log.UId
});
paramters.Add(new MySqlParameter(string.Format("?content{0}", i), MySqlDbType.VarString)
{
Value = log.Content
});
paramters.Add(new MySqlParameter(string.Format("?ctime{0}", i), MySqlDbType.Timestamp)
{
Value = log.Ctime
});
i++;
tmp.Remove(0, 1);
tmp.Insert(0, $"insert into operation_log (uid,content,ctime) values ");
using (var connection = Database.Connection(DataBaseConnection.LOCAL_CONNECTION))
{
await connection.OpenAsync();
MySqlCommand cmd = new MySqlCommand(tmp.ToString(), (MySqlConnection)connection);
cmd.Parameters.AddRange(paramters.ToArray());
await cmd.ExecuteNonQueryAsync();
}
}
public static void SaveShip(LoriShip ship)
{
var save = Task.Run(async() =>
{
var dbCon = DBConnection.Instance();
dbCon.DatabaseName = LCommandHandler.DATABASE_NAME;
if (dbCon.IsConnect())
{
while (LCommandHandler.Saving)
{
await Task.Delay(50);
}
LCommandHandler.Saving = true;
var cmd = new MySqlCommand("INSERT INTO relationships (id1, id2, name1, name2, shipname, percentage) VALUES (@id1, @id2, @name1, @name2, @shipname, @percentage)", dbCon.Connection);
cmd.Parameters.Add("@id1", MySqlDbType.UInt64).Value = ship.User1;
cmd.Parameters.Add("@id2", MySqlDbType.UInt64).Value = ship.User2;
cmd.Parameters.Add("@name1", MySqlDbType.String).Value = ship.Name1;
cmd.Parameters.Add("@name2", MySqlDbType.String).Value = ship.Name2;
cmd.Parameters.Add("@shipname", MySqlDbType.String).Value = ship.Shipname;
cmd.Parameters.Add("@percentage", MySqlDbType.Int32).Value = ship.Percentage;
try
{
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
}
catch (Exception e)
{
Console.WriteLine($"Failed to add relationship to database: {e.Message}");
cmd.Dispose();
}
LCommandHandler.Saving = false;
dbCon.Close();
}
});
}
public static async Task AddGameSession(GameSession state, string sessionOrchestratorId)
{
// add a new game session
using (MySqlConnection conn = new MySqlConnection(connStr))
{
await conn.OpenAsync();
using (MySqlCommand cmd = new MySqlCommand("gamesession_INSERT", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("@param_guid", sessionOrchestratorId));
cmd.Parameters.Add(new MySqlParameter("@param_createdplayer_id", state.Players[0]));
cmd.Parameters.Add(new MySqlParameter("@param_status", state.Status));
cmd.Parameters.Add(new MySqlParameter("@param_boardstate", JsonConvert.SerializeObject(state.Board)));
cmd.Parameters.Add(new MySqlParameter("@param_movesleft", state.RemainingMoves));
cmd.Parameters.Add(new MySqlParameter("@param_currentturnplayer_id", DBNull.Value));
cmd.Parameters.Add(new MySqlParameter("@param_winningplayer_id", state.Winner.Value));
await cmd.ExecuteNonQueryAsync();
}
}
}
public static async Task LogWarning(uint userId, string reason)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
Logger.Logger.Log($"Warned user with id {userId} for reason '{reason}'");
const string command = "INSERT INTO warnings (user_id, reason, time) VALUES (@param_val_1, @param_val_2, @param_val_3)";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", userId);
m.Parameters.AddWithValue("@param_val_2", reason);
m.Parameters.AddWithValue("@param_val_3", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public async Task DeleteEndings()
{
MySqlCommand command = new MySqlCommand();
MySqlConnection connection = getConnection();
command.Connection = connection;
command.CommandText = "DeleteEndings";
command.CommandType = System.Data.CommandType.StoredProcedure;
try
{
OpenConnection(connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Logger.Log(e.Message);
}
finally
{
CloseConnection(connection);
}
}
public async static void InsertAppointment(Appointment appointment)
{
MySqlConnection con = InitConnection();
try
{
await con.OpenAsync();
String query = $"INSERT INTO appointment VALUES('{appointment.ID}', '{appointment.Patient.ID}', " +
$"'{appointment.Doctor.ID}', '{appointment.Date.ToString("yyyy-MM-dd hh:mm:ss")}', {appointment.Duration})";
MySqlCommand command = new MySqlCommand(query, con);
await command.ExecuteNonQueryAsync();
}
catch
{
Console.WriteLine("Error Inserting Appointment.");
}
finally
{
con.Close();
}
}
public async Task EditPatientInfo(int id, string fname, string lname, string ssn, DateTime dob, string sex, string address, string city, string state, int zip, string phone)
{
using (MySqlConnection conn = DbConnection.GetConnection())
{
//Open the connection
await conn.OpenAsync();
string query = "update `patient` set ssn=@ssn, fname=@fname, lname=@lname, dob=@dob, sex=@sex, " +
"address=@address, city=@city, state=@state, zip=@zip, phone=@phone where id=@id";
using (MySqlCommand comm = new MySqlCommand(query, conn))
{
comm.Parameters.Add("@id", (DbType)MySqlDbType.Int32).Value = id;
comm.Parameters.Add("@fname", (DbType)MySqlDbType.VarChar).Value = fname;
comm.Parameters.Add("@lname", (DbType)MySqlDbType.VarChar).Value = lname;
comm.Parameters.Add("@ssn", (DbType)MySqlDbType.String).Value = ssn;
comm.Parameters.Add("@dob", (DbType)MySqlDbType.DateTime).Value = dob;
comm.Parameters.Add("@sex", (DbType)MySqlDbType.VarChar).Value = sex;
comm.Parameters.Add("@address", (DbType)MySqlDbType.VarChar).Value = address;
comm.Parameters.Add("@city", (DbType)MySqlDbType.VarChar).Value = city;
comm.Parameters.Add("@state", (DbType)MySqlDbType.String).Value = state;
comm.Parameters.Add("@zip", (DbType)MySqlDbType.VarChar).Value = zip;
comm.Parameters.Add("@phone", (DbType)MySqlDbType.String).Value = phone;
await comm.ExecuteNonQueryAsync();
}
}
}
public async static void InsertMedicine(Medicine medicine, Patient patient)
{
MySqlConnection con = InitConnection();
try
{
await con.OpenAsync();
String query = $"INSERT INTO medicine VALUES('{medicine.ID}', '{medicine.Name}', " +
$"'{medicine.StartingDate.ToString("yyyy-MM-dd")}', '{medicine.EndingDate.ToString("yyyy-MM-dd")}', '{patient.ID}')";
MySqlCommand command = new MySqlCommand(query, con);
await command.ExecuteNonQueryAsync();
}
catch
{
Console.WriteLine("Error Inserting Medicine.");
}
finally
{
con.Close();
}
}
private async void result_delete_record_button_Click(object sender, EventArgs e)
{
// TBD не работает, дает 0
//int row = dataGridView_resources.SelectedCells[0].RowIndex; //номер строки, где отмечен cell
main_form = this.Owner as MainApp;
if (id_results_textbox.Text != "")
{
string delete_result_command = "DELETE FROM results WHERE id = @id;";
using (MySqlCommand mysqlcommand = new MySqlCommand(delete_result_command, main_form.conn))
{
mysqlcommand.Parameters.AddWithValue("id", id_results_textbox.Text);
await mysqlcommand.ExecuteNonQueryAsync();
}
}
dataGridView_results.Update();
dataGridView_results.Refresh();
tabControl1_Update();
//TBD пометить строчку, рядом с той, которую удалили
//dataGridView_results.CurrentCell = dataGridView_results.Rows[row - 1].Cells[0];
}
private async Task <bool> SetCacheItemFromDatabaseAsync(string key, CacheItemInfo value, CancellationToken token = default(CancellationToken))
{
using (var connection = new MySqlConnection(_databaseOptionsFixture.Options.Value.WriteConnectionString))
{
var command = new MySqlCommand(
$"INSERT INTO {_databaseOptionsFixture.Options.Value.TableName} " +
"(Id, Value, ExpiresAtTime, SlidingExpirationInSeconds, AbsoluteExpiration) " +
"VALUES (@Id, @Value, @ExpiresAtTime, @SlidingExpirationInSeconds, @AbsoluteExpiration)",
connection);
command.Parameters.AddWithValue("Id", value.Id);
command.Parameters.AddWithValue("Value", value.Value);
command.Parameters.AddWithValue("ExpiresAtTime", value.ExpiresAtTime);
command.Parameters.AddWithValue("SlidingExpirationInSeconds", value.SlidingExpirationInSeconds);
command.Parameters.AddWithValue("AbsoluteExpiration", value.AbsoluteExpiration);
await connection.OpenAsync(token);
var changed_rows = await command.ExecuteNonQueryAsync(token);
return(changed_rows > 0);
}
}
private async Task BulkInsert(IEnumerable <LogEvent> events, MySqlConnection connection)
{
var eventData = events.Select(i => _core.GetColumnsAndValues(i)).ToList();
var commandText = _core.GetBulkInsertStatement(eventData);
using (var cmd = new MySqlCommand(commandText, connection))
{
int i = 0;
foreach (var columnValues in eventData)
{
foreach (var columnValue in columnValues)
{
if (columnValue.Value != null)
{
cmd.Parameters.AddWithValue($"{columnValue.Key}{i}", columnValue.Value);
}
}
i++;
}
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
/// <summary>
/// Delete a list of uploads using ids.
/// </summary>
/// <param name="ids">list of ids of uploads to delete.</param>
/// <returns>bool representing whether the operation passed.</returns>
public async Task <bool> DeleteByIdsAsync(List <int> ids)
{
// Get the connnection inside a using statement to properly dispose/close.
using (MySqlConnection connection = new MySqlConnection(_SQLConnection))
{
connection.Open();
var sqlString = $"DELETE FROM {Constants.UploadDAOTableName} WHERE {Constants.UploadDAOUploadIdColumn} IN (";
// Number of ids to delete.
var idsToDelete = ids.Count;
// Loop through the ids
for (var i = 0; i < idsToDelete; i++)
{
// Construcet the sql string for deleting an all the uploads.
sqlString += $"@UPLOAD_ID{i},";
}
// delete the trailing comma and add a semicolon to complete sql string.
sqlString = sqlString.TrimEnd(new char[] { ',' });
sqlString += ");";
// Get the command object inside a using statement to properly dispose/close.
using (MySqlCommand command = new MySqlCommand(sqlString, connection))
{
// Loop through the idsOfRows and replace them with the parameters.
for (var i = 0; i < idsToDelete; i++)
{
command.Parameters.AddWithValue($"@UPLOAD_ID{i}", ids[i]);
}
// Result is the number of rows affected.
var result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
// Return false when no rows are affected.
return(result != 0);
}
}
}
public async Task RenameTableAsync(ParserName oldTableName)
{
var tableNameString = this.trackingName.Quoted().ToString();
var oldTableNameString = oldTableName.Quoted().ToString();
var commandText = $"RENAME TABLE {oldTableNameString} TO {tableNameString}; ";
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
using (var command = new MySqlCommand(commandText, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during RenameTableAsync : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public static async Task ExecuteAsync(MySqlCommand cmd)
{
#region Execute
try
{
cmd.Connection = new MySqlConnection(_connectionString);
await cmd.Connection.OpenAsync();
await cmd.ExecuteNonQueryAsync();
}
catch (MySqlException exception)
{
Logger.Log(exception, null, Logger.ErrorLevel.Error);
}
finally
{
cmd.Connection?.Close();
}
#endregion
}
/// <summary>
/// Executes a Non Query Asynchonously
/// </summary>
/// <param name="context">Database Context</param>
/// <param name="query">SQL</param>
/// <param name="parameters">Parameters</param>
/// <returns>awaitable Task</returns>
public static async Task MySqlNonQueryAsync(this DatabaseContext context, string query, MySqlParameter[] parameters = null)
{
//Creates a new Connection
await using MySqlConnection connection = new(context.GetMySqlConnectionString());
//Open Connection
await connection.OpenAsync();
//Creates a Command and assings the Command Text to desired Query
MySqlCommand command = connection.CreateCommand();
command.CommandText = query;
//Add Parameters if there arent any
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
//Execute Non Query
await command.ExecuteNonQueryAsync();
//Close Connection
await connection.CloseAsync();
}
public void ExecuteNonQueryAsync()
{
if (st.Version < new Version(5, 0)) return;
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL("DROP PROCEDURE IF EXISTS spTest");
st.execSQL("CREATE TABLE Test (id int)");
st.execSQL("CREATE PROCEDURE spTest() BEGIN SET @x=0; REPEAT INSERT INTO Test VALUES(@x); " +
"SET @x=@x+1; UNTIL @x = 100 END REPEAT; END");
MySqlCommand proc = new MySqlCommand("spTest", st.conn);
proc.CommandType = CommandType.StoredProcedure;
System.Threading.Tasks.Task<int> result = proc.ExecuteNonQueryAsync();
Assert.NotEqual(-1, result.Result);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test;", st.conn);
cmd.CommandType = CommandType.Text;
object cnt = cmd.ExecuteScalar();
Assert.Equal(100, Convert.ToInt32(cnt));
}
public async Task ExecuteNonQueryAsync()
{
if (st.Version < new Version(5, 0)) return;
st.execSQL("CREATE TABLE CMDNonQueryAsyncTest (id int)");
st.execSQL("CREATE PROCEDURE CMDNonQueryAsyncSpTest() BEGIN SET @x=0; REPEAT INSERT INTO CMDNonQueryAsyncTest VALUES(@x); SET @x=@x+1; UNTIL @x = 100 END REPEAT; END");
MySqlCommand proc = new MySqlCommand("CMDNonQueryAsyncSpTest", st.conn);
proc.CommandType = CommandType.StoredProcedure;
int result = await proc.ExecuteNonQueryAsync();
Assert.NotEqual(-1, result);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM CMDNonQueryAsyncTest;", st.conn);
cmd.CommandType = CommandType.Text;
object cnt = cmd.ExecuteScalar();
Assert.Equal(100, Convert.ToInt32(cnt));
}
