public List <CovidChart> GetCovidChartList()
{
List <CovidChart> covidCharts = new List <CovidChart>();
using (var command = _context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = "select tarih,[1],[2],[3],[4],[5] FROM(select[City],[Count], Cast([CovidDate] as date) as tarih FROM Covids) as covidT PIVOT (SUM(Count) For City IN([1],[2],[3],[4],[5]) ) as ptable order by tarih asc";
command.CommandType = System.Data.CommandType.Text;
_context.Database.OpenConnection();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
CovidChart cc = new CovidChart();
cc.CovidDate = reader.GetDateTime(0).ToShortDateString();
Enumerable.Range(1, 5).ToList().ForEach(x =>
{
if (System.DBNull.Value.Equals(reader[x]))
{
cc.Counts.Add(0);
}
else
{
cc.Counts.Add(reader.GetInt32(x));
}
});
covidCharts.Add(cc);
}
}
_context.Database.CloseConnection();
return(covidCharts);
}
}
public List <CovidChart> GetCovidCharts()
{
List <CovidChart> covidCharts = new List <CovidChart>();
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = "SELECT TARIH, [16],[22],[41],[34],[65] FROM (SELECT City,[Count], CAST(CovidDate AS DATE) AS TARIH FROM[CovidChart].[dbo].[Covids]) AS QUERY PIVOT (SUM([COUNT]) FOR City IN([16],[22],[41],[34],[65])) AS PTABLE ORDER BY TARIH ";
command.CommandType = System.Data.CommandType.Text;
context.Database.OpenConnection();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
CovidChart covidChart = new CovidChart();
covidChart.CovidDate = reader.GetDateTime(0).ToString();
Enumerable.Range(1, 5).ToList().ForEach(x =>
{
if (System.DBNull.Value.Equals(reader.GetInt32(x)))
{
covidChart.Counts.Add(0);
}
else
{
covidChart.Counts.Add(reader.GetInt32(x));
}
});
covidCharts.Add(covidChart);
}
}
context.Database.CloseConnection();
return(covidCharts);
}
}
public List <CovidChart> GetCovidChartList()
{
List <CovidChart> charts = new List <CovidChart>();
using (var command = _context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = "SELECT tarih,[1],[2],[3],[4],[5]\r\nFROM \r\n (SELECT [City], [Count], Cast([CovidDate] as date) as tarih from Covids) as covidT \r\nPIVOT \r\n( \r\n Sum(Count) for City in([1],[2],[3],[4],[5])\r\n) as PTable order by tarih asc";
command.CommandType = CommandType.Text;
_context.Database.OpenConnection();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
CovidChart covidChart = new CovidChart
{
CovidDate = reader.GetDateTime(0).ToShortDateString()
};
Enumerable.Range(1, 5).ToList().ForEach(i =>
{
if (System.DBNull.Value.Equals(reader[i]))
{
covidChart.Counts.Add(0);
}
else
{
covidChart.Counts.Add(reader.GetInt32(i));
}
});
charts.Add(covidChart);
}
}
_context.Database.CloseConnection();
}
return(charts);
}