Beispiel #1
0
        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
            }
        }
Beispiel #2
0
        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();
        }
Beispiel #3
0
 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());
     }
 }
Beispiel #4
0
        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);
            }
        }
Beispiel #6
0
        //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;
 }
Beispiel #8
0
        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();
            }
        }
Beispiel #9
0
        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);
                }
            }
        }
Beispiel #10
0
        private void btnLoadData_Click(object sender, EventArgs e)
        {
            if (!(txtPath.Text == null))
            {
                dbh.OpenConnectionToDB();

                dbh.CloseConnectionToDB();
            }
            else
            {
                MessageHandler.ShowMessage("No filename selected.");
            }
        }
Beispiel #11
0
 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.");
     }
 }
Beispiel #12
0
 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();
         }
 }
Beispiel #13
0
        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();
        }
Beispiel #14
0
        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");
                }
            }
        }
Beispiel #15
0
        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);
                }
            }
        }
Beispiel #17
0
        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.");
            }
        }
Beispiel #19
0
        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();
            }
        }
Beispiel #20
0
        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();
            }
        }
Beispiel #21
0
        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.");
            }
        }
Beispiel #22
0
        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();
            }
        }
Beispiel #23
0
        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();
        }
Beispiel #25
0
        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();
        }
Beispiel #26
0
        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.");
            }
        }