public static USStateCaseCountBLDto ToUSStateCasesBLDto(this USStateCaseCountDADto rec)
{
return(new USStateCaseCountBLDto
{
State = rec.State,
Date = rec.Date,
Count = rec.Count
});
}
public List <USStateCaseCountDADto> GetCountOfUSCasesByState(Metrics metrics, Locations locations, string State, DateTime?Date)
{
List <USStateCaseCountDADto> covidUSStateCasesDALRecords = new List <USStateCaseCountDADto>();
SqlConnection connection = new SqlConnection(_configuration.GetConnectionString("MSSQL_MainDB"));
SqlParameter St = new SqlParameter("St", System.Data.SqlDbType.NVarChar);
St.Value = State;
SqlCommand sqlCommand = new SqlCommand(@"select [State], sum(Count) as Count from (select [state], county, MAX(c.ConfirmedCasesCount) as Count from ConfirmedCases_US c where [state] = @St group by [state], l.County) numbers group by [state]", connection);
if (Date == DateTime.MinValue)
{
Date = GetLastUpdateDate(metrics, locations).Date;
}
if (Date != DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select [State], sum(Count) as Count
from
(
select [state], county, MAX(c.ConfirmedCasesCount) as Count
from ConfirmedCases_US c
inner join Locations_US l on c.Combined_Key = l.Combined_Key
where [state] = @St and [Date] = @dte
group by [state], l.County
) numbers
group by [state]", connection);
}
if (metrics == Metrics.DEATHS)
{
if (Date == DateTime.MinValue)
{
sqlCommand = new SqlCommand(@"select [State], sum(Count) as Count
from
(
select [state], county, MAX(d.DeathCount) as Count
from DEATHS_US d
inner join Locations_US l on d.Combined_Key = l.Combined_Key
where [state] = @St
group by [state], l.County
) numbers
group by [state]", connection);
}
else
{
sqlCommand = new SqlCommand(@"select [State], sum(Count) as Count
from
(
select [state], county, MAX(d.DeathCount) as Count
from DEATHS_US d
inner join Locations_US l on d.Combined_Key = l.Combined_Key
where [state] = @St and [Date] = @dte
group by [state], l.County
) numbers
group by [state]", connection);
}
}
sqlCommand.Parameters.Add(St);
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())
{
USStateCaseCountDADto CaseCountDto = new USStateCaseCountDADto();
CaseCountDto.State = (string)reader["State"];
CaseCountDto.Count = (Int32)reader["Count"];
if (Date != DateTime.MinValue)
{
CaseCountDto.Date = (DateTime)Date;
}
covidUSStateCasesDALRecords.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(covidUSStateCasesDALRecords);
}
