/// <summary>
/// Writes a <see cref="Bookstore.Rental"/> to the database's table
/// </summary>
/// <param name="SQLStatement">The command to write a <see cref="Bookstore.Rental"/></param>
/// <param name="rental">The <see cref="Bookstore.Rental"/> that will have its data written to the table</param>
/// <returns>Whether or not the <see cref="Bookstore.Rental"/> was successfully written</returns>
private static bool WriteRental(string SQLStatement, Rental rental)
{
SqlCommand objCommand;
int rowsAffected;
bool result = false;
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue('@' + parameters[0], rental.movie_number);
objCommand.Parameters.AddWithValue('@' + parameters[1], rental.member_number);
objCommand.Parameters.AddWithValue('@' + parameters[2], rental.media_checkout_date);
objCommand.Parameters.AddWithValue('@' + parameters[3], rental.media_return_date);
rowsAffected = objCommand.ExecuteNonQuery();
if (rowsAffected > 0)
{
result = true; //Record was added successfully
}
}
objConn.Close();
}
return(result);
}
/// <summary>
/// Increases a <see cref="Bookstore.Movie"/>'s copies_on_hand
/// </summary>
/// <param name="rental">The <see cref="Bookstore.Rental"/> whose associated <see cref="Bookstore.Movie"/>'s copies_on_hand will be increased</param>
/// <returns>Whether or not the <see cref="Bookstore.Movie"/> was successful UPDATE'd</returns>
private static bool SQLUpdateMovieAdd(Rental rental)
{
string primary,
secondary,
SQLStatement;
SqlCommand objCommand;
int rowsAffected;
bool result = false;
primary = Movies.key + " = @" + Movies.key;
secondary = Movies.count + " = " + Movies.count + " + 1";
SQLStatement = SQLHelper.Update("Movie",
primary,
secondary
);
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue('@' + parameters[0], rental.movie_number);
rowsAffected = objCommand.ExecuteNonQuery();
if (rowsAffected > 0)
{
result = true; //Record was added successfully
}
}
objConn.Close();
}
return(result);
}
/// <summary>
/// Writes a <see cref="Bookstore.Movie"/> to the database's table
/// </summary>
/// <param name="SQLStatement">The command to write a <see cref="Bookstore.Movie"/></param>
/// <param name="movie">The <see cref="Bookstore.Movie"/> that will have its data written to the table</param>
/// <returns>Whether or not the <see cref="Bookstore.Movie"/> was successfully written</returns>
private static bool WriteMovie(string SQLStatement, Movie movie)
{
SqlCommand objCommand;
int rowsAffected;
bool result = false;
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue('@' + parameters[0], movie.movie_number);
objCommand.Parameters.AddWithValue('@' + parameters[1], movie.movie_title);
objCommand.Parameters.AddWithValue('@' + parameters[2], movie.Description);
objCommand.Parameters.AddWithValue('@' + parameters[3], movie.movie_year_made);
objCommand.Parameters.AddWithValue('@' + parameters[4], movie.genre_id);
objCommand.Parameters.AddWithValue('@' + parameters[5], movie.movie_rating);
objCommand.Parameters.AddWithValue('@' + parameters[6], movie.media_type);
objCommand.Parameters.AddWithValue('@' + parameters[7], movie.movie_retail_cost);
objCommand.Parameters.AddWithValue('@' + parameters[8], movie.copies_on_hand);
objCommand.Parameters.AddWithValue('@' + parameters[9], movie.image);
objCommand.Parameters.AddWithValue('@' + parameters[10], movie.trailer);
rowsAffected = objCommand.ExecuteNonQuery();
if (rowsAffected > 0)
{
result = true; //Record was added successfully
}
}
objConn.Close();
}
return(result);
}
/// <summary>
/// Reads from a <see cref="Bookstore.Member"/>, and puts the relevant fields into a <see cref="Bookstore.Rental"/>
/// </summary>
/// <param name="rental">The <see cref="Bookstore.Rental"/> that will be read into</param>
private static void ReadMember(Rental rental)
{
string SQLStatement;
SqlCommand objCommand;
SqlDataReader memberReader;
SQLStatement = SQLHelper.Select("Member",
" FROM " + "Member",
string.Empty,
Members.extra1 + ", Member." + Members.extra2
) + " WHERE ";
SQLStatement += "Member." + Members.key + " = @" + Members.key;
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue('@' + Members.key, rental.member_number);
using ((memberReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
while (memberReader.Read())
{
DateTime joindate = new DateTime(1753, 1, 1, 0, 0, 0);
DateTime.TryParse(memberReader[Members.extra1].ToString(), out joindate);
rental.joindate = joindate;
rental.member_status = memberReader[Members.extra2].ToString();
}
}
}
objConn.Close();
}
}
/// <summary>
/// Get the maximum value of the primary key in a table
/// </summary>
/// <param name="tableName">The name of the table to get the MAX of</param>
/// <param name="key">The field to get the MAX of</param>
/// <returns>The maximum value of the primary key</returns>
public static int GetMax(string tableName, string key)
{
string SQLStatement;
int max;
SqlCommand objCommand;
SqlDataReader reader;
SQLStatement = SQLHelper.Select("MAX(" + tableName,
" FROM " + tableName,
key,
")");
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
using ((reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
reader.Read();
Int32.TryParse(reader[0].ToString(), out max);
}
}
objConn.Close();
}
return(max);
}
/// <summary>
/// Returns a list of generic type objects from the table
/// </summary>
/// <returns>All fields of all <see cref="Bookstore.Rental"/>'s, plus the extras from <see cref="Bookstore.Movie"/> and <see cref="Bookstore.Member"/></returns>
/// <exception cref="System.Exception" />
public static List <Rental> GetRentals()
{
List <Rental> rentals = new List <Rental>();
SqlCommand objCommand;
SqlDataReader rentalReader;
//Step #1: Add code to call the appropriate method from the inherited AccessDataSQLServer class
//To return a database connection object
try
{
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
//Step #2: Code Logic to create appropriate SQL Server objects calls
// Code Logic to retrieve data from database
// Add Try..Catch appropriate block and throw exception back to calling program
using (objCommand = new SqlCommand(SQLGetList, objConn))
{
//Step #3: Return the objtemp generic list variable back to the calling UI
using ((rentalReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
while (rentalReader.Read())
{
Rental objRental = new Rental();
int movie_number,
member_number;
DateTime media_checkout_date,
joindate;
Int32.TryParse(rentalReader[parameters[0]].ToString(), out movie_number);
objRental.movie_number = movie_number;
Int32.TryParse(rentalReader[parameters[1]].ToString(), out member_number);
objRental.member_number = member_number;
DateTime.TryParse(rentalReader[parameters[2]].ToString(), out media_checkout_date);
objRental.media_checkout_date = media_checkout_date;
SetSecondary(rentalReader, objRental);
objRental.movie_title = rentalReader[Movies.extra].ToString();
DateTime.TryParse(rentalReader[Members.extra1].ToString(), out joindate);
objRental.joindate = joindate;
objRental.member_status = rentalReader[Members.extra2].ToString();
objRental.login_name = rentalReader[Members.extra3].ToString();
rentals.Add(objRental);
}
}
}
objConn.Close();
}
}
catch (SqlException SQLex)
{
throw new Exception(SQLex.Message);
}
catch (InvalidOperationException IOex)
{
throw new Exception(IOex.Message);
}
return(rentals);
}
/// <summary>
/// Deletes a record from the database with a Boolean returned status of True or False
/// </summary>
/// <param name="rental">accepts a custom object of that type as a parameter</param>
/// <returns>Whether or not the <see cref="Bookstore.Rental"/> was successfully deleted</returns>
/// <exception cref="System.Exception" />
public static bool DeleteRental(ref Rental rental)
{
SqlCommand objCommand;
int rowsAffected;
bool result = false;
//Step# 1: Add code to call the appropriate method from the inherited AccessDataSQLServer class
//To return a database connection object
try
{
rental = GetRental(rental.movie_number, rental.member_number, rental.media_checkout_date);
if (rental == null)
{
throw new Exception(Rental.doesNotExist); //Record was not added successfully
}
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
//Step #2: Code logic to create appropriate SQL Server objects calls
// Code logic to retrieve data from database
// Add Try..Catch appropriate block and throw exception back to calling program
using (objCommand = new SqlCommand(SQLDeleteRental, objConn))
{
objCommand.Parameters.AddWithValue('@' + parameters[0], rental.movie_number);
objCommand.Parameters.AddWithValue('@' + parameters[1], rental.member_number);
objCommand.Parameters.AddWithValue('@' + parameters[2], rental.media_checkout_date);
//Step #3: return false if record was not added successfully
// return true if record was added successfully
rowsAffected = objCommand.ExecuteNonQuery();
if (rowsAffected > 0)
{
result = true; //Record was added successfully
}
}
objConn.Close();
}
if ((result) && (rental.media_return_date < rental.media_checkout_date)) //Only when the movie hasn't been returned
{
result = SQLUpdateMovieAdd(rental);
}
}
catch (SqlException SQLex)
{
throw new Exception(SQLex.Message); //Record was not added successfully
}
catch (InvalidOperationException IOex)
{
throw new Exception(IOex.Message); //Record was not added successfully
}
return(result);
}
/// <summary>
/// Returns a single record from the table whose parameter matches a table field condition
/// </summary>
/// <param name="parameter">accepts a parameter to return a specific record</param>
/// <returns>All the fields (except the primary key) of a <see cref="Bookstore.Movie"/></returns>
/// <exception cref="System.Exception" />
public static Movie GetMovie(int parameter)//string parameter)
{
Movie objMovie = null;
string SQLStatement;
SqlCommand objCommand;
SqlDataReader movieReader;
SQLStatement = SQLGetMovie;
SQLStatement += "Movie." + parameters[0] + " = @" + parameters[0];
//Step #1: Add code to call the appropriate method from the inherited AccessDataSQLServer class
//To return a database connection object
try
{
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
//Step #2: Code logic to create appropriate SQL Server objects calls
// Code logic to retrieve data from database
// Add Try..Catch appropriate block and throw exception back to calling program
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue('@' + parameters[0], parameter);
//Step #3: Return the objtemp variable back to the calling UI
using ((movieReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
while (movieReader.Read())
{
objMovie = new Movie();
objMovie.movie_number = parameter;
SetSecondary(movieReader, objMovie);
}
}
}
objConn.Close();
}
}
catch (SqlException SQLex)
{
throw new Exception(SQLex.Message);
}
catch (InvalidOperationException IOex)
{
throw new Exception(IOex.Message);
}
return(objMovie);
}
/// <summary>
/// Returns a list of generic type objects from the table
/// </summary>
/// <returns>All fields of all <see cref="Bookstore.Movie"/>'s, plus the extras from <see cref="Bookstore.Genre"/></returns>
/// <exception cref="System.Exception" />
public static List <Movie> GetMovies()
{
List <Movie> movies = new List <Movie>();
SqlCommand objCommand;
SqlDataReader movieReader;
//Step #1: Add code to call the appropriate method from the inherited AccessDataSQLServer class
//To return a database connection object
try
{
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
//Step #2: Code Logic to create appropriate SQL Server objects calls
// Code Logic to retrieve data from database
// Add Try..Catch appropriate block and throw exception back to calling program
using (objCommand = new SqlCommand(SQLGetList, objConn))
{
//Step #3: Return the objtemp generic list variable back to the calling UI
using ((movieReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
while (movieReader.Read())
{
Movie objMovie = new Movie();
int movie_number;
Int32.TryParse(movieReader[parameters[0]].ToString(), out movie_number);
objMovie.movie_number = movie_number;
SetSecondary(movieReader, objMovie);
objMovie.name = movieReader[Genres.extra].ToString();
movies.Add(objMovie);
}
}
}
objConn.Close();
}
}
catch (SqlException SQLex)
{
throw new Exception(SQLex.Message);
}
catch (InvalidOperationException IOex)
{
throw new Exception(IOex.Message);
}
return(movies);
}
/// <summary>
/// Deletes a record from the database with a Boolean returned status of True or False
/// </summary>
/// <param name="movie">accepts a custom object of that type as a parameter</param>
/// <returns>Whether or not the <see cref="Bookstore.Movie"/> was successfully deleted</returns>
/// <exception cref="System.Exception" />
public static bool DeleteMovie(Movie movie)
{
SqlCommand objCommand;
int rowsAffected;
bool result = false;
//Step# 1: Add code to call the appropriate method from the inherited AccessDataSQLServer class
//To return a database connection object
try
{
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
//Step #2: Code logic to create appropriate SQL Server objects calls
// Code logic to retrieve data from database
// Add Try..Catch appropriate block and throw exception back to calling program
using (objCommand = new SqlCommand(SQLDeleteMovie, objConn))
{
objCommand.Parameters.AddWithValue('@' + parameters[0], movie.movie_number);
//Step #3: return false if record was not added successfully
// return true if record was added successfully
rowsAffected = objCommand.ExecuteNonQuery();
if (rowsAffected > 0)
{
result = true; //Record was added successfully
}
}
objConn.Close();
}
}
catch (SqlException SQLex)
{
throw new Exception(SQLex.Message); //Record was not added successfully
}
catch (InvalidOperationException IOex)
{
throw new Exception(IOex.Message); //Record was not added successfully
}
return(result);
}
/// <summary>
/// Writes a <see cref="Bookstore.Genre"/> to the database's table
/// </summary>
/// <param name="SQLStatement">The command to write a <see cref="Bookstore.Genre"/></param>
/// <param name="genre">The <see cref="Bookstore.Genre"/> that will have its data written to the table</param>
/// <returns>Whether or not the <see cref="Bookstore.Genre"/> was successfully written</returns>
private static bool WriteGenre(string SQLStatement, Genre genre)
{
SqlCommand objCommand;
int rowsAffected;
bool result = false;
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue('@' + parameters[0], genre.id);
objCommand.Parameters.AddWithValue('@' + parameters[1], genre.name);
rowsAffected = objCommand.ExecuteNonQuery();
if (rowsAffected > 0)
{
result = true; //Record was added successfully
}
}
objConn.Close();
}
return(result);
}
/// <summary>
/// Writes a <see cref="Bookstore.Member"/> to the database's table
/// </summary>
/// <param name="SQLStatement">The command to write a <see cref="Bookstore.Member"/></param>
/// <param name="member">The <see cref="Bookstore.Member"/> that will have its data written to the table</param>
/// <returns>Whether or not the <see cref="Bookstore.Member"/> was successfully written</returns>
private static bool WriteMember(string SQLStatement, Member member)
{
SqlCommand objCommand;
int rowsAffected;
bool result = false;
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue('@' + parameters[0], member.number);
objCommand.Parameters.AddWithValue('@' + parameters[1], member.joindate);
objCommand.Parameters.AddWithValue('@' + parameters[2], member.firstname);
objCommand.Parameters.AddWithValue('@' + parameters[3], member.lastname);
objCommand.Parameters.AddWithValue('@' + parameters[4], member.address);
objCommand.Parameters.AddWithValue('@' + parameters[5], member.city);
objCommand.Parameters.AddWithValue('@' + parameters[6], member.state);
objCommand.Parameters.AddWithValue('@' + parameters[7], member.zipcode);
objCommand.Parameters.AddWithValue('@' + parameters[8], member.phone);
objCommand.Parameters.AddWithValue('@' + parameters[9], member.member_status);
objCommand.Parameters.AddWithValue('@' + parameters[10], member.login_name);
objCommand.Parameters.AddWithValue('@' + parameters[11], member.password);
objCommand.Parameters.AddWithValue('@' + parameters[12], member.email);
objCommand.Parameters.AddWithValue('@' + parameters[13], member.contact_method);
objCommand.Parameters.AddWithValue('@' + parameters[14], member.subscription_id);
objCommand.Parameters.AddWithValue('@' + parameters[15], member.photo);
rowsAffected = objCommand.ExecuteNonQuery();
if (rowsAffected > 0)
{
result = true; //Record was added successfully
}
}
objConn.Close();
}
return(result);
}
/// <summary>
/// Returns a single record from the table whose parameter matches a table field condition
/// </summary>
/// <param name="parameter1">accepts a parameter to return a specific record, movie_number</param>
/// <param name="parameter2">member_number</param>
/// <param name="parameter3">media_checkout_date</param>
/// <returns>All the fields (except the primary keys) of a <see cref="Bookstore.Rental"/></returns>
/// <exception cref="System.Exception" />
public static Rental GetRental(int parameter1, int parameter2, DateTime parameter3)//string parameter)
{
Rental objRental = null;
string SQLStatement;
SqlCommand objCommand;
SqlDataReader rentalReader;
SQLStatement = SQLGetRental;
if (parameter1 > -1)
{
SQLStatement += "Rental." + parameters[0] + " = @" + parameters[0];
if ((parameter2 > -1) || (parameter3 > new DateTime(1753, 1, 1, 0, 0, 0)))
{
SQLStatement += " AND ";
}
}
if (parameter2 > -1)
{
SQLStatement += "Rental." + parameters[1] + " = @" + parameters[1];
if (parameter3 > new DateTime(1753, 1, 1, 0, 0, 0))
{
SQLStatement += " AND ";
}
}
if (parameter3 > new DateTime(1753, 1, 1, 0, 0, 0))
{
SQLStatement += "Rental." + parameters[2] + " = @" + parameters[2]; //TODO make four parameters
}
//Step #1: Add code to call the appropriate method from the inherited AccessDataSQLServer class
//To return a database connection object
try
{
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
//Step #2: Code logic to create appropriate SQL Server objects calls
// Code logic to retrieve data from database
// Add Try..Catch appropriate block and throw exception back to calling program
int i = 0;
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue('@' + parameters[0], parameter1);
objCommand.Parameters.AddWithValue('@' + parameters[1], parameter2);
objCommand.Parameters.AddWithValue('@' + parameters[2], parameter3);
//Step #3: Return the objtemp variable back to the calling UI
using ((rentalReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
while (rentalReader.Read())
{
objRental = new Rental();
int movie_number,
member_number;
DateTime media_checkout_date = new DateTime(1753, 1, 1, 0, 0, 0);
Int32.TryParse(rentalReader[parameters[0]].ToString(), out movie_number);
objRental.movie_number = movie_number;
Int32.TryParse(rentalReader[parameters[1]].ToString(), out member_number);
objRental.member_number = member_number;
DateTime.TryParse(rentalReader[parameters[2]].ToString(), out media_checkout_date);
objRental.media_checkout_date = media_checkout_date;
SetSecondary(rentalReader, objRental);
i++;
}
}
}
objConn.Close();
if (i > 1)
{
throw new Exception(Rental.notUnique);
}
}
}
catch (SqlException SQLex)
{
throw new Exception(SQLex.Message);
}
catch (InvalidOperationException IOex)
{
throw new Exception(IOex.Message);
}
return(objRental);
}
/// <summary>
/// This method should determine if the user logging into the database is valid or not. The method should return a Boolean (True or False).
/// </summary>
/// <returns>Whether or not the password provided matches the password in the table</returns>
/// <exception cref="System.Exception" />
public bool IsValid()
{
string SQLStatement;
SqlCommand objCommand;
SqlDataReader memberReader;
bool result = false;
SQLStatement = SQLHelper.Select("Member",
" FROM " + "Member",
"password",
string.Empty
) + " WHERE ";
SQLStatement += "Member.login_name = @Credentials";
//The IsValid method will be using the SqlConnection, SqlCommand and SqlData Reader objects.
//Note: **The IsValid() method is reading the member table to see if the credentials that are passed in are valid.
try
{
using (SqlConnection objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue("@Credentials", Credentials);
using ((memberReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
while (memberReader.Read())
{
string password;
password = memberReader["password"].ToString();
if (password.Equals(Password))
{
result = true; //represents the credentials are valid and you should enable the Menu Items.
}
else
{
result = false; //represents the credentials are invalid and should not enabled the Menu Items.
}
}
}
}
objConn.Close();
}
}
catch (SqlException SQLex)
{
throw new Exception(SQLex.Message);
}
catch (InvalidOperationException IOex)
{
throw new Exception(IOex.Message);
}
return(result);
}