public static async Task <bool> Close()
{
var openAsync = Connection?.CloseAsync();
if (openAsync == null)
{
return(false);
}
await openAsync;
return(true);
}
public override async UniTask DeleteCharacter(string userId, string id)
{
object result = await ExecuteScalar("SELECT COUNT(*) FROM characters WHERE id=@id AND userId=@userId",
new MySqlParameter("@id", id),
new MySqlParameter("@userId", userId));
long count = result != null ? (long)result : 0;
if (count > 0)
{
MySqlConnection connection = NewConnection();
await OpenConnection(connection);
MySqlTransaction transaction = await connection.BeginTransactionAsync();
try
{
await ExecuteNonQuery(connection, transaction, "DELETE FROM characters WHERE id=@characterId", new MySqlParameter("@characterId", id));
await DeleteCharacterAttributes(connection, transaction, id);
await DeleteCharacterBuffs(connection, transaction, id);
await DeleteCharacterHotkeys(connection, transaction, id);
await DeleteCharacterItems(connection, transaction, id);
await DeleteCharacterQuests(connection, transaction, id);
await DeleteCharacterSkills(connection, transaction, id);
await DeleteCharacterSkillUsages(connection, transaction, id);
await DeleteCharacterSummons(connection, transaction, id);
await transaction.CommitAsync();
}
catch (System.Exception ex)
{
Logging.LogError(ToString(), "Transaction, Error occurs while deleting character: " + id);
Logging.LogException(ToString(), ex);
await transaction.RollbackAsync();
}
await transaction.DisposeAsync();
await connection.CloseAsync();
this.InvokeInstanceDevExtMethods("DeleteCharacter", userId, id);
}
}
private async void ConnectCommand_Executed(object sender, ExecutedRoutedEventArgs e)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var dbName = CbxDataBases?.SelectedItem?.ToString();
var userName = BookUser?.GetUserName();
MessageBox((BookUser == null).ToString());
MessageBox.Show(userName);
MessageBox.Show(dbName);
var commandString = "SELECT `permission_id` FROM `laboratory_books`.`db_list`" +
"JOIN `laboratory_books`.`db_users` ON `db_list`.`db_id` = `db_users`.`db_id`" +
"JOIN `laboratory_books`.`users` ON `db_users`.`user_id` = `users`.`user_id`" +
$"WHERE `db_name` = '{dbName}' AND `user_name` = '{userName}';";
var sqlCommand = new MySqlCommand(commandString, connection);
await connection.OpenAsync();
var permission = (sbyte)(await sqlCommand.ExecuteScalarAsync());
await connection.CloseAsync();
try
{
LaboratoryBookPermission = (Permission?)permission;
}
catch
{
var foregraundBrush = new SolidColorBrush(Colors.Red);
TbkConnectionStatus.Foreground = foregraundBrush;
TbkConnectionStatus.Text = $"permission {permission} does not exist";
return;
}
if ((LaboratoryBookPermission != null) && (BookUser != null))
{
var mainWindow = new MainWindow(BookUser, (Permission)LaboratoryBookPermission, dbName);
mainWindow.Show();
this.Close();
}
}
public static async Task <Player> GetAsync(long id)
{
#region Get
try
{
using (var connection = new MySqlConnection(_connectionString))
{
await connection.OpenAsync();
Player player = null;
using (var cmd = new MySqlCommand($"SELECT * FROM {Name} WHERE Id = '{id}'", connection))
{
var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
player = JsonConvert.DeserializeObject <Player>((string)reader["Home"],
Configuration.JsonSettings);
player.Home.Sessions = JsonConvert.DeserializeObject <List <Session> >(
(string)reader["Sessions"],
Configuration.JsonSettings) ?? new List <Session>(50);
break;
}
}
await connection.CloseAsync();
return(player);
}
}
catch (Exception exception)
{
Logger.Log(exception, null, ErrorLevel.Fatal);
return(null);
}
#endregion
}
public async Task <int> UpdateUser(int userID, User user)
{
MySqlConnection connection = GetConnection();
await connection.OpenAsync();
MySqlCommand myCommand = new MySqlCommand("UPDATE DBUser SET username = @username, email = email, first_name = @first_name, last_name = @last_name WHERE userID = @userID", connection);
myCommand.Parameters.AddWithValue("@username", user.Username);
myCommand.Parameters.AddWithValue("@email", user.Email);
myCommand.Parameters.AddWithValue("@first_name", user.First_Name);
myCommand.Parameters.AddWithValue("@last_name", user.Last_Name);
myCommand.Parameters.AddWithValue("@userID", userID);
await myCommand.ExecuteNonQueryAsync();
await connection.CloseAsync();
return(userID);
}
public static async Task <ErrorClass> DeleteAllRelations(string id)
{
await using var conn = new MySqlConnection(ConnStr);
await conn.OpenAsync();
if (await IdHasRelation(id) == false)
{
return(new ErrorClass
{
ErrorCode = ErrorList.ERROR_NON_EXISTENT, Description = "person doesn't have relationships"
});
}
await conn.ExecuteAsync("DELETE FROM relations_tbl WHERE PersonId = @a OR RelationId = @a", new { a = id });
await conn.CloseAsync();
return(new ErrorClass {
ErrorCode = ErrorList.OK, Description = "relations Deleted"
});
}
private int FindCountInDB(string query)
{
using (MySqlConnection conn = _context.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(query, conn);
try
{
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
catch
{
return(0);
}
finally
{
conn.CloseAsync();
}
}
}
private async Task <bool> CloseConnectionAsync()
{
bool isClose = false;
try
{
if (_mySqlConnection.State == ConnectionState.Open)
{
await _mySqlConnection.CloseAsync();
isClose = true;
}
}
catch (Exception ex)
{
ConsoleHelper.WriteLine(ex.StackTrace);
}
return(isClose);
}
private async Task <TimeSpan> TestWriteOneRow(Stopwatch sw)
{
using (var connection = new MySqlConnection(MySqlConnectionString))
{
var localCounter = Interlocked.Increment(ref s_myCounter);
await connection.OpenAsync();
var insertCommand = connection.CreateCommand();
insertCommand.CommandText = "INSERT INTO bookTable (ID, Title) VALUES (@ID, @Title)";
insertCommand.Parameters.Add(new MySqlParameter("@ID", MySqlDbType.String));
insertCommand.Parameters.Add(new MySqlParameter("@Title", MySqlDbType.String));
insertCommand.Parameters["@ID"].Value = $"{s_guid}{localCounter}";
insertCommand.Parameters["@Title"].Value = "Title";
await insertCommand.ExecuteNonQueryAsync();
await connection.CloseAsync();
}
return(sw.Elapsed);
}
public bool GetStatusConnectDatabase(string connectionString)
{
using (var connect = new MySqlConnection(connectionString))
{
try
{
connect.Open();
if (connect.State == ConnectionState.Open && connect.Database != string.Empty)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
connect.CloseAsync();
return(false);
}
}
}
public static async Task <object[]> SelectSingle(string connectionString, string query, params object[] dataTypesAndValues)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
MySqlCommand command = CreateMySqlCommand(connection, query, dataTypesAndValues);
DbDataReader reader = await command.ExecuteReaderAsync();
object[] currentRow = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(currentRow);
}
reader.Close();
await connection.CloseAsync();
return(currentRow);
}
}
/// <summary>
/// Get ForumUser from MySQL
/// </summary>
/// <param name="emailUsername">The User's Email or Username</param>
/// <returns></returns>
public virtual async Task <Account> GetUserAsync(string emailUsername)
{
emailUsername = MySqlHelper.EscapeString(emailUsername);
string sqlWhere;
if (emailUsername.Contains("@") && emailUsername.Contains("."))
{
sqlWhere = "WHERE email = @EmailUsername";
}
else
{
sqlWhere = "WHERE name = @EmailUsername";
}
var command = new MySqlCommand()
{
CommandText = $"SELECT id, name, email, password, salt FROM users {sqlWhere}",
Connection = _connection,
CommandType = CommandType.Text
};
command.Parameters.AddWithValue("@EmailUsername", emailUsername);
await _connection.OpenAsync();
var reader = command.ExecuteReader();
var result = new Account();
while (reader.Read())
{
result._id = reader[0].ToString();
result.Email = reader[1].ToString();
result.Password = reader[5].ToString();
result.PasswordSalt = reader[4].ToString();
}
await _connection.CloseAsync();
return(result);
}
public async override Task <PreconditionResult> CheckPermissions(ICommandContext context, CommandInfo command, IServiceProvider services)
{
MySqlConnection conn = DatabaseHelper.GetClosedConnection();
bool isRegistered = false;
try
{
await conn.OpenAsync();
string cmdString = $"SELECT COUNT(uid) FROM participants WHERE uid = {context.User.Id} AND `tid` = " + (await DatabaseHelper.GetLatestTourneyAsync()).ID;
MySqlCommand cmd = new MySqlCommand(cmdString, conn);
using (MySqlDataReader reader = (MySqlDataReader)await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
isRegistered = reader.GetInt16(0) > 0 ? false : true;
}
}
}
catch (Exception e)
{
await Program.Log(new LogMessage(LogSeverity.Error, "UserRegistered - SignupPrecon", e.ToString()));
}
finally
{
await conn.CloseAsync();
}
if (!isRegistered)
{
try
{
await context.Message.DeleteAsync();
}
catch { }
await(await context.User.GetOrCreateDMChannelAsync()).SendMessageAsync("You are already registered for the upcoming tournament.");
}
return(isRegistered ? PreconditionResult.FromSuccess() : null);
}
public virtual async Task <DataTable> SearchInLaboratoryBookAsync(string LaboratoryBookName, int permission, string[] columnNames, string restriction)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var searchBuilder = new StringBuilder();
searchBuilder.Append($"SELECT * FROM `laboratory_book_{LaboratoryBookName}`");
searchBuilder.Append($" WHERE `permissionID`<={(int)permission} AND (");
for (var i = 0; i < (columnNames.Length - 1); i++)
{
searchBuilder.Append($"`{columnNames[i]}` LIKE '%{restriction}%' OR");
}
searchBuilder.Append($"`{columnNames[columnNames.Length - 1]}` LIKE '%{restriction}%');");
var commandString = searchBuilder.ToString();
var sqlCommand = new MySqlCommand(commandString, connection);
var result = new DataTable();
try
{
await connection.OpenAsync();
var dbReader = await sqlCommand.ExecuteReaderAsync();
result.Load(dbReader);
}
finally
{
await connection.CloseAsync();
sqlCommand?.Dispose();
}
return(result);
}
public static LinkedList <user> getPostLikes(int postId)
{
MySqlConnection Connection = db.getcon();
if (Connection != null)
{
MySqlCommand sql = new MySqlCommand();
sql.CommandText = "get_likes_of_post";
sql.CommandType = System.Data.CommandType.StoredProcedure;
sql.Parameters.Add(new MySqlParameter("postId", postId));
sql.Connection = Connection;
MySqlDataReader reader = sql.ExecuteReader();
LinkedList <user> res = new LinkedList <user>();
while (reader.HasRows && reader.Read())
{
user u = new user();
if (reader["FirstName"].GetType().Name != "DBNull")
{
u.FirstName = (String)reader["FirstName"];
}
if (reader["LastName"].GetType().Name != "DBNull")
{
u.LastName = (String)reader["LastName"];
}
if (reader["Email"].GetType().Name != "DBNull")
{
u.Email = (String)reader["Email"];
}
if (reader["PicturePath"].GetType().Name != "DBNull")
{
u.PicturePath = (String)reader["PicturePath"];
}
res.AddLast(u);
}
reader.Close();
Connection.CloseAsync();
return(res);
}
return(null);
}
public static IEnumerable <user> SearchCaption(string caption)
{
MySqlConnection Connection = db.getcon();
if (Connection != null)
{
MySqlCommand sql = new MySqlCommand();
sql.CommandText = "search_by_caption";
sql.CommandType = System.Data.CommandType.StoredProcedure;
sql.Parameters.Add(new MySqlParameter("caption_", caption));
sql.Connection = Connection;
MySqlDataReader reader = sql.ExecuteReader();
LinkedList <user> res = new LinkedList <user>();
while (reader.HasRows && reader.Read())
{
user u = new user();
if (reader["FirstName"].GetType().Name != "DBNull")
{
u.FirstName = (String)reader["FirstName"];
}
if (reader["LastName"].GetType().Name != "DBNull")
{
u.LastName = (String)reader["LastName"];
}
if (reader["Email"].GetType().Name != "DBNull")
{
u.Email = (String)reader["Email"];
}
if (reader["PicturePath"].GetType().Name != "DBNull")
{
u.PicturePath = (String)reader["PicturePath"];
}
res.AddLast(u);
}
reader.Close();
Connection.CloseAsync();
return(res);
}
return(null);
}
public async Task SetTest(string name)
{
IUserMessage msg = await Context.Channel.SendMessageAsync("...");
MySqlConnection conn = DatabaseHelper.GetClosedConnection();
try
{
await msg.ModifyAsync(x => x.Content = "Connecting to Database...");
await conn.OpenAsync();
await msg.ModifyAsync(x => x.Content = "Succesfully established connection :white_check_mark:");
Pokemon pokemon = new Pokemon()
{
Name = name
};
string str = pokemon.Serialize();
string sql = "UPDATE players SET testdata = '" + str + "' WHERE UserID = '" + Context.User.Id.ToString() + "'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
await Program.Log((await cmd.ExecuteNonQueryAsync()).ToString());
await msg.ModifyAsync(x => x.Content = "Successfully updated database.");
}
catch (Exception e)
{
await Program.Log(new LogMessage(LogSeverity.Error, "Database", "error while connection to database: " + e.ToString()));
await msg.ModifyAsync(x => x.Content = "Failed to establish connection to database. See console for error details. :x:");
}
finally
{
await conn.CloseAsync();
}
}
public List <MedicalSpecialist> Handle(string specialistEmail, string specialistPassword)
{
List <MedicalSpecialist> list = new List <MedicalSpecialist>();
using (MySqlConnection conn = _context.GetConnection())
{
conn.Open();
string query = string.Format("select * from Specialists where (email, password_hash) = ('{0}', '{1}')",
specialistEmail, Hash.FindHash(specialistPassword));
MySqlCommand cmd = new MySqlCommand(query, conn);
try
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new MedicalSpecialist()
{
Id = Convert.ToInt32(reader["specialist_id"]),
LastName = reader["last_name"].ToString(),
FirstName = reader["first_name"].ToString(),
MiddleName = reader["middle_name"].ToString(),
Email = reader["email"].ToString(),
HealthFacilitiesFacultyId = Convert.ToInt32(reader["Health_Facilities_faculty_id"]),
});
}
}
}
catch (Exception ex)
{
return(null);
}
finally
{
conn.CloseAsync();
}
}
return(list);
}
public async UniTask <long> ExecuteInsertData(MySqlConnection connection, MySqlTransaction transaction, string sql, params MySqlParameter[] args)
{
bool createLocalConnection = false;
if (connection == null)
{
connection = NewConnection();
transaction = null;
await OpenConnection(connection);
createLocalConnection = true;
}
long result = 0;
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
{
if (transaction != null)
{
cmd.Transaction = transaction;
}
foreach (MySqlParameter arg in args)
{
cmd.Parameters.Add(arg);
}
try
{
await cmd.ExecuteNonQueryAsync();
result = cmd.LastInsertedId;
}
catch (MySqlException ex)
{
Logging.LogException(ex);
}
}
if (createLocalConnection)
{
await connection.CloseAsync();
}
return(result);
}
public static async Task <List <Player> > GetGlobalPlayerRanking()
{
var list = new List <Player>();
try
{
using (var connection = new MySqlConnection(_connectionString))
{
await connection.OpenAsync();
using (var cmd = new MySqlCommand("SELECT * FROM `player` ORDER BY `Score` DESC LIMIT 200", connection))
{
var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var player = JsonConvert.DeserializeObject <Player>((string)reader["Avatar"], Settings);
player.Score = Convert.ToInt32(reader["Score"]);
player.Language = reader["Language"].ToString();
player.LogicGameObjectManager =
JsonConvert.DeserializeObject <LogicGameObjectManager>((string)reader["GameObjects"],
Settings);
list.Add(player);
}
}
await connection.CloseAsync();
}
return(list);
}
catch (Exception exception)
{
if (Configuration.Debug)
{
Console.WriteLine(exception);
}
return(list);
}
}
public async static Task <bool> ExecuteSQLInstruction(string sql)
{
try
{
MySqlConnection conn = new MySqlConnection(starter.MysqlConnectionData_login);
await conn.OpenAsync();
MySqlCommand command = new MySqlCommand(sql, conn);
int r = await command.ExecuteNonQueryAsync();
await conn.CloseAsync();
return(true);
}
catch (Exception ex)
{
Console.WriteLine("==============ERROR================\n" + ex + "\n" + sql + "\n" + DateTime.Now + "\n" + "==================ERROR_END===========\n");
}
return(false);
}
public static bool insertPost(post p)
{
MySqlConnection Connection = db.getcon();
if (Connection != null)
{
MySqlCommand sql = new MySqlCommand();
sql.CommandText = "insert_post";
sql.CommandType = System.Data.CommandType.StoredProcedure;
sql.Parameters.Add(new MySqlParameter("email", p.PosterMail));
sql.Parameters.Add(new MySqlParameter("caption_", p.Caption));
sql.Parameters.Add(new MySqlParameter("date_post", DateTime.Now));
sql.Parameters.Add(new MySqlParameter("picture_id", p.ImagePath));
sql.Parameters.Add(new MySqlParameter("is_public", p.IsPublic?"Y":"N"));
sql.Connection = Connection;
sql.ExecuteNonQuery();
Connection.CloseAsync();
return(true);
}
return(false);
}
public static int insertImage(String url)
{
MySqlConnection Connection = db.getcon();
if (Connection != null)
{
MySqlCommand sql = new MySqlCommand();
sql.CommandText = "insert_image";
sql.CommandType = System.Data.CommandType.StoredProcedure;
sql.Parameters.Add(new MySqlParameter("url", url));
MySqlParameter output = new MySqlParameter("id", MySqlDbType.Int32);
output.Direction = System.Data.ParameterDirection.Output;
sql.Parameters.Add(output);
sql.Connection = Connection;
sql.ExecuteNonQuery();
int id = (int)sql.Parameters["id"].Value;
Connection.CloseAsync();
return(id);
}
return(-1);
}
public async Task ConnTest()
{
MySqlConnection conn = DatabaseHelper.GetClosedConnection();
try
{
await conn.OpenAsync();
await Context.Channel.SendMessageAsync("Success.");
}
catch (Exception e)
{
await Context.Channel.SendMessageAsync(e.ToString().Length > 1999?e.ToString().Remove(1999) : e.ToString());
await Program.Log(e.ToString(), "Connection Test", Discord.LogSeverity.Error);
}
finally
{
await conn.CloseAsync();
}
}
public virtual async Task UpdateUser(User user)
{
MemoryCache.Remove(GetUserKey + user);
await using var connection = new MySqlConnection(ConnectionString);
try
{
await EnsureUserCreated(user.Id);
await connection.OpenAsync();
await connection.UpdateAsync(user);
}
catch (Exception ex)
{
Logger.LogError(ex, "Exception occured while executing UpdateUser method in Database class!");
}
finally
{
await connection.CloseAsync();
}
}
public async Task <string> WriteSession(string username)
{
string session_id = createSession(username);
var query = $"update `{MemberTable}` set `Session_id`= @session_id where `Username`= @Username ";
//var query = "update `member` set `Session_id`='abc' where `Username`='user'";
using (MySqlConnection conn = GetConnection())
{
await conn.OpenAsync();
MySqlCommand cmd = new MySqlCommand(query, conn);
BindParam(cmd, session_id, username);
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
return(session_id);
}
/// <summary>
/// Closes the database then disposes it.
/// </summary>
public async Task CloseAsync()
{
try
{
//Close the connection asyncronously.
Logger.Log("Closing SQL");
await _connection.CloseAsync();
IsConnected = false;
}
catch (Exception e)
{
//An error has occured while trying to close it.
Logger.LogError(e, "SQL Close Exception.");
}
finally
{
//Finally dispose of the connection.
DisposeConnection();
}
}
public static async Task <List <Player> > GetLocalPlayerRankingAsync(string language)
{
#region GetLocal
var list = new List <Player>();
try
{
using (var connection = new MySqlConnection(_connectionString))
{
await connection.OpenAsync();
using (var cmd =
new MySqlCommand(
$"SELECT * FROM {Name} WHERE Language = '{language}' ORDER BY `Trophies` DESC LIMIT 200",
connection))
{
var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
list.Add(JsonConvert.DeserializeObject <Player>((string)reader["Home"],
Configuration.JsonSettings));
}
}
await connection.CloseAsync();
}
return(list);
}
catch (Exception exception)
{
Logger.Log(exception, null, Logger.ErrorLevel.Error);
return(list);
}
#endregion
}
public static async Task <Player> GetPlayer(long id)
{
try
{
using (var connection = new MySqlConnection(_connectionString))
{
await connection.OpenAsync();
Player player = null;
using (var cmd = new MySqlCommand($"SELECT * FROM `player` WHERE PlayerId = '{id}'", connection))
{
var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
player = JsonConvert.DeserializeObject <Player>((string)reader["Avatar"], Settings);
player.Score = Convert.ToInt32(reader["Score"]);
player.Language = reader["Language"].ToString();
player.LogicGameObjectManager =
JsonConvert.DeserializeObject <LogicGameObjectManager>((string)reader["GameObjects"],
Settings);
}
}
await connection.CloseAsync();
return(player);
}
}
catch (Exception exception)
{
if (Configuration.Debug)
{
Console.WriteLine(exception);
}
return(null);
}
}
public static async Task <List <Alliance> > GetJoinableAlliances(int limit)
{
var list = new List <Alliance>();
try
{
using (var connection = new MySqlConnection(_connectionString))
{
await connection.OpenAsync();
using (var cmd = new MySqlCommand($"SELECT * FROM `clan` WHERE IsFull = '0' LIMIT {limit}",
connection))
{
var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var alliance = JsonConvert.DeserializeObject <Alliance>((string)reader["Data"], Settings);
alliance.Name = reader["Name"].ToString();
alliance.Id = Convert.ToInt64(reader["ClanId"]);
list.Add(alliance);
}
}
await connection.CloseAsync();
}
return(list);
}
catch (Exception exception)
{
if (Configuration.Debug)
{
Console.WriteLine(exception);
}
return(list);
}
}