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 insertPredictions(string un)
{
int numberOfGoalsHome = Convert.ToInt32(this.numberOfGoalsHome.Text);
int numberOfGoalsAway = Convert.ToInt32(this.numberOfGoalsAway.Text);
dbh.TestConnection();
dbh.OpenConnectionToDB();
int Id;
using (SqlCommand cmd = new SqlCommand("SELECT Id FROM [TblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Username", un);
Id = (int)cmd.ExecuteScalar();
}
int Points;
using (SqlCommand cmd = new SqlCommand("SELECT Points FROM [TblUsers] WHERE Id = @Id", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Id", Id);
Points = (int)cmd.ExecuteScalar();
}
int Game_id = Convert.ToInt32(game_id.Text);
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblPredictions ( User_id, Game_id, PredictedHomeScore, PredictedAwayScore) VALUES (@user_id, @Game_id, @PredictedHomeScore, @PredictedAwayScore)"))
{
cmd.Parameters.AddWithValue("@user_id", Id);
cmd.Parameters.AddWithValue("@Game_id", Game_id);
cmd.Parameters.AddWithValue("PredictedHomeScore", numberOfGoalsHome);
cmd.Parameters.AddWithValue("PredictedAwayScore", numberOfGoalsAway);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
dbh.CloseConnectionToDB();
}
private int SetUserId()
{
using (SqlCommand cmd = new SqlCommand("SELECT Id FROM TblUsers WHERE Username = @username AND Password = @password", dbh.GetCon()))
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
cmd.Parameters.AddWithValue("username", GetUsername());
cmd.Parameters.AddWithValue("password", password);
return(userid = (int)cmd.ExecuteScalar());
}
}
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 ShowResults()
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
DataTable hometable = dbh.FillDT("SELECT tblTeams.TeamName, tblGames.HomeTeamScore FROM tblGames INNER JOIN tblTeams ON tblGames.HomeTeam = tblTeams.Team_ID");
DataTable awayTable = dbh.FillDT("SELECT tblTeams.TeamName, tblGames.AwayTeamScore FROM tblGames INNER JOIN tblTeams ON tblGames.AwayTeam = tblTeams.Team_ID");
dbh.CloseConnectionToDB();
for (int i = 0; i < hometable.Rows.Count; i++)
{
DataRow dataRowHome = hometable.Rows[i];
DataRow dataRowAway = awayTable.Rows[i];
ListViewItem lstItem = new ListViewItem(dataRowHome["TeamName"].ToString());
lstItem.SubItems.Add(dataRowHome["HomeTeamScore"].ToString());
lstItem.SubItems.Add(dataRowAway["AwayTeamScore"].ToString());
if (Int32.Parse(dataRowAway["AwayTeamScore"].ToString()) > 0 | Int32.Parse(dataRowHome["HomeTeamScore"].ToString()) > 0)
{
CheckPrediction(dataRowHome["Teamname"].ToString(), dataRowAway["TeamName"].ToString(), Int32.Parse(dataRowAway["AwayTeamScore"].ToString()), Int32.Parse(dataRowHome["HomeTeamScore"].ToString()), i);
}
lstItem.SubItems.Add(dataRowAway["TeamName"].ToString());
lvOverview.Items.Add(lstItem);
}
}
//using (SqlCommand cmd = new SqlCommand("SELECT Id FROM TblUsers WHERE Username = @username", dbh.GetCon()))
//{
// cmd.Parameters.AddWithValue("@username", userName);
// int userId = (int)cmd.ExecuteScalar();
// return userId;
//}
private void button1_Click(object sender, EventArgs e, string un)
{
//SoundPlayer simpleSound = new SoundPlayer(@"C:\Users\Gebruiker\Documents\GitHub\project_fifa\mysite_downloads\ProjectFifaV2\Sounds\Chaching.wav");
//simpleSound.Play();
int numberOfGoalsHome = Convert.ToInt32(this.numberOfGoalsHome.Text);
int numberOfGoalsAway = Convert.ToInt32(this.numberOfGoalsAway.Text);
int numberOfPoints = Convert.ToInt32(betAmount.Value);
dbh.TestConnection();
dbh.OpenConnectionToDB();
int Id;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", un);
Id = (int)cmd.ExecuteScalar();
}
int Game_id = Convert.ToInt32(game_id.Text);
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblPredictions ( User_id, Game_id, PredictedHomeScore, PredictedAwayScore) VALUES (@user_id, @Game_id, @PredictedHomeScore, @PredictedAwayScore)"))
{
cmd.Parameters.AddWithValue("@user_id", Id);
cmd.Parameters.AddWithValue("@Game_id", Game_id);
cmd.Parameters.AddWithValue("PredictedHomeScore", numberOfGoalsHome);
cmd.Parameters.AddWithValue("PredictedAwayScore", numberOfGoalsAway);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
dbh.CloseConnectionToDB();
}
public frmPlayer(Form frm, string un)
{
this.userName = un;
this.ControlBox = false;
frmRanking = frm;
dbh = new DatabaseHandler();
dbh.OpenConnectionToDB();
using (SqlCommand cmd = new SqlCommand("SELECT * FROM [tblUsers] WHERE Username = @username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", userName);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
this.userID = dr.GetInt32(0);
dr.Close();
}
dbh.CloseConnectionToDB();
InitializeComponent();
if (DisableEditButton())
{
btnEditPrediction.Enabled = false;
}
ShowResults();
ShowScoreCard();
this.Text = "Welcome " + un;
}
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 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
// Update DB
dbh.OpenConnectionToDB();
int Id;
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("DELETE * FROM TblPredictions WHERE User_id = @id"))
{
cmd.Parameters.AddWithValue("id", Id);
}
}
}
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 SendUserScore()
{
using (SqlConnection conn = dbh.GetCon())
using (SqlCommand cmd = new SqlCommand("UPDATE TblUsers SET Score = @userscore WHERE Id = @id", conn))
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
cmd.Parameters.AddWithValue("id", this.currentuserId);
cmd.Parameters.AddWithValue("userscore", this.currentuserScore);
cmd.ExecuteNonQuery();
conn.Close();
}
}
private void ShowScore()
{
dbh.CloseConnectionToDB();
dbh.OpenConnectionToDB();
DataTable table = dbh.FillDT("SELECT Username, Score FROM tblUsers WHERE (IsAdmin = 0) ORDER BY Score DESC");
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow dataRow = table.Rows[i];
ListViewItem lstItem = new ListViewItem((i + 1).ToString());
lstItem.SubItems.Add(dataRow["Username"].ToString());
lstItem.SubItems.Add(dataRow["Score"].ToString());
lvRanking.Items.Add(lstItem);
}
dbh.CloseConnectionToDB();
}
private void btnClearPrediction_Click(object sender, EventArgs e)
{
int userID = this.userID;
DialogResult result = MessageBox.Show("Are you sure you want to clear your prediction?", "Clear Predictions", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result.Equals(DialogResult.OK))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM tblPredictions WHERE User_ID = @userID;", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("UserID", userID);
dbh.OpenConnectionToDB();
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
MessageBox.Show("Your predictions have been removed");
}
}
}
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 button1_Click(object sender, EventArgs e)
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
DataTable hometable = dbh.FillDT("SELECT tblTeams.TeamName, tblGames.HomeTeamScore FROM tblGames INNER JOIN tblTeams ON tblGames.HomeTeam = tblTeams.Team_ID");
DataTable awayTable = dbh.FillDT("SELECT tblTeams.TeamName, tblGames.AwayTeamScore FROM tblGames INNER JOIN tblTeams ON tblGames.AwayTeam = tblTeams.Team_ID");
dbh.CloseConnectionToDB();
for (int i = 0; i < hometable.Rows.Count; i++)
{
DataRow dataRowHome = hometable.Rows[i];
DataRow dataRowAway = awayTable.Rows[i];
if (dataRowHome["HomeTeamScore"] != null && dataRowAway["AwayTeamScore"] != null)
{
ListViewItem lstItem = new ListViewItem(dataRowHome["TeamName"].ToString());
lstItem.SubItems.Add(dataRowHome["HomeTeamScore"].ToString());
lstItem.SubItems.Add(dataRowAway["AwayTeamScore"].ToString());
lstItem.SubItems.Add(dataRowAway["TeamName"].ToString());
lvOverview.Items.Add(lstItem);
}
}
}
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 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 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 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 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 btnClearPrediction_Click(object sender, EventArgs e)
{
// This is letting the user to clear his/her preditions.
DialogResult result = MessageBox.Show("Are you sure you want to clear your prediction?", "Clear Predictions", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (result.Equals(DialogResult.Yes))
{
// We are trying to get the users id to make sure we delete his/her preditions.
DataTable tblUsers = dbh.FillDT("SELECT * FROM TblUsers WHERE (Username='******')");
dbh.TestConnection();
dbh.OpenConnectionToDB();
using (SqlCommand cmd = new SqlCommand("SELECT id FROM TblUsers WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", this.Text);
string sql = Convert.ToString(cmd.ExecuteScalar());
int.TryParse(sql, out this.resultId);
}
dbh.CloseConnectionToDB();
int userId = resultId;
int counterCounts = 0;
string home = "";
string away = "";
string sqlStr = "DELETE FROM TblPredictions WHERE user_id ='" + userId + "'";
for (; counterCounts < lengthOutterArray; counterCounts++)
{
home = rowLeft[counterCounts].Text;
away = rowRight[counterCounts].Text;
}
dbh.Execute(sqlStr);
lvPredictions.Items.Clear();
ShowPredictions(userId);
dbh.TestConnection();
dbh.OpenConnectionToDB();
// Making sure that 0 predictions from the user will stay in the database.
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblPredictions] WHERE User_id = @User_id AND Saved = 1", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("User_id", userId);
saved = (int)cmd.ExecuteScalar() > 0;
}
if (saved)
{
MessageBox.Show("Er gaat iets fout...");
}
else
{
btnSaveButton.Enabled = true;
btnClearPrediction.Enabled = false;
}
dbh.CloseConnectionToDB();
}
}
public frmPlayer(Form frm, string un)
{
// This is letting the user to see the preditions, result and scorecard. We need an dbh to excute sqls.
int amount = dbh.DTInt("SELECT COUNT(*) FROM TblGames");
rowLeft = new NumericUpDown[amount];
rowRight = new NumericUpDown[amount];
this.ControlBox = false;
frmRanking = frm;
this.counter--;
dbh = new DatabaseHandler();
InitializeComponent();
// Disables buttons if its passed it expire date.
if (DisableEditButton())
{
btnClearPrediction.Enabled = false;
btnSaveButton.Enabled = false;
}
this.Text = un;
// Checks if some preditions already has been saved.
DataTable tblUsers = dbh.FillDT("SELECT * FROM TblUsers WHERE (Username='******')");
dbh.TestConnection();
dbh.OpenConnectionToDB();
using (SqlCommand cmd = new SqlCommand("SELECT id FROM TblUsers WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", this.Text);
string sql = Convert.ToString(cmd.ExecuteScalar());
int.TryParse(sql, out this.resultId);
}
int userId = resultId;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblPredictions] WHERE User_id = @User_id AND Saved = 1", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("User_id", userId);
saved = (int)cmd.ExecuteScalar() > 0;
}
dbh.CloseConnectionToDB();
if (saved)
{
btnSaveButton.Enabled = false;
}
else
{
btnClearPrediction.Enabled = false;
}
ShowResults();
ShowScoreCard();
ShowPredictions(userId);
}
private void ShowScore()
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
int userID = 0;
int score = 0;
int matches;
DataTable users = dbh.FillDT("SELECT Username, Score FROM tblUsers WHERE (IsAdmin = 0) ORDER BY Score DESC");
//using (SqlCommand cmd = new SqlCommand("SELECT COUNT(Game_id) FROM tblGames"))
//{
// dbh.OpenConnectionToDB();
// cmd.Connection = dbh.GetCon();
// matches = (int)cmd.ExecuteScalar();
// dbh.CloseConnectionToDB();
//}
//DataTable scoreHome = dbh.FillDT("SELECT HomeTeamScore FROM tblGames");
//DataTable scoreAway = dbh.FillDT("SELECT AwayTeamScore FROM tblGames");
//List<int> predictedHomeScores = new List<int>();
//List<int> predictedAwayScores = new List<int>();
//using (SqlCommand cmd = new SqlCommand("SELECT predictedHomeScore FROM tblPredictions WHERE User_ID = @userID"))
//{
// dbh.OpenConnectionToDB();
// cmd.Connection = dbh.GetCon();
// cmd.Parameters.AddWithValue("userID", userID);
// using (SqlDataReader objReader = cmd.ExecuteReader())
// {
// if(objReader.HasRows)
// {
// while(objReader.Read())
// {
// int item = objReader.GetInt32(objReader.GetOrdinal("predictedHomeScore"));
// predictedHomeScores.Add(item);
// }
// }
// }
// dbh.CloseConnectionToDB();
//}
//using (SqlCommand cmd = new SqlCommand("SELECT predictedAwayScore FROM tblPredictions WHERE User_ID = @userID"))
//{
// dbh.OpenConnectionToDB();
// cmd.Connection = dbh.GetCon();
// cmd.Parameters.AddWithValue("userID", userID);
// using (SqlDataReader objReader = cmd.ExecuteReader())
// {
// if (objReader.HasRows)
// {
// while (objReader.Read())
// {
// int item = objReader.GetInt32(objReader.GetOrdinal("predictedHomeScore"));
// predictedHomeScores.Add(item);
// }
// }
// }
// dbh.CloseConnectionToDB();
//}
//for (int i = 0; i < matches; i++)
//{
// if (scoreHome.AsEnumerable().ToString() == predictedHomeScores[i].ToString() && scoreAway.AsEnumerable().ToString() == predictedAwayScores[i].ToString())
// score = score + 2;
// else if (scoreHome.AsEnumerable().ToString() == predictedHomeScores[i].ToString() || scoreAway.AsEnumerable().ToString() == predictedAwayScores[i].ToString())
// score++;
//}
//using (SqlCommand cmd = new SqlCommand("UPDATE [tblUsers] score = @score WHERE id = @userID"))
//{
// cmd.Parameters.AddWithValue("userID", userID);
// cmd.Parameters.AddWithValue("score", score);
//}
for (int i = 0; i < users.Rows.Count; i++)
{
DataRow dataRow = users.Rows[i];
ListViewItem lstItem = new ListViewItem((i + 1).ToString());
lstItem.SubItems.Add(dataRow["Username"].ToString());
lstItem.SubItems.Add(dataRow["Score"].ToString());
lvRanking.Items.Add(lstItem);
}
dbh.CloseConnectionToDB();
}
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.");
}
}