public static List <ClinkerServices> GetAllClinkerServices()
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var getAllClinkerServicesCommand = connection.CreateCommand();
getAllClinkerServicesCommand.CommandText = @"select cs.Id, cs.ClinkerId, cs.ServiceId
from clinkerServices cs";
var reader = getAllClinkerServicesCommand.ExecuteReader();
while (reader.Read())
{
var id = (int)reader["Id"];
var clinkerId = (int)reader["ClinkerId"];
var serviceId = (int)reader["ServiceId"];
var newClinkerService = new ClinkerServices()
{
Id = id, ClinkerId = clinkerId, ServiceId = serviceId
};
_ClinkerServices.Add(newClinkerService);
}
}
return(_ClinkerServices);
}
public IEnumerable <ClinkerServices> GetAllServicesByClinkerId(int clinkerId)
{
var sqlForClinkers = @"select * from Clinker where Id = @ClinkerId";
var sqlForServices = @"select * from Services";
using (var db = new SqlConnection(ConnectionString))
{
var parameters = new { ClinkerId = clinkerId };
var clinkers = db.Query <Clinker>(sqlForClinkers, parameters);
var serviceList = db.Query <Services>(sqlForServices);
var clinkersWithServices = new List <ClinkerServices>();
foreach (var clinker in clinkers)
{
var clinkerWithServices = new ClinkerServices
{
ClinkerId = clinker.Id,
Name = $"{clinker.FirstName} {clinker.LastName}",
Services = serviceList.Where(x => x.ClinkerId == clinker.Id).Select(x => x.Name)
};
clinkersWithServices.Add(clinkerWithServices);
}
return(clinkersWithServices);
}
}