Compare all Columns between two DataTable using C# and VB.Net in ASP.Net

Hi fredo1094,

Refer below sample.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<br />
<asp:Button Text="Insert" runat="server" OnClick="Insert" />
<br />
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

Namespaces

C#

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

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

public static DataTable dtDatos = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        dtDatos.Columns.AddRange(new DataColumn[3] { new DataColumn("CustomerId", typeof(int)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("Country",typeof(string)) });
        dtDatos.Rows.Add(1, "John Hammond", "United States");
        dtDatos.Rows.Add(2, "Mudassar Khan", "India");
        dtDatos.Rows.Add(3, "Suzanne Mathews", "France");
        dtDatos.Rows.Add(4, "Robert Schidner", "Russia");
        GridView1.DataSource = dtDatos;
        GridView1.DataBind();
        loadFileDirectly();
    }
}

protected void Insert(object sender, EventArgs e)
{
    // New Record for insert.
    dtDatos.Rows.Clear();
    dtDatos.Rows.Add(6, "Robert Schidner", "Russia 1");
    validateDuplicates();
    loadFileDirectly();
}

public void loadFileDirectly()
{
    try
    {
        DataTable dataTable = validateDuplicates();
        IEnumerable<DataRow> drs = dtDatos.AsEnumerable().Except(dataTable.AsEnumerable(), DataRowComparer.Default);
        if (drs.Count() > 0)
        {
            DataTable matchingRows = drs.CopyToDataTable();
            string connStr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            SqlConnection connection = new SqlConnection(connStr);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
            connection.Open();
            string nameTable = "CustomerTest";
            bulkCopy.DestinationTableName = nameTable;
            bulkCopy.WriteToServer(matchingRows);
            GridView2.DataSource = validateDuplicates();
            GridView2.DataBind();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

public DataTable validateDuplicates()
{
    DataTable dataTable = new DataTable();
    try
    {
        string connStr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(connStr))
        {
            string SQLquery = "SELECT * FROM CustomerTest";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(SQLquery, con);
            dataAdapter.Fill(dataTable);
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    return dataTable;
}

VB.Net

Public Shared dtDatos As DataTable = New DataTable()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        dtDatos.Columns.AddRange(New DataColumn(2) {New DataColumn("CustomerId", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
        dtDatos.Rows.Add(1, "John Hammond", "United States")
        dtDatos.Rows.Add(2, "Mudassar Khan", "India")
        dtDatos.Rows.Add(3, "Suzanne Mathews", "France")
        dtDatos.Rows.Add(4, "Robert Schidner", "Russia")
        GridView1.DataSource = dtDatos
        GridView1.DataBind()
        loadFileDirectly()
    End If
End Sub

Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
    dtDatos.Rows.Clear()
    dtDatos.Rows.Add(6, "Robert Schidner", "Russia 1")
    validateDuplicates()
    loadFileDirectly()
End Sub

Public Sub loadFileDirectly()
    Try
        Dim dataTable As DataTable = validateDuplicates()
        Dim drs As IEnumerable(Of DataRow) = dtDatos.AsEnumerable().Except(dataTable.AsEnumerable(), DataRowComparer.[Default])

        If drs.Count() > 0 Then
            Dim matchingRows As DataTable = drs.CopyToDataTable()
            Dim connStr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Dim connection As SqlConnection = New SqlConnection(connStr)
            Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(connection)
            connection.Open()
            Dim nameTable As String = "CustomerTest"
            bulkCopy.DestinationTableName = nameTable
            bulkCopy.WriteToServer(matchingRows)
            GridView2.DataSource = validateDuplicates()
            GridView2.DataBind()
        End If

    Catch ex As Exception
        Throw ex
    End Try
End Sub

Public Function validateDuplicates() As DataTable
    Dim dataTable As DataTable = New DataTable()

    Try
        Dim connStr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(connStr)
            Dim SQLquery As String = "SELECT * FROM CustomerTest"
            Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(SQLquery, con)
            dataAdapter.Fill(dataTable)
        End Using

    Catch ex As Exception
        Throw ex
    End Try

    Return dataTable
End Function

Screenshot