public void initIndexes(SqlConnection conn1, SqlConnection conn2, List <Table> tables)
{
// Logger
DateTime start = DateTime.Now;
logger.Info("[ initIndexes ] - start time : " + start.ToString());
if (tables.Count() == 0)
{
return;
}
List <DBIndexes> indexes1 = new List <DBIndexes>();
List <DBIndexes> indexes2 = new List <DBIndexes>();
string command = "SELECT indexname = a.name , tablename = c. name , indexcolumns = d .name , a .indid " +
"FROM sysindexes a JOIN sysindexkeys b ON a .id = b .id AND a .indid = b.indid " +
"JOIN sysobjects c ON b .id = c .id " +
"JOIN syscolumns d ON b .id = d .id AND b .colid = d .colid " +
"WHERE a .indid NOT IN ( 0 , 255 ) " +
"AND (c.name = '" + tables[0].name + "' ";
for (int i = 1; i < tables.Count(); i++)
{
command += " or c.name = '" + tables[i].name + "' ";
}
command += ") ORDER BY c. name , a.name , d.name";
SqlDataReader dr1 = SqlServer.ExcuteSqlCommandReader(command, conn1);
SqlDataReader dr2 = SqlServer.ExcuteSqlCommandReader(command, conn2);
while (dr1.Read())
{
if (indexes1.Where(a => a.indexName == dr1[0].ToString() && a.tbName == dr1[1].ToString()).Count() == 0)
{
indexes1.Add(new DBIndexes(dr1[0].ToString(), dr1[1].ToString()));
}
}
while (dr2.Read())
{
if (indexes2.Where(a => a.indexName == dr2[0].ToString() && a.tbName == dr2[1].ToString()).Count() == 0)
{
indexes2.Add(new DBIndexes(dr2[0].ToString(), dr2[1].ToString()));
}
}
foreach (Table table in tables)
{
List <DBIndexes> index1 = indexes1.Where(a => a.tbName == table.name).ToList();
List <DBIndexes> index2 = indexes2.Where(a => a.tbName == table.name).ToList();
table.indexes = compareIndexs(conn1.Database, conn2.Database, table.name, index1, index2);
}
// Logger
DateTime end = DateTime.Now;
logger.Info("[ initIndexes ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n");
}
public void initKeys(SqlConnection conn1, SqlConnection conn2, List <Table> tables)
{
// Logger
DateTime start = DateTime.Now;
logger.Info("[ initKeys ] - start time : " + start.ToString());
if (tables.Count() == 0)
{
return;
}
List <DBKeys> keys1 = new List <DBKeys>();
List <DBKeys> keys2 = new List <DBKeys>();
string command = "SELECT t.TABLE_NAME, t.CONSTRAINT_TYPE, c.COLUMN_NAME " +
"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c " +
"WHERE t.CONSTRAINT_NAME = c.CONSTRAINT_NAME AND (OBJECT_ID(t.TABLE_NAME) = OBJECT_ID('" + tables[0].name + "') ";
for (int i = 1; i < tables.Count(); i++)
{
command += " or OBJECT_ID(t.TABLE_NAME) = OBJECT_ID('" + tables[i].name + "') ";
}
command += ")";
SqlDataReader dr1 = SqlServer.ExcuteSqlCommandReader(command, conn1);
SqlDataReader dr2 = SqlServer.ExcuteSqlCommandReader(command, conn2);
while (dr1.Read())
{
keys1.Add(new DBKeys()
{
tbname = dr1[0].ToString(), constType = dr1[1].ToString(), colName = dr1[2].ToString()
});
}
while (dr2.Read())
{
keys2.Add(new DBKeys()
{
tbname = dr2[0].ToString(), constType = dr2[1].ToString(), colName = dr2[2].ToString()
});
}
foreach (Table table in tables)
{
List <DBKeys> key1 = keys1.Where(a => a.tbname == table.name).ToList();
List <DBKeys> key2 = keys2.Where(a => a.tbname == table.name).ToList();
table.keys = compareKeys(conn1.Database, conn2.Database, table.name, key1, key2);
}
// Logger
DateTime end = DateTime.Now;
logger.Info("[ initKeys ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n");
}
/// <summary>
/// Get all the stored producers from the database
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
private List <StoredProcedure> getStoredProcedures(SqlConnection connection)
{
// TEST
//string commandTest = "select text from syscomments where id=object_id('SP_COPY_EQUIPMENT_DATA_FROM_ONESOURCE_TO_EAM')";
//SqlDataReader drTest = SqlServer.ExcuteSqlCommandReader(commandTest, connection);
//while(drTest.Read()){
// string statement = drTest[0].ToString();
//}
// Logger
DateTime start = DateTime.Now;
logger.Info("[ getStoredProcedures ] - start time : " + start.ToString());
List <StoredProcedure> pros = new List <StoredProcedure>();
List <DBProcedures> dbProceduers = new List <DBProcedures>();
// get stored procedures
string command = "select name ,text from dbo.sysobjects a join syscomments b on a.id = b.id where OBJECTPROPERTY(a.id, N'IsProcedure') = 1 order by name";
SqlDataReader dr = SqlServer.ExcuteSqlCommandReader(command, connection);
while (dr.Read())
{
// A large function will send by several times
List <DBProcedures> beforeProducers = dbProceduers.Where(a => a.name == dr[0].ToString()).ToList();
if (beforeProducers.Count() > 0)
{
beforeProducers[0].statement += dr[1].ToString();
}
else
{
dbProceduers.Add(new DBProcedures()
{
name = dr[0].ToString(), statement = dr[1].ToString()
});
}
}
foreach (DBProcedures dbprocedure in dbProceduers)
{
pros.Add(new StoredProcedure(connection.Database, dbprocedure.name, dbprocedure.statement, true));
}
// Logger
DateTime end = DateTime.Now;
logger.Info("[ getStoredProcedures ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n");
return(pros);
}
private List <Function> getFunctions(SqlConnection connection)
{
// Logger
DateTime start = DateTime.Now;
logger.Info("[ getFunctions ] - start time : " + start.ToString());
List <Function> rtn = new List <Function>();
// get function names
List <DBFunctions> dbfunctions = new List <DBFunctions>();
string command = "select name,text from sys.objects a join sys.syscomments b on a.object_id = b.id where type='FN'";
SqlDataReader dr = SqlServer.ExcuteSqlCommandReader(command, connection);
while (dr.Read())
{
List <DBFunctions> befireFunction = dbfunctions.Where(a => a.name == dr[0].ToString()).ToList();
if (befireFunction.Count() > 0)
{
befireFunction[0].statement += dr[1].ToString();
}
else
{
dbfunctions.Add(new DBFunctions()
{
name = dr[0].ToString(), statement = dr[1].ToString()
});
}
}
foreach (DBFunctions dbfunction in dbfunctions)
{
rtn.Add(new Function(connection.Database, dbfunction.name, dbfunction.statement, true));
}
// Logger
DateTime end = DateTime.Now;
logger.Info("[ getFunctions ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n");
return(rtn);
}
/// <summary>
/// Init the different columns information in the tables list
/// </summary>
/// <param name="conn1"></param>
/// <param name="conn2"></param>
/// <param name="tables"></param>
private void initDiffColumns(SqlConnection conn1, SqlConnection conn2, List <Table> tables)
{
// Logger
DateTime start = DateTime.Now;
logger.Info("[ initDiffColumns ] - start time : " + start.ToString());
if (tables.Count() == 0)
{
return;
}
List <DBColumns> dbColumns1 = new List <DBColumns>();
List <DBColumns> dbColumns2 = new List <DBColumns>();
// Excute sql command to get all the columns information of all tables
string command = "SELECT TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX,NUMERIC_SCALE FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + tables[0].name + "' ";
for (int i = 1; i < tables.Count(); i++)
{
command += " OR TABLE_NAME='" + tables[i].name + "' ";
}
SqlDataReader dr1 = SqlServer.ExcuteSqlCommandReader(command, conn1);
SqlDataReader dr2 = SqlServer.ExcuteSqlCommandReader(command, conn2);
while (dr1.Read())
{
dbColumns1.Add(new DBColumns()
{
tbName = dr1[0].ToString(),
colName = dr1[1].ToString(),
colDefault = dr1[2].ToString(),
isNullable = dr1[3].ToString(),
dataType = dr1[4].ToString(),
charMaxLen = dr1[5].ToString(),
charOctetLen = dr1[6].ToString(),
numericPrecision = dr1[7].ToString(),
numericPrecisionRadix = dr1[8].ToString(),
numericScale = dr1[9].ToString()
});
}
while (dr2.Read())
{
dbColumns2.Add(new DBColumns()
{
tbName = dr2[0].ToString(),
colName = dr2[1].ToString(),
colDefault = dr2[2].ToString(),
isNullable = dr2[3].ToString(),
dataType = dr2[4].ToString(),
charMaxLen = dr2[5].ToString(),
charOctetLen = dr2[6].ToString(),
numericPrecision = dr2[7].ToString(),
numericPrecisionRadix = dr2[8].ToString(),
numericScale = dr2[9].ToString()
});
}
foreach (Table table in tables)
{
string tbname = table.name;
List <DBColumns> columns1 = dbColumns1.Where(a => a.tbName == tbname).ToList();
List <DBColumns> columns2 = dbColumns2.Where(a => a.tbName == tbname).ToList();
table.columns = compareColumns(conn1.Database, conn2.Database, columns1, columns2);
}
// Logger
DateTime end = DateTime.Now;
logger.Info("[ initDiffColumns ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n");
return;
}
/// <summary>
/// Compare all the table information between two databases
/// </summary>
/// <param name="conn1"></param>
/// <param name="conn2"></param>
/// <returns></returns>
public List <Table> compareTables(SqlConnection conn1, SqlConnection conn2)
{
// Logger
DateTime start = DateTime.Now;
logger.Info("[ compareTables ] - start time : " + start.ToString());
List <Table> rtnTables = new List <Table>();
// Excute the sql command to get all the table names in a database
string command = "SELECT SysObjects.name AS Tablename FROM sysobjects WHERE xtype = 'U'";
SqlDataReader dr1 = SqlServer.ExcuteSqlCommandReader(command, conn1);
SqlDataReader dr2 = SqlServer.ExcuteSqlCommandReader(command, conn2);
List <string> tables1 = new List <string>();
List <string> tables2 = new List <string>();
while (dr1.Read())
{
tables1.Add(dr1[0].ToString());
}
while (dr2.Read())
{
tables2.Add(dr2[0].ToString());
}
// Get the unique table only in database1 or database2
foreach (string item in tables1.Except(tables2))
{
Table table = new Table(item, false, conn1.Database);
rtnTables.Add(table);
}
foreach (string item in tables2.Except(tables1))
{
Table table = new Table(item, false, conn2.Database);
rtnTables.Add(table);
}
// Get the coeixt tables in database1 and database2
List <Table> candidateTables = new List <Table>();
foreach (string item in tables1.Intersect(tables2))
{
Table table = new Table(item, true, "");
candidateTables.Add(table);
}
initDiffColumns(conn1, conn2, candidateTables);
initIndexes(conn1, conn2, candidateTables);
initKeys(conn1, conn2, candidateTables);
//table.indexes = compareIndexs(conn1, conn2, item);
//table.keys = compareKeys(conn1, conn2, item);
foreach (Table table in candidateTables)
{
if (table.columns.Count() != 0 || table.indexes.Count() != 0 || table.keys.Count() != 0)
{
rtnTables.Add(table);
}
}
// Logger
DateTime end = DateTime.Now;
logger.Info("[ compareTables ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n");
return(rtnTables);
}
