If I have an SQL statement like,
SELECT Count(id) as TotalRows, transaction_date
FROM Accounts
Group By transaction_date
What do I use as a return type an display it in my View using Dapper Dot Net? I tried using ViewData["mydata"]
and I tried using List<dynamic>
, but I could not iterate because the return type is IActionResult
and as such does not have an Enumerator. I can't cast it (I don't know how to) to a specific type with IEnumerable
because there is not custom type like (Accounts, Person, etc.). None of these have a property for enumerating.
I would like to not use Linq unless it is necessary.
Create a POCO to represent your result set item
public class GroupedData
{
public int TotalRows { set;get;}
public DateTime TransactionDate { set;get;}
}
Using Dapper, execute the query and load result to a list of this class and pass that to your view
public IActionResult Index()
{
var groupedDataList = GetData();
return View(groupedDataList);
}
private List<GroupedData> GetData()
{
var q=@"SELECT Count(id) as TotalRows,
transaction_date as TransactionDate
FROM Accounts
Group By transaction_date";
return yourSqlConnection.Query<GroupedData>(q).ToList();
}
Now make sure your view is strongly typed the same type you are passing from your action method (List of GroupedData
). You can enumerate this list.
@model List<GroupedData>
@foreach(var item in Model)
{
<p>@item.TransactionDate</p>
<p>@item.TotalRows</p>
}