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); }