private void DeleteCustomer_Click(object sender, EventArgs e) { if (NetworkingHelpers.GetUserRentedMovies(CustomerRowID) > 0) { DisplayError("This Customer has rented movies!"); } else { // Delete the customer from list using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Open Connection conn.Open(); string query = "DELETE FROM Customers WHERE CustomerID=@custID"; SqlCommand _cmd = new SqlCommand(query, conn); _cmd.Parameters.AddWithValue("@custID", CustomerRowID); _cmd.ExecuteNonQuery(); } // Update the list GetCustomersList(); ResetCustomersField(); } }
private void CustomerUsername_TextChanged(object sender, EventArgs e) { // Verify Username in Real-time using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { using (SqlCommand _cmd = new SqlCommand("SELECT * FROM Customers WHERE Username=@user", conn)) { try { conn.Open(); _cmd.Parameters.AddWithValue("@user", CustomerUsername.Text.ToString()); object count = _cmd.ExecuteScalar(); if (count != null) { UsernameExists = true; } else { UsernameExists = false; } Error(UsernameExists.ToString()); } catch (Exception err) { throw err; } } } }
private void ReturnMovie_Click(object sender, EventArgs e) { NetworkingHelpers.DeleteFromTable("Rented", "RentalID", RentalRawID.ToString()); using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Open Connection conn.Open(); // Execute Query using (SqlCommand _cmd = new SqlCommand("UPDATE Movies SET MovieCopies=@copies WHERE MovieID=@id", conn)) { int copies = 0; SqlCommand _getCopies = new SqlCommand("SELECT MovieCopies FROM Movies WHERE MovieID=@id", conn); _getCopies.Parameters.AddWithValue("@id", MovieRowID); SqlDataReader reader = _getCopies.ExecuteReader(); while (reader.Read()) { copies = Convert.ToInt32(reader["MovieCopies"]); } reader.Close(); _cmd.Parameters.AddWithValue("@copies", copies + 1); _cmd.Parameters.AddWithValue("@id", MovieRowID); _cmd.ExecuteNonQuery(); } // Update Tables to keep the table data in sync with database GetMoviesList(); GetRentingList(); } }
/*********************************************************************************/ // RENTED MOVIES TAB private void IssueMovie_Click(object sender, EventArgs e) { if (CustomerRowID > 0 && MovieRowID > 0) { using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Open Connection conn.Open(); // Only executes if there are copies of movies available // NOTE: For Production Purpose - This is not a very good way to do this if (MovieCopiesField.Value > 0) { // Update Movies table to decrease available copies count using (SqlCommand _cmd = new SqlCommand("UPDATE Movies SET MovieCopies=@copies WHERE MovieID=@id", conn)) { _cmd.Parameters.AddWithValue("@copies", Convert.ToInt32(MovieCopiesField.Value - 1)); _cmd.Parameters.AddWithValue("@id", MovieRowID); _cmd.ExecuteNonQuery(); } // Add Movie to Rented Movies table using (SqlCommand _cmd = new SqlCommand("INSERT INTO Rented (CustomerID, Username, MovieID, RentFrom, RentTill) VALUES(@uid, @user, @id, @from, @till)", conn)) { _cmd.Parameters.AddWithValue("@uid", CustomerRowID); _cmd.Parameters.AddWithValue("@user", CustomerUsername.Text); _cmd.Parameters.AddWithValue("@id", MovieRowID); _cmd.Parameters.AddWithValue("@from", rentingPeriodStart.Value); _cmd.Parameters.AddWithValue("@till", rentingPeriodEnd.Value); _cmd.ExecuteNonQuery(); } // Display Pop-up message to let user know total days the customer has rented movie for TimeSpan time = rentingPeriodEnd.Value.Subtract(rentingPeriodStart.Value); // This message will let user of the app know, which customer has hired which movie for how many days DisplayError(CustomerUsername.Text + " has rented " + MovieTitleField.Text + " for " + Convert.ToInt32(time.TotalDays)); // Update the tables to keep data in sync with the database GetMoviesList(); GetRentingList(); ResetRentingFields(); } else { DisplayError("All copies of " + MovieTitleField.Text + " are rented!"); } } } else { if (CustomerRowID <= 0) { DisplayError("Please Select the customer!"); } else { DisplayError("Please Select the Movie!"); } } }
private void DeleteMovie_Click(object sender, EventArgs e) { if (NetworkingHelpers.GetMovieOnRent(MovieRowID) > 0) { DisplayError("Copy of this movie is Currently on Rent!"); } else { NetworkingHelpers.DeleteFromTable("Movies", "MovieID", MovieRowID.ToString()); GetMoviesList(); } }
private void Add_ModifyMovie_Click(object sender, EventArgs e) { if (MovieTitleField.Text == "" || MovieRentingCostField.Text == "") { MessageBox.Show("One or More fields are Empty!"); } else { if (MovieAdd.Checked) { string columns = "(MovieTitle, MovieReleaseDate, MovieRatings, MovieCopies, MovieRatingCost, MovieGenre)"; string values = "(@title, @date, @ratings, @copies, @rentingCost, @genre)"; string[] fieldNames = { "@title", "@date", "@ratings", "@copies", "@rentingCost", "@genre" }; using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Open Connection conn.Open(); SqlCommand _cmd = new SqlCommand("INSERT INTO Movies " + columns + " VALUES " + values, conn); _cmd.Parameters.AddWithValue("@title", MovieTitleField.Text); _cmd.Parameters.AddWithValue("@date", MovieRelease.Value); _cmd.Parameters.AddWithValue("@ratings", MovieRatingsField.Value); _cmd.Parameters.AddWithValue("@copies", MovieCopiesField.Value); _cmd.Parameters.AddWithValue("@rentingCost", MovieRentingCostField.Text); _cmd.Parameters.AddWithValue("@genre", MovieGenreField.SelectedItem.ToString()); _cmd.ExecuteNonQuery(); } } else { string values = "MovieTitle=@title, MovieReleaseDate=@date, MovieRatings=@ratings, MovieCopies=@copies, MovieRatingCost=@rentingCost, MovieGenre=@genre"; using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Open Connection conn.Open(); SqlCommand _cmd = new SqlCommand("UPDATE Movies SET " + values + " WHERE MovieID=@id", conn); _cmd.Parameters.AddWithValue("@title", MovieTitleField.Text); _cmd.Parameters.AddWithValue("@date", MovieRelease.Value); _cmd.Parameters.AddWithValue("@ratings", MovieRatingsField.Value); _cmd.Parameters.AddWithValue("@copies", MovieCopiesField.Value); _cmd.Parameters.AddWithValue("@rentingCost", MovieRentingCostField.Text); _cmd.Parameters.AddWithValue("@genre", MovieGenreField.SelectedItem.ToString()); _cmd.Parameters.AddWithValue("@id", MovieRowID); _cmd.ExecuteNonQuery(); } } GetMoviesList(); ResetMovieFields(); } }
// Add to DataGridView public static void AddToDataGridView(string table, DataGridView view, MetroFramework.Controls.MetroTile tile) { using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Establish Connection conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM " + table, conn); DataTable dataTable = new DataTable(); dataTable.Clear(); adapter.Fill(dataTable); view.AutoGenerateColumns = false; view.DataSource = dataTable; if (table == "Customers") { if (view.Rows.Count - 1 > 0) { tile.Text = (view.Rows.Count - 1).ToString() + " Registered Customer(s)"; } else { tile.Text = "0 Registered Customers"; } } else if (table == "Movies") { if (view.Rows.Count - 1 > 0) { tile.Text = (view.Rows.Count - 1).ToString() + " Movie(s) in Store"; } else { tile.Text = "0 Movies in Store"; } } else { if (view.Rows.Count - 1 > 0) { tile.Text = (view.Rows.Count - 1).ToString() + " Movie(s) Rented"; } else { tile.Text = "0 Movies Rented"; } } } }
// This function can be used to add items to any table public static void AddToTable(string TableName, string columns, string values, string[] inputs, string[] fieldValues) { using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Open Connection conn.Open(); SqlCommand _cmd = new SqlCommand("INSERT INTO " + TableName + columns + " VALUES " + values, conn); for (int index = 0; index < inputs.Length; index++) { _cmd.Parameters.AddWithValue(inputs[index], fieldValues[index]); } _cmd.ExecuteNonQuery(); } }
private void DeleteCustomer_Click(object sender, EventArgs e) { if (NetworkingHelpers.GetUserRentedMovies(CustomerRowID) > 0) { DisplayError("This Customer has rented movies!"); } else { // Delete the customer from list NetworkingHelpers.DeleteFromTable("Customers", "CustomerID", CustomerRowID.ToString()); // Update the list GetCustomersList(); ResetCustomersField(); } }
// Add to DataGridView public static void AddToDataGridView(string table, DataGridView view, MetroFramework.Controls.MetroTile tile) { using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Establish Connection conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM " + table, conn); DataTable dataTable = new DataTable(); dataTable.Clear(); adapter.Fill(dataTable); view.AutoGenerateColumns = false; view.DataSource = dataTable; } }
/*********************************************************************************/ // CUSTOMERS TAB private void AddCustomer_Click(object sender, EventArgs e) { if (CustomerUsername.Text == "" || CustomerFirstName.Text == "" || CustomerLastName.Text == "" || CustomerAddress.Text == "" || CustomerPhone.Text == "") { MessageBox.Show("One or More fields are Empty!"); } else { if (addNewCustomer.Checked) { if (!UsernameExists) { // Add new User Query string[] fieldNames = { "@Username", "@FirstName", "@LastName", "@Address", "@PhoneNumber" }; string[] fieldValues = { CustomerUsername.Text.ToString(), CustomerFirstName.Text.ToString(), CustomerLastName.Text.ToString(), CustomerAddress.Text.ToString(), CustomerPhone.Text.ToString() }; // Function to add new entries to table NetworkingHelpers.AddToTable("Customers", "(Username, FirstName, LastName, Address, PhoneNumber)", "(@Username, @FirstName, @LastName, @Address, @PhoneNumber)", fieldNames, fieldValues); MessageBox.Show(CustomerUsername.Text + " has been added successfully!"); GetCustomersList(); } else { DisplayError("Username Already Exists!"); } } else { // Update User Query string[] fieldNames = { "@user", "@firstName", "@lastName", "@address", "@phone" }; string[] fieldValues = { CustomerUsername.Text, CustomerFirstName.Text, CustomerLastName.Text, CustomerAddress.Text, CustomerPhone.Text }; NetworkingHelpers.UpdateTable("Customers", "Username=@user, FirstName=@firstName, LastName=@lastName, Address=@address, PhoneNumber=@phone", "CustomerID=" + CustomerRowID.ToString(), fieldNames, fieldValues); GetCustomersList(); } ResetCustomersField(); } }
private void DeleteMovie_Click(object sender, EventArgs e) { if (NetworkingHelpers.GetMovieOnRent(MovieRowID) > 0) { DisplayError("Copy of this movie is Currently on Rent!"); } else { // Delete the customer from list using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Open Connection conn.Open(); string query = "DELETE FROM Movies WHERE MovieID=@movID"; SqlCommand _cmd = new SqlCommand(query, conn); _cmd.Parameters.AddWithValue("@movID", MovieRowID); _cmd.ExecuteNonQuery(); } GetMoviesList(); } }
private void GetCustomersList() { NetworkingHelpers.AddToDataGridView("Customers", dataGridView1, null); }
private void GetRentingList() { NetworkingHelpers.AddToDataGridView("Rented", dataGridView2, RentedMoviesTile); }
private void GetRentingList() { NetworkingHelpers.AddToDataGridView("Rented", dataGridView2, null); }
/*********************************************************************************/ // CUSTOMERS TAB private void AddCustomer_Click(object sender, EventArgs e) { if (CustomerUsername.Text == "" || CustomerFirstName.Text == "" || CustomerLastName.Text == "" || CustomerAddress.Text == "" || CustomerPhone.Text == "") { MessageBox.Show("One or More fields are Empty!"); } else { if (addNewCustomer.Checked) { if (!UsernameExists) { // Add new User Query string[] fieldNames = { "@Username", "@FirstName", "@LastName", "@Address", "@PhoneNumber" }; string[] fieldValues = { CustomerUsername.Text.ToString(), CustomerFirstName.Text.ToString(), CustomerLastName.Text.ToString(), CustomerAddress.Text.ToString(), CustomerPhone.Text.ToString() }; // Function to add new entries to table using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Open Connection conn.Open(); SqlCommand _cmd = new SqlCommand("INSERT INTO Customers (Username, FirstName, LastName, Address, PhoneNumber) VALUES (@Username, @FirstName, @LastName, @Address, @PhoneNumber)", conn); for (int index = 0; index < fieldNames.Length; index++) { _cmd.Parameters.AddWithValue(fieldNames[index], fieldValues[index]); } _cmd.ExecuteNonQuery(); } MessageBox.Show(CustomerUsername.Text + " has been added successfully!"); GetCustomersList(); } else { DisplayError("Username Already Exists!"); } } else { // Update User Query string[] fieldNames = { "@user", "@firstName", "@lastName", "@address", "@phone", "@cid" }; string[] fieldValues = { CustomerUsername.Text, CustomerFirstName.Text, CustomerLastName.Text, CustomerAddress.Text, CustomerPhone.Text, CustomerRowID.ToString() }; using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig())) { // Open Connection conn.Open(); string query = "UPDATE Customers SET Username=@user, FirstName=@firstName, LastName=@lastName, Address=@address, PhoneNumber=@phone WHERE CustomerID=@cid"; SqlCommand _cmd = new SqlCommand(query, conn); for (int index = 0; index < fieldNames.Length; index++) { _cmd.Parameters.AddWithValue(fieldNames[index], fieldValues[index]); } _cmd.ExecuteNonQuery(); } GetCustomersList(); } ResetCustomersField(); } }
/*********************************************************************************/ // MOVIES TAB private void GetMoviesList() { NetworkingHelpers.AddToDataGridView("Movies", dataGridView3, null); }