public static List <Incident> GetCustomerIncidents(int customerID)
{
List <Incident> incList = new List <Incident>(); // blank list
// define connection
SqlConnection connection = TechSupportDB.GetConnection();
//SELECT IncidentID, c.Name, ProductCode, TechID, DateOpened, DateClosed, Title, Description
// FROM Incidents i
// INNER JOIN Customers c ON c.CustomerID = i.CustomerID
// WHERE c.CustomerID = 1010;
// define the select query command
string selectQuery = "SELECT IncidentID, c.CustomerID, c.Name, ProductCode, TechID, DateOpened, DateClosed, Title, Description " +
"FROM Incidents i " +
"INNER JOIN Customers c ON c.CustomerID = i.CustomerID " +
"WHERE c.CustomerID = @CustomerID;";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@CustomerID", customerID);
try
{
// open the connection
connection.Open();
// execute the query (MULTIPLE ROWS)
SqlDataReader reader = selectCommand.ExecuteReader();
// process the result if any
while (reader.Read()) // if there is incident
{
Incident inc = new Incident(); // new object
inc.IncidentID = (int)reader["IncidentID"];
inc.CustomerID = (int)reader["CustomerID"];
inc.Name = reader["Name"].ToString();
inc.ProductCode = reader["ProductCode"].ToString();
inc.DateOpened = (DateTime)reader["DateOpened"];
inc.Title = reader["Title"].ToString();
inc.Description = reader["Description"].ToString();
//handle NULL TechID (wasn't needed on Lab3 because parameter was techID)
if (reader.IsDBNull(reader.GetOrdinal("TechID"))) //if SQL NULL
{
inc.TechID = null; //then return ASP.NET null
}
else
{
inc.TechID = (int)reader["TechID"];
}
//handle NULL DateTime
if (reader.IsDBNull(reader.GetOrdinal("DateClosed"))) //if SQL NULL
{
inc.DateClosed = null; //then return ASP.NET null
}
else
{
inc.DateClosed = (DateTime)reader["DateClosed"]; //else return value
}
//add to list
incList.Add(inc);
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connecto no matter what
}
return(incList);
}
public static List <Customer> GetAllCustomers()
{
List <Customer> custList = new List <Customer>(); // blank list
// define connection
SqlConnection connection = TechSupportDB.GetConnection();
// define the select query command
//SELECT * FROM Customers
//WHERE CustomerID = 1002;
string selectQuery = "SELECT CustomerID, Name, Address, City, State, ZipCode, Phone, Email " +
"FROM Customers " +
"ORDER BY Name;";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader();
// process the result if any
while (reader.Read()) // if there is technician
{
Customer cust = new Customer();
cust.CustomerID = (int)(reader["CustomerID"]);
cust.Name = reader["Name"].ToString();
cust.Address = reader["Address"].ToString();
cust.City = reader["City"].ToString();
cust.State = reader["State"].ToString();
cust.ZipCode = reader["ZipCode"].ToString();
//null phone
if (reader.IsDBNull(reader.GetOrdinal("Phone"))) //if SQL NULL
{
cust.Phone = null; //then return ASP.NET null
}
else
{
cust.Phone = reader["Phone"].ToString(); //else return value
}
//null email
if (reader.IsDBNull(reader.GetOrdinal("Email"))) //if SQL NULL
{
cust.Email = null; //then return ASP.NET null
}
else
{
cust.Email = reader["Email"].ToString(); //else return value
}
//add to list
custList.Add(cust);
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connecto no matter what
}
return(custList);
}