/*
* Sign in an Admin or Free User in the database.
*
* @return true if the operation success, false if not
*/
public bool Login(bool isAdmin, string username, string password)
{
// Database connection
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
switch (isAdmin)
{
case true:
command.CommandText = "SELECT * FROM Cinema.Admin WHERE UsernameAdmin = @username AND Password = @password";
break;
case false:
command.CommandText = "SELECT * FROM Cinema.UtenteFree WHERE UsernameUtenteFree = @username AND Password = @password";
break;
}
command.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
command.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
// Attempt to commit the transaction.
transaction.Commit();
using (SqlDataReader reader = command.ExecuteReader()) {
if (reader.Read())
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex) {
Console.WriteLine("\nCommit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Get the list containing the Film of the database
*/
public List <Film> GetFilmList()
{
// Database connection
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
// Define a new list of Users
List <Film> filmList = new List <Film>();
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandText = "SELECT * FROM Cinema.Film;";
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
var filmCode = reader.GetInt32(0);
var title = reader.GetString(1);
var year = reader.GetInt32(2);
var direction = reader.GetString(3);
var duration = reader.GetInt32(4);
var releaseDate = reader.GetDateTime(5);
var genre = reader.GetString(6);
filmList.Add(new Film(filmCode, title, year, direction, duration, releaseDate, genre));
}
}
// Attempt to commit the transaction.
transaction.Commit();
return(filmList);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new List <Film>()
{
});
}
}
}
/*
* Get the list containing the Events of the database
*/
public List <Event> GetEventsList()
{
// Database connection
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
// Define a new list of Users
List <Event> eventsList = new List <Event>();
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandText = "SELECT * FROM Cinema.Evento;";
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
var eventCode = reader.GetInt32(0);
var dateTime = reader.GetDateTime(1);
var filmCode = reader.GetInt32(2);
var hallCode = reader.GetInt32(3);
var usernameAdmin = reader.GetString(4);
var price = reader.GetDecimal(5);
eventsList.Add(new Event(eventCode, dateTime, filmCode, hallCode, usernameAdmin, price));
}
}
// Attempt to commit the transaction.
transaction.Commit();
return(eventsList);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new List <Event>()
{
});
}
}
}
/*
* Delete a film from the database.
*
* @return true if the operation success, false if not
*/
public bool DeleteFilm(int filmCode)
{
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
// Call the stored procedure to delete the film
// given the filmCode (@primaryKey)
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.DeleteFilm";
command.Parameters.Add("@CodiceFilm", SqlDbType.Int).Value = filmCode;
command.ExecuteNonQuery();
// Initialize a int value to check if the Stored Procedure success
var returnParameter = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
// Commit the transaction.
transaction.Commit();
// If the int value > 0 the Stored Procedure success
if (returnParameter.Direction > 0)
{
return(true);
}
else
{
command.Parameters.Clear();
return(false);
}
}
catch (SqlException ex) {
Console.WriteLine("\nCommit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Get the User of the database given his username
*/
public User GetUser(bool isAdmin, string username)
{
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
switch (isAdmin)
{
case true:
command.CommandText = "SELECT * FROM Cinema.Admin WHERE Cinema.Admin.UsernameAdmin = @username";
break;
case false:
command.CommandText = "SELECT * FROM Cinema.UtenteFree WHERE Cinema.UtenteFree.UsernameUtenteFree = @username";
break;
}
command.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
User user = new User();
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
user.Username = reader.GetString(0);
user.Name = reader.GetString(2);
user.Surname = reader.GetString(3);
}
}
// Attempt to commit the transaction.
transaction.Commit();
return(user);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new User());
}
}
}
/*
* Get the Subscription of a User.
*
* @return true if the user has a subscription, false if not
*/
public bool GetSubscription(string username)
{
// Database connection
using (SqlConnection connection = DatabaseHandler.GetConnection())
{
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try
{
// Check if the User has taken out a Subscription to the Cinema
command.CommandText = "SELECT * FROM Cinema.Abbonamento WHERE Username = @Username";
command.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
// Attempt to commit the transaction.
transaction.Commit();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
Console.WriteLine("\nCommit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Get the list containing the Users of the database
*/
public List <User> GetUsersList()
{
// Database connection
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
// Define a new list of Users
List <User> usersList = new List <User>();
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandText = "SELECT * FROM Cinema.UtenteFree;";
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
var username = reader.GetString(0);
var hashedPassword = reader.GetString(1);
var name = reader.GetString(2);
var surname = reader.GetString(3);
usersList.Add(new User(username, hashedPassword, name, surname));
}
}
// Attempt to commit the transaction.
transaction.Commit();
return(usersList);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new List <User>()
{
});
}
}
}
/*
* Get the list containing the Halls of the database
*/
public List <Hall> GetHallsList()
{
// Database connection
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
// Define a new list of Users
List <Hall> hallsList = new List <Hall>();
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandText = "SELECT * FROM Cinema.Sala;";
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
var hallCode = reader.GetInt32(0);
var capacity = reader.GetInt32(1);
hallsList.Add(new Hall(hallCode, capacity));
}
}
// Attempt to commit the transaction.
transaction.Commit();
return(hallsList);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new List <Hall>()
{
});
}
}
}
/*
* Get the Film of the database given its code
*/
public Film GetFilm(int filmCode)
{
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandText = "SELECT * FROM Cinema.Film WHERE CodiceFilm = @filmCode ";
command.Parameters.Add("@filmCode", SqlDbType.Int).Value = filmCode;
Film film = new Film();
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
film.FilmCode = reader.GetInt32(0);
film.Title = reader.GetString(1);
film.Year = reader.GetInt32(2);
film.Direction = reader.GetString(3);
film.Duration = reader.GetInt32(4);
film.ReleaseDate = reader.GetDateTime(5);
film.Genre = reader.GetString(6);
}
}
// Attempt to commit the transaction.
transaction.Commit();
return(film);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new Film());
}
}
}
/*
* Get the Event of the database given its code
*/
public Event GetEvent(int eventCode)
{
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandText = "SELECT * FROM Cinema.Evento WHERE CodiceEvento = @eventCode ";
command.Parameters.Add("@eventCode", SqlDbType.Int).Value = eventCode;
Event _event = new Event();
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
_event.EventCode = reader.GetInt32(0);
_event.DateTime = reader.GetDateTime(1);
_event.FilmCode = reader.GetInt32(2);
_event.HallCode = reader.GetInt32(3);
_event.UsernameAdmin = reader.GetString(4);
_event.Price = reader.GetDecimal(5);
}
}
// Attempt to commit the transaction.
transaction.Commit();
return(_event);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new Event());
}
}
}
/*
* Check Foreign Key bond
*
* @return true if the foreign key exists, false if not
*/
public bool CheckStringFK(string value, string valueType)
{
// Datbase Connection
using (SqlConnection connection = DatabaseHandler.GetConnection())
{
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try
{
command.CommandText = "SELECT * FROM Cinema." + valueType + ";";
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (value == reader.GetString(0))
{
return(true);
}
}
return(false);
}
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Check if the place the User wants to buy is a valid one.
* The place must exist in that hall and must not already be reserved
*
* @return true if the place is a valid one, false otherwise
*/
public bool CheckPlace(int eventCode, int placeNumber)
{
// Define two list, the first containing the existing place
// the second containing the reserved ones
List <Reservation> reservationsList = new List <Reservation>();
List <Place> availablePlacesList = new List <Place>();
// Define two bool value, he first check if the place exists,
// the second whether it is already reserved
bool placeExists = false;
bool placeReserved = false;
// Connessione al DB Cinema
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.VisualizzaPostiSala";
command.Parameters.Add("@CodiceEvento", SqlDbType.Int).Value = eventCode;
// Fill the availablePlaceList with the places of the hall of the event
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Place p = new Place();
p.PlaceNumber = reader.GetInt32(0);
p.HallCode = reader.GetInt32(1);
availablePlacesList.Add(p);
}
}
// Check if the place exists
foreach (Place p in availablePlacesList)
{
if (p.PlaceNumber == placeNumber)
{
placeExists = true;
}
}
// Reset the parameters
command.Parameters.Clear();
// Takes the Reservations and fill the list reservationsList with bought places
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.VisualizzaPostiRiservati";
command.Parameters.Add("@CodiceEvento", SqlDbType.Int).Value = eventCode;
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Reservation r = new Reservation();
r.PlaceNumber = reader.GetInt32(0);
r.PrenotationCode = reader.GetInt32(1);
reservationsList.Add(r);
}
}
// Attempt to commit the transaction.
transaction.Commit();
// Check if the place is already in Reservation table
foreach (Reservation r in reservationsList)
{
if (r.PlaceNumber == placeNumber)
{
placeReserved = true;
}
}
// Return false if the place doesn't exist or it's been bought
if (placeExists == false || placeReserved == true)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Add a film into the database.
*
* @return true if the operation success, false if not
*/
public bool AddFilm(string title, int year, string direction, int duration, DateTime releaseDate, string genre)
{
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.AddNewFilm";
command.Parameters.Add("@Titolo", SqlDbType.VarChar).Value = title;
command.Parameters.Add("@Anno", SqlDbType.Int).Value = year;
command.Parameters.Add("@Regia", SqlDbType.VarChar).Value = direction;
command.Parameters.Add("@Durata", SqlDbType.Int).Value = duration;
command.Parameters.Add("@Data_Uscita", SqlDbType.DateTime).Value = releaseDate;
command.Parameters.Add("@Genere", SqlDbType.VarChar).Value = genre;
command.Parameters.Add("@CodiceFilm", SqlDbType.Int).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
// Initialize a int value to check if the Stored Procedure success
var returnParameter = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
// Commit the transaction.
transaction.Commit();
// If the int value > 0 the Stored Procedure success
if (returnParameter.Direction > 0)
{
return(true);
}
else
{
command.Parameters.Clear();
return(false);
}
}
catch (SqlException ex) {
Console.WriteLine("\nCommit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Get the list containing the tickets of a user
*/
public List <Ticket> GetTicketsList(string username)
{
// Database connection
using (SqlConnection connection = DatabaseHandler.GetConnection())
{
// Define a new list of Users
List <Ticket> ticketsList = new List <Ticket>();
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try{
// Insert the Ticket in the list
command.Parameters.Clear();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.VisualizzaPrenotazione";
command.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Prenotation p = new Prenotation();
p.PrenotationCode = reader.GetInt32(0);
p.DateTime = reader.GetDateTime(1);
p.UsernameUser = reader.GetString(2);
p.EventCode = reader.GetInt32(3);
Event e = new Event();
e.EventCode = reader.GetInt32(3);
e.DateTime = reader.GetDateTime(5);
e.HallCode = reader.GetInt32(6);
e.Price = reader.GetDecimal(7);
Film f = new Film();
f.Title = reader.GetString(4);
Reservation r = new Reservation();
r.PlaceNumber = reader.GetInt32(8);
ticketsList.Add(new Ticket(p, e, f, r));
}
}
// Attempt to commit the transaction.
transaction.Commit();
return(ticketsList);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new List <Ticket>()
{
});
}
}
}
/*
* Get the list containing the avilable Places for an Event of the database
*/
public List <Place> GetAvailablePlacesList(int eventCode)
{
// Define two list: one for available places,
// the second for the booked ones
List <Place> availablePlacesList = new List <Place>();
List <Reservation> reservationList = new List <Reservation>();
// Connessione al DB Cinema
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.VisualizzaPostiSala";
command.Parameters.Add("@CodiceEvento", SqlDbType.Int).Value = eventCode;
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Place place = new Place();
place.PlaceNumber = reader.GetInt32(0);
place.HallCode = reader.GetInt32(1);
availablePlacesList.Add(place);
}
}
// Reset the parameters
command.Parameters.Clear();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.VisualizzaPostiRiservati";
command.Parameters.Add("@CodiceEvento", SqlDbType.Int).Value = eventCode;
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Reservation reserve = new Reservation();
reserve.PlaceNumber = reader.GetInt32(0);
reserve.PrenotationCode = reader.GetInt32(1);
reservationList.Add(reserve);
}
}
// Attempt to commit the transaction.
transaction.Commit();
for (int x = availablePlacesList.Count - 1; x >= 0; x--)
{
foreach (Reservation r in reservationList)
{
if (availablePlacesList[x].PlaceNumber.Equals(r.PlaceNumber))
{
// If an element is in both the lists remove it from the availablePlaceList
availablePlacesList.Remove(availablePlacesList[x]);
break; // Stop the cycle
}
}
}
return(availablePlacesList);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new List <Place>()
{
});
}
}
}
/*
* Add a Subscription into the database.
*
* @return true if the operation success, false if not
*/
public bool AddSubscription(string username)
{
using (SqlConnection connection = DatabaseHandler.GetConnection())
{
if (GetSubscription(username))
{
return(false);
}
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
if (GetSubscription(username))
{
return(false);
}
// Insert the Prenotation into the database
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.AddAbbonamento";
command.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
command.ExecuteNonQuery();
// Initialize a int value to check if the Stored Procedure success
var returnParameter1 = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter1.Direction = ParameterDirection.ReturnValue;
// Commit the transaction
transaction.Commit();
// If both the int value > 0 the Stored Procedure both success
if (returnParameter1.Direction > 0)
{
return(true);
}
else
{
command.Parameters.Clear();
return(false);
}
}
catch (SqlException ex)
{
Console.WriteLine("\nCommit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Add a Prenotation into the database.
*
* @return true if the operation success, false if not
*/
public bool AddPrenotation(DateTime dateTime, string usernameUser, int eventCode, int placeNumber)
{
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
// Bool value for user subscription
// true if the user is subscribed, false otherwise
bool IsSubscribed = GetSubscription(usernameUser);
decimal price = 6.40M;
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
// If the User is not subscribed take the price from the event, if he is
// insert the price of the ticket at 6,40€
if (!IsSubscribed)
{
command.CommandText = "SELECT Cinema.Evento.Prezzo FROM Cinema.Evento WHERE Cinema.Evento.CodiceEvento = @CodiceEvento";
command.Parameters.Add("@CodiceEvento", SqlDbType.Int).Value = eventCode;
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
price = reader.GetDecimal(0);
}
}
}
// Reset the Parameters
command.Parameters.Clear();
// Insert the Prenotation into the database
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.AddNewPrenotazione";
command.Parameters.Add("@DataOra", SqlDbType.DateTime).Value = dateTime;
command.Parameters.Add("@Username_UtenteFree", SqlDbType.VarChar).Value = usernameUser;
command.Parameters.Add("@Codice_Evento", SqlDbType.Int).Value = eventCode;
command.Parameters.Add("@Prezzo", SqlDbType.Decimal).Value = price;
command.Parameters.Add("@CodicePrenotazione", SqlDbType.Int).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
// Create a Prenotation Object
Prenotation prenotation = new Prenotation(Convert.ToInt32(command.Parameters["@CodicePrenotazione"].Value),
dateTime, usernameUser, eventCode);
// Initialize a int value to check if the Stored Procedure success
var returnParameter1 = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter1.Direction = ParameterDirection.ReturnValue;
//Reset the parameters
command.Parameters.Clear();
// Insert data in Reserve table in the database
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.AddNewRiserva";
command.Parameters.Add("@Numero_Posto", SqlDbType.Int).Value = placeNumber;
command.Parameters.Add("@Codice_Prenotazione", SqlDbType.Int).Value = prenotation.PrenotationCode;
command.ExecuteNonQuery();
// Initialize a int value to check if the Stored Procedure success
var returnParameter2 = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter2.Direction = ParameterDirection.ReturnValue;
// Commit the transaction
transaction.Commit();
// If both the int value > 0 the Stored Procedure both success
if (returnParameter1.Direction > 0 && returnParameter2.Direction > 0)
{
return(true);
}
else
{
command.Parameters.Clear();
return(false);
}
}
catch (SqlException ex) {
Console.WriteLine("\nCommit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Get the list containing the Events of the database
*/
public List <Show> GetShowsList()
{
// Database connection
using (SqlConnection connection = DatabaseHandler.GetConnection())
{
// Define a new list of Users
List <Show> showsList = new List <Show>();
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try
{
command.CommandText = "SELECT * FROM Show;";
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Event e = new Event();
e.EventCode = reader.GetInt32(0);
e.DateTime = reader.GetDateTime(1);
e.HallCode = reader.GetInt32(3);
e.Price = reader.GetDecimal(4);
Film f = new Film();
f.Title = reader.GetString(2);
showsList.Add(new Show(e, f));
}
}
// Attempt to commit the transaction.
transaction.Commit();
return(showsList);
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(new List <Show>()
{
});
}
}
}
/*
* Register an Admin or Free User in the database.
*
* @return true if the operation success, false if not
*/
public bool Registration(bool isAdmin, string username, string password, string name, string surname)
{
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
switch (isAdmin)
{
case true:
command.CommandText = "INSERT Cinema.Admin VALUES ( @username, @password, @name, @surname)";
break;
case false:
command.CommandText = "INSERT Cinema.UtenteFree VALUES ( @username, @password, @name, @surname)";
break;
}
command.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
command.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
command.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
command.Parameters.Add("@surname", SqlDbType.VarChar).Value = surname;
// Commit the transaction.
transaction.Commit();
int result = command.ExecuteNonQuery();
if (result > 0)
{
return(true);
}
else
{
command.Parameters.Clear();
return(false);
}
}
catch (SqlException ex) {
Console.WriteLine("\nCommit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Show the places in a hall.
* Simple console rappresentation of the places disposition in a hall
*/
public string DrawHall(int eventCode)
{
string drawHall = string.Empty;
Hall h = new Hall();
// Database connection
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.RichiestaCodiceSala";
command.Parameters.Add("@CodiceEvento", SqlDbType.Int).Value = eventCode;
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
h.HallCode = reader.GetInt32(0);
}
}
// Attempt to commit the transaction.
transaction.Commit();
switch (h.HallCode)
{
case 1:
drawHall = "\n ________________________________\n \\______________________________/\n\n | 1 | 2 | 3 | 4 | 5 | 6 |\n" +
" | 7 | 8 | 9 | 10| 11| 12|\n | 13| 14| 15| 16| 17| 18|\n | 19| 20| 21| 22| 23| 24|\n";
break;
case 2:
drawHall = " \n________________________\n\\______________________/\n\n | 1 | 2 | 3 | 4 |\n" +
" | 5 | 6 | 7 | 8 |\n | 9 | 10| 11| 12|\n | 13| 14| 15| 16|\n";
break;
case 3:
drawHall = " \n________________________________\n\\______________________________/\n\n | 1 | 2 | 3 | 4 | 5 | 6 |\n" +
" | 7 | 8 | 9 | 10| 11| 12|\n | 13| 14| 15| 16| 17| 18|\n | 19| 20| 21| 22| 23| 24|\n | 25| 26| 27| 28| 29| 30|\n";
break;
default:
drawHall = "Hall not found.";
break;
}
return(drawHall);
}
catch (Exception ex) {
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(drawHall);
}
}
}
/*
* Add an event into the database
*
* @return true if the operation success, false if not
*/
public bool AddEvent(string usernameAdmin, DateTime dateTime, int filmCode, int hallCode, decimal price)
{
using (SqlConnection connection = DatabaseHandler.GetConnection()) {
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try {
// Check if in the hall is already present a film proiection in the same dateTime.
// If so, return false, add the event otherwise.
command.CommandText = "SELECT * FROM Cinema.Evento;";
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
if (dateTime == reader.GetDateTime(1) && hallCode == reader.GetInt32(3))
{
return(false);
}
}
}
// Reset the parameters
command.Parameters.Clear();
// Insert the event in the database
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.AddNewEvento";
command.Parameters.Add("@DataOra", SqlDbType.DateTime).Value = dateTime;
command.Parameters.Add("@Codice_Film", SqlDbType.Int).Value = filmCode;
command.Parameters.Add("@Codice_Sala", SqlDbType.Int).Value = hallCode;
command.Parameters.Add("@Username_Admin", SqlDbType.VarChar).Value = usernameAdmin;
command.Parameters.Add("@Prezzo", SqlDbType.Decimal).Value = price;
command.Parameters.Add("@CodiceEvento", SqlDbType.Int).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
// Initialize a int value to check if the Stored Procedure success
var returnParameter = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
// Commit the transaction
transaction.Commit();
// If the int value > 0 the Stored Procedure success
if (returnParameter.Direction > 0)
{
return(true);
}
else
{
command.Parameters.Clear();
return(false);
}
}
catch (SqlException ex) {
Console.WriteLine("\nCommit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try {
transaction.Rollback();
}
catch (Exception ex2) {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
/*
* Edit a User in the database
*
* @return true if the operation success, false if not
*/
public bool EditUser(string oldUsername, string newUsername, string newPassword, string newName, string newSurname)
{
using (SqlConnection connection = DatabaseHandler.GetConnection())
{
connection.Open();
// Start a local transaction.
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
// Assign both transaction object and connection to Command object
command.Connection = connection;
command.Transaction = transaction;
try
{
// Update an User
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Cinema.EditUser";
command.Parameters.Add("@OldUsername", SqlDbType.VarChar).Value = oldUsername;
command.Parameters.Add("@NewUsername", SqlDbType.VarChar).Value = newUsername;
command.Parameters.Add("@NewPassword", SqlDbType.VarChar).Value = newPassword;
command.Parameters.Add("@NewNome", SqlDbType.VarChar).Value = newName;
command.Parameters.Add("@NewCognome", SqlDbType.VarChar).Value = newSurname;
command.ExecuteNonQuery();
// Initialize a int value to check if the Stored Procedure success
var returnParameter = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
// Commit the transaction.
transaction.Commit();
// If the int value > 0 the Stored Procedure success
if (returnParameter.Direction > 0)
{
return(true);
}
else
{
command.Parameters.Clear();
return(false);
}
}
catch (SqlException ex)
{
Console.WriteLine("\nCommit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
return(false);
}
}
}
