public List <Show> FindAll()
{
log.Info("Entering findAll");
String sql = "SELECT * FROM Shows";
var conn = DBUtils.getConnection();
using (var cmd = new SQLiteCommand(sql, conn))
{
using (var reader = cmd.ExecuteReader())
{
List <Show> shows = new List <Show>();
while (reader.Read())
{
shows.Add(new Show(reader.GetString(0),
reader.GetString(1),
reader.GetDateTime(5), reader["venue"].ToString(),
Int32.Parse(reader["remainingTickets"].ToString()),
Int32.Parse(reader["totalTickets"].ToString())));
}
log.Info("Exiting findAll");
DBUtils.closeConnection();
return(shows);
}
}
}
public Show FindOne(string id)
{
log.InfoFormat("Entering findOne for show {0}", id);
String sql = "SELECT * FROM Shows where id = @id";
var conn = DBUtils.getConnection();
using (var cmd = new SQLiteCommand(sql, conn))
{
var param1 = cmd.CreateParameter();
param1.ParameterName = "@id";
param1.Value = id;
cmd.Parameters.Add(param1);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Show s = new Show(reader["id"].ToString(), reader["artistName"].ToString(),
reader.GetDateTime(5), reader["venue"].ToString(),
Int32.Parse(reader["remainingTickets"].ToString()),
Int32.Parse(reader["totalTickets"].ToString()));
log.InfoFormat("Exiting findOne with value {0}", s);
DBUtils.closeConnection();
return(s);
}
else
{
log.Info("Exiting findAll with value null");
DBUtils.closeConnection();
return(null);
}
}
}
}
public List <Show> findByDate(DateTime date)
{
log.InfoFormat("Entering findByDate, date: {0}", date);
String sql = "SELECT * FROM Shows where showDate = @date;";
var conn = DBUtils.getConnection();
using (var cmd = new SQLiteCommand(sql, conn))
{
var paramDate = cmd.CreateParameter();
paramDate.ParameterName = "@date";
paramDate.Value = date;
cmd.Parameters.Add(paramDate);
using (var reader = cmd.ExecuteReader())
{
List <Show> shows = new List <Show>();
while (reader.Read())
{
shows.Add(new Show(reader.GetString(0),
reader.GetString(1),
reader.GetDateTime(5), reader["venue"].ToString(),
Int32.Parse(reader["remainingTickets"].ToString()),
Int32.Parse(reader["totalTickets"].ToString())));
}
log.Info("Exiting findAll");
DBUtils.closeConnection();
return(shows);
}
}
}
public void Delete(string id, int number)
{
log.InfoFormat("Entering delete for ticket nr {0}, show {1}", number, id);
String sql = "DELETE FROM Tickets where idShow = @id and numberTicket = @number";
var conn = DBUtils.getConnection();
using (var cmd = new SQLiteCommand(sql, conn))
{
var param1 = cmd.CreateParameter();
param1.ParameterName = "@id";
param1.Value = id;
cmd.Parameters.Add(param1);
var param2 = cmd.CreateParameter();
param2.ParameterName = "@number";
param2.Value = number;
cmd.Parameters.Add(param2);
var dataR = cmd.ExecuteNonQuery();
DBUtils.closeConnection();
log.Info("Delete was successful");
if (dataR == 0)
{
log.Info("Delete was unsuccessful");
throw new Exception("No ticket was deleted!");
}
}
}
public Ticket FindOne(string id, int number)
{
log.InfoFormat("Entering findOne for ticket nr {0}, show {1}", number, id);
String sql = "SELECT * FROM Tickets where idShow = @id and numberTicket = @number";
var conn = DBUtils.getConnection();
using (var cmd = new SQLiteCommand(sql, conn))
{
var param1 = cmd.CreateParameter();
param1.ParameterName = "@id";
param1.Value = id;
cmd.Parameters.Add(param1);
var param2 = cmd.CreateParameter();
param2.ParameterName = "@number";
param2.Value = number;
cmd.Parameters.Add(param2);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Ticket t = new Ticket(reader.GetString(0), reader.GetInt32(1), reader.GetString(2));
log.InfoFormat("Exiting findOne with value {0}", t);
DBUtils.closeConnection();
return(t);
}
log.Info("Exiting findOne with value null");
DBUtils.closeConnection();
return(null);
}
}
}
public void Save(Ticket ticket)
{
log.InfoFormat("Entering save for ticket {0}", ticket.ToString());
String sql = "INSERT INTO Tickets values (@id, @number, @buyerName)";
var conn = DBUtils.getConnection();
using (var cmd = new SQLiteCommand(sql, conn))
{
var paramId = cmd.CreateParameter();
paramId.ParameterName = "@id";
paramId.Value = ticket.IdShow;
cmd.Parameters.Add(paramId);
var paramNumber = cmd.CreateParameter();
paramNumber.ParameterName = "@number";
paramNumber.Value = ticket.NrTicket;
cmd.Parameters.Add(paramNumber);
var paramName = cmd.CreateParameter();
paramName.ParameterName = "@buyerName";
paramName.Value = ticket.BuyerName;
cmd.Parameters.Add(paramName);
var result = cmd.ExecuteNonQuery();
DBUtils.closeConnection();
log.Info("Save was successful");
if (result == 0)
{
log.Info("Save was unsucccessful");
throw new Exception("No ticket was saved!");
}
}
}
public void Update(Show s)
{
log.InfoFormat("Entering update for show {0}", s.Id);
String sql =
"UPDATE Shows SET artistName = @artist, showDate = @date ,venue = @venue, remainingTickets = @remaining, totalTickets = @total where id = @id";
var conn = DBUtils.getConnection();
using (var cmd = new SQLiteCommand(sql, conn))
{
var paramId = cmd.CreateParameter();
paramId.ParameterName = "@id";
paramId.Value = s.Id;
cmd.Parameters.Add(paramId);
var paramArtist = cmd.CreateParameter();
paramArtist.ParameterName = "@artist";
paramArtist.Value = s.ArtistName;
cmd.Parameters.Add(paramArtist);
var paramVenue = cmd.CreateParameter();
paramVenue.ParameterName = "@venue";
paramVenue.Value = s.Venue;
cmd.Parameters.Add(paramVenue);
var paramTotal = cmd.CreateParameter();
paramTotal.ParameterName = "@total";
paramTotal.Value = s.TotalTickets;
cmd.Parameters.Add(paramTotal);
var paramRemaining = cmd.CreateParameter();
paramRemaining.ParameterName = "@remaining";
paramRemaining.Value = s.RemainingTickets;
cmd.Parameters.Add(paramRemaining);
var paramDate = cmd.CreateParameter();
paramDate.ParameterName = "@date";
paramDate.Value = s.DateOfShow;
cmd.Parameters.Add(paramDate);
var result = cmd.ExecuteNonQuery();
DBUtils.closeConnection();
log.Info("Update was successful");
if (result == 0)
{
log.Info("Update was unsuccessful");
throw new Exception("No show was updated!");
}
}
}