Compare tables in two DB using C#

Compare tables of two DB

It is not possible to compare two DB easily, there are tools with which we can compare, but the tools costly, the following simple C# program will help to find whether there is any difference in tables in DB, and it won’t compare data to find whether it is same but compares the table in the DB. In C# we can get the list of all table names from the SQL Server DB easily, the following code will help us in getting the table names from two databases and help us to compare for the tables created in the DB.

try
{ int recPos=0;
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
object[] objArrRestrict;
objArrRestrict = new object[] {null, null, null, "TABLE"};
schemaTbl = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,objArrRestrict);

OleDbConnection con1 = new OleDbConnection(connectionString);
con1.Open();
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = con1;
object[] objArrRestrict1;
objArrRestrict1 = new object[] {null, null, null, "TABLE"};
schemaTbl1 = con1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,objArrRestrict1);

if(schemaTbl.Rows.Count!= schemaTbl1.Rows.Count)
{
MessageBox.Show(“Both DB don’t have equal number of tables! “);
}
Else
{
DataView schema1TblView = schemaTbl.DefaultView;
schema1TblView.Sort = "TABLE_NAME";
foreach (DataRow row in schemaTbl1.Rows)
{
recPos = schema1TblView.Find(tblName);
if(recPos==-1)
{
MessageBox(“DB’s are different”);
break;
}
}
}
}
catch (Exception ex){ MessageBox.Show("Error" + ex.Message);
}

The basic idea here is get table names from both the tables, create view for the 1st table names list, loop through the 2nd table names list try to find that in the first list if it is not there then just show message box saying that “DB are different”. Also first compare whether the table count in both of the DB are equal before comparing the each table names.

Explanation

GetOleDbSchemaTable method of OleDbConnection object will help us in getting the table names list from DB. The table view will help us to locate the name in the list, so we create a view in one list and loop through another list to check for all table names. If the recPos==-1 means that the name we are searching on the 1st list is not available so we can confirm that the table is not available in the DB.

Get table list in DB using C# and ADO.NET

How to get list of all tables from SQL Server DB in C#?

In C# we can get the list of all table names from the SQL Server DB easily, the following code will help us in getting the table names and it also load all the table data to the dataset.


Option#1

If we use the following code we need not know all the table names to get the data from the DB.

try
{
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
object[] objArrRestrict;
objArrRestrict = new object[] {null, null, null, "TABLE"};
schemaTbl = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,objArrRestrict);
foreach (DataRow row in schemaTbl.Rows)
{
cmd.CommandText = "select * from ["+row["TABLE_NAME"]+"]";
da.SelectCommand = cmd;
da.Fill(ds, row["TABLE_NAME"].ToString());
}
}
catch (SqlException ex)
{
MessageBox.Show("Error" + ex.Message);
}

The above code will load the dataset “ds” with the data in all the tables in DB given in the connection string.

The method "GetOleDbSchemaTable" does the magic here, it helps us to retrieve all the tables names.

Option#2

The below code snipper will also help us to retrieve set of table names.

try
{
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "select * from sysobjects where type='U'";
ds = cmd.ExecuteQuery();
}
catch (SqlException ex)
{
MessageBox.Show("Error" + ex.Message);
}

This code makes use of the SQL server system table to fetch all the user tables.

Note
Option#1 is used to retrieve Schema information of any OLEDB data source. Option #2 is restricted to SQL Server because othee RDBMS don't have system table which lists the tables. Also note that SqlConnection object don't have the method "GetOleDbSchemaTable" to retrieve schema information.