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