The problem is that I’m getting a timeout error while querying the database in a for loop to fetch paginated set of records. Each loop is not expensive, but querying for more than 30 seconds using the same connection object gives the transaction/timeout error :
I’ve tried adding commandTimeout property for Dapper query, and Connection Timeout in app.config (both being above 30 seconds), but it doesn’t affect anything.
int pages = totalCount / pageSize;
for(int pageNumber = 1; pageNumber <= pages; pageNumber++){
string sql = @"select * from MyTable order by 1 desc offset @pageSize * (@pageNumber-1) rows fetch next @pageSize rows only;";
_connection.Query<MyEntity>(sql, param: new {pageSize, pageNumber}).ToList();
}