public static USCountyCaseCountBLDto ToUSCountyCasesBLDto(this USCountyCaseCountDADto rec)
{
return(new USCountyCaseCountBLDto
{
County = rec.County,
State = rec.State,
Date = rec.Date,
Count = rec.Count
});
}
public List <USCountyCaseCountDADto> GetCountOfUSCasesByCounty(Metrics metrics, Locations location, string State, string County, DateTime?Date)
{
List <USCountyCaseCountDADto> covidUSCountyCasesDALRecords = new List <USCountyCaseCountDADto>();
SqlConnection connection = new SqlConnection(_configuration.GetConnectionString("MSSQL_MainDB"));
SqlParameter St = new SqlParameter("St", System.Data.SqlDbType.NVarChar);
St.Value = State;
SqlParameter Cty = new SqlParameter("Cty", System.Data.SqlDbType.NVarChar);
Cty.Value = County;
SqlCommand sqlCommand = new SqlCommand(@"select [State], [County], MAX(CUS.ConfirmedCasesCount) AS 'Count' from Locations_US l INNER JOIN ConfirmedCases_US CUS ON CUS.Combined_Key = l.Combined_Key where l.[State] = @St and l.County = @Cty group by l.[State], l.County", connection);
if (Date == DateTime.MinValue)
{
Date = GetLastUpdateDate(metrics, location).Date;
}
if (Date != DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select [State], [County], MAX(CUS.ConfirmedCasesCount) AS Count
from Locations_US l INNER JOIN ConfirmedCases_US CUS ON CUS.Combined_Key = l.Combined_Key
where l.[State] = @St and l.County = @Cty and CUS.[Date] = @dte
group by l.[State], l.County" , connection);
}
if (metrics == Metrics.DEATHS)
{
if (Date == DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select [State], [County], Max(d.DeathCount) AS Count
from Locations_US l INNER JOIN DEATHS_US d ON d.Combined_Key = l.Combined_Key
WHERE l.[State] = @St and l.County = @Cty
group by l.[State], l.County", connection);
}
else
{
sqlCommand = new SqlCommand(@"select [State], [County], Max(d.DeathCount) AS Count
from Locations_US l INNER JOIN DEATHS_US d ON d.Combined_Key = l.Combined_Key
WHERE l.[State] = @St and l.County = @Cty and d.Date = @dte
group by l.[State], l.County", connection);
}
}
sqlCommand.Parameters.Add(St);
sqlCommand.Parameters.Add(Cty);
if (Date != DateTime.MinValue)
{
SqlParameter dte = new SqlParameter("dte", System.Data.SqlDbType.DateTime);
dte.Value = (DateTime)Date;
sqlCommand.Parameters.Add(dte);
}
connection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
USCountyCaseCountDADto CaseCountDto = new USCountyCaseCountDADto
{
State = (string)reader["State"],
County = (string)reader["County"],
Date = (DateTime)Date,
Count = (Int32)reader["Count"],
};
covidUSCountyCasesDALRecords.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(covidUSCountyCasesDALRecords);
}
