コード例 #1
0
ファイル: RentalDB.cs プロジェクト: mmueller4991/Project2
        public static List<Rental> GetRental()
        {
            string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";

            List<Rental> rentalList = new List<Rental>();

            string rentalSQL = "SELECT movie_number, member_number, media_checkout_date," +
                                " media_return_date, media_exp_return_date, media_rental_cost FROM Rental";

            SqlCommand objRCommand = null;
            SqlConnection objRConn = null;
            SqlDataReader rentReader = null;

            try
            {
                using (objRConn = new SqlConnection(connectionString))
                {
                    //Opens the connection to the database
                    objRConn.Open();
                    //Command object created with the SQL statement
                    using (objRCommand = new SqlCommand(rentalSQL, objRConn))
                    {
                        //SQL executes and returns a DataReader
                        using ((rentReader = objRCommand.ExecuteReader(CommandBehavior.CloseConnection)))
                        {
                            while (rentReader.Read())
                            {
                                Rental objRent = new Rental();
                                objRent.MovieNumber = rentReader["movie_number"].ToString();
                                objRent.MemberNumber = rentReader["member_number"].ToString();
                                objRent.Checkedout = rentReader["media_checkout_date"].ToString();
                                objRent.Returned = rentReader["media_return_date"].ToString();
                                objRent.ExpectedReturn = rentReader["media_exp_return_date"].ToString();
                                objRent.RentalCost = rentReader["media_rental_cost"].ToString();

                                //Add Rental to collection
                                rentalList.Add(objRent);
                            }
                        }
                    }
                    return rentalList;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (objRConn != null)
                {
                    objRConn.Close();
                }
            }
        }
コード例 #2
0
ファイル: AdminScreen.cs プロジェクト: mmueller4991/Project2
        private void btnRentAdd_Click(object sender, EventArgs e)
        {
            if (txtRentMovNum.Text.Trim() == String.Empty)
            {
                MessageBox.Show("Please enter a Movie Number.", "",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtRentMovNum.Focus();
                return;
            }
            else
            {
                Movie objMovie = MovieDB.GetMovie(Convert.ToInt16(txtRentMovNum.Text.Trim()));
                if (objMovie != null)
                {
                    txtRentCost.Text = objMovie.RentalCost;
                }
                else
                {
                    MessageBox.Show("Movie Number " + txtRentMovNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    txtRentMovNum.Clear();
                    txtRentMovNum.Focus();
                    return;
                }
            }
            if (txtRentMemNum.Text.Trim() == String.Empty)
            {
                MessageBox.Show("Please enter a Member Number.", "",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtRentMemNum.Focus();
                return;
            }
            else
            {
                Member objMember = MemberDB.GetMember(Convert.ToInt16(txtRentMemNum.Text.Trim()));
                if (objMember == null)
                {
                    MessageBox.Show("Member Number " + txtRentMemNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    txtRentMemNum.Clear();
                    txtRentMemNum.Focus();
                    return;
                }
            }
            if (txtCheckout.Text.Trim() == String.Empty)
            {
                MessageBox.Show("Please enter a Date Checked Out.", "",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtCheckout.Focus();
                return;
            }
            if (txtExpReturn.Text.Trim() == String.Empty)
            {
                MessageBox.Show("Please enter an Exp. Return Date.", "",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtExpReturn.Focus();
                return;
            }
            if (txtReturn.Text.Trim() == String.Empty)
            {
                MessageBox.Show("Please enter a Date Returned.", "",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtReturn.Focus();
                return;
            }

            Rental objRental = new Rental();
            objRental.MovieNumber = txtRentMovNum.Text.Trim();
            objRental.MemberNumber = txtRentMemNum.Text.Trim();
            objRental.Checkedout = txtCheckout.Text.Trim();
            objRental.Returned = txtReturn.Text.Trim();
            objRental.ExpectedReturn = txtExpReturn.Text.Trim();
            objRental.RentalCost = txtRentCost.Text.Trim();
            try
            {
                bool status = RentalDB.AddRental(objRental);
                if (status) //You can use this syntax as well..if (status ==true)
                {
                    MessageBox.Show("Rental added to the database.", "",
                                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                    ClearRentalFields();
                    RentalListLoad();
                }
                else
                {
                    MessageBox.Show("Rental was not added to the database.", "",
                                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
コード例 #3
0
ファイル: AdminScreen.cs プロジェクト: mmueller4991/Project2
        private void btnRentDelete_Click(object sender, EventArgs e)
        {
            if (txtRentMovNum.Text.Trim() == String.Empty)
            {
                MessageBox.Show("Please enter a Movie Number.", "",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtRentMovNum.Focus();
                return;
            }
            else
            {
                Movie objMovie = MovieDB.GetMovie(Convert.ToInt16(txtRentMovNum.Text.Trim()));
                if (objMovie == null)
                {                  
                    MessageBox.Show("Movie Number " + txtRentMovNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    txtRentMovNum.Clear();
                    txtRentMovNum.Focus();
                    return;
                }
            }
            if (txtRentMemNum.Text.Trim() == String.Empty)
            {
                MessageBox.Show("Please enter a Member Number.", "",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtRentMemNum.Focus();
                return;
            }
            else
            {
                Member objMember = MemberDB.GetMember(Convert.ToInt16(txtRentMemNum.Text.Trim()));
                if (objMember == null)
                {
                    MessageBox.Show("Member Number " + txtRentMemNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    txtRentMemNum.Clear();
                    txtRentMemNum.Focus();
                    return;
                }
            }

            Rental objRental = new Rental();
            objRental.MovieNumber = txtRentMovNum.Text.Trim();
            objRental.MemberNumber = txtRentMemNum.Text.Trim();
            try
            {
                bool status = RentalDB.DeleteRental(objRental);
                if (status) //You can use this syntax as well..if (status ==true)
                {
                    MessageBox.Show("Rental deleted from the database.", "",
                                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                    ClearRentalFields();
                    RentalListLoad();
                }
                else
                {
                    MessageBox.Show("Rental was not deleted from the database.", "",
                                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
コード例 #4
0
        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (txtRentDelete.Text.Trim() == String.Empty)
            {
                MessageBox.Show("Please enter a Movie Number to delete.", "Rental Delete Error",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtRentalSave.Focus();
                return;
            }

            Rental objRental = new Rental();
            objRental.MovieNumber = txtRentDelete.Text.Trim();
            objRental.MemberNumber = mNumber.ToString();

            try
            {
                bool status = RentalDB.DeleteRental(objRental);
                if (status) //You can use this syntax as well..if (status ==true)
                {
                    MessageBox.Show("Movie deleted from database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("Movie was not found on list to delete.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            txtRentDelete.Clear();
        }       
コード例 #5
0
        private void btnMSave_Click(object sender, EventArgs e)
        {
            GetDate();
            string rCost = "";

            if (txtRentalSave.Text.Trim() == String.Empty)
            {
                MessageBox.Show("Please enter a Movie Number to save.", "Rental Save Error", 
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtRentalSave.Focus();
                return;
            }

            string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";
            string rentalSQL = "SELECT tape_rental_cost FROM Movie WHERE movie_number = '" +
                                    txtRentalSave.Text.Trim() + "'";
            SqlCommand objRentCommand = null;
            SqlConnection objRentConn = null;
            SqlDataReader rentReader = null;

            try
            {
                using (objRentConn = new SqlConnection(connectionString))
                {
                    //Opens the connection to the database
                    objRentConn.Open();
                    //Command object created with the SQL statement
                    using (objRentCommand = new SqlCommand(rentalSQL, objRentConn))
                    {
                        //SQL executes and returns a DataReader
                        using ((rentReader = objRentCommand.ExecuteReader(CommandBehavior.CloseConnection)))
                        {
                            while (rentReader.Read())
                            {                                
                                rCost = rentReader["tape_rental_cost"].ToString();
                            }
                        }
                    }
                }
                if (objRentConn != null)
                {
                    objRentConn.Close();
                }
                Rental objRental = new Rental();
                objRental.MovieNumber = txtRentalSave.Text.Trim();
                objRental.MemberNumber = mNumber.ToString();
                objRental.Checkedout = rentDate.ToString();
                objRental.Returned = returnDate.ToString();
                objRental.ExpectedReturn = returnDate.ToString();
                objRental.RentalCost = rCost.ToString();

                bool status = RentalDB.AddRental(objRental);
                if (status) //You can use this syntax as well..if (status ==true)
                {
                    MessageBox.Show("Movie added to rental list.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("Movie was not added to rental list.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            txtRentalSave.Clear();
        }
コード例 #6
0
ファイル: RentalDB.cs プロジェクト: mmueller4991/Project2
        public static Rental GetRental(int memNumber, int movNumber)
        {
            string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";

            string rentalSQL = "SELECT movie_number, member_number, media_checkout_date, media_return_date, " +
                               " media_exp_return_date, media_rental_cost FROM Rental" +
                               " WHERE member_number = @member_number AND movie_number = @movie_number";

            SqlCommand objRCommand = null;
            SqlConnection objRConn = null;
            SqlDataReader rentReader = null;
            Rental objRental = null;
            try
            {
                using (objRConn = new SqlConnection(connectionString))
                {
                    //Open the connection to the datbase
                    objRConn.Open();
                    //Create a command object with the SQL statement
                    using (objRCommand = new SqlCommand(rentalSQL, objRConn))
                    {
                        //Set command parameter
                        objRCommand.Parameters.AddWithValue("@member_number", memNumber);
                        objRCommand.Parameters.AddWithValue("@movie_number", movNumber);
                        //Execute the SQL and return a DataReader
                        using ((rentReader = objRCommand.ExecuteReader(CommandBehavior.CloseConnection)))
                        {
                            while (rentReader.Read())
                            {
                                objRental = new Rental();
                                //Fill the customer object if found
                                objRental.MovieNumber = rentReader["movie_number"].ToString();
                                objRental.MemberNumber = rentReader["member_number"].ToString();
                                objRental.Checkedout = rentReader["media_checkout_date"].ToString();
                                objRental.Returned = rentReader["media_return_date"].ToString();
                                objRental.ExpectedReturn = rentReader["media_exp_return_date"].ToString();
                                objRental.RentalCost = rentReader["media_rental_cost"].ToString();
                            }
                        }
                    }
                    return objRental;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (objRConn != null)
                {
                    objRConn.Close();
                }
            }
        }
コード例 #7
0
ファイル: RentalDB.cs プロジェクト: mmueller4991/Project2
        public static bool DeleteRental(Rental objRental)
        {
            string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";

            int rowsAffected = 0;

            SqlConnection objConn = null;
            SqlCommand objCommand = null;

            string sqlString;
            try
            {
                using (objConn = new SqlConnection(connectionString))
                {
                    //Open the connection to the datbase
                    objConn.Open();
                    sqlString = "DELETE Rental WHERE member_number = @member_number AND movie_number = @movie_number";

                    //Create a command object with the SQL statement
                    using (objCommand = new SqlCommand(sqlString, objConn))
                    {
                        //Use the command parameters method to set the paramater values of the SQL Insert statement
                        objCommand.Parameters.AddWithValue("@member_number", objRental.MemberNumber);
                        objCommand.Parameters.AddWithValue("@movie_number", objRental.MovieNumber);
                        //Execute the SQL and return the number of rows affected
                        rowsAffected = objCommand.ExecuteNonQuery();
                        //Close the database connection
                        objConn.Close();

                        if (rowsAffected > 0)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                }
            }
        }
コード例 #8
0
ファイル: RentalDB.cs プロジェクト: mmueller4991/Project2
        public static bool UpdateRental(Rental objRental)
        {
            string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";

            int rowsAffected = 0;

            SqlCommand objRCommand = null;
            SqlConnection obRjConn = null;

            string rentalSQL;
            try
            {
                using (obRjConn = new SqlConnection(connectionString))
                {
                    //Open the connection to the datbase
                    obRjConn.Open();
                    rentalSQL = "UPDATE rental " + Environment.NewLine +
                                " SET movie_number = @movie_number, " + Environment.NewLine +
                                "    member_number = @member_number, " + Environment.NewLine +
                                "    media_checkout_date = @media_checkout_date, " + Environment.NewLine +
                                "    media_return_date = @media_return_date, " + Environment.NewLine +
                                "    media_exp_return_date = @media_exp_return_date, " + Environment.NewLine +
                                "    media_rental_cost = @media_rental_cost " + Environment.NewLine +
                                " WHERE member_number = @member_number AND movie_number = @movie_number ";

                    //Create a command object with the SQL statement
                    using (objRCommand = new SqlCommand(rentalSQL, obRjConn))
                    {
                        //Use the command parameters method to set the paramater values of the SQL Insert statement
                        objRCommand.Parameters.AddWithValue("@movie_number", Convert.ToInt16(objRental.MovieNumber));
                        objRCommand.Parameters.AddWithValue("@member_number", Convert.ToInt16(objRental.MemberNumber));
                        objRCommand.Parameters.AddWithValue("@media_checkout_date", objRental.Checkedout);
                        objRCommand.Parameters.AddWithValue("@media_return_date", objRental.Returned);
                        objRCommand.Parameters.AddWithValue("@media_exp_return_date", objRental.ExpectedReturn);
                        objRCommand.Parameters.AddWithValue("@media_rental_cost", Convert.ToDouble(objRental.RentalCost));
                        //Execute the SQL and return the number of rows affected
                        rowsAffected = objRCommand.ExecuteNonQuery();
                    }
                    if (rowsAffected > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {                
                if (obRjConn != null)
                {
                    obRjConn.Close();
                }
            }
        }