/// <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; }