Pagination in Oracle (edit)

• row_number()
• rank()
• dense_rank()
• count(*)

Oracle with DbDataReader

Retrieving Data Using a DataReader - ADO.NET | Microsoft Docs

c# - What is the fastest way to read data from a DbDataReader? - Stack Overflow

.net - Timeout for OracleDataReader.Read Method - Stack Overflow

c# - Changing the where clause to use Oracle Command Parameter - Stack Overflow

Example

  1  select ename,sal,
  2   row_number()
  3     over (order by sal desc)rn,
  4   rank()
  5     over (order by sal desc)rnk,
  6   dense_rank()
  7     over (order by sal desc)drnk
  8   from emp
  9  order by sal desc

Oracle sql pagination with total pages or total entries - Stack Overflow (HAY HAY HAY)

Best Practice (HAY HAY HAY)

SELECT res.*,
 CEIL(total_num_rows/10) total_num_pages
FROM (SELECT o.*,
 row_number() OVER (ORDER BY CREATION_TS DESC, ID DESC) rn,
 COUNT(*) OVER () total_num_rows
 FROM ORCL.TODOITEM o
 WHERE DESCRIPTION LIKE '%ask%') res
WHERE rn BETWEEN (1 - 1) * 10 + 1 AND 1 * 10;

SELECT res.*,
 CEIL(total_num_rows/pageSize) total_num_pages
FROM (SELECT o.*,
 row_number() OVER (ORDER BY CREATION_TS DESC, ID DESC) rn,
 COUNT(*) OVER () total_num_rows
 FROM ORCL.TODOITEM o
 WHERE DESCRIPTION LIKE '%ask%') res
WHERE rn BETWEEN (pageNumber - 1) * pageSize + 1 AND pageNumber * pageSize;

Pagination And Total Number Of Rows From One SELECT – Andrey Zavadskiy – SQL and .NET developer, trainer, speaker

SELECT res.*,
       CEIL(total_num_rows/pagesize) total_num_pages
FROM   (SELECT o.*,
               row_number() OVER (ORDER BY orderdate DESC, shippingdate DESC) rn,
               COUNT(*) OVER () total_num_rows
        FROM   orders o
        WHERE  customerid LIKE 'A%') res
WHERE  rn BETWEEN (pagenumber - 1) * pagesize + 1 AND pagenumber * pagesize;

Sql - (Oracle) How get total number of results when using a pagination query? - Stack Overflow

How To Do Paging With ASP.NET Web API (c-sharpcorner.com)

Simple Solution

SELECT ID, DESCRIPTION, CREATION_TS, DONE, 
 (SELECT COUNT(*) FROM (SELECT ID, DESCRIPTION, CREATION_TS, DONE
 FROM ORCL.TODOITEM
 ORDER BY CREATION_TS DESC)) TOTAL
 FROM (SELECT A.ID, A.DESCRIPTION, A.CREATION_TS, A.DONE, rownum rn
 FROM (SELECT ID, DESCRIPTION, CREATION_TS, DONE
 FROM ORCL.TODOITEM
 ORDER BY CREATION_TS DESC) A
 WHERE rownum <= 20)
 WHERE rn >= 11;

Oracle Data Provider for .NET

Oracle Data Provider for .NET (ODP.NET) features optimized ADO.NET data access to the Oracle database. ODP.NET allows developers to take advantage of advanced Oracle database functionality, including Real Application Clusters, self-tuning statement cache, Application Continuity, and Fast Connection Failover. Developers can also use popular .NET features, such as Entity Framework Core.

There are three driver types: ODP.NET Core; ODP.NET, Managed Driver; and ODP.NET, Unmanaged Driver. ODP.NET Core is designed for multi-platform .NET (Core) applications. ODP.NET, Managed Driver is 100% managed code .NET Framework provider. Developers deploy a single assembly in a deployment package smaller than 10 MB. ODP.NET, Unmanaged Driver is the traditional Oracle ADO.NET provider that uses the Oracle Database Client.

ODP.NET 21c add support for user-defined types, .NET 6, Entity Framework Core 6, binary JSON data type, Client Initiated Continuous Query Notification, and more.

Oracle Data Provider for .NET (ODP.NET)

Microsoft PowerPoint - Oracle Perf .NET BP.pptx

Best practice for pagination in Oracle? - Stack Overflow

C# - What are the best practices working with Oracle.DataAccess.Client? - Stack Overflow

C# - OracleCommand SQL Parameters Binding - Stack Overflow

C# OracleCommand SQL Call Procedure with Params - Stack Overflow

Using StringBuilder To Build Query

SELECT ID, DESCRIPTION, CREATION_TS, DONE
FROM ORCL.TODOITEM;

SELECT ID, DESCRIPTION, CREATION_TS, DONE
FROM (SELECT A.ID, A.DESCRIPTION, A.CREATION_TS, A.DONE, rownum rn
FROM (SELECT ID, DESCRIPTION, CREATION_TS, DONE
FROM ORCL.TODOITEM
ORDER BY CREATION_TS DESC) A
WHERE rownum <= 10)
WHERE rn >= 1;

 SELECT * 
 FROM (SELECT A.*, rownum rn
 FROM (SELECT *
 FROM your_table
 ORDER BY your_column) A
 WHERE rownum <= :Y)
 WHERE rn >= :X