public void update(SalesPerson obj)
{
throw new NotImplementedException();
}
public List <District> GetAll()
{
List <District> districts = new List <District>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
string query = "SELECT "
+ "A.ID AS DistrictID,"
+ "A.Name AS DistrictName,"
+ "C.ID AS PrimeID,"
+ "C.FirstName AS PrimeFirstName,"
+ "C.LastName AS PrimeSecondName,"
+ "B.ID AS SecondID,"
+ "B.FirstName AS SecondFirstname,"
+ "B.LastName AS SecondLastname,"
+ "D.ID AS StoreID,"
+ "D.Franchice AS StoreFranchice "
+ "FROM District A "
+ "JOIN Personnel X ON A.ID = X.DistrictID "
+ "JOIN SalesPerson B ON X.PersonID = B.ID "
+ "JOIN SalesPerson C ON A.PrimarySalesPerson = C.ID "
+ "JOIN Store D ON D.DistrictID = A.ID";
// Create SQL command with parameterized
SqlCommand cmd = new SqlCommand(query, conn);
//cmd.Parameters.AddWithValue("@id", id);
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// District
District d = new District();
d.ID = Convert.ToInt32(reader["DistrictID"].ToString());
d.Name = reader["DistrictName"].ToString();
d.PrimarySalesPerson = new SalesPerson();
d.PrimarySalesPerson.ID = Convert.ToInt32(reader["PrimeID"].ToString());
d.PrimarySalesPerson.FirstName = reader["PrimeFirstName"].ToString();
d.PrimarySalesPerson.LastName = reader["PrimeSecondName"].ToString();
// Secondary
SalesPerson sp = new SalesPerson();
sp.ID = Convert.ToInt32(reader["SecondID"].ToString());
sp.FirstName = reader["SecondFirstname"].ToString();
sp.LastName = reader["SecondLastname"].ToString();
// Store
Store s = new Store();
s.DistrictID = d.ID;
s.ID = Convert.ToInt32(reader["StoreID"].ToString());
s.Franchise = reader["StoreFranchice"].ToString();
// Add district if not in list and go to next iteration
if (!districts.Any(x => x.ID == d.ID))
{
d.Personnel = new List <SalesPerson>();
d.Personnel.Add(sp);
d.Stores = new List <Store>();
d.Stores.Add(s);
districts.Add(d);
continue;
}
// Find index of exisisting district
int index = districts.FindIndex(x => x.ID == d.ID);
// Add secondary salesperson if not found on district
if (!districts.ElementAt(index).Personnel.Any(x => x.ID == sp.ID))
{
districts.ElementAt(index).Personnel.Add(sp);
}
// Add store if not found on district
if (!districts.ElementAt(index).Stores.Any(x => x.ID == s.ID))
{
districts.ElementAt(index).Stores.Add(s);
}
}
}
}
return(districts);
}