public List <AllSector> GetAllSectors()
{
var lookup = new Dictionary <string, AllSector>();
var lookupSub = new Dictionary <string, SubSector>();
var command = @"select Count(*) from MainSector";
var nResult = _dal.ExecuteScalar <int>(command, null, CommandType.Text);
if (nResult == 0)
{
ParseHTML();
}
command = String.Format(CultureInfo.InvariantCulture, "select distinct main.*, sub.SubSectorId, sub.SubSectorName, det.DetailSectorId, det.DetailSectorName from " +
"MainSector main left join SubSector sub on sub.MainSectorSubId = main.MainSectorID left join " +
"DetailSector det on det.SubSectorID = sub.SubSectorId");
_dal.Query <AllSector, SubSector, DetailSector, AllSector>(command,
(mainSector, subSector, detailSector) =>
{
AllSector sector;
SubSector subsector;
if (!lookup.TryGetValue(mainSector.MainSectorName, out sector))
{
lookup.Add(mainSector.MainSectorName, sector = mainSector);
}
if (subSector != null)
{
if (sector.subSector == null)
{
sector.subSector = new List <SubSector>();
}
if (sector.subSector.Find(x => x.SubSectorName == subSector.SubSectorName) == null)
{
sector.subSector.Add(subSector);
}
if (!lookupSub.TryGetValue(subSector.SubSectorName, out subsector))
{
lookupSub.Add(subSector.SubSectorName, subsector = subSector);
}
if (detailSector != null)
{
if (subsector.detailSector == null)
{
subsector.detailSector = new List <DetailSector>();
}
subsector.detailSector.Add(detailSector);
}
}
return(sector);
}, null, "SubSectorId, DetailSectorId", CommandType.Text);
return(lookup.Values.ToList());
}