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