Implement skip and take in DataTable using C# and VB.Net in Console Application

on Jun 29, 2021 06:39 AM


I am implementing currently split database function using below link of approach.

[Solved] Bulk upload large data into oracle database Error: invalid buffer length for ttc field

Based on the size original DataTable it may create multiple DataTables like 20 to 50 DataTables.

If the size of original DataTables varies from 2 to 5 million.

Will it create any memory exceptions?

Is there any way we can use single original DataTable to use skip and take functions?

Like first we will insert 1 lakh records then we will skip it and insert next one lakh records.

Please suggest me. 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
on Jun 30, 2021 12:30 AM

Hi vijay9471,

Check this example. Now please take its reference and correct your code.



using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;


Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq



static void Main(string[] args)
    int batchSize = 100000;
    DataTable dt = GetData();
    int total = (dt.Rows.Count / batchSize) + ((dt.Rows.Count % batchSize) > 0 ? 1 : 0);
    for (int i = 1; i <= total; i++)
        int startIndex = (i - 1) * batchSize;
        DataTable resultDatatable = dt.Rows.Cast<DataRow>().Skip(startIndex).Take(batchSize).CopyToDataTable();
        // Insert in database.

// Get data from database.
private static DataTable GetData()
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT * FROM Customers";
    using (SqlConnection con = new SqlConnection(conString))
        SqlCommand cmd = new SqlCommand(query);
        using (SqlDataAdapter sda = new SqlDataAdapter())
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())

                return dt;


Sub Main()
    Dim batchSize As Integer = 100000
    Dim dt As DataTable = GetData()
    Dim total As Integer = (dt.Rows.Count / batchSize) + (If((dt.Rows.Count Mod batchSize) > 0, 1, 0))
    For i As Integer = 1 To total Step 1
        Dim startIndex As Integer = (i - 1) * batchSize
        Dim resultDatatable As DataTable = dt.Rows.Cast(Of DataRow)().Skip(startIndex).Take(batchSize).CopyToDataTable()
        ' Insert in database.
End Sub

' Get data from database.
Private Function GetData() As DataTable
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT * FROM Customers"
    Using con As SqlConnection = New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand(query)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using dt As DataTable = New DataTable()
                Return dt
            End Using
        End Using
    End Using
End Function