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); } }