//Received the credentials the user inputed in the login window public bingoLib GetAccount(string user, string pass) { bingoLib account = new bingoLib(); // Object that will be returned with all the account information //Connection to the database. Edit it with the bingoAccounts.mdf location on the Solution Explorer SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\caraz\source\repos\bingoLibrary\bingoLibrary\bingoAccounts.mdf;Integrated Security=True"); // SELECT query SqlCommand cmd = new SqlCommand("SELECT * from Accounts WHERE username='******' AND password='******'", con); using (con) // Open the connection { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) // Read all the information the database returned { account.Id = (int)reader["Id"]; account.Username = (string)reader["username"]; account.Password = (string)reader["password"]; account.Fname = (string)reader["fname"]; account.Lname = (string)reader["lname"]; account.CardInfo = (string)reader["cardInfo"]; account.Cvv = (string)reader["cvv"]; account.Balance = (string)reader["balance"]; } } con.Close(); return(account); // Return the object with all the information }
// Similar to AddAccount, uses the same regular expressions public int UpdateAccountInfo(bingoLib acc) { // Only letters followed by ONE hyphen/space and ending in a letter var regexItem = new Regex(@"^([A-Z][a-z][A-Z]?[a-z]+)((-|\s)[A-Z][a-z][A-Z]?[a-z]+)?$"); // Can not contain spaces var upregexItem = new Regex(@"\s"); // Card information must be 16-digits long var cardregex = new Regex(@"^(\d{16})$"); // CVV number should only be 3-digits long var cvvregex = new Regex(@"^(\d{3})$"); // If there are not only letters or a hyphen, then input is invalid for First/Last Name if (!regexItem.IsMatch(acc.Fname) || !regexItem.IsMatch(acc.Lname) || acc.Fname[acc.Fname.Length - 1] == ' ' || acc.Lname[acc.Lname.Length - 1] == ' ') { return(0); // Specific error message number } // If there are spaces then the Username or Password is invalid else if (upregexItem.IsMatch(acc.Username) || upregexItem.IsMatch(acc.Password)) { return(1); // Specific error message number } // If the card info is not 16-digits long else if (!cardregex.IsMatch(acc.CardInfo)) { return(2); } // If the CVV is not 3-digits long else if (!cvvregex.IsMatch(acc.Cvv)) { return(3); } // If your edited username is already taken else if (CheckUsername(acc, 1) == 1) { return(4); } // No problems found, go ahead and update the database with this information else { SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\caraz\source\repos\bingoLibrary\bingoLibrary\bingoAccounts.mdf;Integrated Security=True"); sda = new SqlDataAdapter("UPDATE Accounts SET fname='" + acc.Fname + "', lname = '" + acc.Lname + "', username = '******', password = '******', cardInfo='" + acc.CardInfo + "', cvv='" + acc.Cvv + "', balance='" + acc.Balance + "' WHERE Id = " + acc.Id.ToString(), con); dt = new DataTable(); sda.Fill(dt); scb = new SqlCommandBuilder(sda); sda.Update(dt); return(5); // Success number } }
// Used when the user sign's up for an account or edits their username public int CheckUsername(bingoLib acc, int op) { SqlCommand cmd; string validate = ""; // Value of the returned database query gets stored here SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\caraz\source\repos\bingoLibrary\bingoLibrary\bingoAccounts.mdf;Integrated Security=True"); //For user sign up if (op == 0) { //Check all the accounts in the database cmd = new SqlCommand("SELECT username from Accounts WHERE username='******'", con); using (con) { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); // Get the username from the database while (reader.Read()) { validate = (string)reader["username"]; } } con.Close(); } //For user editted username else if (op == 1) { //Check through the accounts that do not have this ID cmd = new SqlCommand("SELECT username from Accounts WHERE username='******' AND Id != " + acc.Id, con); using (con) { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); // Get the username from the database while (reader.Read()) { validate = (string)reader["username"]; } } con.Close(); } // If no duplicate username, then the username is valid if (string.IsNullOrEmpty(validate)) { return(0); } // If matching username, invalid else { return(1); } }
// Used only to update the account's balance. All the checking gets done in the form public void UpdateBalance(bingoLib acc) { SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\caraz\source\repos\bingoLibrary\bingoLibrary\bingoAccounts.mdf;Integrated Security=True"); sda = new SqlDataAdapter("UPDATE Accounts SET balance = '" + acc.Balance + "' WHERE Id = " + acc.Id.ToString(), con); dt = new DataTable(); sda.Fill(dt); scb = new SqlCommandBuilder(sda); sda.Update(dt); }
//When the user decides to sign up for an account public int AddAccount(bingoLib newAcc) { // Only letters followed by ONE hyphen/space and ending in a letter var regexItem = new Regex(@"^([A-Z][a-z]?[A-Z]?[a-z]+)((-|\s)[A-Z][a-z][A-Z]?[a-z]+)?$"); // Can not contain spaces var upregexItem = new Regex(@"\s"); // Card number must be 16-digits long var cardregex = new Regex(@"^(\d{16})$"); // CVV must be 3-digits long var cvvregex = new Regex(@"^(\d{3})$"); // If there are not only letters or a hyphen, then input is invalid for First/Last Name if (!regexItem.IsMatch(newAcc.Fname) || !regexItem.IsMatch(newAcc.Lname) || newAcc.Fname[newAcc.Fname.Length - 1] == ' ' || newAcc.Lname[newAcc.Lname.Length - 1] == ' ') { return(0); // Specific error message number } // If there are spaces then the Username or Password is invalid else if (upregexItem.IsMatch(newAcc.Username) || upregexItem.IsMatch(newAcc.Password)) { return(1); // Specific error message number } // If the card information is not 16-digits long else if (!cardregex.IsMatch(newAcc.CardInfo)) { return(2); } // If the CVV is not 3-digits long else if (!cvvregex.IsMatch(newAcc.Cvv)) { return(3); } // If the username is not unique else if (newAcc.CheckUsername(newAcc, 0) == 1) { return(4); } // If the starting balance is higher than $1200 else if (Convert.ToInt32(newAcc.Balance) > 1200) { return(5); } // No problems found, add this new record to the database else { SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\caraz\source\repos\bingoLibrary\bingoLibrary\bingoAccounts.mdf;Integrated Security=True"); string value = "('" + newAcc.Username + "', '" + newAcc.Password + "', '" + newAcc.Fname + "', '" + newAcc.Lname + "', '" + newAcc.CardInfo + "', '" + newAcc.Cvv + "', '" + newAcc.Balance + "');"; sda = new SqlDataAdapter("INSERT INTO Accounts (username, password, fname, lname, cardInfo, cvv, balance) VALUES " + value, con); dt = new DataTable(); sda.Fill(dt); scb = new SqlCommandBuilder(sda); sda.Update(dt); return(6); // Success number } }