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