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 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);
}
}
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 ExecuteSQL(string selectCommandText)
{
try
{
dbh.TestConnection();
dataAdapter = new SqlDataAdapter(selectCommandText, dbh.GetCon());
dataAdapter.Fill(table);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dgvAdminData.DataSource = table;
dataAdapter.Dispose();
}
//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();
}
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 ExecuteSQL(string selectCommandText)
{
dbh.TestConnection();
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommandText, dbh.GetCon());
dataAdapter.Fill(table);
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 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 ExecuteSQL(string selectCommandText)
{
dbh.TestConnection();
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommandText, dbh.GetCon());
try
{
dataAdapter.Fill(table);
}
catch
{
MessageBox.Show("Wrong SQL querry");
}
dgvAdminData.DataSource = table;
}
private void ShowScore()
{
dbh.TestConnection();
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 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 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();
}
}
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 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)
{
// 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 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();
}
}