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))
{
object[,] obj = { { "username", unLbl.Text } };
DataTable tblUsers = dbh.FillDT("select * from tblUsers WHERE (Username=@username)", obj);
DataRow rowUser = tblUsers.Rows[0];
// Clear predections
// Update DB
for (int i = 0; i < lengthOutterArray; i++)
{
rows[i, 0].Text = "";
rows[i, 1].Text = "";
}
object[,] del = { { "id", rowUser["id"] } };
dbh.Execute("DELETE FROM tblPredictions WHERE (User_id=@id)", del);
dbh.Execute("DELETE FROM tblPlayoffPredictions WHERE (User_Id=@id)", del);
if (DisableButtons())
{
btnClearPrediction.Enabled = false;
btnEditPrediction.Enabled = false;
btnInsertPrediction.Enabled = true;
}
else
{
btnClearPrediction.Enabled = true;
btnEditPrediction.Enabled = true;
btnInsertPrediction.Enabled = false;
}
}
}
private void SubmitScore()
{
string home = "";
string away = "";
DataTable tblGames = dbh.FillDT("SELECT * FROM tblGames");
DataTable playoffs = dbh.FillDT("SELECT * FROM tblPlayoffs");
if (tblGames.Rows.Count > 0)
{
for (int j = 0; j < lengthOutterArray; j++)
{
DataRow game = tblGames.Rows[j];
for (int k = 0; k < lengthInnerArray; k++)
{
if (k == 0)
{
if (rows[j, k].Text == "")
{
home = null;
}
else
{
home = rows[j, k].Text;
}
}
else
{
if (rows[j, k].Text == "")
{
away = null;
}
else
{
away = rows[j, k].Text;
}
}
}
if ((home != null && home != "") && (away != null && away != ""))
{
object[,] obj2 = { { "game", index[j] } };
string query2 = "SELECT * FROM tblGames WHERE (Game_id = @game)";
DataTable checkup = dbh.FillDT(query2, obj2);
if (checkup.Rows.Count == 1)
{
object[,] upd = { { "home", home }, { "away", away }, { "game", index[j] } };
dbh.Execute("UPDATE tblGames SET HomeTeamScore=@home, AwayTeamScore=@away WHERE (Game_id=@game)", upd);
}
}
}
}
else
{
DataTable dt = dbh.FillDT("SELECT * FROM tblPlayoffs");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow game = dt.Rows[i];
for (int k = 0; k < lengthInnerArray; k++)
{
if (k == 0)
{
if (rows[i, k].Text == "")
{
home = null;
}
else
{
home = rows[i, k].Text;
}
}
else
{
if (rows[i, k].Text == "")
{
away = null;
}
else
{
away = rows[i, k].Text;
}
}
}
if ((home != null && home != "") && (away != null && away != ""))
{
object[,] pla = { { "game", index[i] } };
string query2 = "SELECT * FROM tblPlayoffs WHERE (Game_id=@game)";
DataTable checkup = dbh.FillDT(query2, pla);
if (checkup.Rows.Count == 1)
{
object[,] upd = { { "home", home }, { "away", away }, { "game", index[i] } };
dbh.Execute("UPDATE tblPlayoffs SET scoreHomeTeam=@home, scoreAwayTeam=@away WHERE (Game_id=@game)", upd);
}
}
}
}
/*string home = "";
* string away = "";
*
* for (int j = 0; j < lengthOutterArray - 1; j++)
* {
* for (int k = 0; k < lengthInnerArray; k++)
* {
* if (k == 0)
* {
* if (rows[j, k].Text == "")
* {
* home = null;
* }
* else
* {
* home = rows[j, k].Text;
* }
* }
* else
* {
* if (rows[j, k].Text == "")
* {
* away = null;
* }
* else
* {
* away = rows[j, k].Text;
* }
* }
* }
* if (home != null && away != null)
* {
* dbh.Execute("UPDATE tblGames SET HomeTeamScore=" + home + ", AwayTeamScore=" + away + " WHERE (Game_id=" + index[j] + ")");
* }
* }*/
}
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 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();
}
}
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();
}
}
