public static List <Technician> GetAllTechnicians()
{
List <Technician> technicians = new List <Technician>(); // empty list for return
Technician technician;
SqlConnection connection = TechSupportDB.GetConnection(); // DB connection
// create select command
string selectString = "SELECT TechID, Name, Email, Phone " +
"FROM Technicians " +
"ORDER BY Name";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
// try to connect and execute command
try {
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read()) // get next row
{
technician = new Technician((int)reader["TechID"],
reader["Name"].ToString(),
reader["Email"].ToString(),
reader["Phone"].ToString());
technicians.Add(technician); // build the list
}
reader.Close(); // close the reader
} catch (SqlException ex) { throw ex; } // throw exception to form
finally { connection.Close(); }
return(technicians);
} // end GetAllTechnicians
public static List <Incident> GetOpenTechIncidents(int techID)
{
List <Incident> openTechIncidents = new List <Incident>(); // empty list to return
Incident techIncident = null; // empty incident to build list
SqlConnection connection = TechSupportDB.GetConnection(); // db connection
string selectString = "SELECT Incidents.IncidentID, Customers.Name, Incidents.ProductCode, Incidents.TechID, " +
" Incidents.DateOpened, Incidents.DateClosed, Incidents.Title, Incidents.Description " +
"FROM Incidents JOIN Customers " +
"ON Incidents.CustomerID = Customers.CustomerID " +
"WHERE Incidents.TechID = @TechID AND Incidents.DateClosed IS NULL " +
"ORDER BY Incidents.DateOpened"; // get open incidents for tech Incidents.DateClosed IS NULL AND
SqlCommand selectCommand = new SqlCommand(selectString, connection);
//selectCommand.Parameters.AddWithValue("@TechID", techID);
SqlParameter param = new SqlParameter("@TechID", SqlDbType.Int);
param.Value = techID;
selectCommand.Parameters.Add(param);
// try to connect and execute command
try {
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read()) // get next row
{
techIncident = new Incident();
techIncident.IncidentID = (int)reader["IncidentID"];
techIncident.CustomerName = reader["Name"].ToString();
techIncident.ProductCode = reader["ProductCode"].ToString();
if (reader["TechID"] != DBNull.Value) // check for null values
{
techIncident.TechID = (int)reader["TechID"];
}
else
{
techIncident.TechID = null;
}
techIncident.DateOpened = Convert.ToDateTime(reader["DateOpened"]);
if (reader["DateClosed"] != DBNull.Value) // check for null values
{
techIncident.DateClosed = (DateTime)reader["DateClosed"];
}
else
{
techIncident.DateClosed = null;
}
techIncident.Title = reader["Title"].ToString();
techIncident.Description = reader["Description"].ToString();
openTechIncidents.Add(techIncident); // build the list of open incidents
}
reader.Close(); // close the readre
} catch (SqlException ex) { throw ex; } // throw exception to form
finally { connection.Close(); }
return(openTechIncidents);
} // end GetOpenTechIncidents