Split DataTable into multiple DataTables using C# and VB.Net in ASP.Net

Hi amar,

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

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespaces

C#

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

VB.Net

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

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = GetData();
    List<DataTable> dts = SplitDataTableTomultiple(dt, 10);
}

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;
            }
        }
    }
}

private static List<DataTable> SplitDataTableTomultiple(DataTable originalTable, int batchSize)
{
    List<DataTable> dts = new List<DataTable>();
    DataTable dt = new DataTable();
    dt = originalTable.Clone();
    int j = 0;
    int k = 1;
    if (originalTable.Rows.Count <= batchSize)
    {
        dt.TableName = "Table_" + k;
        dt = originalTable.Copy();
        dts.Add(dt.Copy());
    }
    else
    {
        for (int i = 0; i < originalTable.Rows.Count; i++)
        {
            dt.NewRow();
            dt.ImportRow(originalTable.Rows[i]);
            if ((i + 1) == originalTable.Rows.Count)
            {
                dt.TableName = "Table_" + k;
                dts.Add(dt.Copy());
                dt.Rows.Clear();
                k++;
            }
            else if (++j == batchSize)
            {
                dt.TableName = "Table_" + k;
                dts.Add(dt.Copy());
                dt.Rows.Clear();
                k++;
                j = 0;
            }
        }
    }

    return dts;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dt As DataTable = GetData()
    Dim dts As List(Of DataTable) = SplitDataTableToMultiple(dt, 10)
End Sub

Private Shared 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

Private Shared Function SplitDataTableToMultiple(originalTable As DataTable, batchSize As Integer) As List(Of DataTable)
    Dim dts As List(Of DataTable) = New List(Of DataTable)()
    Dim dt As DataTable = New DataTable()
    dt = originalTable.Clone()
    Dim j As Integer = 0
    Dim k As Integer = 1
    If originalTable.Rows.Count <= batchSize Then
        dt.TableName = "Table_" & k
        dt = originalTable.Copy()
        dts.Add(dt.Copy())
    Else
        For i As Integer = 0 To originalTable.Rows.Count - 1
            dt.NewRow()
            dt.ImportRow(originalTable.Rows(i))
            If (i + 1) = originalTable.Rows.Count Then
                dt.TableName = "Table_" & k
                dts.Add(dt.Copy())
                dt.Rows.Clear()
                k += 1
            ElseIf Threading.Interlocked.Increment(j) = batchSize Then
                dt.TableName = "Table_" & k
                dts.Add(dt.Copy())
                dt.Rows.Clear()
                k += 1
                j = 0
            End If
        Next
    End If

    Return dts
End Function

Screenshot