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);
}
