Saturday, July 22, 2006

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.

{ int recPos=0;
OleDbConnection con = new OleDbConnection(connectionString);
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);
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! “);
DataView schema1TblView = schemaTbl.DefaultView;
schema1TblView.Sort = "TABLE_NAME";
foreach (DataRow row in schemaTbl1.Rows)
recPos = schema1TblView.Find(tblName);
MessageBox(“DB’s are different”);
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.


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.


Post a Comment

<< Home