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 void ExecuteSQL(string selectCommandText)
{
dbh.TestConnection();
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommandText, dbh.GetCon());
dataAdapter.Fill(table);
dgvAdminData.DataSource = table;
}
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 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();
}
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 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
}
}
//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 void Compare()
{
int a;
int b;
int c;
int d;
using (SqlConnection conn = dbh.GetCon())
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT HomeTeamScore, AwayTeamScore, home_team_pre_score, away_team_pre_score FROM tbl_predictions LEFT JOIN TblGames ON Match_id=Game_id WHERE userId = @userId", conn);
command.Parameters.AddWithValue("userId", this.currentuserId);
using (SqlDataReader dataReader = command.ExecuteReader())
{
try
{
if (dataReader != null && dataReader.HasRows)
{
while (dataReader.Read())
{
a = Convert.ToInt32(dataReader["home_team_pre_score"]);
b = Convert.ToInt32(dataReader["away_team_pre_score"]);
c = Convert.ToInt32(dataReader["HomeTeamScore"]);
d = Convert.ToInt32(dataReader["AwayTeamScore"]);
if (a == c && b == d)
{
this.currentuserScore = +3;
}
else if (a < b && c < d || a > b && c > d)
{
this.currentuserScore = +1;
}
}
}
}
finally
{
SendUserScore();
dataReader.Close();
conn.Close();
}
}
}
}
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 ExecuteSQL(string selectCommandText)
{
// This executes the query and sets it in the datatable.
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 btnExit_Click(object sender, EventArgs e)
{
// Gives the option to quit the application.
DialogResult result = MessageBox.Show("Are you sure you want to quit?", "Quit", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (result.Equals(DialogResult.Yes))
{
if (dbh.GetCon().State == ConnectionState.Open)
{
dbh.CloseConnectionToDB();
}
Application.Exit();
}
}
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;
}
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 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();
}
}
public void CheckPrediction(string HomeTeam, string AwayTeam, int AwayScore, int HomeScore, int index)
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
int Home_id = 0;
int Away_id = 0;
int Game_id = 0;
int Count = 0;
using (SqlCommand cmd = new SqlCommand("SELECT Team_Id FROM TblTeams WHERE TeamName = @HomeTeam", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("HomeTeam", HomeTeam);
Home_id = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT Team_Id FROM TblTeams WHERE TeamName = @AwayTeam", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("AwayTeam", AwayTeam);
Away_id = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT Game_id FROM TblGames WHERE HomeTeam = @HomeTeam AND AwayTeam = @AwayTeam", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("HomeTeam", Home_id);
cmd.Parameters.AddWithValue("AwayTeam", Away_id);
Game_id = (int)cmd.ExecuteScalar();
}
using (SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM TblPredictions WHERE Game_id = @Game_id AND PredictedHomeScore = @HomeScore AND PredictedAwayScore = @AwayScore AND User_id = @uid", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Game_id", Game_id);
cmd.Parameters.AddWithValue("HomeScore", HomeScore);
cmd.Parameters.AddWithValue("AwayScore", AwayScore);
cmd.Parameters.AddWithValue("uid", GetUID());
Count = (int)cmd.ExecuteScalar();
}
Finished[index] = 1;
if (Count == 1)
{
int rewarded = 0;
using (SqlCommand cmd = new SqlCommand("SELECT [rewarded] FROM tblPredictions WHERE Game_id = @game_id AND User_id = @uid", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("game_id", Game_id);
cmd.Parameters.AddWithValue("uid", GetUID());
rewarded = (int)cmd.ExecuteScalar();
}
if (rewarded == 0)
{
MessageBox.Show("You Have Correctly Predicted Game : " + HomeTeam + " VS " + AwayTeam);
using (SqlCommand cmd = new SqlCommand("UPDATE TblPredictions SET rewarded = 1 WHERE Game_id = @game_id AND User_id = @uid", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("game_id", Game_id);
cmd.Parameters.AddWithValue("uid", GetUID());
cmd.ExecuteNonQuery();
}
using (SqlCommand cmd = new SqlCommand("UPDATE TblUsers SET score = score + 1 WHERE id = @uid", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("uid", GetUID());
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 btnEditPrediction_Click(object sender, EventArgs e)
{
//Edit prediction code
//Inserting data into database
dbh.OpenConnectionToDB();
DataTable importData = dbh.FillDT("SELECT * FROM TblPredictions");
DataTable select_user_id = dbh.FillDT("SELECT 'Id' AS user_id From TblUsers");
DataTable select_game_id = dbh.FillDT("SELECT 'Game_id' AS game_id FROM TblGames");
SqlCommand cmd = new SqlCommand("INSERT INTO Tblpredictions (User_id, Game_id, PredictedHomeScore, PredictedAwayScore)" +
"Values (@User_id, @Game_id, @PredictedHomeScore, @PredictidAwayScore)", dbh.GetCon());
cmd.Parameters.AddWithValue("@User_id", "user_id");
cmd.Parameters.AddWithValue("@Game_id", "game_id");
cmd.Parameters.AddWithValue("@PredictedHomeScore", txtHomePred.Text);
cmd.Parameters.AddWithValue("@PredictidAwayScore", txtAwayPred.Text);
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
}
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 void fillcombobox()
{
string sql = "SELECT * FROM [TblGames]";
SqlCommand cmd = new SqlCommand(sql, dbh.GetCon());
SqlDataReader myreader;
try
{
dbh.GetCon().Open();
myreader = cmd.ExecuteReader();
while (myreader.Read())
{
string sname = myreader.GetInt32(0).ToString();
comboBox1.Items.Add(sname);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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;
}