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