Date:

Share:

Comparing Rows of Two Tables with ADO.NET

Related Articles

The following code example describes comparing rows of two tables with ADO.NET. Basically, in this example, I will explain how to use DataRowComparer to compare lines.

Creating the database

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

Candidate table

Candidate table

Members table

Members table
Members table

Comparison of table data

In order to work with these tables, we must first create the data sets. Therefore, in this example, we create two sets of data, each of which contains 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:UsersKAVITADocumentsd1.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("nnRows in Members Table...");
            foreach (DataRow dr in ds2.Tables["Members"].Rows)
            
                Console.WriteLine(dr[0].ToString() + "    " + dr[1].ToString());
            
        

We then need to create another method that compares the data. The following code shows how to compare data. In order to select data from any data set, we must first formulate the LINQ queries. Next, we need to fetch the data from the queries and copy the DataTable objects.

Once we have data from either of these two queries we can perform other operations. For example, suppose we want to find which candidates are not members, we need to find the rows that 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 also find the member candidates, 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 the 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 full 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:UsersKAVITADocumentsd1.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("nnRows in Members Table...");
            foreach (DataRow dr in ds2.Tables["Members"].Rows)
            
                Console.WriteLine(dr[0].ToString() + "    " + dr[1].ToString());
            
        
    


Productivity

The output of a comparison between rows of two tables
The output of a comparison between rows of two tables
Meeting between the two tables
Meeting between the two tables
Merge rows of two tables
Merge rows of two tables

Source

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Popular Articles