/// <summary>
/// Executes the paged query.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">The SQL.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="pagedata">the paging parameters</param>
/// <param name="timeout">The timeout.</param>
/// <returns></returns>
/// <exception cref="System.NotImplementedException"></exception>
public IPagedQueryResponse <T> ExecutePagedQuery <T>(string sql, object parameters, PagedQueryRequest pagedata, int?timeout = 120)
{
using (var connection = new MySqlConnection(_connectionString))
{
connection.Open();
var sqlparameters = new DynamicParameters(parameters);
sqlparameters.AddDynamicParams(pagedata);
int totalCount = 0;
var data = connection.Query <T, int, T>(sql, (a, b) => { totalCount = b; return(a); }, parameters, commandType: CommandType.StoredProcedure, splitOn: "TotalCount");
if (data.Any())
{
return(new PagedQueryResponse <T> {
ItemsPerPage = pagedata.ItemsPerPage, TotalItems = totalCount, PageResults = data
});
}
return(new PagedQueryResponse <T> {
ItemsPerPage = pagedata.ItemsPerPage, TotalItems = 0, PageResults = new List <T>()
});
}
}
public async Task <IPagedQueryResponse <T> > ExecutePagedQueryAsync <T>(string sql, object parameters, PagedQueryRequest pageData, int?timeout = null)
{
using (var connection = new SqlConnection(_connectionString))
{
// Did you know you have to call Open expicitly if you use a Dapper multi query
// cause I fucken didn't and spent 9 f*****g hours trying to get this to work
await connection.OpenAsync();
var sqlparameters = new DynamicParameters(parameters);
sqlparameters.AddDynamicParams(pageData);
int totalCount = 0;
// the dataset will trail with TotalCount so we can split on that usining a multiquery and set a varuable and only collect T
var data = await connection.QueryAsync <T, int, T>(sql, (a, b) => { totalCount = b; return(a); }, sqlparameters, commandType : CommandType.StoredProcedure, splitOn : "TotalCount");
if (data.Any())
{
return(new PagedQueryResponse <T> {
ItemsPerPage = pageData.ItemsPerPage, TotalItems = totalCount, PageResults = data
});
}
return(new PagedQueryResponse <T> {
ItemsPerPage = pageData.ItemsPerPage, TotalItems = 0, PageResults = new List <T>()
});
}
}