public static Rental GetRental(int movie_number, int member_number, string media_checkout_date)
{
//Pre-step: Replace the general object parameter with the appropriate data type parameter for retrieving a specific item from the specific database table.
string SQLStatement = String.Empty;
//Change the MyCustomObject references to your customer business object
//Rental objTemp = new Rental();
string sqlString = "Select movie_number, member_number, media_checkout_date, media_return_date " +
"from rental where movie_number = @movie_number AND member_number = @member_number AND media_checkout_date = @media_checkout_date;";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader rentalReader = null;
Rental objRental = null;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
//Set command parameter
objCommand.Parameters.AddWithValue("@movie_number", movie_number);
objCommand.Parameters.AddWithValue("@member_number", member_number);
objCommand.Parameters.AddWithValue("@media_checkout_date", media_checkout_date);
//Execute the SQL and return a DataReader
using (rentalReader = objCommand.ExecuteReader())
{
while (rentalReader.Read())
{
objRental = new Rental();
//Fill the customer object if found
objRental.movie_number = rentalReader["movie_number"].ToString();
objRental.member_number = rentalReader["member_number"].ToString();
objRental.media_checkout_date = rentalReader["media_checkout_date"].ToString();
objRental.media_return_date = rentalReader["media_return_date"].ToString();
}
}
}
return(objRental);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static bool UpdateRental(Rental objRental)
{
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "UPDATE Rental " + Environment.NewLine +
"set member_number = @member_number, " + Environment.NewLine +
"movie_number = @movie_number, " + Environment.NewLine +
"media_checkout_date = @media_checkout_date, " + Environment.NewLine +
"media_return_date = @media_return_date " + Environment.NewLine +
"where member_number = @memeber_number AND movie_number = @movie_number AND media_checkout_date = @media_checkout_date;";
//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("@movie_number", objRental.movie_number);
objCommand.Parameters.AddWithValue("@member_number", objRental.member_number);
objCommand.Parameters.AddWithValue("@media_checkout_date", objRental.media_checkout_date);
objCommand.Parameters.AddWithValue("@media_return_date", objRental.media_return_date);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static bool DeleteGenre(Genre objGenre)
{
//Pre-step: Replace the general object parameter with the appropriate business class object that you are using to insert data in the underline database table
string SQLStatement = String.Empty;
//Uncomment either Example #1 or #2 to use appropriate connection string
//Example #1 for connecting to a remote SQL Server instance via IP address and SQL Server authenication..For Meramec
//string connectionString = "Server=mc-sluggo.stlcc.edu;Database=IS253_251;User Id=csharp2;Password=csharp2;";
//Example #2 for connecting to SQL Server locally with Windows Authenication. Change accordingly to your environment.
//string connectionString = @"Data Source=STEVIE-LAPTOP\MSSQLSERVER1;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "Delete Genre where id = @genre_id";
//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("@genre_id", objGenre.ID);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static bool AddRental(Rental objRental)
{
//Pre-step: Replace the general object parameter with the appropriate business class object that you are using to insert data in the underline database table
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "INSERT into Rental (movie_number, member_number, media_checkout_date) " +
" values (@movie_number, @member_number, @media_checkout_date)";
//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("@movie_number", objRental.movie_number);
objCommand.Parameters.AddWithValue("@member_number", objRental.member_number);
objCommand.Parameters.AddWithValue("@media_checkout_date", objRental.media_checkout_date);
//objCommand.Parameters.AddWithValue("@media_return_date",objRental.media_return_date);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static bool UpdateSubscription(Subscription objSubscription)
{
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "UPDATE Subscription " + Environment.NewLine +
"set name = @subscription_name, " + Environment.NewLine +
"set cost = @cost " + Environment.NewLine +
"where id = @subscription_id ";
//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("@subscription_name", objSubscription.Name);
objCommand.Parameters.AddWithValue("@subscription_id", objSubscription.ID);
objCommand.Parameters.AddWithValue("@cost", objSubscription.Cost);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static Subscription GetSubscription(int subscriptionID)
{
//Pre-step: Replace the general object parameter with the appropriate data type parameter for retrieving a specific item from the specific database table.
string SQLStatement = String.Empty;
//Change the MyCustomObject references to your customer business object
//Subscription objTemp = new Subscription();
string sqlString = "Select id, name, cost from subscription where id = @subscription_id order by id";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader subReader = null;
Subscription objSubscription = null;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
//Set command parameter
objCommand.Parameters.AddWithValue("@subscription_id", subscriptionID);
//Execute the SQL and return a DataReader
using (subReader = objCommand.ExecuteReader())
{
while (subReader.Read())
{
objSubscription = new Subscription();
//Fill the customer object if found
objSubscription.ID = subReader["id"].ToString();
objSubscription.Name = subReader["name"].ToString();
objSubscription.Cost = Convert.ToDecimal(subReader["cost"]);
}
}
}
return(objSubscription);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
//Instructions:
//Replace all ???TableNameHere phrases with the name of your specific SQL Server Database Table Name
//Replace yourCustomeObject phrase with the name of the business object (represents database table name) you are referencing or returning
//Replace datatype phrase with the appropriate C# data type or custom data type based on Project #2 CRUD specs
//Replace parameter phrase with the appropriate input parameter based on Project #2 CRUD specs
//Refer to the ADO.Net Demo for method examples below
public static List <Rental> GetRentals()
{
//Change the MyCustomObject name to your customer business object that is returning data from the specific table
List <Rental> objTemp = new List <Rental>();
string SQLStatement = "select movie_number, member_number, media_checkout_date, media_return_date " +
"from rental order by movie_number";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader objReader = null;
try
{
//using (objConn = AccessDataSQLServer.GetConnection())
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the database
objConn.Open();
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
//Execute the SQL and return a DataReader Object
using (objReader = objCommand.ExecuteReader())
{
while (objReader.Read())
{
Rental objRental = new Rental();
objRental.movie_number = objReader["movie_number"].ToString();
objRental.member_number = objReader["member_number"].ToString();
objRental.media_checkout_date = objReader["media_checkout_date"].ToString();
objRental.media_return_date = objReader["media_return_date"].ToString();
//Add the rental to the collection
objTemp.Add(objRental);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objConn != null)
{
objConn.Close();
}
}
return(objTemp);
}
//Instructions:
//Replace all ???TableNameHere phrases with the name of your specific SQL Server Database Table Name
//Replace yourCustomeObject phrase with the name of the business object (represents database table name) you are referencing or returning
//Replace datatype phrase with the appropriate C# data type or custom data type based on Project #2 CRUD specs
//Replace parameter phrase with the appropriate input parameter based on Project #2 CRUD specs
//Refer to the ADO.Net Demo for method examples below
//public static void populateSubscription()
//{
// string[] subscriptions = {"Comedy","Crime","Documentary","Drama","Family","Fantasy","Film Noir","History","Horror","Music","Musical","Mystery","Romance","Sci-Fi","Short","Sport","Superhero","Thriller","War","Western" };
// string sql = "INSERT into subscription (id, name) values (@id, @name)";
// SqlCommand objCommand = null;
// SqlConnection objConn = null;
// using (objConn = AccessDataSQLServer.GetConnection())
// {
// objConn.Open();
// for (int i = 5; i < subscriptions.Length+5; i++ )
// {
// using (objCommand = new SqlCommand(sql, objConn))
// {
// objCommand.Parameters.AddWithValue("@id", i);
// objCommand.Parameters.AddWithValue("@name", subscriptions[i - 5]);
// objCommand.ExecuteNonQuery();
// }
// }
// objConn.Close();
// }
//}
public static List <Subscription> GetSubscriptions()
{
//Change the MyCustomObject name to your customer business object that is returning data from the specific table
List <Subscription> objTemp = new List <Subscription>();
string SQLStatement = "select id, name, cost from subscription order by id";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader objReader = null;
try
{
//using (objConn = AccessDataSQLServer.GetConnection())
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the database
objConn.Open();
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
//Execute the SQL and return a DataReader Object
using (objReader = objCommand.ExecuteReader())
{
while (objReader.Read())
{
Subscription objSubscription = new Subscription();
objSubscription.ID = objReader["id"].ToString();
objSubscription.Name = objReader["name"].ToString();
objSubscription.Cost = Convert.ToDecimal(objReader["cost"]);
//Add the subscription to the collection
objTemp.Add(objSubscription);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objConn != null)
{
objConn.Close();
}
}
return(objTemp);
}
public static bool DeleteMember(Member objMember)
{
string SQLStatement = String.Empty;
string connectionString = "Server=mc-sluggo.stlcc.edu; Initial Catalog = IS253_Emery; Persist Security Info = True; User ID = emery; Password = emery";
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
// Open DB connection
objConn.Open();
sqlString = "delete Member where member_number = @member_number";
// Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
// Use the command parameters method to set hte parameter values of the SQL insert statement
objCommand.Parameters.AddWithValue("@member_number", objMember.Member_number);
// Execute the SQL and reutrn the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (objConn != null)
{
objConn.Close();
}
}
}
public static bool AddMember(Member objMember)
{
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
// Open DB connection
objConn.Open();
sqlString = "INSERT into Member values (@member_number, @lastname)";
// Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
// Use the command parameters method to set hte parameter values of hte SQL insert statement
objCommand.Parameters.AddWithValue("@member_number", objMember.Member_number);
objCommand.Parameters.AddWithValue("@firstname", objMember.Firstname);
// Excecute hte SQL command and return the number of rows affectedd
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (objConn != null)
{
objConn.Close();
}
}
}
public static Member GetMember(int memberNumber)
{
string SQLStatement = String.Empty;
string sqlString = "select member_number, lastName from genre where id = @member_number";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader custReader = null;
Member objMember = null;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
// Open DB connection
objConn.Open();
// Create command object with SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
// Set command parameter
objCommand.Parameters.AddWithValue("@member_number", memberNumber);
// Execute the SQL and return DataReader
using (custReader = objCommand.ExecuteReader())
{
while (custReader.Read())
{
objMember = new Member();
// Fill the object if it is found
// objMember.Member_number = custReader["member_number"].ToString();
objMember.Lastname = custReader["lastname"].ToString();
}
}
}
return(objMember);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objConn != null)
{
objConn.Close();
}
}
}
public static bool UpdateMember(Member objMember)
{
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
objConn.Open();
sqlString = "update Member " + Environment.NewLine + " set name = @lastname " + Environment.NewLine +
"where member_number = @member_number";
// Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
objCommand.Parameters.AddWithValue("@lastname", objMember.Lastname);
objCommand.Parameters.AddWithValue("member_number", objMember.Member_number);
// Execute the SQL query and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (objConn != null)
{
objConn.Close();
}
}
}
public static List <Member> GetMembers()
{
List <Member> objTemp = new List <Member>();
string SQLStatement = "select member_number, name from member order by member_number";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader objReader = null;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
// Open the connection to the database
objConn.Open();
// Create a command object iwth the SQL statement
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
// Execute the SQL and return a DataReader Object
using (objReader = objCommand.ExecuteReader())
{
while (objReader.Read())
{
Member objMember = new Member();
objMember.Lastname = objReader["lastname"].ToString();
objMember.Firstname = objReader["first_name"].ToString();
objMember.Address = objReader["address"].ToString();
// objMember.Member_number = objReader["member_number"].ToString();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objConn != null)
{
objConn.Close();
}
}
return(objTemp);
}
public static bool UpdateMember(Member objMember)
{
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "UPDATE Member " + Environment.NewLine +
"set joindate = @joindate, " + Environment.NewLine +
"firstname = @firstname, " + Environment.NewLine +
"lastname = @lastname, " + Environment.NewLine +
"address = @address, " + Environment.NewLine +
"city = @city, " + Environment.NewLine +
"state = @state, " + Environment.NewLine +
"zipcode = @zipcode, " + Environment.NewLine +
"phone = @phone, " + Environment.NewLine +
"member_status = @member_status, " + Environment.NewLine +
"login_name = @login_name, " + Environment.NewLine +
"password = @password, " + Environment.NewLine +
"email = @email, " + Environment.NewLine +
"contact_method = @contact_method, " + Environment.NewLine +
"subscription_id = @subscription_id, " + Environment.NewLine +
"photo = @photo " + Environment.NewLine +
"where number = @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("@number", objMember.number);
objCommand.Parameters.AddWithValue("@joindate", objMember.joindate);
objCommand.Parameters.AddWithValue("@firstname", objMember.firstname);
objCommand.Parameters.AddWithValue("@lastname", objMember.lastname);
objCommand.Parameters.AddWithValue("@address", objMember.address);
objCommand.Parameters.AddWithValue("@city", objMember.city);
objCommand.Parameters.AddWithValue("@state", objMember.state);
objCommand.Parameters.AddWithValue("@zipcode", objMember.zipcode);
objCommand.Parameters.AddWithValue("@phone", objMember.phone);
objCommand.Parameters.AddWithValue("@member_status", objMember.member_status);
objCommand.Parameters.AddWithValue("@login_name", objMember.login_name);
objCommand.Parameters.AddWithValue("@password", objMember.password);
objCommand.Parameters.AddWithValue("@email", objMember.email);
objCommand.Parameters.AddWithValue("@contact_method", objMember.contact_method);
objCommand.Parameters.AddWithValue("@subscription_id", objMember.subscription_id);
objCommand.Parameters.AddWithValue("@photo", objMember.photo);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "Error");
throw;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static List <Movie> GetMoviesByGenre(int genreID)
{
//Change the MyCustomObject name to your customer business object that is returning data from the specific table
List <Movie> objTemp = new List <Movie>();
string SQLStatement = "select movie_number, movie_title, description, movie_year_made, genre_id, movie_rating, " +
" media_type, movie_retail_cost, copies_on_hand, image, trailer " +
" from movie " +
" where genre_id = @genreid order by movie_number";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader objReader = null;
try
{
//using (objConn = AccessDataSQLServer.GetConnection())
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the database
objConn.Open();
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
objCommand.Parameters.AddWithValue("@genreid", genreID);
//Execute the SQL and return a DataReader Object
using (objReader = objCommand.ExecuteReader())
{
while (objReader.Read())
{
Movie objMovie = new Movie();
objMovie.movie_number = objReader["movie_number"].ToString();
objMovie.movie_title = objReader["movie_title"].ToString();
objMovie.description = objReader["description"].ToString();
objMovie.movie_year_made = objReader["movie_year_made"].ToString();
objMovie.genre_id = objReader["genre_id"].ToString();
objMovie.movie_rating = objReader["movie_rating"].ToString();
objMovie.media_type = objReader["media_type"].ToString();
objMovie.movie_retail_cost = Convert.ToDecimal(objReader["movie_retail_cost"].ToString());
objMovie.copies_on_hand = objReader["copies_on_hand"].ToString();
objMovie.image = objReader["image"].ToString();
objMovie.trailer = objReader["trailer"].ToString();
//Add the movie to the collection
objTemp.Add(objMovie);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objConn != null)
{
objConn.Close();
}
}
return(objTemp);
}
public static bool UpdateMovie(Movie objMovie)
{
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "UPDATE Movie " + Environment.NewLine +
"set movie_title = @movie_title, " + Environment.NewLine +
"description = @description, " + Environment.NewLine +
"movie_year_made = @movie_year_made, " + Environment.NewLine +
"genre_id = @genre_id, " + Environment.NewLine +
"movie_rating = @movie_rating, " + Environment.NewLine +
"media_type = @media_type, " + Environment.NewLine +
"movie_retail_cost = @movie_retail_cost, " + Environment.NewLine +
"copies_on_hand = @copies_on_hand, " + Environment.NewLine +
"image = @image, " + Environment.NewLine +
"trailer = @trailer " + Environment.NewLine +
"where 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("@movie_number", objMovie.movie_number);
objCommand.Parameters.AddWithValue("@movie_title", objMovie.movie_title);
objCommand.Parameters.AddWithValue("@description", objMovie.description);
objCommand.Parameters.AddWithValue("@movie_year_made", objMovie.movie_year_made);
objCommand.Parameters.AddWithValue("@genre_id", objMovie.genre_id);
objCommand.Parameters.AddWithValue("@movie_rating", objMovie.movie_rating);
objCommand.Parameters.AddWithValue("@media_type", objMovie.media_type);
objCommand.Parameters.AddWithValue("@movie_retail_cost", objMovie.movie_retail_cost);
objCommand.Parameters.AddWithValue("@copies_on_hand", objMovie.copies_on_hand);
objCommand.Parameters.AddWithValue("@image", objMovie.image);
objCommand.Parameters.AddWithValue("@trailer", objMovie.trailer);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static bool AddMovie(Movie objMovie)
{
//Pre-step: Replace the general object parameter with the appropriate business class object that you are using to insert data in the underline database table
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "INSERT into Movie (movie_number, movie_title, description, movie_year_made, genre_id, " +
"movie_rating, media_type, movie_retail_cost, copies_on_hand, image, trailer) values (@movie_number, @movie_title, @description, @movie_year_made," +
" @genre_id, @movie_rating, @media_type, @movie_retail_cost, @copies_on_hand," +
" @image, @trailer)";
//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("@movie_number", objMovie.movie_number);
objCommand.Parameters.AddWithValue("@movie_title", objMovie.movie_title);
objCommand.Parameters.AddWithValue("@description", objMovie.description);
objCommand.Parameters.AddWithValue("@movie_year_made", objMovie.movie_year_made);
objCommand.Parameters.AddWithValue("@genre_id", objMovie.genre_id);
objCommand.Parameters.AddWithValue("@movie_rating", objMovie.movie_rating);
objCommand.Parameters.AddWithValue("@media_type", objMovie.media_type);
objCommand.Parameters.AddWithValue("@movie_retail_cost", objMovie.movie_retail_cost);
objCommand.Parameters.AddWithValue("@copies_on_hand", objMovie.copies_on_hand);
objCommand.Parameters.AddWithValue("@image", objMovie.image);
objCommand.Parameters.AddWithValue("@trailer", objMovie.trailer);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static Movie GetMovie(int movieID)
{
//Pre-step: Replace the general object parameter with the appropriate data type parameter for retrieving a specific item from the specific database table.
string SQLStatement = String.Empty;
//Change the MyCustomObject references to your customer business object
//Movie objTemp = new Movie();
string sqlString = "Select movie_number, movie_title, description, movie_year_made, genre_id, movie_rating, " +
" media_type, movie_retail_cost, copies_on_hand, image, trailer " +
"from movie where movie_number = @movie_id ";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader movieReader = null;
Movie objMovie = null;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
//Set command parameter
objCommand.Parameters.AddWithValue("@movie_id", movieID);
//Execute the SQL and return a DataReader
using (movieReader = objCommand.ExecuteReader())
{
while (movieReader.Read())
{
objMovie = new Movie();
//Fill the customer object if found
objMovie.movie_number = movieReader["movie_number"].ToString();
objMovie.movie_title = movieReader["movie_title"].ToString();
objMovie.description = movieReader["description"].ToString();
objMovie.movie_year_made = movieReader["movie_year_made"].ToString();
objMovie.genre_id = movieReader["genre_id"].ToString();
objMovie.movie_rating = movieReader["movie_rating"].ToString();
objMovie.media_type = movieReader["media_type"].ToString();
objMovie.movie_retail_cost = Convert.ToDecimal(movieReader["movie_retail_cost"].ToString());
objMovie.copies_on_hand = movieReader["copies_on_hand"].ToString();
objMovie.image = movieReader["image"].ToString();
objMovie.trailer = movieReader["trailer"].ToString();
}
}
}
return(objMovie);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static Member GetMember(int id)
{
//Pre-step: Replace the general object parameter with the appropriate data type parameter for retrieving a specific item from the specific database table.
string SQLStatement = String.Empty;
//Change the MyCustomObject references to your customer business object
//Member objTemp = new Member();
string sqlString = "Select number, joindate, firstname, lastname, address, city, state, zipcode, phone, " +
"member_status, login_name, password, email, contact_method, subscription_id, photo " +
"from member where number = @id;";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader memberReader = null;
Member objMember = null;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
//Set command parameter
objCommand.Parameters.AddWithValue("@id", id);
//Execute the SQL and return a DataReader
using (memberReader = objCommand.ExecuteReader())
{
while (memberReader.Read())
{
objMember = new Member();
//Fill the customer object if found
objMember.number = memberReader["number"].ToString();
objMember.joindate = memberReader["joindate"].ToString();
objMember.firstname = memberReader["firstname"].ToString();
objMember.lastname = memberReader["lastname"].ToString();
objMember.address = memberReader["address"].ToString();
objMember.city = memberReader["city"].ToString();
objMember.state = memberReader["state"].ToString();
objMember.zipcode = memberReader["zipcode"].ToString();
objMember.phone = memberReader["phone"].ToString();
objMember.member_status = memberReader["member_status"].ToString();
objMember.login_name = memberReader["login_name"].ToString();
objMember.password = memberReader["password"].ToString();
objMember.email = memberReader["email"].ToString();
objMember.contact_method = memberReader["contact_method"].ToString();
objMember.subscription_id = memberReader["subscription_id"].ToString();
objMember.photo = memberReader["photo"].ToString();
}
}
}
return(objMember);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
//Instructions:
//Replace all ???TableNameHere phrases with the name of your specific SQL Server Database Table Name
//Replace yourCustomeObject phrase with the name of the business object (represents database table name) you are referencing or returning
//Replace datatype phrase with the appropriate C# data type or custom data type based on Project #2 CRUD specs
//Replace parameter phrase with the appropriate input parameter based on Project #2 CRUD specs
//Refer to the ADO.Net Demo for method examples below
public static List <Member> GetMembers()
{
//Change the MyCustomObject name to your customer business object that is returning data from the specific table
List <Member> objTemp = new List <Member>();
string SQLStatement = "select number, joindate, firstname, lastname, address, city, state, zipcode, phone, " +
"member_status, login_name, password, email, contact_method, subscription_id, photo " +
"from member order by number";
SqlCommand objCommand = null;
SqlConnection objConn = null;
SqlDataReader objReader = null;
try
{
//using (objConn = AccessDataSQLServer.GetConnection())
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the database
objConn.Open();
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(SQLStatement, objConn))
{
//Execute the SQL and return a DataReader Object
using (objReader = objCommand.ExecuteReader())
{
while (objReader.Read())
{
Member objMember = new Member();
objMember.number = objReader["number"].ToString();
objMember.joindate = objReader["joindate"].ToString();
objMember.firstname = objReader["firstname"].ToString();
objMember.lastname = objReader["lastname"].ToString();
objMember.address = objReader["address"].ToString();
objMember.city = objReader["city"].ToString();
objMember.state = objReader["state"].ToString();
objMember.zipcode = objReader["zipcode"].ToString();
objMember.phone = objReader["phone"].ToString();
objMember.member_status = objReader["member_status"].ToString();
objMember.login_name = objReader["login_name"].ToString();
objMember.password = objReader["password"].ToString();
objMember.email = objReader["email"].ToString();
objMember.contact_method = objReader["contact_method"].ToString();
objMember.subscription_id = objReader["subscription_id"].ToString();
objMember.photo = objReader["photo"].ToString();
//Add the member to the collection
objTemp.Add(objMember);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objConn != null)
{
objConn.Close();
}
}
return(objTemp);
}
public static bool AddMember(Member objMember)
{
//Pre-step: Replace the general object parameter with the appropriate business class object that you are using to insert data in the underline database table
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDataSQLServer.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "INSERT into Member (joindate, firstname, lastname, address, " +
"city, state, zipcode, phone, member_status, login_name, password, email, contact_method, subscription_id, photo) " +
"values (@joindate, @firstname, @lastname," +
" @address, @city, @state, @zipcode, @phone," +
" @member_status, @login_name, @password, @email, @contact_method, @subscription_id, @photo)";
//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("@number", objMember.number);
objCommand.Parameters.AddWithValue("@joindate", objMember.joindate);
objCommand.Parameters.AddWithValue("@firstname", objMember.firstname);
objCommand.Parameters.AddWithValue("@lastname", objMember.lastname);
objCommand.Parameters.AddWithValue("@address", objMember.address);
objCommand.Parameters.AddWithValue("@city", objMember.city);
objCommand.Parameters.AddWithValue("@state", objMember.state);
objCommand.Parameters.AddWithValue("@zipcode", objMember.zipcode);
objCommand.Parameters.AddWithValue("@phone", objMember.phone);
objCommand.Parameters.AddWithValue("@member_status", objMember.member_status);
objCommand.Parameters.AddWithValue("@login_name", objMember.login_name);
objCommand.Parameters.AddWithValue("@password", objMember.password);
objCommand.Parameters.AddWithValue("@email", objMember.email);
objCommand.Parameters.AddWithValue("@contact_method", objMember.contact_method);
objCommand.Parameters.AddWithValue("@subscription_id", objMember.subscription_id);
objCommand.Parameters.AddWithValue("@photo", objMember.photo);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}