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