public List <InvestmentProjectList> InvestmentProjectHotspotList()
{
List <InvestmentProjectList> lstHotSpot = new List <InvestmentProjectList>();
string dataQuery = @"SELECT '' AS code, 'Choose...' AS name
UNION ALL
SELECT hotspot AS code, hotspot AS name FROM (
SELECT DISTINCT hotspot
FROM public.map_investment_project_info
WHERE hotspot IS NOT NULL AND hotspot != '' AND is_project_active = 1
ORDER BY hotspot ASC
) AS hotspot;";
try
{
using DbCommand command = db.Database.GetDbConnection().CreateCommand();
command.CommandText = dataQuery;
db.Database.OpenConnection();
using DbDataReader result = command.ExecuteReader();
while (result.Read())
{
InvestmentProjectList _mei = new InvestmentProjectList
{
Code = result[0].ToString(),
Name = result[1].ToString()
};
lstHotSpot.Add(_mei);
}
}
catch (Exception ex)
{
lstHotSpot = new List <InvestmentProjectList>();
InvestmentProjectList _mei_ex = new InvestmentProjectList
{
Code = "",
Name = ex.Message
};
lstHotSpot.Add(_mei_ex);
}
return(lstHotSpot);
}
public List <InvestmentProjectList> InvestmentProjectList(string hotspot)
{
List <InvestmentProjectList> lstInvProj = new List <InvestmentProjectList>();
string dataQuery = @"SELECT '' AS code, 'Choose a project...' AS name
UNION ALL
SELECT distinct p.code, p.title
FROM public.map_investment_project p
INNER JOIN public.map_investment_project_info i ON p.code = i.project_code
WHERE i.hotspot = '" + hotspot + @"'
ORDER BY code;";
try
{
using DbCommand command = db.Database.GetDbConnection().CreateCommand();
command.CommandText = dataQuery;
db.Database.OpenConnection();
using DbDataReader result = command.ExecuteReader();
while (result.Read())
{
InvestmentProjectList _mei = new InvestmentProjectList
{
Code = result[0].ToString(),
Name = result[1].ToString()
};
lstInvProj.Add(_mei);
}
}
catch (Exception ex)
{
lstInvProj = new List <InvestmentProjectList>();
InvestmentProjectList _mei_ex = new InvestmentProjectList
{
Code = "",
Name = ex.Message
};
lstInvProj.Add(_mei_ex);
}
return(lstInvProj);
}
