/// <summary> /// Retrieves a list of Products from the database /// </summary> /// <returns>Returns a list of Product objects based on what is returned from the database</returns> public static List <Product> GetProductList() { List <Product> productList = new List <Product>(); string selectStatement = "SELECT ProductCode, Name FROM Products;"; SqlConnection connection = IncidentsDBConnection.GetConnection(); SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int productCode = reader.GetOrdinal("ProductCode"); int productName = reader.GetOrdinal("Name"); while (reader.Read()) { Product product = new Product(); product.ProductCode = reader.GetString(productCode); product.Name = reader.GetString(productName); productList.Add(product); } reader.Close(); } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } return(productList); }
/// <summary> /// Retrieves a list of techs from the database /// </summary> /// <returns>Returns a list of Technician objects based on what is returned from the database</returns> public static List <Technician> GetTechnicianList() { List <Technician> techList = new List <Technician>(); string selectStatement = "SELECT TechID, Name FROM Technicians;"; SqlConnection connection = IncidentsDBConnection.GetConnection(); SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int techID = reader.GetOrdinal("TechID"); int techName = reader.GetOrdinal("Name"); while (reader.Read()) { Technician tech = new Technician(); tech.TechID = reader.GetInt32(techID); tech.Name = reader.GetString(techName); techList.Add(tech); } reader.Close(); } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } return(techList); }
/// <summary> /// Retrieves a list of Customers from the database /// </summary> /// <returns>Returns a list of Customer objects based on what is returned from the database</returns> public static List <Customer> GetCustomerList() { List <Customer> customerList = new List <Customer>(); string selectStatement = "SELECT CustomerID, Name FROM Customers;"; SqlConnection connection = IncidentsDBConnection.GetConnection(); SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int customerID = reader.GetOrdinal("CustomerID"); int customerName = reader.GetOrdinal("Name"); while (reader.Read()) { Customer customer = new Customer(); customer.CustomerID = reader.GetInt32(customerID); customer.Name = reader.GetString(customerName); customerList.Add(customer); } reader.Close(); } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } return(customerList); }
/// <summary> /// Returns if the incident has been closed successfully /// </summary> /// <param name="incidentID">ID of the incident to be closed</param> /// <returns>Returns true or false if the incident was properly closed</returns> public static bool CloseIncident(int incidentID) { SqlConnection connection = IncidentsDBConnection.GetConnection(); string updateStatement = "UPDATE Incidents " + "SET DateClosed = @DateClosed " + "WHERE IncidentID = @IncidentID " + "AND DateClosed IS NULL"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@DateClosed", DateTime.Now); updateCommand.Parameters.AddWithValue("@IncidentID", incidentID); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } }
/// <summary> /// Retrieves the Incidents from the database /// </summary> /// public static List <Incident> GetIncidents() { List <Incident> incidentList = new List <Incident>(); string selectStatement = "SELECT ProductCode, DateOpened, c.name as Customer, t.name as Technician, Title " + "FROM Incidents i Join Customers c On " + "i.CustomerID = c.CustomerID " + "LEFT OUTER JOIN Technicians t ON " + "i.TechID = t.TechID " + "WHERE DateClosed IS NULL " + "ORDER BY DateOpened DESC; "; using (SqlConnection connection = IncidentsDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { int incidentProductCode = reader.GetOrdinal("ProductCode"); int incidentDateOpened = reader.GetOrdinal("DateOpened"); int incidentCustomer = reader.GetOrdinal("Customer"); int incidentTechnician = reader.GetOrdinal("Technician"); int incidentTitle = reader.GetOrdinal("Title"); while (reader.Read()) { Incident incident = new Incident(); incident.ProductCode = reader.GetString(incidentProductCode); incident.DateOpened = reader.GetDateTime(incidentDateOpened); incident.Customer = reader.GetString(incidentCustomer); if (reader.IsDBNull(incidentTechnician)) { incident.Technician = ""; } else { incident.Technician = reader.GetString(incidentTechnician); } incident.Title = reader.GetString(incidentTitle); incidentList.Add(incident); } } } } return(incidentList); }
private static int CheckRegistration(Incident incident) { int registrationCount; SqlConnection connection = IncidentsDBConnection.GetConnection(); string registrationCheckStatement = "SELECT COUNT(*) FROM Registrations " + "WHERE CustomerID = @CustomerID " + "AND ProductCode = @ProductCode"; SqlCommand registrationCommand = new SqlCommand(registrationCheckStatement, connection); registrationCommand.Parameters.AddWithValue("@CustomerID", incident.CustomerID); registrationCommand.Parameters.AddWithValue("@ProductCode", incident.ProductCode); connection.Open(); registrationCount = Convert.ToInt32(registrationCommand.ExecuteScalar()); return(registrationCount); }
/// <summary> /// Retrieves list of incidents for a certain technician /// </summary> /// <param name="techID">TechID used to retrieve a lsit of incidents for that tech</param> /// <returns>Returns list of incidents for a certain technician</returns> public static List <Incident> GetIncidentsForTech(int techID) { List <Incident> myIncidents = new List <Incident>(); SqlConnection connection = IncidentsDBConnection.GetConnection(); string selectStatement = "SELECT p.Name AS Product, i.DateOpened, c.Name AS Customer, i.Title " + "FROM Incidents i JOIN Products p ON " + "i.ProductCode = p.ProductCode " + "JOIN Customers c ON " + "i.CustomerID = c.CustomerID " + "WHERE techID = @TechID " + "AND i.DateClosed IS NULL"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@TechID", techID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int product = reader.GetOrdinal("Product"); int dateOpened = reader.GetOrdinal("DateOpened"); int customer = reader.GetOrdinal("Customer"); int title = reader.GetOrdinal("Title"); while (reader.Read()) { Incident incident = new Incident(); incident.Customer = reader.GetString(customer); incident.Product = reader.GetString(product); incident.DateOpened = reader.GetDateTime(dateOpened).Date; incident.Title = reader.GetString(title); myIncidents.Add(incident); } reader.Close(); } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } return(myIncidents); }
/// <summary> /// Adds given incident to database /// </summary> /// <param name="incident">Incident to be added to the database</param> /// <returns>Returns incidentID given by the databse when created</returns> public static int AddIncident(Incident incident) { IncidentController incidentController = new IncidentController(); SqlConnection connection = IncidentsDBConnection.GetConnection(); string insertStatement = "INSERT Incidents (CustomerID, ProductCode, DateOpened, Title, Description) " + "VALUES (@CustomerID, @ProductCode, @DateOpened, @Title, @Description)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@CustomerID", incident.CustomerID); insertCommand.Parameters.AddWithValue("@ProductCode", incident.ProductCode); insertCommand.Parameters.AddWithValue("@DateOpened", incident.DateOpened); insertCommand.Parameters.AddWithValue("@Title", incident.Title); insertCommand.Parameters.AddWithValue("@Description", incident.Description); try { connection.Open(); int registrationCount = CheckRegistration(incident); if (registrationCount == 0) { throw new Exception("The customer and product selected are not registered together."); } else { insertCommand.ExecuteNonQuery(); } string selectStatement = "SELECT IDENT_CURRENT('Incidents') FROM Incidents"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); int incidentID = Convert.ToInt32(selectCommand.ExecuteScalar()); return(incidentID); } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } }
/// <summary> /// Retrieves a list of techs from the database that have incidents /// </summary> /// <returns>Returns a list of Technician objects based on what is returned from the database</returns> public static List <Technician> GetTechniciansWithIncidents() { List <Technician> techList = new List <Technician>(); string selectStatement = "SELECT DISTINCT t.TechID, t.Name, t.Email, t.Phone " + "FROM Technicians t " + "INNER JOIN Incidents i ON " + "t.TechID = i.TechID; "; SqlConnection connection = IncidentsDBConnection.GetConnection(); SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int techID = reader.GetOrdinal("TechID"); int techName = reader.GetOrdinal("Name"); int techEmail = reader.GetOrdinal("Email"); int techPhone = reader.GetOrdinal("Phone"); while (reader.Read()) { Technician tech = new Technician(); tech.TechID = reader.GetInt32(techID); tech.Name = reader.GetString(techName); tech.Email = reader.GetString(techEmail); tech.Phone = reader.GetString(techPhone); techList.Add(tech); } reader.Close(); } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } return(techList); }
/// <summary> /// Returns whether or not the incident was updated successfully /// </summary> /// <param name="oldIncident">Old incident as a reference</param> /// <param name="newIncident">new reference used to populate fields</param> /// <returns>Returns true or false depending on if the incident was properly updated</returns> public static bool UpdateIncident(Incident oldIncident, Incident newIncident) { SqlConnection connection = IncidentsDBConnection.GetConnection(); string updateStatement = "UPDATE Incidents " + "SET techID = @NewTechID, " + "Description = @NewDescription " + "WHERE IncidentID = @OldIncidentID " + "AND Description = @OldDescription"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@NewTechID", newIncident.TechID); // the 200 character logic for the description to be handled in the same place where this method is called updateCommand.Parameters.AddWithValue("@NewDescription", newIncident.Description); updateCommand.Parameters.AddWithValue("@OldIncidentID", oldIncident.IncidentID); updateCommand.Parameters.AddWithValue("@OldDescription", oldIncident.Description); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } }
/// <summary> /// Retrieves specific tech based on its ID passed in /// </summary> /// <param name="techID">Used to find the correct technician</param> /// <returns>Returns technician based on its techID</returns> public static Technician GetTechnician(int techID) { Technician technician = new Technician(); SqlConnection connection = IncidentsDBConnection.GetConnection(); string selectStatement = "SELECT TechID, Name " + "FROM Technicians " + "WHERE TechID = @TechID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@TechID", techID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow); int tech_ID = reader.GetOrdinal("TechID"); int name = reader.GetOrdinal("Name"); while (reader.Read()) { technician.TechID = reader.GetInt32(tech_ID); technician.Name = reader.GetString(name); } reader.Close(); } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } return(technician); }
/// <summary> /// Retrieves the incident that has the incidentID provided /// </summary> /// <param name="incidentID">Incident ID used to retrieve a certain incident</param> /// <returns>Returns incident based on its incidentID</returns> public static Incident GetIncident(int incidentID) { // I couldn't quite get the technician being null figured out. Many hours were spent trying // to figure out how it can work within the current context but nothing seemed to work. From // my testing, anytime the technician was null, the reader wouldnt even read() and therefore // everything would be blank if you searched for an incident that didnt have a technician. I // am not sure what could be off Incident incident = new Incident(); SqlConnection connection = IncidentsDBConnection.GetConnection(); string selectStatement = "SELECT IncidentID, c.name as Customer, ProductCode, t.name as Technician, DateOpened, DateClosed, Title, Description " + "FROM incidents i JOIN Customers c ON " + "i.CustomerID = c.CustomerID " + "LEFT OUTER JOIN Technicians t ON " + "i.TechID = t.TechID " + "WHERE IncidentID = @IncidentID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@IncidentID", incidentID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow); int incident_ID = reader.GetOrdinal("IncidentID"); int customer = reader.GetOrdinal("Customer"); int productCode = reader.GetOrdinal("ProductCode"); //int tech = reader.GetOrdinal("Technician"); int dateOpened = reader.GetOrdinal("DateOpened"); int dateClosed = reader.GetOrdinal("DateClosed"); int title = reader.GetOrdinal("Title"); int description = reader.GetOrdinal("Description"); while (reader.Read()) { incident.IncidentID = reader.GetInt32(incident_ID); incident.Customer = reader.GetString(customer); incident.ProductCode = reader.GetString(productCode); if (reader["Technician"].GetType() != typeof(DBNull)) { incident.Technician = reader["Technician"].ToString(); } else { incident.Technician = DBNull.Value.ToString(); } //incident.Technician = reader.GetString(tech); incident.DateOpened = reader.GetDateTime(dateOpened); if (reader["DateClosed"].GetType() != typeof(DBNull)) { incident.DateClosed = reader.GetDateTime(dateClosed); } else { incident.DateClosed = Convert.ToDateTime("1/1/0001"); } incident.Title = reader.GetString(title); incident.Description = reader.GetString(description); } reader.Close(); } catch (SqlException sqlex) { throw sqlex; } finally { connection.Close(); } return(incident); }