public static bool UpdateInvoices(Invoice oldInvoice, Invoice newInvoice)
        {
            SqlConnection connection      = TableclothDB.GetConnection();
            string        updateStatement =
                "UPDATE Invoices SET " +
                "BorrowerId = @NewBorrowerId, " +
                "ProductId = @NewProductId, " +
                "Quantity = @NewQuantity, " +
                "InvoiceDate = @NewInvoiceDate, " +
                "WHERE InvoiceId = @oldInvoiceId " +
                "AND BorrowerId = @OldBorrowerId " +
                "AND ProductId = @OldProductId " +
                "AND Quantity = @OldQuantity " +
                "AND InvoiceDate = @OldInvoiceDate ";
            SqlCommand updateCommand =
                new SqlCommand(updateStatement, connection);

            updateCommand.Parameters.AddWithValue(
                "@NewBorrowerId", newInvoice.BorrowerId);
            updateCommand.Parameters.AddWithValue(
                "@NewProductId", newInvoice.ProductId);
            updateCommand.Parameters.AddWithValue(
                "@NewQuantity", newInvoice.Quantity);
            updateCommand.Parameters.AddWithValue(
                "@NewInvoiceDate", newInvoice.InvoiceDate);
            updateCommand.Parameters.AddWithValue(
                "@OldInvoiceId", oldInvoice.InvoiceId);
            updateCommand.Parameters.AddWithValue(
                "@OldBorrowerId", oldInvoice.BorrowerId);
            updateCommand.Parameters.AddWithValue(
                "@OldProductId", oldInvoice.ProductId);
            updateCommand.Parameters.AddWithValue(
                "@OldQuantity", oldInvoice.Quantity);
            updateCommand.Parameters.AddWithValue(
                "@OldInvoiceDate", oldInvoice.InvoiceDate);

            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public static bool UpdateBorrower(Borrowers oldBorrower, Borrowers newBorrower)
        {
            SqlConnection connection      = TableclothDB.GetConnection();
            string        updateStatement =
                "UPDATE Borrowers SET " +
                "BorrowerFName = @NewBorrowerFName, " +
                "BorrowerLName = @NewBorrowerLName, " +
                "BorrowerPhoneNumber = @NewBorrowerPhoneNumber, " +
                "BorrowerEmail = @NewBorrowerEmail, " +
                "WHERE BorrowerId = @oldBorrowerId " +
                "AND BorrowerFName = @OldBorrowerFName " +
                "AND BorrowerLName = @OldBorrowerLName " +
                "AND BorrowerPhoneNumber = @OldBorrowerPhoneNumber " +
                "AND BorrowerEmail = @OldBorrowerEmail ";
            SqlCommand updateCommand =
                new SqlCommand(updateStatement, connection);

            updateCommand.Parameters.AddWithValue(
                "@NewBorrowerFName", newBorrower.BorrowerFName);
            updateCommand.Parameters.AddWithValue(
                "@NewBorrowerLName", newBorrower.BorrowerLName);
            updateCommand.Parameters.AddWithValue(
                "@NewBorrowerPhoneNumber", newBorrower.BorrowerPhoneNumber);
            updateCommand.Parameters.AddWithValue(
                "@NewBorrowerEmail", newBorrower.BorrowerEmail);
            updateCommand.Parameters.AddWithValue(
                "@OldBorrowerId", oldBorrower.BorrowerId);
            updateCommand.Parameters.AddWithValue(
                "@OldBorrowerFName", oldBorrower.BorrowerFName);
            updateCommand.Parameters.AddWithValue(
                "@OldBorrowerLName", oldBorrower.BorrowerLName);
            updateCommand.Parameters.AddWithValue(
                "@OldBorrowerPhoneNumber", oldBorrower.BorrowerPhoneNumber);
            updateCommand.Parameters.AddWithValue(
                "@OldBorrowerEmail", oldBorrower.BorrowerEmail);

            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Beispiel #3
0
        private void PopulateProducts()
        {
            using (connection = TableclothDB.GetConnection())
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ProductId, ProductName FROM Product", connection))
                {
                    DataTable nameTable = new DataTable();
                    adapter.Fill(nameTable);

                    cmbProduct.DisplayMember = "ProductName";
                    cmbProduct.ValueMember   = "ProductId";
                    cmbProduct.DataSource    = nameTable;
                }
        }
Beispiel #4
0
        private void PopulateBorrowers()
        {
            using (connection = TableclothDB.GetConnection())
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT BorrowerId, BorrowerFName + BorrowerLName " +
                                                                   "as FullName FROM Borrowers", connection))
                {
                    DataTable nameTable = new DataTable();
                    adapter.Fill(nameTable);

                    cmbBorrower.DisplayMember = "FullName";
                    cmbBorrower.ValueMember   = "BorrowerId";
                    cmbBorrower.DataSource    = nameTable;
                }
        }
        public static bool DeleteInvoice(Invoice invoice)
        {
            SqlConnection connection      = TableclothDB.GetConnection();
            string        deleteStatement =
                "DELETE FROM Invoices " +
                "WHERE InvoiceId = @InvoiceId " +
                "AND BorrowerId = @BorrowerId " +
                "AND ProductId = @ProductId " +
                "AND Quantity = @Quantity " +
                "AND InvoiceDate = @InvoiceDate ";
            SqlCommand deleteCommand =
                new SqlCommand(deleteStatement, connection);

            deleteCommand.Parameters.AddWithValue(
                "@InvoiceId", invoice.InvoiceId);
            deleteCommand.Parameters.AddWithValue(
                "@BorrowerId", invoice.BorrowerId);
            deleteCommand.Parameters.AddWithValue(
                "@ProductId", invoice.ProductId);
            deleteCommand.Parameters.AddWithValue(
                "@Quantity", invoice.Quantity);
            deleteCommand.Parameters.AddWithValue(
                "@InvoiceDate", invoice.InvoiceDate);
            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public static bool DeleteBorrower(Borrowers borrower)
        {
            SqlConnection connection      = TableclothDB.GetConnection();
            string        deleteStatement =
                "DELETE FROM Borrowers " +
                "WHERE BorrowerId = @BorrowerId " +
                "AND BorrowerFName = @BorrowerFName " +
                "AND BorrowerLName = @BorrowerLName " +
                "AND BorrowerPhoneNumber = @BorrowerPhoneNumber " +
                "AND BorrowerEmail = @BorrowerEmail ";
            SqlCommand deleteCommand =
                new SqlCommand(deleteStatement, connection);

            deleteCommand.Parameters.AddWithValue(
                "@BorrowerId", borrower.BorrowerId);
            deleteCommand.Parameters.AddWithValue(
                "@BorrowerFName", borrower.BorrowerFName);
            deleteCommand.Parameters.AddWithValue(
                "@BorrowerLName", borrower.BorrowerLName);
            deleteCommand.Parameters.AddWithValue(
                "@BorrowerPhoneNumber", borrower.BorrowerPhoneNumber);
            deleteCommand.Parameters.AddWithValue(
                "@BorrowerEmail", borrower.BorrowerEmail);
            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public static Invoice GetInvoice(int InvoiceId)
        {
            SqlConnection connection = TableclothDB.GetConnection();
            string        selectStatement
                = "SELECT InvoiceId, BorrowerId, ProductId, Quantity, InvoiceDate"
                  + "FROM Invoices "
                  + "WHERE InvoiceId = @InvoiceId";

            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@InvoiceID", InvoiceId);

            try
            {
                connection.Open();
                SqlDataReader custReader =
                    selectCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (custReader.Read())
                {
                    Invoice invoice = new Invoice();
                    invoice.InvoiceId   = (int)custReader["InvoiceId"];
                    invoice.BorrowerId  = (int)custReader["BorrowerId"];
                    invoice.ProductId   = (int)custReader["ProductId"];
                    invoice.Quantity    = (int)custReader["Quantity"];
                    invoice.InvoiceDate = (DateTime)custReader["InvoiceDate"];
                    return(invoice);
                }
                else
                {
                    return(null);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public static Borrowers GetBorrower(int BorrowerId)
        {
            SqlConnection connection = TableclothDB.GetConnection();
            string        selectStatement
                = "SELECT BorrowerId, BorrowerFName, BorrowerLName, BorrowerPhoneNumber, BorrowerEmail"
                  + "FROM Borrowers "
                  + "WHERE BorrowerId = @BorrowerId";

            SqlCommand selectCommand =
                new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@BorrowerId", BorrowerId);

            try
            {
                connection.Open();
                SqlDataReader custReader =
                    selectCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (custReader.Read())
                {
                    Borrowers borrower = new Borrowers();
                    borrower.BorrowerId          = (int)custReader["BorrowerId"];
                    borrower.BorrowerFName       = custReader["BorrowerFName"].ToString();
                    borrower.BorrowerLName       = custReader["BorrowerLName"].ToString();
                    borrower.BorrowerPhoneNumber = custReader["BorrowerPhoneNumber"].ToString();
                    borrower.BorrowerEmail       = custReader["BorrowerEmail"].ToString();
                    return(borrower);
                }
                else
                {
                    return(null);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public static int AddInvoice(Invoice invoice)
        {
            SqlConnection connection      = TableclothDB.GetConnection();
            string        insertStatement =
                "INSERT Invoices" +
                "(BorrowerId, ProductId, Quantity, InvoiceDate)" +
                "VALUES (@BorrowerId, @ProductId, @Quantity, @InvoiceDate)";
            SqlCommand insertCommand =
                new SqlCommand(insertStatement, connection);

            insertCommand.Parameters.AddWithValue(
                "@BorrowerId", invoice.BorrowerId);
            insertCommand.Parameters.AddWithValue(
                "@ProductId", invoice.ProductId);
            insertCommand.Parameters.AddWithValue(
                "@Quantity", invoice.Quantity);
            insertCommand.Parameters.AddWithValue(
                "@InvoiceDate", invoice.InvoiceDate);

            try
            {
                connection.Open();
                insertCommand.ExecuteNonQuery();
                string selectStatement =
                    "SELECT IDENT_CURRENT('Invoices') FROM Invoices";
                SqlCommand selectCommand =
                    new SqlCommand(selectStatement, connection);
                int customerID = Convert.ToInt32(selectCommand.ExecuteScalar());
                return(customerID);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public static int AddBorrower(Borrowers borrower)
        {
            SqlConnection connection      = TableclothDB.GetConnection();
            string        insertStatement =
                "INSERT Borrowers" +
                "(BorrowerFName, BorrowerLName, BorrowerPhoneNumber, BorrowerEmail)" +
                "VALUES (@BorrowerFName, @BorrowerLName, @BorrowerPhoneNumber, @BorrowerEmail)";
            SqlCommand insertCommand =
                new SqlCommand(insertStatement, connection);

            insertCommand.Parameters.AddWithValue(
                "@BorrowerFName", borrower.BorrowerFName);
            insertCommand.Parameters.AddWithValue(
                "@BorrowerLName", borrower.BorrowerLName);
            insertCommand.Parameters.AddWithValue(
                "@BorrowerPhoneNumber", borrower.BorrowerPhoneNumber);
            insertCommand.Parameters.AddWithValue(
                "@BorrowerEmail", borrower.BorrowerEmail);

            try
            {
                connection.Open();
                insertCommand.ExecuteNonQuery();
                string selectStatement =
                    "SELECT IDENT_CURRENT('Borrowers') FROM Borrowers";
                SqlCommand selectCommand =
                    new SqlCommand(selectStatement, connection);
                int customerID = Convert.ToInt32(selectCommand.ExecuteScalar());
                return(customerID);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        private void PopulateInvoice()
        {
            /* query to get the invoices matching to the selected borrower from the combo box,
             * using an alias so I can refference that borrowerId value in the PopulateNames method*/
            string query = "SELECT *" +
                           "FROM Invoices a INNER JOIN Borrowers b ON a.BorrowerId = b.BorrowerId " +
                           "WHERE b.BorrowerId = @BorrowerId";

            using (connection = TableclothDB.GetConnection())
                using (SqlCommand command = new SqlCommand(query, connection))
                    /* sqlcommand is the same as a data adapter except it allows parameters,
                     * and thats why we pass the query and connection there first unlike by populateName*/
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        command.Parameters.AddWithValue("@BorrowerId", cmbChooseBrrwr.SelectedValue);
                        DataTable invoiceTable = new DataTable();
                        adapter.Fill(invoiceTable);

                        lstInvoiceId.DisplayMember = "InvoiceId";
                        lstInvoiceId.ValueMember   = "InvoiceId";
                        lstInvoiceId.DataSource    = invoiceTable;

                        lstBorrowerId.DisplayMember = "BorrowerId";
                        lstBorrowerId.ValueMember   = "BorrowerId";
                        lstBorrowerId.DataSource    = invoiceTable;

                        lstProductId.DisplayMember = "ProductId";
                        lstProductId.ValueMember   = "ProductId";
                        lstProductId.DataSource    = invoiceTable;

                        lstQuantity.DisplayMember = "Quantity";
                        lstQuantity.ValueMember   = "Quantity";
                        lstQuantity.DataSource    = invoiceTable;

                        lstDate.DisplayMember = "InvoiceDate";
                        lstDate.ValueMember   = "InvoiceDate";
                        lstDate.DataSource    = invoiceTable;
                    }
        }
        private void btnDeleteInvoice_Click(object sender, EventArgs e)
        {
            bool   works;
            string message  = "Are you sure you want to delete invoice" + lstInvoiceId.SelectedValue + "?";
            string command1 = "SELECT *" +
                              "FROM Invoices a INNER JOIN Borrowers b ON a.BorrowerId = b.BorrowerId " +
                              "WHERE b.BorrowerId = @BorrowerId";

            DialogResult button =
                MessageBox.Show(message, "Confirm Delete",
                                MessageBoxButtons.YesNo);

            if (button == DialogResult.Yes)
            {
                using (connection = TableclothDB.GetConnection())
                    using (SqlCommand command = new SqlCommand(command1, connection))
                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            command.Parameters.AddWithValue("@BorrowerId", lstInvoiceId.SelectedValue);
                            DataTable invoiceTable = new DataTable();
                            adapter.Fill(invoiceTable);

                            string deleteStatement =
                                "DELETE FROM Invoices " +
                                "WHERE InvoiceId = @InvoiceId " +
                                "AND BorrowerId = @BorrowerId " +
                                "AND ProductId = @ProductId " +
                                "AND Quantity = @Quantity " +
                                "AND InvoiceDate = @InvoiceDate ";
                            SqlCommand deleteCommand =
                                new SqlCommand(deleteStatement, connection);
                            deleteCommand.Parameters.AddWithValue(
                                "@InvoiceId", Convert.ToInt32(lstInvoiceId.SelectedValue));
                            deleteCommand.Parameters.AddWithValue(
                                "@BorrowerId", Convert.ToInt32(lstBorrowerId.SelectedValue));
                            deleteCommand.Parameters.AddWithValue(
                                "@ProductId", Convert.ToInt32(lstProductId.SelectedValue));
                            deleteCommand.Parameters.AddWithValue(
                                "@Quantity", Convert.ToInt32(lstQuantity.SelectedValue));
                            deleteCommand.Parameters.AddWithValue(
                                "@InvoiceDate", Convert.ToDateTime(lstDate.SelectedValue));
                            try
                            {
                                connection.Open();
                                int count = deleteCommand.ExecuteNonQuery();
                                if (count > 0)
                                {
                                    works = true;
                                }
                                else
                                {
                                    works = false;
                                }
                                if (works == false)
                                {
                                    MessageBox.Show("Problem with SQL Command!");
                                }
                            }
                            catch (SqlException ex)
                            {
                                throw ex;
                            }
                            finally
                            {
                                connection.Close();
                            }
                        }

                PopulateInvoice();
            }
        }