public static NationalCasesBLDto ToNationalCasesBLDto(this NationalCasesDADto rec)
{
return(new NationalCasesBLDto
{
Country = rec.Country,
Count = rec.Count
});
}
public List <NationalCasesDADto> GetCountOfCasesForAllNations(Metrics metrics)
{
List <NationalCasesDADto> covidCasesDALRecords = new List <NationalCasesDADto>();
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_MainDB"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand("select trim(LocationsGlobal.Country) AS 'Country', Max(ConfirmedCases.ConfirmedCasesCount) AS 'Count' from LocationsGlobal INNER JOIN ConfirmedCases ON ConfirmedCases.Id = LocationsGlobal.Id group by LocationsGlobal.Country", connection);
if (metrics == Metrics.DEATHS)
{
sqlCommand = new SqlCommand("select trim(LocationsGlobal.Country) as Country, Max(Deaths.DeathCount) AS Count from LocationsGlobal INNER JOIN Deaths ON Deaths.Id = LocationsGlobal.Id group by LocationsGlobal.Country", connection);
}
else if (metrics == Metrics.RECOVERIES)
{
sqlCommand = new SqlCommand("select trim(LocationsGlobal.Country)as Country, Max(Recoveries.RecoveriesCount) AS 'Count' from LocationsGlobal INNER JOIN Recoveries ON Recoveries.Id = LocationsGlobal.Id group by LocationsGlobal.Country", connection);
}
connection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
NationalCasesDADto CaseCountDto = new NationalCasesDADto
{
Country = (string)reader["Country"],
Count = (Int32)reader["Count"],
};
covidCasesDALRecords.Add(CaseCountDto);
}
}
catch (SqlException ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(covidCasesDALRecords);
}
public List <NationalCasesDADto> GetCountOfCasesByCountry(Metrics metrics, string Country, DateTime?Date)
{
List <NationalCasesDADto> covidCasesDALRecords = new List <NationalCasesDADto>();
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_MainDB"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand(@"select trim(l.Country) AS 'Country', Max(c.ConfirmedCasesCount) AS 'Count'
from LocationsGlobal l INNER JOIN ConfirmedCases c ON c.Id = l.Id
WHERE l.Country = @ctry
group by l.Country", connection);
if (Date != DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select trim(l.Country) AS 'Country', Max(c.ConfirmedCasesCount) AS 'Count'
from LocationsGlobal l INNER JOIN ConfirmedCases c ON c.Id = l.Id
WHERE Country = @ctry and c.[Date] = @dte
group by l.Country", connection);
}
if (metrics == Metrics.DEATHS)
{
if (Date == DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select trim(l.Country) as Country, Max(d.DeathCount) AS Count
from LocationsGlobal l INNER JOIN Deaths d ON d.Id = l.Id
WHERE Country = @ctry
group by l.Country", connection);
}
else
{
sqlCommand = new SqlCommand(@"select trim(l.Country) as Country, Max(d.DeathCount) AS Count
from LocationsGlobal l INNER JOIN Deaths d ON d.Id = l.Id
WHERE l.Country = @ctry and d.[Date] = @dte
group by l.Country", connection);
}
}
else if (metrics == Metrics.RECOVERIES)
{
if (Date == DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select trim(l.Country)as Country, Max(r.RecoveriesCount) AS 'Count'
from LocationsGlobal l INNER JOIN Recoveries r ON r.Id = l.Id
WHERE l.Country = @ctry
group by l.Country", connection);
}
else
{
sqlCommand = new SqlCommand(@"select trim(l.Country)as Country, Max(r.RecoveriesCount) AS 'Count'
from LocationsGlobal l INNER JOIN Recoveries r ON r.Id = l.Id
WHERE l.Country = @ctry and r.[Date] = @dte
group by l.Country", connection);
}
}
SqlParameter ctry = new SqlParameter("ctry", System.Data.SqlDbType.NVarChar);
ctry.Value = Country;
sqlCommand.Parameters.Add(ctry);
if (Date != DateTime.MinValue)
{
SqlParameter dte = new SqlParameter("dte", System.Data.SqlDbType.NVarChar);
dte.Value = Date;
sqlCommand.Parameters.Add(dte);
}
connection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
NationalCasesDADto CaseCountDto = new NationalCasesDADto
{
Country = (string)reader["Country"],
Count = (Int32)reader["Count"],
};
covidCasesDALRecords.Add(CaseCountDto);
}
}
catch (SqlException ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(covidCasesDALRecords);
}
public List <NationalCasesDADto> GetCountOfCasesByCountry(Metrics metrics, string Country, DateTime?Date)
{
List <NationalCasesDADto> covidCasesDALRecords = new List <NationalCasesDADto>();
SqlConnection connection = new SqlConnection(_configuration.GetConnectionString("MSSQL_MainDB"));
SqlCommand sqlCommand = new SqlCommand(@"select Country, sum(Count) as Count
from
(
select Country, [state], MAX(c.ConfirmedCasesCount) as Count
from ConfirmedCases c
inner join LocationsGlobal l on c.ID = l.ID
WHERE l.Country = @ctry
group by [state], l.Country
) numbers
group by Country", connection);
if (Date != DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select Country, sum(Count) as Count
from
(
select Country, [state], MAX(c.ConfirmedCasesCount) as Count
from ConfirmedCases c
inner join LocationsGlobal l on c.ID = l.ID
WHERE Country = @ctry and c.[Date] = @dte
group by [state], l.Country
) numbers
group by Country", connection);
}
if (metrics == Metrics.DEATHS)
{
if (Date == DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select Country, sum(Count) as Count
from
(
select Country, [state], MAX(d.DeathCount) as Count
from DEATHS d
inner join LocationsGlobal l on d.ID = l.ID
WHERE l.Country = @ctry
group by [state], l.Country
) numbers
group by Country", connection);
}
else
{
sqlCommand = new SqlCommand(@"select Country, sum(Count) as Count
from
(
select Country, [state], MAX(d.DeathCount) as Count
from DEATHS d
inner join LocationsGlobal l on d.ID = l.ID
WHERE Country = @ctry and d.[Date] = @dte
group by [state], l.Country
) numbers
group by Country", connection);
}
}
else if (metrics == Metrics.RECOVERIES)
{
if (Date == DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select Country, sum(Count) as Count
from
(
select Country, [state], MAX(r.RecoveriesCount) as Count
from RECOVERIES r
inner join LocationsGlobal l on r.ID = l.ID
WHERE Country = @ctry
group by [state], l.Country
) numbers
group by Country", connection);
}
else
{
sqlCommand = new SqlCommand(@"select Country, sum(Count) as Count
from
(
select Country, [state], MAX(r.RecoveriesCount) as Count
from RECOVERIES r
inner join LocationsGlobal l on r.ID = l.ID
WHERE Country = @ctry and d.[Date] = @dte
group by [state], l.Country
) numbers
group by Country", connection);
}
}
SqlParameter ctry = new SqlParameter("ctry", System.Data.SqlDbType.NVarChar);
ctry.Value = Country;
sqlCommand.Parameters.Add(ctry);
if (Date != DateTime.MinValue)
{
SqlParameter dte = new SqlParameter("dte", System.Data.SqlDbType.NVarChar);
dte.Value = Date;
sqlCommand.Parameters.Add(dte);
}
connection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
NationalCasesDADto CaseCountDto = new NationalCasesDADto
{
Country = (string)reader["Country"],
Count = (Int32)reader["Count"],
};
covidCasesDALRecords.Add(CaseCountDto);
}
}
catch (SqlException ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(covidCasesDALRecords);
}
public List <NationalCasesDADto> GetCountOfCasesForAllNations(Metrics metrics)
{
List <NationalCasesDADto> covidCasesDALRecords = new List <NationalCasesDADto>();
SqlConnection connection = new SqlConnection(_configuration.GetConnectionString("MSSQL_MainDB"));
SqlCommand sqlCommand = new SqlCommand(@"select Country, sum(Count) as Count
from
(
select Country, [state], MAX(c.ConfirmedCasesCount) as Count
from ConfirmedCases c
inner join LocationsGlobal l on c.ID = l.ID
group by [state], l.Country
) numbers
group by Country", connection);
if (metrics == Metrics.DEATHS)
{
sqlCommand = new SqlCommand(@"select Country, sum(Count) as Count
from
(
select Country, [state], MAX(d.DeathCount) as Count
from DEATHS d
inner join LocationsGlobal l on d.ID = l.ID
group by [state], l.Country
) numbers
group by Country", connection);
}
else if (metrics == Metrics.RECOVERIES)
{
sqlCommand = new SqlCommand(@"select Country, sum(Count) as Count
from
(
select Country, [state], MAX(r.RecoveriesCount) as Count
from RECOVERIES r
inner join LocationsGlobal l on r.ID = l.ID
group by [state], l.Country
) numbers
group by Country", connection);
}
connection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
NationalCasesDADto CaseCountDto = new NationalCasesDADto
{
Country = (string)reader["Country"],
Count = (Int32)reader["Count"],
};
covidCasesDALRecords.Add(CaseCountDto);
}
}
catch (SqlException ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(covidCasesDALRecords);
}
