/// <summary>
/// Selects a single ticket from the database.
/// </summary>
/// <param name="TagNumber">
/// TicketNumber: the related ticket number.
/// </param>
/// <param name="selectCommands">
/// selectCommands: the SQL query to insert into the select parameters of the SQL query. default: *
/// </param>
/// <returns>
/// Ticket.
/// </returns>
public Ticket SelectTicket(int TicketNumber, string selectCommands = "*")
{
string syntax = "SELECT * FROM tblTickets WHERE [TicketNumber] = " + TicketNumber + "";
OleDbConnection dbConnection = null;
OleDbCommand dbCommand = null;
OleDbDataReader dbReader = null;
//Create the Connection.
try
{
dbConnection = new OleDbConnection(connectionString);
}
catch (Exception)
{ throw; }
//Create the Command.
try
{
dbCommand = new OleDbCommand(syntax, dbConnection);
}
catch (Exception)
{ throw; }
//Open the connection.
try
{
dbConnection.Open();
}
catch (Exception)
{ throw; }
try { dbReader = dbCommand.ExecuteReader(); }
catch { throw; }
Ticket Ticket = new Ticket();
//Retrieve Data.
while (dbReader.Read())
{
int ticketNumber = -1;
int.TryParse(dbReader[0].ToString(), out ticketNumber);
Ticket.TicketNumber = ticketNumber;
Ticket.ServiceCode = dbReader[1].ToString();
Ticket.TagNumber = dbReader[2].ToString();
Ticket.Date = dbReader[3].ToString();
Ticket.Time = dbReader[4].ToString();
Ticket.TenderCode = dbReader[5].ToString();
Ticket.Status = dbReader[6].ToString();
}
dbReader.Close();
dbReader.Dispose();
dbReader = null;
dbCommand.Dispose();
dbCommand = null;
dbConnection.Close();
dbConnection.Dispose();
dbConnection = null;
return Ticket;
}
//Update Ticket
public bool UpdateTicket(Ticket iTicket)
{
OleDbConnection dbConnection = null;
OleDbCommand dbCommand = null;
string syntax = "UPDATE tblTickets SET ";
StringBuilder stgBuilder = new StringBuilder();
for (int i = 1; i < TicketDatabaseAttributes.Length; i++)
{
string datafield = TicketDatabaseAttributes[i];
var value = iTicket.GetValue(TicketAttributes[i].ToString());
stgBuilder.Append("[");
stgBuilder.Append(datafield);
stgBuilder.Append("]='");
if (value != null)
{
stgBuilder.Append(value);
}
else
{
stgBuilder.Append("$null");
}
stgBuilder.Append("'");
if (i != TicketAttributes.Length - 1)
{
stgBuilder.Append(",");
}
}
stgBuilder.Append(" WHERE [TICKETNUMBER]=");
stgBuilder.Append(iTicket.TicketNumber);
syntax += stgBuilder.ToString();
try { dbConnection = new OleDbConnection(connectionString); }
catch (Exception ex) { throw ex; }
try { dbCommand = new OleDbCommand(syntax, dbConnection); }
catch (Exception ex) { throw ex; }
try { dbConnection.Open(); }
catch (Exception ex) { throw ex; }
dbCommand.ExecuteNonQuery();
try
{
dbCommand.Dispose();
dbCommand = null;
dbConnection.Close();
dbConnection.Dispose();
dbConnection = null;
}
catch (Exception)
{
throw;
}
return true;
}
public List<Ticket> FillTickets(string selectCommand = "*", string whereCommands = "")
{
OleDbConnection myConnection = null;
OleDbCommand myCommand = null;
string syntax = "SELECT " + selectCommand + " FROM tblTickets ";
if (whereCommands != "")
{
syntax += " WHERE " + whereCommands;
}
OleDbDataReader myReader = null;
List<Ticket> tickets = new List<Ticket>();
try { myConnection = new OleDbConnection(connectionString); }
catch (Exception ex) { throw ex; }
try { myCommand = new OleDbCommand(syntax, myConnection); }
catch (Exception ex) { throw ex; }
try { myConnection.Open(); }
catch (Exception ex) { throw ex; }
try { myReader = myCommand.ExecuteReader(); }
catch (Exception ex) { throw ex; }
try
{
while (myReader.Read())
{
Ticket ticket = new Ticket();
int ticketNumber = -1;
int.TryParse(myReader[0].ToString(), out ticketNumber);
ticket.TicketNumber = ticketNumber;
ticket.ServiceCode = myReader[1].ToString();
ticket.TagNumber = myReader[2].ToString();
ticket.Date = myReader[3].ToString();
ticket.Time = myReader[4].ToString();
ticket.TenderCode = myReader[5].ToString();
ticket.Status = myReader[6].ToString();
tickets.Add(ticket);
}
}
catch (Exception ex) { throw ex; }
try
{
myReader.Close();
myReader.Dispose();
myCommand.Dispose();
myConnection.Close();
myConnection.Dispose();
}
catch (Exception ex) { throw ex; }
return tickets;
}