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(); } }
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(); } }