private void btnClearPrediction_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Are you sure you want to clear your prediction?", "Clear Predictions", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result.Equals(DialogResult.OK))
{
// Clear predections
int user_id = int.Parse(user[0]);
string query = String.Format("DELETE FROM TblPredictions WHERE User_id = '{0}'", user[0]);
using (SqlCommand cmd = new SqlCommand(query, dbh.GetCon()))
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
int tblPredictionResult = cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
if (tblPredictionResult > 0)
{
MessageHandler.ShowMessage(String.Format("You have deleted {0} results.", tblPredictionResult), "Result", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
MessageHandler.ShowMessage(String.Format("You didn't delete any rows."), "Result", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
// Update DB
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
bool exist = false;
string username = txtUsername.Text;
string password = txtPassword.Text;
txtUsername.Text = "";
txtPassword.Text = "";
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username AND Password = @Password", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
cmd.Parameters.AddWithValue("Password", password);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
bool admin;
int user_id;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) from [tblUsers] WHERE Username = @Username AND IsAdmin = 1", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
admin = (int)cmd.ExecuteScalar() > 0;
}
dbh.CloseConnectionToDB();
dbh.TestConnection();
dbh.OpenConnectionToDB();
using (SqlCommand cmd = new SqlCommand("SELECT id FROM [tblUsers] WHERE Username = @Username AND Password = @Password", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
cmd.Parameters.AddWithValue("Password", password);
user_id = (int)cmd.ExecuteScalar();
}
dbh.CloseConnectionToDB();
if (admin)
{
frmAdmin.Show();
}
else
{
frmPlayer = new frmPlayer(frmRanking, username, user_id);
frmPlayer.Show();
}
}
else
{
dbh.CloseConnectionToDB();
MessageHandler.ShowMessage("Wrong username and/or password.");
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(txtPath.Text == null))
{
dbh.OpenConnectionToDB();
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(string.IsNullOrEmpty(txtPath.Text)))
{
dbh.OpenConnectionToDB();
BindDataCSV(txtPath.Text);
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (txtPath.Text.Contains("Matches.csv"))
{
//Show wait cursor
Cursor = Cursors.WaitCursor;
if (!(txtPath.Text == null))
{
//Open db connection
dbh.OpenConnectionToDB();
DataTable importData = GetDataFromCsv();
if (importData != null)
{
//Call save import data from db handler
dbh.SaveImportDataToDatabase(importData);
MessageBox.Show("Import Games Completed");
//Show normal cursor
Cursor = Cursors.Default;
}
dbh.CloseConnectionToDB();
}
}
if (txtPath.Text.Contains("Teams.csv"))
{
//Show wait cursor
Cursor = Cursors.WaitCursor;
if (!(txtPath.Text == null))
{
//Open db connection
dbh.OpenConnectionToDB();
DataTable importData = GetDataFromCsv();
if (importData != null)
{
//Call save import data from db handler
dbh.SaveImportDataToDatabase(importData, "Teams");
MessageBox.Show("Import Teams Completed");
//Show normal cursor
Cursor = Cursors.Default;
}
dbh.CloseConnectionToDB();
}
}
if (txtPath.Text == "")
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnSelectFile_Click(object sender, EventArgs e)
{
txtPath.Text = null;
this.path = GetFilePath();
if (CheckExtension(path, "csv"))
{
txtPath.Text = path;
}
else
{
MessageHandler.ShowMessage("The wrong filetype is selected.");
}
}
private void ExecuteSQL(string selectCommandText)
{
dbh.TestConnection();
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommandText, dbh.GetCon());
try
{
dataAdapter.Fill(table);
}
catch (System.Data.SqlClient.SqlException)
{
MessageHandler.ShowMessage("Unknown SQL command", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
dgvAdminData.DataSource = table;
}
private void btnRegister_Click(object sender, EventArgs e)
{
if (txtUsername.Text == "" || txtPassword.Text == "")
{
MessageBox.Show("Both fields are required");
}
else
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
bool exist = false;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", txtUsername.Text);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
MessageHandler.ShowMessage("This user already exists.");
}
else
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblUsers] ([Username], [Password], [IsAdmin], [Score]) VALUES (@Username, @Password, @IsAdmin, @Score)"))
{
cmd.Parameters.AddWithValue("Username", txtUsername.Text);
cmd.Parameters.AddWithValue("Password", txtPassword.Text);
cmd.Parameters.AddWithValue("IsAdmin", 0);
cmd.Parameters.AddWithValue("Score", 0);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
txtUsername.Text = "";
txtPassword.Text = "";
MessageBox.Show("Succesfully registered");
}
}
dbh.CloseConnectionToDB();
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
// This is letting us to load in a CSV file.
if (!(txtPath.Text == null))
{
string sql = "BULK INSERT TblTeams" +
" FROM '" + txtPath.Text + "'" +
"WITH" +
"(" +
" FIRSTROW = 2," +
" FIELDTERMINATOR = ',', " +
" ROWTERMINATOR = '\n', " +
" TABLOCK" +
")";
dbh.OpenConnectionToDB();
ExecuteSQL(sql);
dbh.CloseConnectionToDB();
// This disables a couple of buttons and/or text boxes to be sure that an exception won't happen.
btnExecute.Enabled = true;
btnLoadData.Enabled = false;
btnSelectFile.Enabled = false;
txtQuery.Enabled = true;
txtPath.Enabled = false;
}
else
{
// This shows a message if nothing is selected.
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnSelectFile_Click(object sender, EventArgs e)
{
// This enables the option to select a file from the file explorer aka "Verkenner".
txtPath.Text = null;
string path = GetFilePath();
if (CheckExtension(path, "csv"))
{
txtPath.Text = path;
// This disables a button to be sure that an exception won't happen.
btnLoadData.Enabled = true;
}
else
{
// This shows a message if the file isn't a CSV file.
MessageHandler.ShowMessage("Nothing selected or the wrong filetype has been selected.");
}
}
private void btnSelectFile_Click(object sender, EventArgs e)
{
try
{
txtPath.Text = null;
string path = "";
path = GetFilePath();
if (!CheckExtension(path, ".csv"))
{
MessageHandler.ShowMessage("The wrong filetype is selected.");
}
else
{
txtPath.Text = path;
//
}
}
catch (Exception ex)
{ MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); }
}
private void btnRegister_Click(object sender, EventArgs e)
{
// This is letting the user to make an account.
if (txtUsername.Text == "" || txtPassword.Text == "")
{
// This shows a message is the files are empty.
MessageBox.Show("Both fields are required");
}
else
{
// Making sure that the username doesn't exists in the database.
dbh.TestConnection();
dbh.OpenConnectionToDB();
bool exist = false;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", txtUsername.Text);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
// This shows a message if the user already exists.
MessageHandler.ShowMessage("This user already exists.");
}
else
{
// This is Elton's secret account.
string user = txtUsername.Text.ToLower();
if (user == "Ninja")
{
dbh.CloseConnectionToDB();
string password = txtPassword.Text;
string userName = txtUsername.Text;
int admin = 2;
int score = 0;
// Preparing array to initialize later.
byte[] salt;
new RNGCryptoServiceProvider().GetBytes(salt = new byte[16]);
// The hashing formula is executed 10000 times just to be sure that the security level is high.
Rfc2898DeriveBytes passwordToHash = new Rfc2898DeriveBytes(password, salt, 10000);
byte[] hashArray = passwordToHash.GetBytes(20);
// Copys the value of an byte array and paste them in an other array.
byte[] hashBytes = new byte[36];
Array.Copy(salt, 0, hashBytes, 0, 16);
Array.Copy(hashArray, 0, hashBytes, 16, 20);
// Converting hashed password to a string.
string savedPasswordHash = Convert.ToBase64String(hashBytes);
string sql = "INSERT INTO [tblUsers] ([Username], [Password], [IsAdmin], [Score]) VALUES ('" + userName + "', '" + savedPasswordHash + "', '" + admin + "', '" + score + "')";
dbh.Execute(sql);
}
else
{
// Password hashing for registering.
dbh.CloseConnectionToDB();
string password = txtPassword.Text;
string userName = txtUsername.Text;
// Preparing array to initialize later.
byte[] salt;
new RNGCryptoServiceProvider().GetBytes(salt = new byte[16]);
// The hashing formula is executed 10000 times just to be sure that the security level is high.
Rfc2898DeriveBytes passwordToHash = new Rfc2898DeriveBytes(password, salt, 10000);
byte[] hashArray = passwordToHash.GetBytes(20);
// Copys the value of an byte array and paste them in an other array.
byte[] hashBytes = new byte[36];
Array.Copy(salt, 0, hashBytes, 0, 16);
Array.Copy(hashArray, 0, hashBytes, 16, 20);
// Converting hashed password to a string.
int admin = 0;
int score = 0;
string savedPasswordHash = Convert.ToBase64String(hashBytes);
string sql = "INSERT INTO [tblUsers] ([Username], [Password], [IsAdmin], [Score]) VALUES ('" + userName + "', '" + savedPasswordHash + "', '" + admin + "', '" + score + "')";
dbh.Execute(sql);
}
}
dbh.CloseConnectionToDB();
}
}
private void PayOut(string un)
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
int Id;
int score;
using (SqlCommand cmd = new SqlCommand("SELECT Id FROM [TblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Username", un);
Id = (int)cmd.ExecuteScalar();
}
int UserIdPredictions;
using (SqlCommand cmd = new SqlCommand("SELECT User_id FROM TblPredictions", dbh.GetCon()))
{
UserIdPredictions = (int)cmd.ExecuteScalar();
}
for (int i = 0; i < 5; i++)
{
if (UserIdPredictions != Id)
{
btnPayOut.Enabled = false;
}
}
using (SqlCommand cmd = new SqlCommand("SELECT Score FROM [TblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Username", un);
score = (int)cmd.ExecuteScalar();
}
int predictedHomeScore;
int predictedAwayScore;
int gameId;
using (SqlCommand cmd = new SqlCommand("SELECT PredictedHomeScore FROM [TblPredictions] WHERE User_id = @Id", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Id", Id);
predictedHomeScore = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT PredictedAwayScore FROM [TblPredictions] WHERE User_id = @Id", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Id", Id);
predictedAwayScore = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT Game_id FROM [tblPredictions] WHERE User_id = @Id", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Id", Id);
gameId = (int)cmd.ExecuteScalar();
}
int homeTeamScore;
int awayTeamScore;
using (SqlCommand cmd = new SqlCommand("SELECT HomeTeamScore FROM [tblGames] WHERE Game_id = @gameId", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@gameId", gameId);
homeTeamScore = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT AwayTeamScore FROM [tblGames] WHERE Game_id = @gameId", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@gameId", gameId);
awayTeamScore = (int)cmd.ExecuteScalar();
}
int teamWon;
if (homeTeamScore > awayTeamScore)
{
teamWon = 1;
}
else if (awayTeamScore > homeTeamScore)
{
teamWon = 2;
}
else
{
teamWon = 3;
}
string homeTeamWon = null;
int homeTeamWin;
int awayTeamWon;
if (predictedHomeScore > predictedAwayScore)
{
// nu weet ik dus dat hij denkt dat de home team gaat winnen
homeTeamWin = 1;
}
else if (predictedHomeScore > predictedAwayScore)
{
// en hier het uit team
awayTeamWon = 2;
}
bool isCorrect = false;
if (homeTeamScore == predictedHomeScore && awayTeamScore == predictedAwayScore)
{
score += 1;
isCorrect = true;
using (SqlCommand cmd = new SqlCommand("UPDATE TblUsers SET score = @score WHERE Id = @id"))
{
cmd.Parameters.AddWithValue("score", score);
cmd.Parameters.AddWithValue("Id", Id);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
using (SqlCommand cmd = new SqlCommand("DELETE FROM TblPredictions WHERE User_id = @id"))
{
cmd.Parameters.AddWithValue("id", Id);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
else
{
MessageHandler.ShowMessage("you did not win.");
}
//if (isCorrect && teamWon == homeTeamWin)
//{
// score += 3;
//}
}
private void btnLogin_Click(object sender, EventArgs e)
{
//SoundPlayer simpleSound = new SoundPlayer(@"C:\Users\Gebruiker\Documents\GitHub\project_fifa\mysite_downloads\ProjectFifaV2\Sounds\button_click.wav");
//simpleSound.Play();
dbh.TestConnection();
dbh.OpenConnectionToDB();
bool exist = false;
string username = txtUsername.Text;
string password = txtPassword.Text;
txtUsername.Text = "";
txtPassword.Text = "";
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username AND Password = @Password", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
cmd.Parameters.AddWithValue("Password", password);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
bool admin;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) from [tblUsers] WHERE Username = @Username AND IsAdmin = 1", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
admin = (int)cmd.ExecuteScalar() > 0;
}
dbh.CloseConnectionToDB();
if (admin)
{
frmAdmin.Show();
}
else
{
frmPlayer = new frmPlayer(frmRanking, username);
frmPlayer.Show();
}
}
else
{
dbh.CloseConnectionToDB();
MessageHandler.ShowMessage("Wrong username and/or password.");
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
string tableName = tableSelector.Text;
if (!(txtPath.Text == null) || !(txtPath.Text == ""))
{
if (tableName != "" && txtPath.Text != "")
{
if (!CheckExtension(txtPath.Text, ".csv"))
{
MessageHandler.ShowMessage("The wrong filetype is selected.");
}
else
{
try
{
dbh.Execute("DROP table " + tableName);
}
catch
{
}
if (tableName == "tblTeams")
{
dbh.Execute("CREATE table tblTeams (id int NOT NULL, pouleId int NOT NULL, teamName varchar(255) NOT NULL, teamNr int NOT NULL, pouleRanking int NOT NULL, playoffRanking int NOT NULL, PRIMARY KEY (id)) ");
}
else if (tableName == "tblGames")
{
dbh.Execute("CREATE table tblGames (Game_ID int NOT NULL, homeTeam int NOT NULL, awayTeam int NOT NULL, pouleId int NOT NULL, HomeTeamScore int NULL, AwayTeamScore int NULL, finished bit NOT NULL, PRIMARY KEY (Game_ID))");
}
else if (tableName == "tblPlayoffs")
{
dbh.Execute("CREATE TABLE tblPlayoffs (id int NOT NULL, pouleIdA int NULL, pouleIdB int NULL, pouleRankingA int NULL, pouleRankingB int NULL, playoffIdA int NULL, playoffIdB int NULL, playoffRankingA int NOT NULL, playoffRankingB int NOT NULL, scoreHomeTeam int NULL, scoreAwayTeam int NULL, finished bit NOT NULL, PRIMARY KEY (id))");
}
try
{
using (StreamReader reader = new StreamReader(txtPath.Text))
{
string line;
string[] lineWords;
do
{
line = reader.ReadLine();
lineWords = line.Split(',');
if (tableName == "tblTeams")
{
string insert = "INSERT INTO tblTeams (id, pouleId, teamName, teamNr, pouleRanking, playoffRanking) VALUES ('" + lineWords[0].Trim('"') + "', '" + lineWords[1].Trim('"') + "', '" + lineWords[2].Trim('"') + "', '" + lineWords[3].Trim('"') + "', '" + lineWords[4].Trim('"') + "', '" + lineWords[5].Trim('"') + "')";
dbh.Execute(insert);
}
else if (tableName == "tblGames")
{
string insert;
if (lineWords[6].Trim('"') == "0")
{
insert = "INSERT INTO tblGames (Game_ID, homeTeam, awayTeam, pouleId, finished) VALUES ('" + lineWords[0].Trim('"') + "', '" + lineWords[1].Trim('"') + "', '" + lineWords[2].Trim('"') + "', '" + lineWords[3].Trim('"') + "', '" + lineWords[6].Trim('"') + "')";
}
else
{
insert = "INSERT INTO tblGames (Game_ID, homeTeam, awayTeam, pouleId, HomeTeamScore, AwayTeamScore, finished) VALUES ('" + lineWords[0].Trim('"') + "', '" + lineWords[1].Trim('"') + "', '" + lineWords[2].Trim('"') + "', '" + lineWords[3].Trim('"') + "', '" + lineWords[4].Trim('"') + "', '" + lineWords[5].Trim('"') + "', '" + lineWords[6].Trim('"') + "')";
}
dbh.Execute(insert);
}
else if (tableName == "tblPlayoffs")
{
string insert;
if (lineWords[12].Trim('"') == "1")
{
insert = "INSERT INTO tblPlayoffs (id, pouleIdA, pouleIdB, pouleRankingA, pouleRankingB, playoffIdA, playoffIdB, playoffRankingA, playoffRankingB, scoreHomeTeam, scoreAwayTeam, finished) VALUES ('" + lineWords[0].Trim('"') + "', '" + lineWords[1].Trim('"') + "', '" + lineWords[2].Trim('"') + "', '" + lineWords[3].Trim('"') + "', '" + lineWords[4].Trim('"') + "', '" + lineWords[5].Trim('"') + "', '" + lineWords[6].Trim('"') + "', '" + lineWords[7].Trim('"') + "', '" + lineWords[8].Trim('"') + "', '" + lineWords[9].Trim('"') + "', '" + lineWords[10].Trim('"') + "', '" + lineWords[12].Trim('"') + "')";
}
else
{
insert = "INSERT INTO tblPlayoffs (id, pouleIdA, pouleIdB, pouleRankingA, pouleRankingB, playoffIdA, playoffIdB, playoffRankingA, playoffRankingB, finished) VALUES ('" + lineWords[0].Trim('"') + "', '" + lineWords[1].Trim('"') + "', '" + lineWords[2].Trim('"') + "', '" + lineWords[3].Trim('"') + "', '" + lineWords[4].Trim('"') + "', '" + lineWords[5].Trim('"') + "', '" + lineWords[6].Trim('"') + "', '" + lineWords[7].Trim('"') + "', '" + lineWords[8].Trim('"') + "', '" + lineWords[12].Trim('"') + "')";
}
dbh.Execute(insert);
}
else
{
}
} while (!reader.EndOfStream);
}
}
catch
{
}
}
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
}
private void btnCalculate_Click(object sender, EventArgs e)
{
dbh.EditRow("TblUsers", "Score", "0");
DataTable playedMatches = dbh.FillDT("SELECT * FROM TblGames WHERE isPlayed = 1");
if (playedMatches.Rows.Count < 1)
{
MessageHandler.ShowMessage("Please upload a csv which contains played matches");
}
else
{
foreach (DataRow playedMatch in playedMatches.Rows)
{
int gameId = (int)playedMatch["Game_id"];
DataTable predictions = dbh.FillDT(string.Format("SELECT * FROM TblPredictions WHERE Game_id = {0}", gameId));
int homeTeam = (int)playedMatch["HomeTeam"];
int awayTeam = (int)playedMatch["AwayTeam"];
int scoreHomeTeam = (int)playedMatch["HomeTeamScore"];
int scoreAwayTeam = (int)playedMatch["AwayTeamScore"];
int?winningTeam = null;
int tie = 0;
if (scoreHomeTeam > scoreAwayTeam)
{
winningTeam = homeTeam;
}
else if (scoreAwayTeam > scoreHomeTeam)
{
winningTeam = awayTeam;
}
else if (scoreHomeTeam == scoreAwayTeam)
{
tie = 1;
}
foreach (DataRow prediction in predictions.Rows)
{
int score = 0;
int predictedHomeTeam = (int)prediction["HomeTeam"];
int predictedAwayTeam = (int)prediction["AwayTeam"];
int predictedHomeTeamScore = (int)prediction["PredictedHomeScore"];
int predictedAwayTeamScore = (int)prediction["PredictedAwayScore"];
int?predictedWinningTeam = null;
if (predictedHomeTeamScore > predictedAwayTeamScore)
{
predictedWinningTeam = predictedHomeTeam;
}
else if (predictedAwayTeamScore > predictedHomeTeamScore)
{
predictedWinningTeam = predictedAwayTeam;
}
if (predictedHomeTeamScore == scoreHomeTeam && predictedAwayTeamScore == scoreAwayTeam)
{
score = 3;
}
else if (winningTeam != null && tie == 0 && predictedWinningTeam != null)
{
if (winningTeam == predictedWinningTeam)
{
score = 1;
}
}
MessageHandler.ShowMessage(score.ToString());
using (SqlCommand cmd = new SqlCommand("UPDATE TblUsers SET Score = Score + @Score WHERE Id = @Id", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Score", score);
cmd.Parameters.AddWithValue("Id", prediction["User_id"]);
dbh.TestConnection();
dbh.OpenConnectionToDB();
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
}
}
}
}
}
private void btnEditPrediction_Click(object sender, EventArgs e)
{
DataTable games = dbh.FillDT("SELECT * FROM TblGames");
DataTable savedPredictions = dbh.FillDT(string.Format("SELECT * FROM TblPredictions WHERE User_id = '{0}'", user[0]));
for (int i = 0; i < games.Rows.Count; i++)
{
if (savedPredictions.Rows.Count == 0)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblPredictions (User_id, Game_id, HomeTeam, AWayTeam, PredictedHomeScore, PredictedAwayScore) VALUES (@User_id, @Game_id, @HomeTeam, @AwayTeam, @PredictedHomeScore, @PredictedAwayScore)", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("User_id", user[0]);
cmd.Parameters.AddWithValue("Game_id", matchId[i]);
cmd.Parameters.AddWithValue("HomeTeam", (int)games.Rows[i]["HomeTeam"]);
cmd.Parameters.AddWithValue("AwayTeam", (int)games.Rows[i]["AwayTeam"]);
cmd.Parameters.AddWithValue("PredictedHomeScore", predictions[i, 0].Text);
cmd.Parameters.AddWithValue("PredictedAwayScore", predictions[i, 1].Text);
//cmd.Parameters.AddWithValue("TeamHomeId", games.Rows[i]["HomeTeam"])
int homeTeam = (int)games.Rows[i]["HomeTeam"];
dbh.TestConnection();
dbh.OpenConnectionToDB();
try
{
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException)
{
MessageHandler.ShowMessage("Something went wrong", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
dbh.CloseConnectionToDB();
}
}
else
{
using (SqlCommand cmd = new SqlCommand("UPDATE TblPredictions SET HomeTeam = @HomeTeam, AwayTeam = @AwayTeam, PredictedHomeScore = @PredictedHomeScore, PredictedAwayScore = @PredictedAwayScore WHERE User_id = @User_id AND Game_id = @Game_id", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("HomeTeam", (int)games.Rows[i]["HomeTeam"]);
cmd.Parameters.AddWithValue("AwayTeam", (int)games.Rows[i]["AwayTeam"]);
cmd.Parameters.AddWithValue("@PredictedHomeScore", predictions[i, 0].Text);
cmd.Parameters.AddWithValue("@PredictedAwayScore", predictions[i, 1].Text);
cmd.Parameters.AddWithValue("@User_id", user[0]);
cmd.Parameters.AddWithValue("@Game_id", matchId[i]);
dbh.TestConnection();
dbh.OpenConnectionToDB();
try
{
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException)
{
MessageHandler.ShowMessage("Something went wrong", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
dbh.CloseConnectionToDB();
}
}
}
MessageHandler.ShowMessage("Predictions updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (txtPath.Text != "")
{
string[] pathSplit = txtPath.Text.Split('\\');
int latestIndex = pathSplit.Length - 1;
StreamReader sr;
bool success = true;
string fileName = pathSplit[latestIndex];
dbh.OpenConnectionToDB();
try
{
sr = new StreamReader(txtPath.Text);
}
catch (System.IO.DirectoryNotFoundException)
{
MessageHandler.ShowMessage(string.Format("Couldn't find the directory."));
success = false;
}
catch (System.IO.FileNotFoundException)
{
MessageHandler.ShowMessage("Couldn't find the file.");
success = false;
}
catch (System.ArgumentException)
{
MessageHandler.ShowMessage("Unkown path", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
success = false;
}
if (success)
{
sr = new StreamReader(txtPath.Text);
string line = sr.ReadLine();
string[] value = line.Split(',');
DataTable dt = new DataTable();
foreach (string dc in value)
{
dt.Columns.Add(new DataColumn(dc));
}
while (!sr.EndOfStream)
{
value = sr.ReadLine().Split(',');
if (value.Length == dt.Columns.Count)
{
DataRow row = dt.NewRow();
row.ItemArray = value;
dt.Rows.Add(row);
}
else
{
MessageHandler.ShowMessage("Amount of columns not consistent");
return;
}
}
SqlBulkCopy bc = new SqlBulkCopy(dbh.GetConnectionString(), SqlBulkCopyOptions.TableLock);
if (!fileName.Contains("csv"))
{
MessageHandler.ShowMessage("This isn't a CSV file", "CSV Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (fileName.Contains("teams"))
{
bc.DestinationTableName = "TblTeams";
dbh.TruncateTable("TblTeams");
MessageHandler.ShowMessage("Teams toegevoegd");
}
else if (fileName.Contains("matches"))
{
bc.DestinationTableName = "TblGames";
dbh.TruncateTable("TblGames");
MessageHandler.ShowMessage("Matches toegevoegd");
}
else
{
MessageHandler.ShowMessage("There was no matching Table found for this csv file", "CSV Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
bc.BatchSize = dt.Rows.Count;
bc.WriteToServer(dt);
bc.Close();
}
}
dbh.CloseConnectionToDB();
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(txtPath.Text == null))
{
dbh.OpenConnectionToDB();
using (var reader = new StreamReader(path))
{
List <string> listA = new List <string>();
List <string> listB = new List <string>();
while (!reader.EndOfStream)
{
var lines = reader.ReadLine();
var line = lines.Replace("\"", "");
var values = line.Split(';');
listA.Add(values[0]);
listB.Add(values[1]);
if (!values[1].Contains("student_id") && !values[1].Contains("poule_id"))
{
foreach (string value in values)
{
dgvAdminData.Columns.Add("Column1", value);
}
while (!values[1].Contains("student_id") && !reader.EndOfStream)
{
lines = reader.ReadLine();
line = lines.Replace("\"", "").Replace("NULL", "0");
values = line.Split(';');
if (values[1].Contains("student_id"))
{
break;
}
ToSQL(values, 1);
dgvAdminData.Rows.Add(values[0], values[1], values[2], values[3], values[4], values[5]);
}
}
if (!values[1].Contains("poule_id") && !values[1].Contains("team_id_a"))
{
foreach (string value in values)
{
dataGridView1.Columns.Add("Column1", value);
}
while (!values[1].Contains("poule_id") && !reader.EndOfStream)
{
lines = reader.ReadLine();
line = lines.Replace("\"", "").Replace("NULL", "0");
values = line.Split(';');
if (values[1].Contains("poule_id"))
{
break;
}
ToSQL(values, 2);
dataGridView1.Rows.Add(values[0], values[1], values[2], values[3], values[4], values[5]);
}
}
if (!values[1].Contains("team_id_a") && !values[1].Contains("student_id"))
{
foreach (string value in values)
{
dataGridView2.Columns.Add("Column1", value);
}
while (!values[1].Contains("team_id_a") && !reader.EndOfStream)
{
lines = reader.ReadLine();
line = lines.Replace("\"", "");
values = line.Split(';');
if (reader.EndOfStream)
{
break;
}
ToSQL(values, 3);
dataGridView2.Rows.Add(values[0], values[1], values[2], values[3], values[4]);
}
}
break;
}
}
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(txtPath.Text == ""))
{
dbh.OpenConnectionToDB();
MessageHandler.ShowMessage(txtPath.Text);
List <string> listA = new List <string>();
List <string> listB = new List <string>();
List <string> listC = new List <string>();
List <string> listD = new List <string>();
List <string> listE = new List <string>();
List <string> listF = new List <string>();
using (var reader = new StreamReader(txtPath.Text))
{
if (RB_load_matches.Checked)
{
string query = "delete from tblgames";
ExecuteSQL(query);
}
if (RB_load_teams.Checked)
{
string query = "delete from tblteams";
ExecuteSQL(query);
}
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
if (RB_load_matches.Checked)
{
bool score = false;
string id = values[0];
string home_team = values[1];
string away_team = values[2];
string home_team_score = values[3];
string away_team_score = values[4];
id = id.Replace("\"", "");
int game_id = Convert.ToInt32(id);
home_team = home_team.Replace("\"", "");
int home_team_id = Convert.ToInt32(home_team);
away_team = away_team.Replace("\"", "");
int away_team_id = Convert.ToInt32(away_team);
if (home_team_score == "NULL")
{
home_team_score = away_team.Replace("\"", "");
int home_team_score_int = Convert.ToInt32(home_team_score);
away_team_score = away_team.Replace("\"", "");
int away_team_score_int = Convert.ToInt32(away_team_score);
score = true;
string query = "insert into Tblgames (game_id, hometeam, awayteam, hometeamscore, awayteamscore) values ('" + game_id + "','" + home_team_id + "','" + away_team_id + "','" + home_team_score_int + "','" + away_team_score_int + "')";
dbh.FillDT(query);
MessageHandler.ShowMessage("loaded the matches");
}
if (score == true)
{
string query = "insert into Tblgames (game_id, hometeam, awayteam) values ('" + game_id + "','" + home_team_id + "','" + away_team_id + "')";
dbh.FillDT(query);
}
}
else if (RB_load_teams.Checked)
{
string item = values[0];
string itemB = values[2];
string B = item.Replace("\"", "");
int A = Convert.ToInt32(B);
string query = "insert into Tblteams (team_id, teamname) values ('" + A + "','" + itemB + "')";
dbh.FillDT(query);
MessageHandler.ShowMessage("loaded the teams");
}
else
{
MessageHandler.ShowMessage("select an button");
}
}
}
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void InsertPredictions(string un)
{
int Id;
dbh.OpenConnectionToDB();
using (SqlCommand cmd = new SqlCommand("SELECT Id FROM [TblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", userName);
Id = (int)cmd.ExecuteScalar();
}
DataTable gamesIds = dbh.FillDT("SELECT Game_id FROM tblGames");
for (int i = 0; i < gamesIds.Rows.Count; i++)
{
DataRow dataRowIds = gamesIds.Rows[i];
if (txtHomePred.Text == "" && txtAwayPred.Text == "")
{
btnEditPrediction.Enabled = false;
MessageHandler.ShowMessage("you have to insert your prediction!");
}
else if (txtHomePred.Text == "")
{
MessageHandler.ShowMessage("you have to insert the amount of goals scored by the home team.");
}
else if (txtAwayPred.Text == "")
{
MessageHandler.ShowMessage("you have to insert the amount of goals scored by the away team");
}
else if (txtHomePred.Text != "" && txtAwayPred.Text != "")
{
string predictedHomeScore;
string predictedAwayScore;
int gameId;
predictedHomeScore = this.txtHomePred.Text;
predictedAwayScore = this.txtAwayPred.Text;
gameId = Convert.ToInt32(dataRowIds[0]);
dbh.TestConnection();
dbh.OpenConnectionToDB();
using (SqlCommand cmd = new SqlCommand("INSERT INTO tblPredictions ( User_id ,Game_id, PredictedHomeScore, PredictedAwayScore) VALUES ( @id, @gameId, @PredHomeScore, @predAwayScore)"))
{
cmd.Parameters.AddWithValue("id", Id);
cmd.Parameters.AddWithValue("gameId", gameId);
cmd.Parameters.AddWithValue("PredHomeScore", predictedHomeScore);
cmd.Parameters.AddWithValue("PredAwayScore", predictedAwayScore);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
}
}
private void btnRegister_Click(object sender, EventArgs e)
{
//SoundPlayer simpleSound = new SoundPlayer(@"C:\Users\Gebruiker\Documents\GitHub\project_fifa\mysite_downloads\ProjectFifaV2\Sounds\button_click.wav");
//simpleSound.Play();
if (txtUsername.Text == "" || txtPassword.Text == "")
{
MessageBox.Show("Both fields are required");
}
else
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
bool exist = false;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", txtUsername.Text);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
MessageHandler.ShowMessage("This user already exists.");
}
else
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblUsers] ([Username], [Password], [IsAdmin],[Score]) VALUES (@Username, @Password, @IsAdmin, @score)"))
{
cmd.Parameters.AddWithValue("Username", txtUsername.Text);
cmd.Parameters.AddWithValue("Password", txtPassword.Text);
cmd.Parameters.AddWithValue("IsAdmin", 0);
cmd.Parameters.AddWithValue("score", 0);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
string username = txtUsername.Text;
string password = txtPassword.Text;
txtUsername.Text = "";
txtPassword.Text = "";
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username AND Password = @Password", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
cmd.Parameters.AddWithValue("Password", password);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
bool admin;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) from [tblUsers] WHERE Username = @Username AND IsAdmin = 1", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
admin = (int)cmd.ExecuteScalar() > 0;
}
dbh.CloseConnectionToDB();
if (admin)
{
frmAdmin.Show();
}
else
{
frmPlayer = new frmPlayer(frmRanking, username);
frmPlayer.Show();
//frmPlayer.Show();
}
}
else
{
dbh.CloseConnectionToDB();
MessageHandler.ShowMessage("Wrong username and/or password.");
}
}
dbh.CloseConnectionToDB();
}
}
private void PayOut(string un)
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
int Id;
int score;
using (SqlCommand cmd = new SqlCommand("SELECT Id FROM [TblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Username", userName);
Id = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT Score FROM [TblUsers] WHERE Id = @id ", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@id", Id);
score = (int)cmd.ExecuteScalar();
}
int predictedHomeScore;
int predictedAwayScore;
int gameId;
using (SqlCommand cmd = new SqlCommand("SELECT PredictedHomeScore FROM [TblPredictions] WHERE User_id = @Id ", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Id", Id);
predictedHomeScore = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT PredictedAwayScore FROM [TblPredictions] WHERE User_id = @Id", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Id", Id);
predictedAwayScore = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT Game_id FROM [tblPredictions] WHERE User_id = @Id", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Id", Id);
gameId = (int)cmd.ExecuteScalar();
}
int homeTeamScore;
int awayTeamScore;
using (SqlCommand cmd = new SqlCommand("SELECT HomeTeamScore FROM [tblGames] WHERE Game_id = @gameId", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@gameId", gameId);
homeTeamScore = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT AwayTeamScore FROM [tblGames] WHERE Game_id = @gameId", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@gameId", gameId);
awayTeamScore = (int)cmd.ExecuteScalar();
}
bool homeTeamHaveWon = false;
bool awayTeamHaveWon = false;
bool isItEven = false;
if (homeTeamScore > awayTeamScore)
{
homeTeamHaveWon = true;
}
else if (awayTeamScore > homeTeamScore)
{
awayTeamHaveWon = true;
}
else
{
isItEven = true;
}
bool homeTeamWon = false;
bool awayTeamWon = false;
bool betItsEven = false;
if (predictedHomeScore > predictedAwayScore)
{
// nu weet ik dus dat hij denkt dat de home team gaat winnen
homeTeamWon = true;
}
else if (predictedHomeScore < predictedAwayScore)
{
// en hier het uit team
awayTeamWon = true;
}
else
{
betItsEven = true;
}
if (homeTeamHaveWon && homeTeamWon && homeTeamScore == predictedHomeScore && awayTeamScore == predictedAwayScore)
{
score += 3;
using (SqlCommand cmd = new SqlCommand("UPDATE TblUsers SET score = @score WHERE Id = @id"))
{
cmd.Parameters.AddWithValue("score", score);
cmd.Parameters.AddWithValue("Id", Id);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
MessageHandler.ShowMessage("you won, you have guessed that the home team won and the endscore is correct");
using (SqlCommand cmd = new SqlCommand("DELETE FROM TblPredictions WHERE User_id = @id AND Game_id = @game_id"))
{
cmd.Parameters.AddWithValue("id", Id);
cmd.Parameters.AddWithValue("game_id", gameId);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
else if (awayTeamHaveWon && awayTeamWon && homeTeamScore == predictedHomeScore && awayTeamScore == predictedAwayScore)
{
score += 3;
using (SqlCommand cmd = new SqlCommand("UPDATE TblUsers SET score = @score WHERE Id = @id"))
{
cmd.Parameters.AddWithValue("score", score);
cmd.Parameters.AddWithValue("Id", Id);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
MessageHandler.ShowMessage("you won, you have guessed that the home team won and the endscore is correct");
using (SqlCommand cmd = new SqlCommand("DELETE FROM TblPredictions WHERE User_id = @id AND Game_id = @game_id"))
{
cmd.Parameters.AddWithValue("id", Id);
cmd.Parameters.AddWithValue("game_id", gameId);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
else if (homeTeamHaveWon && homeTeamWon)
{
score += 1;
using (SqlCommand cmd = new SqlCommand("UPDATE TblUsers SET score = @score WHERE Id = @id"))
{
cmd.Parameters.AddWithValue("score", score);
cmd.Parameters.AddWithValue("Id", Id);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
MessageHandler.ShowMessage("you won, you have guessed that the home team would win and thats correct");
using (SqlCommand cmd = new SqlCommand("DELETE FROM TblPredictions WHERE User_id = @id AND Game_id = @game_id"))
{
cmd.Parameters.AddWithValue("id", Id);
cmd.Parameters.AddWithValue("game_id", gameId);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
else if (awayTeamHaveWon && awayTeamWon)
{
score += 1;
using (SqlCommand cmd = new SqlCommand("UPDATE TblUsers SET score = @score WHERE Id = @id"))
{
cmd.Parameters.AddWithValue("score", score);
cmd.Parameters.AddWithValue("Id", Id);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
MessageHandler.ShowMessage("you won, you have guessed that the away team would win and thats correct");
using (SqlCommand cmd = new SqlCommand("DELETE FROM TblPredictions WHERE User_id = @id AND Game_id = @game_id"))
{
cmd.Parameters.AddWithValue("id", Id);
cmd.Parameters.AddWithValue("game_id", gameId);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
else
{
MessageHandler.ShowMessage("you did not win.");
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(txtPath.Text == null))
{
dbh.OpenConnectionToDB();
StreamReader sr = new StreamReader(txtPath.Text);
if (boxTableType.Text == "")
{
MessageHandler.ShowMessage("you have to select an table.");
}
else if (boxTableType.Text == "matches")
{
string data = sr.ReadLine();
while (data != null)
{
string[] value = data.Split(',');
int homeTeam = Convert.ToInt32(value[1]);
int awayTeam = Convert.ToInt32(value[2]);
int scoreHome = Convert.ToInt32(value[3]);
int scoreAway = Convert.ToInt32(value[4]);
data = sr.ReadLine();
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblGames ( HomeTeam, AwayTeam, HomeTeamScore, AwayTeamScore) VALUES (@homeTeam, @awayTeam, @scoreHome, @scoreAway)"))
{
cmd.Parameters.AddWithValue("@homeTeam", homeTeam);
cmd.Parameters.AddWithValue("@awayTeam", awayTeam);
cmd.Parameters.AddWithValue("@scoreHome", scoreHome);
cmd.Parameters.AddWithValue("@scoreAway", scoreAway);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
}
else if (boxTableType.Text == "players")
{
string data = sr.ReadLine();
while (data != null)
{
string[] value = data.Split(',');
int id = Convert.ToInt32(value[0]);
string name = value[3];
string newName = name.Trim('"');
string surname = value[4];
string newSurname = surname.Trim('"');
int goalScored = Convert.ToInt32(value[5]);
int team_id = Convert.ToInt32(value[2]);
data = sr.ReadLine();
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblPlayers (Name, Surname, GoalsScored, Team_id) VALUES (@name, @surname, @goalScored, @Team_id)"))
{
cmd.Parameters.AddWithValue("@name", newName);
cmd.Parameters.AddWithValue("@surname", newSurname);
cmd.Parameters.AddWithValue("@goalScored", goalScored);
cmd.Parameters.AddWithValue("@Team_id", team_id);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
}
else if (boxTableType.Text == "teams")
{
string data = sr.ReadLine();
while (data != null)
{
string[] value = data.Split(',');
string TeamName = value[2];
data = sr.ReadLine();
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblTeams (TeamName) VALUES (@teamName)"))
{
cmd.Parameters.AddWithValue("@teamName", TeamName);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
}
MessageHandler.ShowMessage("data insert has been succeeded");
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
// This disables a couple of buttons and/or text boxes to be sure that an exception won't happen.
btnExecute.Enabled = true;
btnLoadData.Enabled = false;
btnSelectFile.Enabled = false;
txtQuery.Enabled = true;
txtPath.Enabled = false;
// This is letting us to load in a CSV file.
// TblTeams:
if ((txtPath.Text != null && rb_Teams.Checked == true))
{
string dropTableTeams = "DROP TABLE TblTeams ";
string createTableTeams =
"CREATE TABLE TblTeams(" +
" Team_id INT NOT NULL," +
" poule_id INT NULL," +
" Teamname VARCHAR(255) NULL," +
" created_at VARCHAR(255) NULL," +
" deleted_at VARCHAR(255) NULL," +
");";
string insertTableTeams = "BULK INSERT TblTeams" +
" FROM '" + txtPath.Text + "'" +
"WITH" +
"(" +
" FIRSTROW = 2," +
" FIELDTERMINATOR = ',', " +
" ROWTERMINATOR = '\n', " +
" TABLOCK" +
");";
dbh.TestConnection();
ExecuteSQL(dropTableTeams);
dbh.ExecuteAdmin(createTableTeams);
dbh.ExecuteAdmin(insertTableTeams);
}
else
{
// This shows a message if nothing is selected.
MessageHandler.ShowMessage("No filename selected.");
}
// TblGames:
if ((txtPath.Text != null && rb_Games.Checked == true))
{
string dropTableGames = "DROP TABLE TblGames ";
string createTableGames = "CREATE TABLE TblGames" +
"(" +
"Game_id VARCHAR(255) NOT NULL," +
"HomeTeam VARCHAR(255) NOT NULL," +
"AwayTeam VARCHAR(255) NOT NULL," +
"HomeTeamScore VARCHAR(255) NULL," +
"AwayTeamScore VARCHAR(255) NULL" +
");";
string insertTableGames = "BULK INSERT TblGames" +
" FROM '" + txtPath.Text + "'" + "WITH" +
"(" +
" FIRSTROW = 2," +
" FIELDTERMINATOR = ',', " +
" ROWTERMINATOR = '\n', " +
" TABLOCK" +
");";
dbh.TestConnection();
ExecuteSQL(dropTableGames);
dbh.ExecuteAdmin(createTableGames);
dbh.ExecuteAdmin(insertTableGames);
}
else
{
// This shows a message if nothing is selected.
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
string[] tables = new string[3];
tables[0] = "[tblGames]";
tables[1] = "[tblPlayers]";
tables[2] = "[tblTeams]";
if (!(txtPath.Text == null))
{
dbh.OpenConnectionToDB();
DialogResult result = MessageBox.Show("Are you sure you want to clear the database?", "Clear database", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result.Equals(DialogResult.OK))
{
DataTable hometable = dbh.FillDT("DELETE FROM [tblGames];");
dbh.FillDT("DELETE FROM [tblPlayers];");
dbh.FillDT("DELETE FROM [tblTeams];");
using (var fs = File.OpenRead(path))
using (var reader = new StreamReader(fs))
{
string[] values = new string[0];
int number = 0;
List <string> numbers = new List <string>();
List <string> valueList;
for (int i = -1; i < tables.Length;)
{
while (!reader.EndOfStream)
{
//split the lines for proper values
string line = reader.ReadLine();
values = line.Split(',', ';', '"');
valueList = new List <string>(values);
//remove any whitespaces
for (int j = 0; j < values.Count(); j++)
{
valueList.Remove("");
}
numbers = new List <string>(valueList.Count());
//change values from csv to integers if possible
for (int j = 0; j < valueList.Count(); j++)
{
if (int.TryParse(valueList[j], out number))
{
numbers.Add(number.ToString());
}
else if (valueList[j] == "NULL")
{
valueList[j] = 0.ToString();
numbers.Add(valueList[j]);
}
else
{
numbers.Add(valueList[j]);
}
}
//check if the value starts with this stringvalue then switch database
if (valueList[0].StartsWith("1") || reader.EndOfStream)
{
i++;
}
break;
}
if (i == 0)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblGames] ([HomeTeam], [AwayTeam], [HomeTeamScore], [AwayTeamScore]) VALUES (@HomeTeam, @AwayTeam, @HomeTeamScore, @AwayTeamScore)"))
{
//parameters for tblGames
cmd.Parameters.AddWithValue("HomeTeam", numbers[1]);
cmd.Parameters.AddWithValue("AwayTeam", numbers[2]);
cmd.Parameters.AddWithValue("HomeTeamScore", numbers[3]);
cmd.Parameters.AddWithValue("AwayTeamScore", numbers[4]);
dbh.OpenConnectionToDB();
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
}
}
if (i == 1)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblPlayers] ([Name], [Surname], [GoalsScored], [Team_id]) VALUES (@name, @surname, @goalsScored, @teamID)"))
{
//parameters for tblPlayers
cmd.Parameters.AddWithValue("name", numbers[3]);
cmd.Parameters.AddWithValue("surname", numbers[4]);
cmd.Parameters.AddWithValue("goalsScored", 0);
cmd.Parameters.AddWithValue("teamID", numbers[2]);
dbh.OpenConnectionToDB();
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
}
}
if (i == 2 || i == 3)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblTeams] ([Team_id], [TeamName]) VALUES (@TeamID, @TeamName)"))
{
//parameters for tblTeams
cmd.Parameters.AddWithValue("TeamID", numbers[0]);
cmd.Parameters.AddWithValue("TeamName", numbers[2]);
dbh.OpenConnectionToDB();
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
}
}
}
}
}
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
// This is letting the user to log in and go towards the form where he/she belongs.
dbh.TestConnection();
dbh.OpenConnectionToDB();
bool exist = false;
string username = txtUsername.Text;
string password = txtPassword.Text;
// Getting hashed password from database.
using (SqlCommand cmd = new SqlCommand("SELECT Password FROM TblUsers WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
this.gettingHashedPassword = Convert.ToString(cmd.ExecuteScalar());
}
// Comparing password from user input and saved password in database.
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username AND Password = @Password", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
cmd.Parameters.AddWithValue("Password", this.gettingHashedPassword);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
bool admin;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username AND IsAdmin = 1", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
admin = (int)cmd.ExecuteScalar() > 0;
}
dbh.CloseConnectionToDB();
if (admin)
{
frmAdmin.Show();
}
else
{
frmPlayer = new frmPlayer(frmRanking, username);
frmPlayer.Show();
}
}
else
{
// This shows a message if the username and or password is wrong.
MessageHandler.ShowMessage("Wrong username and/or password.");
}
dbh.CloseConnectionToDB();
}