/// <summary>
/// method that retrieves all incidents by technician
/// </summary>
/// <param name="techID"></param>
/// <returns DataTable ></returns>
public DataTable RetrieveIncidentsByTechnician(int techID)
{
SqlParameter invParm = new SqlParameter();//as shown in in class example
invParm.ParameterName = "@TechnicianID";
invParm.Value = techID;
invParm.Direction = ParameterDirection.Input;
SqlCommand cmdRetrieveIncidentsByTechnician = new SqlCommand();
cmdRetrieveIncidentsByTechnician.Parameters.Add(invParm);//as shown in in class example
//cmdRetrieveIncidentsByTechnician.Parameters.AddWithValue("@TechnicianID", techID);//different way from in class example// he said "this will work too!"
cmdRetrieveIncidentsByTechnician.CommandText = "SELECT INCIDENTID, CUSTOMERID, PRODUCTCODE, TECHID, DATEOPENED, DATECLOSED, TITLE, DESCRIPTION FROM DBO.INCIDENTS WHERE TECHID = @TechnicianID";
cmdRetrieveIncidentsByTechnician.CommandType = CommandType.Text;
//SqlConnection techSupportConnection = TechSupportDB.GetTechSupportConnection();//this is different form in class example
SqlConnection techSupportConnection = new SqlConnection(); //in class example did it this way
techSupportConnection = TechSupportDB.GetTechSupportConnection(); //in class example did it this way
techSupportConnection.Open(); //OPEN CONNECTION
cmdRetrieveIncidentsByTechnician.Connection = techSupportConnection;
DataTable dtIncidentsByTechnician = new DataTable();
dtIncidentsByTechnician.Load(cmdRetrieveIncidentsByTechnician.ExecuteReader());
techSupportConnection.Close();//CLOSE CONNECTION
return(dtIncidentsByTechnician);
}
public DataTable GetCustomerIDandName()
{
SqlCommand cmdCustomerNames = new SqlCommand();
cmdCustomerNames.CommandText = "SELECT NAME, CUSTOMERID FROM DBO.CUSTOMERS";
cmdCustomerNames.CommandType = CommandType.Text;
SqlConnection connA = new SqlConnection();
connA = TechSupportDB.GetTechSupportConnection();
connA.Open();
cmdCustomerNames.Connection = connA;
DataTable dtCustomerNames = new DataTable();
dtCustomerNames.Load(cmdCustomerNames.ExecuteReader());
return(dtCustomerNames);
}
public DataTable RetrieveTechnicianNames()
{
SqlCommand cmdTechnicianNames = new SqlCommand();
cmdTechnicianNames.CommandText = "SELECT NAME, TECHID FROM DBO.TECHNICIANS";
cmdTechnicianNames.CommandType = CommandType.Text;
SqlConnection connA = new SqlConnection();
connA = TechSupportDB.GetTechSupportConnection();
connA.Open();
cmdTechnicianNames.Connection = connA;
DataTable dtTechnicianNames = new DataTable();
dtTechnicianNames.Load(cmdTechnicianNames.ExecuteReader());
return(dtTechnicianNames);
}
public DataTable GetProductNameAndCode()
{
SqlCommand cmdProductNames = new SqlCommand();
cmdProductNames.CommandText = "SELECT NAME, PRODUCTCODE FROM DBO.PRODUCTS";
cmdProductNames.CommandType = CommandType.Text;
SqlConnection connA = new SqlConnection();
connA = TechSupportDB.GetTechSupportConnection();
connA.Open();
cmdProductNames.Connection = connA;
DataTable dtProductNames = new DataTable();
dtProductNames.Load(cmdProductNames.ExecuteReader());
return(dtProductNames);
}
//Method to retrieve all the incident entries.
public DataTable RetrieveAllIncidents()
{
SqlCommand cmdAllIncidents = new SqlCommand();
cmdAllIncidents.CommandText = "SELECT INCIDENTID, CUSTOMERID, PRODUCTCODE, TECHID, DATEOPENED, DATECLOSED, TITLE, DESCRIPTION FROM DBO.INCIDENTS";
cmdAllIncidents.CommandType = CommandType.Text;
SqlConnection connA = new SqlConnection();
connA = TechSupportDB.GetTechSupportConnection();
connA.Open();
cmdAllIncidents.Connection = connA;
//Execution of the assigned SQL statement
DataTable dtAllIncidents = new DataTable();
dtAllIncidents.Load(cmdAllIncidents.ExecuteReader());
return(dtAllIncidents);
}
/// <summary>
/// method that retrieves all incidents from TechSupportDB and stores them in a datatable
/// </summary>
/// <returns DataTable ></returns>
public DataTable RetrieveAllIncidents()
{
SqlCommand cmdRetrieveAllIncidents = new SqlCommand();
cmdRetrieveAllIncidents.CommandText = "SELECT INCIDENTID, CUSTOMERID, PRODUCTCODE, TECHID, DATEOPENED, DATECLOSED, TITLE, DESCRIPTION FROM DBO.INCIDENTS";
cmdRetrieveAllIncidents.CommandType = CommandType.Text;
//SqlConnection techSupportConnection = TechSupportDB.GetTechSupportConnection();//this is different form in class example
SqlConnection techSupportConnection = new SqlConnection(); //in class example did it this way
techSupportConnection = TechSupportDB.GetTechSupportConnection(); //in class example did it this way
techSupportConnection.Open(); //OPEN CONNECTION
cmdRetrieveAllIncidents.Connection = techSupportConnection;
DataTable dtAllIncidents = new DataTable();
dtAllIncidents.Load(cmdRetrieveAllIncidents.ExecuteReader());
techSupportConnection.Close();//CLOSE CONNECTION
return(dtAllIncidents);
}
public DataTable RetreiveTechnicianNames()
{
SqlCommand cmdAllTechnicianNames = new SqlCommand();
cmdAllTechnicianNames.CommandText = "SELECT TECHID, NAME FROM DBO.TECHNICIANS";
cmdAllTechnicianNames.CommandType = CommandType.Text;
SqlConnection techSupportConnection = new SqlConnection();
techSupportConnection = TechSupportDB.GetTechSupportConnection();
techSupportConnection.Open();//OPEN CONNECTION
cmdAllTechnicianNames.Connection = techSupportConnection;
DataTable dtAllTechnicianNames = new DataTable();
dtAllTechnicianNames.Load(cmdAllTechnicianNames.ExecuteReader());
techSupportConnection.Close();//CLOSE CONNECTION
return(dtAllTechnicianNames);
}
public DataTable GetCustomerIDandName()
{
DataTable dtCustomerIDandName = new DataTable();
SqlConnection techSupportConnection = new SqlConnection();
techSupportConnection = TechSupportDB.GetTechSupportConnection();
SqlCommand cmdGetCustomerIDandName = new SqlCommand();
cmdGetCustomerIDandName.CommandText = "SELECT CUSTOMERID, NAME FROM DBO.CUSTOMERS";
cmdGetCustomerIDandName.CommandType = CommandType.Text;
cmdGetCustomerIDandName.Connection = techSupportConnection;
techSupportConnection.Open();
dtCustomerIDandName.Load(cmdGetCustomerIDandName.ExecuteReader());
techSupportConnection.Close();
return(dtCustomerIDandName);
}
public DataTable RetrieveTechnicianDetails(int techID)
{
SqlConnection techSupportConnection = new SqlConnection();
techSupportConnection = TechSupportDB.GetTechSupportConnection();
techSupportConnection.Open();//OPEN CONNECTION
SqlCommand cmdRetrieveTechnicianDetails = new SqlCommand();
cmdRetrieveTechnicianDetails.CommandText = "SELECT EMAIL, PHONE FROM DBO.TECHNICIANS WHERE TECHID = @techID";
cmdRetrieveTechnicianDetails.CommandType = CommandType.Text;
cmdRetrieveTechnicianDetails.Parameters.AddWithValue("@techID", techID);
cmdRetrieveTechnicianDetails.Connection = techSupportConnection;
DataTable dtTechnicianDetails = new DataTable();
dtTechnicianDetails.Load(cmdRetrieveTechnicianDetails.ExecuteReader());
techSupportConnection.Close();//CLOSE CONNECTION
return(dtTechnicianDetails);
}
public DataTable GetProductNameAndCode()
{
DataTable dtProductNameAndCode = new DataTable();
//1) make connection 2) make command 3) assign connection to command 4) open, load dt, close
SqlConnection techSupportConnection = new SqlConnection();
techSupportConnection = TechSupportDB.GetTechSupportConnection();
SqlCommand cmdProductNameAndCode = new SqlCommand();
cmdProductNameAndCode.CommandText = "SELECT PRODUCTCODE, NAME FROM DBO.PRODUCTS";
cmdProductNameAndCode.CommandType = CommandType.Text;
cmdProductNameAndCode.Connection = techSupportConnection;
techSupportConnection.Open();
dtProductNameAndCode.Load(cmdProductNameAndCode.ExecuteReader());
techSupportConnection.Close();
return(dtProductNameAndCode);
}
private bool CheckRegistration(int customerID, string productCode)
{
SqlCommand cmdCheckRegistration = new SqlCommand();
cmdCheckRegistration.CommandText = "SELECT COUNT(*) FROM dbo.Registrations WHERE PRODUCTCODE = @ProductCode AND CUSTOMERID = @CustomerID";
cmdCheckRegistration.CommandType = CommandType.Text;
cmdCheckRegistration.Connection = TechSupportDB.GetTechSupportConnection();
cmdCheckRegistration.Parameters.AddWithValue("@CustomerID", customerID);
cmdCheckRegistration.Parameters.AddWithValue("@ProductCode", productCode);
cmdCheckRegistration.Connection.Open();
int countCheck = Convert.ToInt32(cmdCheckRegistration.ExecuteScalar());
cmdCheckRegistration.Connection.Close();
if (countCheck == 1)
{
return(true); //The registration already exists
}
else
{
return(false); //The registration does not exist
}
}
private bool AddRegistration(int customerID, string productCode, DateTime regDate)
{
SqlCommand cmdAddReg = new SqlCommand();
cmdAddReg.CommandText = "spAddRegistration"; //Stored Procedure
cmdAddReg.CommandType = CommandType.StoredProcedure;
cmdAddReg.Connection = TechSupportDB.GetTechSupportConnection();
cmdAddReg.Connection.Open();
cmdAddReg.Parameters.AddWithValue("@customerID", customerID);
cmdAddReg.Parameters.AddWithValue("@productCode", productCode);
cmdAddReg.Parameters.AddWithValue("@registrationDate", regDate);
int numOfRecs = cmdAddReg.ExecuteNonQuery();
cmdAddReg.Connection.Close();
if (numOfRecs == 1)
{
return(true);
}
else
{
return(false);
}
}