public DataTable RetrieveTechnicianDetails(int techID)
{
SqlParameter techParam = new SqlParameter();
techParam.ParameterName = "@specifiedTechID";
techParam.Value = techID;
techParam.Direction = ParameterDirection.Input;
SqlCommand cmdTechnicianDetails = new SqlCommand();
cmdTechnicianDetails.Parameters.Add(techParam);
cmdTechnicianDetails.CommandText = "SELECT EMAIL, PHONE FROM DBO.TECHNICIANS WHERE TECHID = @specifiedTechID";
cmdTechnicianDetails.CommandType = CommandType.Text;
SqlConnection connA = new SqlConnection();
connA = TechSupportDB.GetTechSupportConnection();
connA.Open();
cmdTechnicianDetails.Connection = connA;
DataTable dtTechnicianDetails = new DataTable();
dtTechnicianDetails.Load(cmdTechnicianDetails.ExecuteReader());
return(dtTechnicianDetails);
}
private bool AddRegistration(int customerID, string productCode, DateTime regDate)
{
SqlConnection techSupportConnection = new SqlConnection();
techSupportConnection = TechSupportDB.GetTechSupportConnection();
techSupportConnection.Open();
SqlCommand cmdAddRegistration = new SqlCommand("spAddRegistration", techSupportConnection);
cmdAddRegistration.Parameters.AddWithValue("@CustomerID", customerID);
cmdAddRegistration.Parameters.AddWithValue("@ProductCode", productCode);
cmdAddRegistration.Parameters.AddWithValue("@RegistrationDate", regDate);
cmdAddRegistration.CommandType = CommandType.StoredProcedure;
cmdAddRegistration.Connection = techSupportConnection;
int rowsAffected = (int)cmdAddRegistration.ExecuteNonQuery();
techSupportConnection.Close();
if (rowsAffected == 1)//record insertion successful!
{
return(true);
}
else
{
return(false);
}
}
public DataTable RetrieveOpenIncidentsByTechnician(int techID)
{
//command stuff
SqlCommand cmdRetrieveOpenIncidentsByTechnician = new SqlCommand();
cmdRetrieveOpenIncidentsByTechnician.Parameters.AddWithValue("@TechnicianID", techID);
cmdRetrieveOpenIncidentsByTechnician.CommandText = "SELECT INCIDENTID, CUSTOMERID, PRODUCTCODE, TECHID, DATEOPENED, DATECLOSED, TITLE, DESCRIPTION from dbo.Incidents where DateClosed is null and TechID = @TechnicianID";
cmdRetrieveOpenIncidentsByTechnician.CommandType = CommandType.Text;
//connection stuff
SqlConnection techSupportConnection = new SqlConnection();
techSupportConnection = TechSupportDB.GetTechSupportConnection();
techSupportConnection.Open();//OPEN CONNECTION
cmdRetrieveOpenIncidentsByTechnician.Connection = techSupportConnection;
//convert to data table stuff
DataTable dtOpenIncidentsByTechnician = new DataTable();
dtOpenIncidentsByTechnician.Load(cmdRetrieveOpenIncidentsByTechnician.ExecuteReader());
techSupportConnection.Close();//CLOSE CONNECTION
return(dtOpenIncidentsByTechnician);
//select * from Incidents where DateClosed is null and TechID = 12 (sql code used for testing)
}
/// <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 RetrieveOpenIncidentsByTechnician(int techID)
{
SqlParameter openIncidentParam = new SqlParameter();
openIncidentParam.ParameterName = "@specifiedTechID";
openIncidentParam.Value = techID;
openIncidentParam.Direction = ParameterDirection.Input;
SqlCommand cmdOpenIncidentsByTech = new SqlCommand();
cmdOpenIncidentsByTech.Parameters.Add(openIncidentParam);
cmdOpenIncidentsByTech.CommandText = "SELECT INCIDENTID, CUSTOMERID, PRODUCTCODE, TECHID, DATEOPENED, DATECLOSED, TITLE, DESCRIPTION FROM DBO.INCIDENTS WHERE DATECLOSED IS NULL AND TECHID = @specifiedTechID";
cmdOpenIncidentsByTech.CommandType = CommandType.Text;
SqlConnection connA = new SqlConnection();
connA = TechSupportDB.GetTechSupportConnection();
connA.Open();
cmdOpenIncidentsByTech.Connection = connA;
DataTable dtOpenIncidents = new DataTable();
dtOpenIncidents.Load(cmdOpenIncidentsByTech.ExecuteReader());
return(dtOpenIncidents);
}
private bool CheckRegistration(int customerID, string productCode)
{
SqlConnection techSupportConnection = new SqlConnection();
techSupportConnection = TechSupportDB.GetTechSupportConnection();
techSupportConnection.Open();
SqlCommand cmdCheckRegistration = new SqlCommand();
cmdCheckRegistration.Parameters.AddWithValue("@CustomerID", customerID);
cmdCheckRegistration.Parameters.AddWithValue("@ProductCode", productCode);
cmdCheckRegistration.CommandText = "SELECT COUNT (*) FROM DBO.REGISTRATIONS WHERE PRODUCTCODE = @ProductCode and CUSTOMERID = @CustomerID";
cmdCheckRegistration.CommandType = CommandType.Text;//needed system.data for this????
cmdCheckRegistration.Connection = techSupportConnection;
int countValue = (int)cmdCheckRegistration.ExecuteScalar();
techSupportConnection.Close();
if (countValue == 1)//record already exists!
{
return(true);
}
else
{
return(false);
}
}
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);
}
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);
}
//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 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 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 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 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);
}
}
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
}
}