private void UpdatePlayer(PlayerTO player) { using var sqLiteCommand = new SQLiteCommand("UPDATE Player SET Guild = @Guild, Lvl = @Level WHERE Name = @Name", connection); sqLiteCommand.Parameters.AddWithValue("@Guild", player.Guild); sqLiteCommand.Parameters.AddWithValue("@Level", player.Level); sqLiteCommand.Parameters.AddWithValue("@Name", player.Name); sqLiteCommand.ExecuteNonQuery(); }
private bool GetAttendance(RaidTO raid, PlayerTO player) { using var sqLiteCommand = new SQLiteCommand("SELECT COUNT(*) FROM Attendance WHERE Player_ID = @PlayerID AND Raid_ID = @RaidID AND Start_Date = @StartDate AND End_Date = @EndDate", connection); sqLiteCommand.Parameters.AddWithValue("@PlayerID", player.ID); sqLiteCommand.Parameters.AddWithValue("@RaidID", raid.ID); sqLiteCommand.Parameters.AddWithValue("@StartDate", player.Joined.ToFileTimeUtc()); sqLiteCommand.Parameters.AddWithValue("@EndDate", player.Left.ToFileTimeUtc()); using var sqLiteDataReader = sqLiteCommand.ExecuteReader(); while (sqLiteDataReader.Read()) { return(sqLiteDataReader.GetInt32(0) > 0); } return(false); }
private void WriteAttendance(RaidTO raid, PlayerTO player) { if (GetAttendance(raid, player)) { return; } using var sqLiteCommand = new SQLiteCommand("INSERT INTO Attendance(Player_ID, Raid_ID, Start_Date, End_Date) VALUES(@PlayerID,@RaidID,@StartDate,@EndDate)", connection); sqLiteCommand.Parameters.AddWithValue("@PlayerID", player.ID); sqLiteCommand.Parameters.AddWithValue("@RaidID", raid.ID); sqLiteCommand.Parameters.AddWithValue("@StartDate", player.Joined.ToFileTimeUtc()); sqLiteCommand.Parameters.AddWithValue("@EndDate", player.Left.ToFileTimeUtc()); Console.WriteLine("Add new Attendance: " + player.Name + " in Raid " + raid.Name); sqLiteCommand.ExecuteNonQuery(); }
private void WritePlayer(RaidTO raid, DateTime lastRaidTime, PlayerTO player) { var foundPlayer = GetPlayer(player.Name); if (foundPlayer == null) { using var sqLiteCommand = new SQLiteCommand("INSERT INTO Player(Name, Guild, Player_Class, Race, Lvl) VALUES(@Name,@Guild,@PlayerClass,@Race,@Lvl)", connection); sqLiteCommand.Parameters.AddWithValue("@Name", player.Name); sqLiteCommand.Parameters.AddWithValue("@Guild", player.Guild); sqLiteCommand.Parameters.AddWithValue("@PlayerClass", player.PlayerClass); sqLiteCommand.Parameters.AddWithValue("@Race", player.Race); sqLiteCommand.Parameters.AddWithValue("@Lvl", player.Level); Console.WriteLine("Add new Player: " + player.Name + " from " + player.Guild); sqLiteCommand.ExecuteNonQuery(); sqLiteCommand.CommandText = "SELECT last_insert_rowid()"; var lastRowId64 = (long)sqLiteCommand.ExecuteScalar(); player.ID = (int)lastRowId64; } else { player.ID = foundPlayer.ID; //Update only if player info is newer than the last raid if (raid.EndDate <= lastRaidTime) { return; } if (!string.Equals(player.Guild, foundPlayer.Guild)) { Console.WriteLine("Update player " + player.Name + "'s guild: '" + foundPlayer.Guild + "' -> '" + player.Guild + "'"); UpdatePlayer(player); } if (!Equals(player.Level, foundPlayer.Level)) { Console.WriteLine("Update player " + player.Name + "'s level: '" + foundPlayer.Level + "' -> '" + player.Level + "'"); UpdatePlayer(player); } } }
private PlayerTO GetPlayer(string playerName) { using var sqLiteCommand = new SQLiteCommand("SELECT * FROM Player WHERE Name = @Name", connection); sqLiteCommand.Parameters.AddWithValue("@Name", playerName); using var sqLiteDataReader = sqLiteCommand.ExecuteReader(); while (sqLiteDataReader.Read()) { var player = new PlayerTO(); player.ID = sqLiteDataReader.GetInt32(sqLiteDataReader.GetOrdinal("Player_ID")); player.Name = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Name")); if (!sqLiteDataReader.IsDBNull("Guild")) { player.Guild = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Guild")); } player.PlayerClass = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Player_Class")); player.Race = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Race")); player.Level = sqLiteDataReader.GetInt32(sqLiteDataReader.GetOrdinal("Lvl")); return(player); } return(null); }