Programmingempire

The following code example describes Comparing Rows of Two Tables with ADO.NET. Basically, in this example, I will explain how to use DataRowComparer for comparing the rows.

Creating the Database

The following two tables will be their in the database. While the table Candidates represents all the candidates who want to apply for the membership. Further, the Members table represents the candidates who become members. Therefore, if want to compare these two tables, we can use DataRowComparer. Also, here we use the SQL Server database to create these tables.

The Candidates Table

The Members Table

Comparing the Table Data

In order to work with these tables, first we need to create the datasets. Therefore, in this example, we create two datasets, each containing the data from the corresponding table. The following code shows how to create DataSet objects.

public void CreateDataSets()
        {
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\KAVITA\Documents\d1.mdf;Integrated Security=True;Connect Timeout=30");
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from Candidates", con);
            ds1 = new DataSet();
            da.Fill(ds1, "Candidates");

            da = new SqlDataAdapter("select * from Members", con);
            ds2 = new DataSet();
            da.Fill(ds2, "Members");
            Console.WriteLine("Rows in Candidates Table...");
            foreach(DataRow dr in ds1.Tables["Candidates"].Rows)
            {
                Console.WriteLine(dr[0].ToString() + "    " + dr[1].ToString());
            }
            Console.WriteLine("\n\nRows in Members Table...");
            foreach (DataRow dr in ds2.Tables["Members"].Rows)
            {
                Console.WriteLine(dr[0].ToString() + "    " + dr[1].ToString());
            }
        }

After that we need to create another method that compares the data. The following code shows how to compare the data. In order to select data from each dataset, first we need to formulate the LINQ queries. After that, we need to fetch the data from the queries and copy in the DataTable objects.

Once, we have data from each of these two queries we can perform other operations. For instance, suppose we want to find which candidates are not members, we need to find the rows which are there in the first table and not in the second table. For this purpose, we can use the Except operation.

public void CompareData(DataSet ds1, DataSet ds2)
        {
            //Formulate the queries for the two datasets to select all data rows from each

            IEnumerable<DataRow> q1 = from d1 in ds1.Tables["Candidates"].AsEnumerable()
                                      select d1;
            IEnumerable<DataRow> q2 = from d2 in ds2.Tables["Members"].AsEnumerable()
                                      select d2;

            //Retrieve the data from the queries in data tables
            DataTable dt1, dt2;
            dt1 = q1.CopyToDataTable();
            dt2 = q2.CopyToDataTable();

            //Using Except
            var diff = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
            Console.WriteLine("The rows contained in the Candidates table which are missing from the Members table are given below....");
            foreach(DataRow dr in diff)
            {
                Console.WriteLine(dr[0].ToString() + "  " + dr[1].ToString());
            }
        }

In order to find the candidates who are members also, we can use the Interset operation, as shown below.

//Using Intersect
            var inter = dt1.AsEnumerable().Intersect(dt2.AsEnumerable(), DataRowComparer.Default);
            Console.WriteLine("The rows contained in both of these tables are given below....");
            foreach (DataRow dr in inter)
            {
                Console.WriteLine(dr[0].ToString() + "  " + dr[1].ToString());
            }

Similarly, we can also find Union. The following code shows the Union operation.

//Using Union
            var union = dt1.AsEnumerable().Union(dt2.AsEnumerable(), DataRowComparer.Default);
            Console.WriteLine("Union of both of these tables are given below....");
            foreach (DataRow dr in union)
            {
                Console.WriteLine(dr[0].ToString() + "  " + dr[1].ToString());
            }

The complete code is given below.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Globalization;

namespace DataRowComparerExample1
{
    class Program
    {
        DataSet ds1, ds2;
        
