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