public static void CheckIf2009FieldHasOtherNameAndTypeIn2018(string fieldname09, string tablename) { DataTable dt18 = DataClass.GetSQLTableSchema(Settings.ConnStringNav2018, "[" + Settings.TargetDBName + "].[dbo].[" + Settings.TargetCompany + "$" + tablename + "]"); DataTable dt09 = DataClass.GetSQLTableSchema(Settings.ConnStringNav2009, "[" + Settings.SourceDBName + "].[dbo].[" + Settings.SourceCompany + "$" + tablename + "]"); DataColumnCollection columns = dt18.Columns; if (columns.Contains(fieldname09)) { System.Diagnostics.Debug.WriteLine("FieldName in NAV2009: " + fieldname09 + " seems equal as in NAV2018 "); } else { System.Diagnostics.Debug.WriteLine("FieldName in NAV2009: " + fieldname09 + " seems NOT EXISTENT in NAV2018"); } }
public static string GetFieldTypeIn2018(string tableName, string fieldName2018) { DataTable dt18 = DataClass.GetSQLTableSchema(Settings.ConnStringNav2018, "[" + Settings.TargetDBName + "].[dbo].[" + Settings.TargetCompany + "$" + tableName + "]"); string defaultvalforfield = ""; foreach (DataRow field in dt18.Rows) { foreach (DataColumn property in dt18.Columns) { //If Field is the field i want to search if (field[0].ToString() == fieldName2018) { //System.Diagnostics.Debug.WriteLine("Found Field " + field[0]); if (property.ColumnName == "DataType") { //System.Diagnostics.Debug.WriteLine("Found Field " + field[0] + " with DataType " + field[property].ToString() + " set default to: "+GetDefaultForDataType(field[property].ToString())); defaultvalforfield = GetDefaultForDataType(field[property].ToString()); } } } } return(defaultvalforfield); }
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); } } } } }
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); } }