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))
{
List <string> listA = new List <string>();
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 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();
}
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 btnLoadData_Click(object sender, EventArgs e)
{
if (!(txtPath.Text == null))
{
dbh.OpenConnectionToDB();
StreamReader sr = new StreamReader(txtPath.Text);
if (tableSelector.Text == "")
{
MessageHandler.ShowMessage("you have to select an table.");
}
else if (tableSelector.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();
MessageHandler.ShowMessage("data insert has been succeeded");
dbh.CloseConnectionToDB();
}
}
}
else if (tableSelector.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();
MessageHandler.ShowMessage("data insert has been succeeded");
dbh.CloseConnectionToDB();
}
}
MessageHandler.ShowMessage("data insert has been succeeded");
dbh.CloseConnectionToDB();
}
else if (tableSelector.Text == "teams")
{
string data = sr.ReadLine();
while (data != null)
{
string[] value = data.Split(',');
int Teamid = Convert.ToInt32(value[0]);
int temp = Convert.ToInt32(value[1]);
string TeamName = value[2];
data = sr.ReadLine();
DataTable teamId = dbh.FillDT("SELECT Team_id FROM TblTeams");
for (int i = 0; i < teamId.Rows.Count; i++)
{
DataRow rowTeamId = teamId.Rows[0];
int checkTeamId = Convert.ToInt32(rowTeamId[0]);
if (checkTeamId == Teamid)
{
MessageHandler.ShowMessage("there is already an team with that team id");
Hide();
}
else if (checkTeamId != Teamid)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblTeams (Team_id, TeamName) VALUES (@Teamid, @teamname)"))
{
dbh.OpenConnectionToDB();
cmd.Parameters.AddWithValue("@Teamid", Teamid);
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.");
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();
}
}