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

Hi vijay9471,

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

Namespaces

C#

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

VB.Net

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

Code

C#

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())
            {
                sda.Fill(dt);

                return dt;
            }
        }
    }
}

VB.Net

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.
    Next
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()
                sda.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function