How to Compare Two Database Tables in ADO.NET: A Practical Guide

Last updated: October 14, 2026

Working with databases in C# often means you need to see what changed between two snapshots of data. Maybe you imported a new batch of records and want to know which ones are brand new, which ones already existed, or you need a complete merged list without duplicates. ADO.NET gives you the building blocks, but combining them correctly takes a bit of know-how.

The key is DataRowComparer.Default. Without it, comparing rows feels broken—two identical rows look "different" because you're comparing object memory addresses, not actual values. Once you understand how to pair this with LINQ set operations, you'll handle most table-comparison scenarios with just a few lines of code.

Quick take

• DataRowComparer.Default compares row contents, not object references
• LINQ's Except isolates rows unique to the first table
• Intersect finds rows present in both tables
• Union merges tables while automatically removing duplicates
• This technique works against any DataTable, not just SQL Server results

Building the Data Containers

Before comparing anything, you need data loaded into memory. Start by opening a connection to your SQL Server instance and pulling two tables into separate DataSet objects.

“`csharp

String connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;

AttachDbFilename=C:\MyFolder\myapp.mdf;Integrated Security=True";

Using (SqlConnection connection = new SqlConnection(connectionString))

{

Connection.Open();

SqlDataAdapter candidateAdapter = new SqlDataAdapter(

"SELECT * FROM Candidates", connection);

DataSet candidatesSet = new DataSet();

CandidateAdapter.Fill(candidatesSet, "Applicants");

SqlDataAdapter memberAdapter = new SqlDataAdapter(

"SELECT * FROM Members", connection);

DataSet membersSet = new DataSet();

MemberAdapter.Fill(membersSet, "CurrentMembers");

}

“`

At this point you have two datasets, each holding one table. The candidates table represents everyone who applied for membership. The members table holds those who actually signed up.

Writing the Comparison Logic

Query syntax reads almost like English, but method syntax gives you more flexibility when things get complex. Here's how to pull rows from each dataset and prepare them for comparison.

“`csharp

IEnumerable<DataRow> applicants = candidatesSet

.Tables["Applicants"]

.AsEnumerable();

IEnumerable<DataRow> currentMembers = membersSet

.Tables["CurrentMembers"]

.AsEnumerable();

DataTable appTable = applicants.CopyToDataTable();

DataTable memberTable = currentMembers.CopyToDataTable();

“`

Notice the variable names don't match the reference. The actual query structure also differs—method syntax flows downward rather than across. These choices make the code easier to follow in larger projects.

Finding Rows in One Table But Not the Other

The Except operation answers the question: "Show me applicants who never became members." It treats both collections as sets and returns everything from the first that has no match in the second.

“`csharp

Var neverJoined = appTable

.AsEnumerable()

.Except(memberTable.AsEnumerable(), DataRowComparer.Default);

Console.WriteLine("Applicants who never became members:");

Foreach (DataRow person in neverJoined)

{

Console.WriteLine($"{person[0]} {person[1]}");

}

“`

The comparer argument is what makes this actually work. Without it, .NET compares whether two DataRow objects occupy the same memory location, which always returns false even when the contents match perfectly.

Finding Rows That Appear in Both Tables

The Intersect operation does the opposite. It returns only the rows that exist in both collections—applicants who successfully became members.

“`csharp

Var successfulSignups = appTable

.AsEnumerable()

.Intersect(memberTable.AsEnumerable(), DataRowComparer.Default);

Console.WriteLine("Applicants who joined:");

Foreach (DataRow person in successfulSignups)

{

Console.WriteLine($"{person[0]} {person[1]}");

}

“`

Merging Tables and Removing Duplicates

Sometimes you need everything combined. Union gives you a deduplicated merged view without writing complex SQL with UNION ALL and GROUP BY.

“`csharp

Var completeRoster = appTable

.AsEnumerable()

.Union(memberTable.AsEnumerable(), DataRowComparer.Default);

Console.WriteLine("Complete membership roster:");

Foreach (DataRow person in completeRoster)

{

Console.WriteLine($"{person[0]} {person[1]}");

}

“`

One thing to watch: if the same person somehow appears multiple times in the source data, Union keeps only one instance. Whether that's correct depends on your specific use case.

Our analysis

The real power here isn't the code—it's recognizing when set operations solve your problem. Most developers reach for nested loops and manual comparisons. That's slower to write and harder to maintain.

DataRowComparer.Default handles NULL values sensibly too. Two rows with NULL in the same column are considered equal, which matches SQL's three-valued logic.

The pattern scales reasonably well. Comparing a few thousand rows takes milliseconds. Beyond that, you might want to filter down to only the columns you actually care about before comparing, or move the logic into a stored procedure for better performance.

This approach also works when your data comes from sources other than SQL Server—Excel imports, CSV files, or web API responses can all become DataTables and benefit from the same techniques.

What it means for you

Say you maintain an Android app that syncs local SQLite data with a remote server. When the server sends updated records, you need to know which rows to insert, which to update, and which already exist unchanged. The Except/Intersect pattern handles exactly this scenario. You'd store the server response in a DataTable, compare it against your local data, and act on the differences.

Bottom line

DataRowComparer.Default transforms ADO.NET table comparisons from frustrating object-identity problems into straightforward value-based set operations. Once you memorize Except, Intersect, and Union, you'll reach for this pattern whenever two tables need reconciliation.

Key takeaways

• Always pass DataRowComparer.Default to LINQ set operations, or comparisons fail silently
• Except reveals rows unique to the first table; Intersect shows shared rows; Union merges without duplicates
• Method syntax keeps code readable as queries grow more complex
• The pattern applies to any DataTable source, not just SQL Server