        static void Main(string[] args)
        {
            Program ob = new Program();
            ob.CreateDataSets();
            ob.CompareData(ob.ds1, ob.ds2);
        }
        public void CompareData(DataSet ds1, DataSet ds2)
        {
            //Formulate the queries for the two datasets to select all data rows from each

            IEnumerable<DataRow> q1 = from d1 in ds1.Tables["Candidates"].AsEnumerable()
                                      select d1;
            IEnumerable<DataRow> q2 = from d2 in ds2.Tables["Members"].AsEnumerable()
                                      select d2;

            //Retrieve the data from the queries in data tables
            DataTable dt1, dt2;
            dt1 = q1.CopyToDataTable();
            dt2 = q2.CopyToDataTable();

            //Using Except
            var diff = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
            Console.WriteLine("The rows contained in the Candidates table which are missing from the Members table are given below....");
            foreach(DataRow dr in diff)
            {
                Console.WriteLine(dr[0].ToString() + "  " + dr[1].ToString());
            }

            //Using Intersect
            var inter = dt1.AsEnumerable().Intersect(dt2.AsEnumerable(), DataRowComparer.Default);
            Console.WriteLine("The rows contained in both of these tables are given below....");
            foreach (DataRow dr in inter)
            {
                Console.WriteLine(dr[0].ToString() + "  " + dr[1].ToString());
            }

            //Using Union
            var union = dt1.AsEnumerable().Union(dt2.AsEnumerable(), DataRowComparer.Default);
            Console.WriteLine("Union of both of these tables are given below....");
            foreach (DataRow dr in union)
            {
                Console.WriteLine(dr[0].ToString() + "  " + dr[1].ToString());
            }
        }
        public void CreateDataSets()
        {
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\KAVITA\Documents\d1.mdf;Integrated Security=True;Connect Timeout=30");
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from Candidates", con);
            ds1 = new DataSet();
            da.Fill(ds1, "Candidates");

            da = new SqlDataAdapter("select * from Members", con);
            ds2 = new DataSet();
            da.Fill(ds2, "Members");
            Console.WriteLine("Rows in Candidates Table...");
            foreach(DataRow dr in ds1.Tables["Candidates"].Rows)
            {
                Console.WriteLine(dr[0].ToString() + "    " + dr[1].ToString());
            }
            Console.WriteLine("\n\nRows in Members Table...");
            foreach (DataRow dr in ds2.Tables["Members"].Rows)
            {
                Console.WriteLine(dr[0].ToString() + "    " + dr[1].ToString());
            }
        }
    }
}

Output

Further Reading

Selection Sort in C#

Insertion Sort in C#

Bubble Sort in C#

How to Create Instance Variables and Class Variables in Python

Comparing Rows of Two Tables with ADO.NET

Example of Label and Textbox Control in ASP.NET

One Dimensional and Two Dimensuonal Indexers in C#

Private and Static Constructors in C#

Methods of Array Class

Anonymous Functions in C#

Programs to Find Armstrong Numbers in C#

Matrix Multiplication in C#

One Dimensional and Two Dimensional Indexers in C#

Static Class Example in C#

Rotating an Array in C#

Generic IList Interface and its Implementation in C#

Recursive Binary search in C#

C# Practice Questions

Creating Navigation Window Application Using WPF in C#

Find Intersection Using Arrays

An array of Objects and Object Initializer

Performing Set Operations in LINQ

Using Quantifiers in LINQ

Data Binding Using BulletedList Control

Grouping Queries in LINQ

Generic Binary Search in C#

Understanding the Quantifiers in LINQ

Join Operation using LINQ

Deferred Query Execution and Immediate Query Execution in LINQ

Examples of Query Operations using LINQ in C#

An array of Objects and Object Initializer

Language-Integrated Query (LINQ) in C#

How Data Binding Works in WPF

Examples of Connected and Disconnected Approach in ADO.NET

New Features in C# 9

IEnumerable and IEnumerator Interfaces

KeyValuePair and its Applications

C# Root Class – Object

Access Modifiers in C#

Learning Properties in C#

Learning All Class Members in C#

Examples of Extension Methods in C#

How to Setup a Connection with SQL Server Database in Visual Studio

Understanding the Concept of Nested Classes in C#

LINQ To SQL Examples

A Beginner’s Tutorial on WPF in C#

Explaining C# Records with Examples

Everything about Tuples in C# and When to Use?

Creating Jagged Arrays in C#

Linear Search and Binary search in C#

Learning Indexers in C#

Object Initializers in C#

Examples of Static Constructors in C#

When should We Use Private Constructors?

C# Basic Examples

IEqualityComparer Interface

• Angular
• ASP.NET
• C
• C#
• C++
• CSS
• Dot Net Framework
• HTML
• IoT
• Java
• JavaScript
• Kotlin
• PHP
• Power Bi
• Python
• Scratch 3.0
• TypeScript
• VB.NET