public static void InitMakeTSQL() { using (var db = new NAV_FIELDS_DBEntities()) { IEnumerable <int> mytables = new int[] { }; // T-SQL für folgende Tabellen erstellen: //mytables = new int[] { 3, 4, 5, 6, 7, 8, 9, 10, 13, 14, 15, 18, 19, 20, 23, 24, 27, 30, 42, 79, 80, 81, 82, 83, 84, 85, 88, 92, 93, 94, 97, 98, 99, 152, 156, 201, 204, 205, 222, 224, 225, 230, 231, 232, 233, 234, 236, 237, 242, 244, 245, 250, 251, 252, 255, 256, 257, 258, 259, 261, 262, 264, 270, 279, 280, 282, 284, 285, 286, 287, 288, 289, 290, 291, 292, 293, 294, 301, 308, 309, 310, 311, 312, 313, 314, 315, 323, 324, 325, 330, 333, 334, 340, 341, 348, 349, 352, 381, 385, 402, 403, 404, 409, 452, 453, 464, 800, 801, 5050, 5053, 5054, 5055, 5056, 5057, 5058, 5061, 5066, 5067, 5068, 5069, 5070, 5079,5105,5200,5202,5203 ,5206,5208,5210,5212,5213,5217,5218,5220,5401,5404,5600,5603,5611,5700,5715,5717,5718,5719,5720,5721,5722,5723,5769,5813,5814,6502,7002,7004,7012,7014,7301,8618,8619,5001905,5001906,5001907,5001908,5001909,5001943 //,5041152,5041166,5041168,5041171,5041172,5041193,5041195,5041196,5041202,5041204,5041206,5041207,5041223,5041240,5041241,5041260,5041261,5041270,5041351,5041352,5041365,5041366,5041368,5041369,5041397,5041398,5047670,5047671,5047672,5047673,5047674,5047675}; //oder nur eine Tabelle mytables = new int[] { 50 }; // affected tables in DB var affectedTables = from navfields in db.NavFields09vs18 where (mytables.Contains(navfields.TableNo)) group navfields.TableNo by navfields.TableNo into tablegroup orderby tablegroup.Key ascending select tablegroup.Key; foreach (var tno in affectedTables) { System.Diagnostics.Debug.WriteLine("--Create TSQL for TableNo " + tno); RunCreateCreateTSqlS(tno); } } }
public static void StartCompare(int tableNo) { using (var db = new NAV_FIELDS_DBEntities()) { // Display all Data from the database var query = from b in db.NavFields09vs18 where (b.TableNo == tableNo) orderby b.TableNo, b.FieldNo select b ; string TableName = ""; //System.Diagnostics.Debug.WriteLine("All Columns in the Table:"); foreach (var item in query) { TableName = DataClass.ReplaceChar(item.TableName2018); } DataTable dt18 = DataClass.GetSQLTableSchema(Settings.ConnStringNav2018, "[" + Settings.TargetDBName + "].[dbo].[" + Settings.TargetCompany + "$" + TableName + "]"); DataTable dt09 = DataClass.GetSQLTableSchema(Settings.ConnStringNav2009, "[" + Settings.SourceDBName + "].[dbo].[" + Settings.SourceCompany + "$" + TableName + "]"); foreach (DataRow field in dt18.Rows) { foreach (DataColumn property in dt18.Columns) { //System.Diagnostics.Debug.WriteLine("Columnname is " + property); if (property.ColumnName == "ColumnName") { System.Diagnostics.Debug.WriteLine("ColumName is Columnname and has Value " + field[property]); } if (property.ColumnName == "ColumnSize") { System.Diagnostics.Debug.WriteLine("ColumSize for " + field[0] + " is " + field[property]); CompareColumnSizeWith2009(dt09, field, property); } } } } }
public static void CompareTableAndFields() { IEnumerable <int> mytables = new int[] { 3, 270 }; //mytables = new int[] { 3, 4, 5, 6, 7, 8, 9, 10, 13, 14, 15, 18, 19, 20, 23, 24, 27, 30, 42, 79, 80, 81, 82, 83, 84, 85, 88, 92, 93, 94, 97, 98, 99, 152, 156, 201, 204, 205, 222, 224, 225, 230, 231, 232, 233, 234, 236, 237, 242, 244, 245, 250, 251, 252, 255, 256, 257, 258, 259, 261, 262, 264, 270, 279, 280, 282, 284, 285, 286, 287, 288, 289, 290, 291, 292, 293, 294, 301, 308, 309, 310, 311, 312, 313, 314, 315, 323, 324, 325, 330, 333, 334, 340, 341, 348, 349, 352, 381, 385, 402, 403, 404, 409, 452, 453, 464, 800, 801, 5050, 5053, 5054, 5055, 5056, 5057, 5058, 5061, 5066, 5067, 5068, 5069, 5070, 5079,5105,5200,5202,5203 ,5206,5208,5210,5212,5213,5217,5218,5220,5401,5404,5600,5603,5611,5700,5715,5717,5718,5719,5720,5721,5722,5723,5769,5813,5814,6502,7002,7004,7012,7014,7301,8618,8619,5001905,5001906,5001907,5001908,5001909,5001943 //,5041152,5041166,5041168,5041171,5041172,5041193,5041195,5041196,5041202,5041204,5041206,5041207,5041223,5041240,5041241,5041260,5041261,5041270,5041351,5041352,5041365,5041366,5041368,5041369,5041397,5041398,5047670,5047671,5047672,5047673,5047674,5047675}; using (var db = new NAV_FIELDS_DBEntities()) { // All affected tables var affectedTables = from navfields in db.NavFields09vs18 //where (mytables.Contains(navfields.TableNo)) //Only selected tables group navfields.TableNo by navfields.TableNo into tablegroup orderby tablegroup.Key ascending select tablegroup.Key; //Foreach Tableno in Affected Tables foreach (var tno in affectedTables) { //CompareTableFields.StartCompare(5041171); ListTableDifferences(tno); } } }
public static void ListTableDifferences(int tableNo) { using (var db = new NAV_FIELDS_DBEntities()) { // Check for Suspicious Tables var susptables = from entryadata in db.NavFields09vs18 where (entryadata.TableNo == tableNo) orderby entryadata.TableNo group entryadata by entryadata.TableNo into newGroup select newGroup; foreach (var nameGroup in susptables) { string compare = ""; //Console.WriteLine($"Key: {nameGroup.Key}"); //System.Diagnostics.Debug.WriteLine("--Compare Table No.:" + nameGroup.Key); foreach (var navf in nameGroup) { if ( (navf.TableChangedNameIn2018 == 1) || (navf.TableDeletedIn2018 == 1) || (navf.FieldName2009ChangedIn2018 == 1) || (navf.FieldNameLengthChanged == 1) || (navf.FieldTypeChanged == 1) ) { compare = "--Compare Table No.:" + tableNo + "(" + navf.TableName2018 + ")"; } } System.Diagnostics.Debug.WriteLine(compare); } // Display all Data from the database var query = from b in db.NavFields09vs18 where (b.TableNo == tableNo) orderby b.TableNo, b.FieldNo select b ; string TableName = ""; //Foreach Entry in Table NAV09vs2018 foreach (var item in query) { //System.Diagnostics.Debug.WriteLine("--?????.:" + item.FieldName2018); TableName = DataClass.ReplaceChar(item.TableName2018); if ((item.FieldName2009ChangedIn2018 == 1)) { System.Diagnostics.Debug.WriteLine("FieldName Changed: Fieldname NAV2009: " + item.FieldName2009 + " -> Fieldname NAV2018: " + item.FieldName2018 + " FieldNo[" + item.FieldNo + "]"); } if ((item.Length2009 != item.Length2018) & (item.FieldName2009 != null)) { //Only if in 2018 is smaller than in 2009 if (item.Length2009 > item.Length2018) { System.Diagnostics.Debug.WriteLine("FieldLength Changed: NAV2009: " + item.FieldName2009 + "(" + item.Length2009 + ") -> in NAV2018: " + item.FieldName2018 + "(" + item.Length2018 + ")" + " FieldNo[" + item.FieldNo + "]"); } else { } } if ((item.Type2009 != item.Type2018) & (item.FieldName2009 != null)) { System.Diagnostics.Debug.WriteLine("FieldType Changed: NAV2009: " + item.FieldName2009 + "(" + item.Type2009 + ") -> in NAV2018: " + item.FieldName2018 + "(" + item.Type2018 + ")" + " FieldNo[" + item.FieldNo + "]"); } } } }
static void RunCreateCreateTSqlS(int tableNo) { using (var db = new NAV_FIELDS_DBEntities()) { // Display all Data from the database var query = from b in db.NavFields09vs18 where (b.TableNo == tableNo && b.Class2018 != "FlowField" && b.Class2018 != "FlowFilter" && b.Enabled2018 == "Ja") orderby b.TableNo, b.FieldNo select b ; string Columns2018 = ""; string TableName = ""; //System.Diagnostics.Debug.WriteLine("All Columns in the Table:"); foreach (var item in query) { Columns2018 = Columns2018 + '[' + DataClass.ReplaceChar(item.FieldName2018) + ']' + ','; TableName = DataClass.ReplaceChar(item.TableName2018); } Columns2018 = Columns2018.Remove(Columns2018.Length - 1); string sqlcmd = ""; string sqlcmddelete = ""; if (Settings.WithDeleteStatements == true) { sqlcmddelete = sqlcmddelete + "DELETE FROM [" + Settings.TargetDBName + "].[dbo].[" + Settings.TargetCompany + "$" + TableName + "]"; } sqlcmd = sqlcmd + "INSERT INTO [" + Settings.TargetDBName + "].[dbo].[" + Settings.TargetCompany + "$" + TableName + "] ("; sqlcmd = sqlcmd + Columns2018 + ") "; DataTable dt18 = DataClass.GetSQLTableSchema(Settings.ConnStringNav2018, "[" + Settings.TargetDBName + "].[dbo].[" + Settings.TargetCompany + "$" + TableName + "]"); DataTable dt09 = DataClass.GetSQLTableSchema(Settings.ConnStringNav2009, "[" + Settings.SourceDBName + "].[dbo].[" + Settings.SourceCompany + "$" + TableName + "]"); sqlcmd = sqlcmd + " SELECT "; if (Settings.SelectNRowsToInsert > 0) { sqlcmd = sqlcmd + " TOP (" + Settings.SelectNRowsToInsert.ToString() + ") "; } string Columns2009 = ""; foreach (var item in query) { if (item.FieldName2009 != null) { if (item.FieldName2009ChangedIn2018 == 1) { //System.Diagnostics.Debug.WriteLine("FNo: "+ item.FieldNo +" Field2009: " + item.FieldName2009 + " F18: " + item.FieldName2018 + " Type" + item.Type2018); Columns2009 = Columns2009 + TableCompare.GetFieldTypeIn2018(TableName, DataClass.ReplaceCharPoint(item.FieldName2018)) + ","; } else if (CheckTypeIn2009Equivalent(item.FieldName2009, dt09) != "") { Columns2009 = Columns2009 + CheckTypeIn2009Equivalent(item.FieldName2009, dt09) + ','; } else { Columns2009 = Columns2009 + '[' + DataClass.ReplaceCharPoint(item.FieldName2009) + ']' + ','; } } else { //System.Diagnostics.Debug.WriteLine("FNo: " + item.FieldNo + " Field2009: " + item.FieldName2009 + " F18: " + item.FieldName2018 + " Type" + item.Type2018); // TODO: Check which Default value must put here if Field exist in 2018 and not in 2009, and Field are not NULL Allowed switch (item.Type2018) { case "GUID": Columns2009 = Columns2009 + "NEWID() ,"; break; case "Media": Columns2009 = Columns2009 + "NEWID() ,"; break; case "DateTime": Columns2009 = Columns2009 + "SYSDATETIME() ,"; break; case "Decimal": Columns2009 = Columns2009 + "0 ,"; break; case "RecordID": Columns2009 = Columns2009 + "cast('' as varbinary(50)) ,"; break; default: Columns2009 = Columns2009 + " '',"; break; } } } if (Columns2009.EndsWith(",")) { Columns2009 = Columns2009.Remove(Columns2009.Length - 1); } sqlcmd = sqlcmd + Columns2009 + " "; sqlcmd = sqlcmd + " FROM [SQLTEST].[" + Settings.SourceDBName + "].[dbo].[" + Settings.SourceCompany + "$" + TableName + "] t1 "; string strKeyFields = ""; string strIdentityON = ""; string strIdentityOFF = ""; foreach (DataRow field in dt18.Rows) { foreach (DataColumn property in dt18.Columns) { if (property.ColumnName == "IsKey") { if (field[property].ToString() == "True") { strKeyFields = strKeyFields + "[" + field[0] + "],"; //System.Diagnostics.Debug.WriteLine(field[0] + " " + property.ColumnName + " " + field[property].ToString()); } } if (property.ColumnName == "IsIdentity") { //System.Diagnostics.Debug.WriteLine(field[0] + " " + property.ColumnName + " " + field[property].ToString()); if (field[property].ToString() == "True") { strIdentityON = "SET IDENTITY_INSERT [" + Settings.TargetDBName + "].[dbo].[" + Settings.TargetCompany + "$" + TableName + "] ON"; strIdentityOFF = "SET IDENTITY_INSERT [" + Settings.TargetDBName + "].[dbo].[" + Settings.TargetCompany + "$" + TableName + "] OFF"; } } } } if (strKeyFields.Count() > 1) { strKeyFields = strKeyFields.Remove(strKeyFields.Length - 1); } string checkKeys = ""; string[] s = strKeyFields.Split(','); foreach (string w in s) { checkKeys = checkKeys + "t2." + w + "=t1." + w + " " + CheckFieldForCollate(w, TableName, dt18) + " AND "; } checkKeys = checkKeys.Remove(checkKeys.Length - 4); //sqlcmd = sqlcmd + " WHERE NOT " + strKeyFields + " COLLATE Latin1_General_100_CS_AS IN (SELECT " + strKeyFields + " FROM ["+Settings.TargetDBName+"].[dbo].["+ TargetCompany + "$" + TableName + "]) "; sqlcmd = sqlcmd + " WHERE NOT EXISTS(SELECT " + strKeyFields + " FROM [" + Settings.TargetDBName + "].[dbo].[" + Settings.TargetCompany + "$" + TableName + "] t2 WHERE " + checkKeys + ")"; foreach (DataRow field in dt09.Rows) { //System.Diagnostics.Debug.WriteLine(field[0]); //sqlcmd = sqlcmd + "["+ DataClass.ReplaceChar(field[0].ToString())+"],"; } if (TableName == "Payment Terms") { } //System.Diagnostics.Debug.WriteLine("DELETE FROM["+Settings.TargetDBName+"].[dbo].[" + Settings.TargetCompany + "$Payment Terms] WHERE[Last Modified Date Time] >= Convert(datetime, '2018-04-01')"); if (TableName == "Country_Region") { //System.Diagnostics.Debug.WriteLine("DELETE FROM ["+Settings.TargetDBName+"].[dbo].[" + Settings.TargetCompany + "$Country_Region] WHERE Id <> CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER)"); //sqlcmd = sqlcmd.Replace("[" + Settings.SourceCompany + "$Country_Region]", "[Country_Region]"); } //if (TableName == "Shipment Method") //System.Diagnostics.Debug.WriteLine("DELETE FROM ["+Settings.TargetDBName+"].[dbo].[" + Settings.TargetCompany + "$Shipment Method] WHERE Id <> CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER)"); //if (TableName == "Customer") //System.Diagnostics.Debug.WriteLine("DELETE FROM ["+Settings.TargetDBName+"].[dbo].[" + Settings.TargetCompany + "$Customer] WHERE Image <> CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER)"); System.Diagnostics.Debug.WriteLine(sqlcmddelete); if (strIdentityOFF != "") { System.Diagnostics.Debug.WriteLine(strIdentityOFF); } System.Diagnostics.Debug.WriteLine(sqlcmd); if (strIdentityON != "") { System.Diagnostics.Debug.WriteLine(strIdentityON); } //System.Diagnostics.Debug.WriteLine("--================================="); } string CheckTypeIn2009Equivalent(string fieldname, DataTable dt09) { string s = ""; foreach (DataRow field in dt09.Rows) { foreach (DataColumn property in dt09.Columns) { if (property.ColumnName == "IsKey") { } if (fieldname == field[0].ToString()) { if (property.ColumnName == "DataType") { //System.Diagnostics.Debug.WriteLine("Fieldname in CheckTypeIn2009Equivalent Check is " + field[0]); //.Diagnostics.Debug.WriteLine(field[0] + " " + property.ColumnName + " " + field[property].ToString()); if (field[property].ToString() == "System.Byte[]") { if (field[0].ToString() == "Picture") { s = "0x"; } else { s = "NEWID() "; } } } } } } return(s); } string CheckFieldForCollate(string fieldname, string tablename, DataTable dt18) { fieldname = fieldname.Replace("[", ""); fieldname = fieldname.Replace("]", ""); string colstr = ""; foreach (DataRow field in dt18.Rows) { foreach (DataColumn property in dt18.Columns) { if (property.ColumnName == "IsKey") { } if (fieldname == field[0].ToString()) { if (property.ColumnName == "DataType") { if (field[property].ToString() == "System.String") { colstr = "COLLATE Latin1_General_100_CS_AS "; } } } } } return(colstr); } }