Batching oracle

 using System;

using System.Data;

using Oracle.ManagedDataAccess.Client;

class Program

{

    static void Main()

    {

        string connectionString = "User Id=your_user;Password=your_password;Data Source=your_datasource";

        

        using (OracleConnection conn = new OracleConnection(connectionString))

        {

            conn.Open();

            int batchSize = 500;

            int totalRecords = 1500; // Known total records count

            int offset = 0;

            while (offset < totalRecords)

            {

                DataTable dt = FetchBatchRecords(conn, batchSize, offset);

                

                // Bind DataTable to UI or process it

                Console.WriteLine($"Fetched {dt.Rows.Count} records starting from {offset + 1}");

                offset += batchSize; // Move to the next batch

            }

        }

    }

    static DataTable FetchBatchRecords(OracleConnection conn, int batchSize, int offset)

    {

        DataTable dt = new DataTable();

        

        string query = @"

            SELECT * FROM (

                SELECT t.*, ROWNUM rnum FROM (

                    SELECT * FROM your_table ORDER BY some_column -- Adjust ordering

                ) t WHERE ROWNUM <= :maxRow

            ) WHERE rnum > :minRow";

        using (OracleCommand cmd = new OracleCommand(query, conn))

        {

            cmd.Parameters.Add(new OracleParameter("maxRow", offset + batchSize));

            cmd.Parameters.Add(new OracleParameter("minRow", offset));

            using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))

            {

                adapter.Fill(dt); // Fill DataTable with batch results

            }

        }

        return dt;

    }

}

----

Sql

SELECT * FROM your_table ORDER BY some_column OFFSET :offset ROWS FETCH NEXT :batchSize ROWS ONLY

----

C#

using System;

using System.Data;

using Oracle.ManagedDataAccess.Client;

class Program

{

    static void Main()

    {

        string connectionString = "User Id=your_user;Password=your_password;Data Source=your_datasource";

        using (OracleConnection conn = new OracleConnection(connectionString))

        {

            conn.Open();

            int batchSize = 500;

            int offset = 0;

            bool hasMoreData = true;

            while (hasMoreData)

            {

                DataTable dt = FetchBatchRecords(conn, batchSize, offset);

                if (dt.Rows.Count == 0) // No more records to fetch

                    break;

                // Bind DataTable to UI or process it

                Console.WriteLine($"Fetched {dt.Rows.Count} records starting from {offset + 1}");

                offset += batchSize; // Move to the next batch

            }

        }

    }

    static DataTable FetchBatchRecords(OracleConnection conn, int batchSize, int offset)

    {

        DataTable dt = new DataTable();

        string query = @"

            SELECT * FROM (

                SELECT t.*, ROWNUM rnum FROM (

                    SELECT * FROM your_table ORDER BY some_column -- Adjust ordering

                ) t WHERE ROWNUM <= :maxRow

            ) WHERE rnum > :minRow";

        using (OracleCommand cmd = new OracleCommand(query, conn))

        {

            cmd.Parameters.Add(new OracleParameter("maxRow", offset + batchSize));

            cmd.Parameters.Add(new OracleParameter("minRow", offset));

            using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))

            {

                adapter.Fill(dt); // Fill DataTable with batch results

            }

        }

        return dt;

    }

}

--+

Query 

SELECT * FROM your_table ORDER BY some_column 

OFFSET :offset ROWS FETCH NEXT :batchSize ROWS ONLY