public void AddRegistration(Registration registration) { SqlCommand insertCommand = new SqlCommand(); insertCommand.Connection = TechSupportDB.GetConnection(); insertCommand.CommandText = "Insert INTO Registrations ( CustomerID, ProductCode, RegistrationDate) VALUES ( @CustomerID,@ProductCode,@RegistrationDate)"; insertCommand.CommandType = CommandType.Text; insertCommand.Parameters.AddWithValue("@CustomerID", registration.CustomerID); insertCommand.Parameters.AddWithValue("@ProductCode", registration.ProductCode); insertCommand.Parameters.AddWithValue("@RegistrationDate", registration.RegistrationDate); try { insertCommand.Connection.Open(); insertCommand.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { insertCommand.Connection.Close(); } }
public List <Incident> GetOpenIncidents() { SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); string selectStatement = "Select ProductCode, DateOpened,Customers.Name,Technicians.Name AS Technician,Title FROM Incidents left outer join Technicians on Incidents.TechID = Technicians.TechID inner JOIN Customers on Incidents.CustomerID = Customers.CustomerID WHERE DateClosed is NULL"; aCommand.CommandText = selectStatement; aCommand.CommandType = CommandType.Text; List <Incident> listOfIncidents = new List <Incident>(); try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader (CommandBehavior.CloseConnection); while (aReader.Read()) { Incident anIncident = new Incident(); anIncident.ProductCode = aReader["ProductCode"].ToString(); anIncident.DateOpened = (DateTime)aReader["DateOpened"]; anIncident.CustomerName = aReader["Technician"].ToString(); anIncident.TechName = aReader["Technician"].ToString(); anIncident.Title = aReader["Title"].ToString(); listOfIncidents.Add(anIncident); } return(listOfIncidents); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } }
public bool ProductRegistration(int customerID, string productCode) { Registration aRegistration = new Registration(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = "Select CustomerID, ProductCode FROM Registrations WHERE CustomerID = @CustomerID AND ProductCode = @ProductCode"; aCommand.CommandType = CommandType.Text; aCommand.Parameters.AddWithValue("@CustomerID", customerID); aCommand.Parameters.AddWithValue("@ProductCode", productCode); try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection); if (aReader.Read()) { aRegistration.CustomerID = (int)aReader["CustomerID"]; aRegistration.ProductCode = aReader["ProductCode"].ToString(); return(true); } else { aRegistration = null; return(false); } } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } }
public Product GetProductName(String productCode) { Product aProduct = new Product(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = " select Name from Products Where ProductCode = @ProductCode "; aCommand.CommandType = CommandType.Text; aCommand.Parameters.AddWithValue("@ProductCode", productCode); try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection); if (aReader.Read()) { aProduct.Name = aReader["Name"].ToString(); aProduct.ProductCode = aReader["ProductCode"].ToString(); } else { aProduct = null; } aReader.Close(); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } return(aProduct); }
public Customer GetCustomerName(int customerID) { Customer aCustomer = new Customer(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = " select Name from Customers Where CustomerID = @CustomerID "; aCommand.CommandType = CommandType.Text; aCommand.Parameters.AddWithValue("@CustomerID", customerID); try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection); if (aReader.Read()) { aCustomer.Name = aReader["Name"].ToString(); } else { aCustomer = null; } aReader.Close(); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } return(aCustomer); }
public List <Customer> GetCustomerList() { List <Customer> listOfCustomers = new List <Customer>(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = "SELECT CustomerID, Name FROM Customers"; aCommand.CommandType = CommandType.Text; try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader (CommandBehavior.CloseConnection); while (aReader.Read()) { Customer aCustomer = new Customer(); aCustomer.CustomerID = (int)aReader["CustomerID"]; aCustomer.Name = aReader["Name"].ToString(); listOfCustomers.Add(aCustomer); } aReader.Close(); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } return(listOfCustomers); }
public List <Product> GetProductsList() { List <Product> listOfProducts = new List <Product>(); SqlCommand aCommand = new SqlCommand(); aCommand.Connection = TechSupportDB.GetConnection(); aCommand.CommandText = "SELECT Name, ProductCode FROM Products"; aCommand.CommandType = CommandType.Text; try { aCommand.Connection.Open(); SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.CloseConnection); while (aReader.Read()) { Product aProduct = new Product(); aProduct.Name = aReader["Name"].ToString(); //aProduct.Name = aReader["ProductCode"].ToString();\ aProduct.ProductCode = aReader["ProductCode"].ToString(); listOfProducts.Add(aProduct); } aReader.Close(); } catch (SqlException ex) { throw ex; } finally { aCommand.Connection.Close(); } return(listOfProducts); }