public static List <Video> GetVideo()
        {
            List <Video>  videos          = new List <Video>();
            SqlConnection connection      = VideoConnection.GetConnection();
            string        selectstatement = "SELECT VideoName, VideoID " + "FROM Video " + "ORDER BY VideoName";
            SqlCommand    selectCommand   = new SqlCommand(selectstatement, connection);

            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    Video video = new Video();
                    video.Videoname = reader["VideoName"].ToString();

                    videos.Add(video);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(videos);
        }
        public static bool UpdateVideo(Video newVideo, Video oldVideo)
        {
            SqlConnection connection    = VideoConnection.GetConnection();
            SqlCommand    updateCommand = new SqlCommand();

            updateCommand.CommandText = "Update_Video";
            updateCommand.CommandType = CommandType.StoredProcedure;
            updateCommand.Connection  = connection;
            updateCommand.Parameters.AddWithValue("@VideoName", newVideo.Videoname);
            updateCommand.Parameters.AddWithValue("@YearOfRelease", newVideo.Yearofrelease);
            updateCommand.Parameters.AddWithValue("@AgeRating", newVideo.Agerating);
            updateCommand.Parameters.AddWithValue("@Quantity", newVideo.Quantity);

            updateCommand.Parameters.AddWithValue("@VideoName", oldVideo.Videoname);
            updateCommand.Parameters.AddWithValue("@YearOfRelease", oldVideo.Yearofrelease);
            updateCommand.Parameters.AddWithValue("@AgeRating", oldVideo.Agerating);
            updateCommand.Parameters.AddWithValue("@Quantity", oldVideo.Quantity);



            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (DBConcurrencyException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Exemplo n.º 3
0
        public static string AddVideo(Video video, byte[] imgdata)
        {
            SqlConnection connection = VideoConnection.GetConnection();
            SqlCommand    addCommand = new SqlCommand();

            addCommand.Connection  = connection;
            addCommand.CommandType = CommandType.StoredProcedure;
            addCommand.CommandText = "Insert_Video";
            addCommand.Parameters.AddWithValue("@VideoName", video.Videoname);
            addCommand.Parameters.AddWithValue("@YearOfRelease", video.Yearofrelease);
            addCommand.Parameters.AddWithValue("@AgeRating", video.Agerating);
            addCommand.Parameters.AddWithValue("@Quantity", video.Quantity);
            addCommand.Parameters.AddWithValue("@Genre", video.Genre);
            SqlParameter sqlparam = addCommand.Parameters.AddWithValue("@Image", imgdata);

            sqlparam.DbType = DbType.Binary;

            try
            {
                connection.Open();
                const string message = "Record has been successfully committed to database";
                int          count;
                count = addCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return(message);
                }
                else
                {
                    return("The Record Was not committed to the database");
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (DBConcurrencyException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        public static Video Select_by_Video_Name(string VideoName)
        {
            Video         video         = new Video();
            SqlConnection connection    = VideoConnection.GetConnection();
            SqlCommand    selectCommand = new SqlCommand();

            selectCommand.Connection  = connection;
            selectCommand.CommandType = CommandType.StoredProcedure;
            selectCommand.CommandText = "Select_Video";
            selectCommand.Parameters.AddWithValue("@VideoName", VideoName);
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.Read())
                {
                    video.Videoname     = reader["VideoName"].ToString();
                    video.Agerating     = (int)reader["AgeRating"];
                    video.Yearofrelease = reader["YearOfRelease"].ToString();
                    video.Quantity      = (int)reader["Quantity"];
                    video.Genre         = reader["Genre"].ToString();
                    byte[] imgdata = (byte[])reader["Image"];
                    video.ImageData = imgdata;
                }
                else
                {
                    video = null;
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            finally
            {
                connection.Close();
            }
            return(video);
        }
        public static string AddTransaction(Transaction transaction)
        {
            SqlConnection connection = VideoConnection.GetConnection();
            SqlCommand    addCommand = new SqlCommand();

            addCommand.Connection  = connection;
            addCommand.CommandType = CommandType.StoredProcedure;
            addCommand.CommandText = "INSERT_TRANSACTION";
            addCommand.Parameters.AddWithValue("@TransactionID", transaction.TransactionID);
            addCommand.Parameters.AddWithValue("@CustomerID", transaction.CustomerID);
            addCommand.Parameters.AddWithValue("@VideoName", transaction.VideoName);
            addCommand.Parameters.AddWithValue("@TDborrowed", transaction.TdBorrowed);
            addCommand.Parameters.AddWithValue("@RDate", transaction.RDate);
            addCommand.Parameters.AddWithValue("@AmountPaid", transaction.AmountPaid);
            addCommand.Parameters.AddWithValue("@LateReturnFee", transaction.LatereturnFee);
            addCommand.Parameters.AddWithValue("@Quantity", transaction.Quantity);
            try
            {
                connection.Open();
                const string message = "Transaction has been carried out successfully";
                addCommand.ExecuteNonQuery();
                return(message);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (DBConcurrencyException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        // method to select videos form the database by their name

        // method to select video row from database using the year of release as search criterion

        //public static Video Select_by_Video_YearOfRelease(DateTime releaseDate)
        //{
        //    Video video = new Video();
        //    SqlConnection connection = VideoConnection.GetConnection();
        //    SqlCommand selectCommand = new SqlCommand();
        //    selectCommand.Connection = connection;
        //    selectCommand.CommandType = CommandType.StoredProcedure;
        //    selectCommand.CommandText = "Select_Video";
        //    selectCommand.Parameters.AddWithValue("@VideoName", DBNull.Value );
        //    selectCommand.Parameters.AddWithValue("@YearOfrelease", releaseDate);
        //    selectCommand.Parameters.AddWithValue("@AgeRating", DBNull.Value);
        //    try
        //    {
        //        connection.Open();
        //        SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
        //        if (reader.Read())
        //        {
        //            video.Videoid = (int)reader["VideoID"];
        //            video.Videoname = reader["VideoName"].ToString();
        //            video.Yearofrelease = reader["YearOfrelease"].ToString();
        //            video.Agerating = (int)reader["AgeRating"];
        //            video.Quantity = (int)reader["Quantity"];
        //        }
        //        else
        //        {
        //            video = null;
        //        }
        //        reader.Close();
        //    }
        //    catch (SqlException ex)
        //    {
        //        throw ex;
        //    }
        //    catch (DBConcurrencyException ex)
        //    {
        //        throw ex;
        //    }

        //    finally
        //    {
        //        connection.Close();
        //    }
        //    return video;

        //}
        // method for select videos based on the age input query

        // public static Video Select_by_Video_Agerating(int AgeRating)
        //{
        //    Video video = new Video();
        //    SqlConnection connection = VideoConnection.GetConnection();
        //    SqlCommand selectCommand = new SqlCommand();
        //    selectCommand.Connection = connection;
        //    selectCommand.CommandType = CommandType.StoredProcedure;
        //    selectCommand.CommandText = "Select_Video";
        //    selectCommand.Parameters.AddWithValue("@VideoName", DBNull.Value);
        //    selectCommand.Parameters.AddWithValue("@YearOfrelease", DBNull.Value);
        //    selectCommand.Parameters.AddWithValue("@AgeRating", AgeRating);
        //    try
        //    {
        //        connection.Open();
        //        SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
        //        if (reader.Read())
        //        {
        //            video.Videoid = (int)reader["VideoID"];
        //            video.Videoname = reader["VideoName"].ToString();
        //            video.Yearofrelease = reader["YearOfrelease"].ToString();
        //            video.Agerating = (int)reader["AgeRating"];
        //            video.Quantity = (int)reader["Quantity"];
        //        }
        //        else
        //        {
        //            video = null;
        //        }
        //        reader.Close();
        //    }
        //    catch (SqlException ex)
        //    {
        //        throw ex;
        //    }
        //    catch (DBConcurrencyException ex)
        //    {
        //        throw ex;
        //    }

        //    finally
        //    {
        //        connection.Close();
        //    }
        //    return video;

        //}
        public static bool DeleteVideo(Video video)
        {
            SqlConnection connection    = VideoConnection.GetConnection();
            SqlCommand    deleteCommand = new SqlCommand();

            deleteCommand.CommandText = "Delete_Video";
            deleteCommand.CommandType = CommandType.StoredProcedure;
            deleteCommand.Connection  = connection;
            deleteCommand.Parameters.AddWithValue("@VideoName", video.Videoname);
            deleteCommand.Parameters.AddWithValue("@yearOfRelease", video.Yearofrelease);
            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (DBConcurrencyException exception)
            {
                throw exception;
            }
            finally
            {
                connection.Close();
            }
        }