private void BeginUpdateSql() {
if (ConnectionType == ConnectionManagerType.MySql) {
QueryText.AppendLine($@"UPDATE {TN.QuotatedFullName} ut");
QueryText.AppendLine("INNER JOIN (");
if (IsMariaDb)
QueryText.AppendLine($@"WITH ws ( {string.Join(", ", SourceColumnNames.Select(col => $"{QB}{col}{QE}"))} ) AS ( VALUES");
else
QueryText.AppendLine("VALUES");
} else if (ConnectionType == ConnectionManagerType.Postgres) {
QueryText.AppendLine($@"UPDATE {TN.QuotatedFullName} ut");
QueryText.AppendLine($@"SET {string.Join(", ", SetColumnNames.Select(col => $"{QB}{col}{QE} = vt.{QB}{col}{QE}"))}");
QueryText.AppendLine($@"FROM (");
QueryText.AppendLine("VALUES");
}
//https://www.orafaq.com/node/2450
else if (ConnectionType == ConnectionManagerType.Oracle) {
QueryText.AppendLine($@"MERGE INTO {TN.QuotatedFullName} ut");
QueryText.AppendLine($@"USING (");
} else if (ConnectionType == ConnectionManagerType.Db2) {
QueryText.AppendLine($@"MERGE INTO {TN.QuotatedFullName} ut");
QueryText.AppendLine($@"USING (");
QueryText.AppendLine($@"VALUES");
} else {
QueryText.AppendLine($@"UPDATE ut");
QueryText.AppendLine($@"SET {string.Join(", ", SetColumnNames.Select(col => $"ut.{QB}{col}{QE} = vt.{QB}{col}{QE}"))}");
QueryText.AppendLine($@"FROM {TN.QuotatedFullName} ut");
QueryText.AppendLine("INNER JOIN (");
QueryText.AppendLine("VALUES");
}
}
private void EndUpdateSql()
{
//https://www.orafaq.com/node/2450
if (ConnectionType == ConnectionManagerType.Oracle)
{
QueryText.AppendLine(") vt ");
QueryText.Append(" ON ( ");
QueryText.AppendLine(string.Join(Environment.NewLine + " AND ",
JoinColumnNames.Select(col => $@"( ( ut.{QB}{col}{QE} IS NULL AND vt.{QB}{col}{QE} IS NULL) OR ( ut.{QB}{col}{QE} = vt.{QB}{col}{QE} ) )")));
QueryText.AppendLine(") WHEN MATCHED THEN UPDATE SET");
QueryText.AppendLine($@"{string.Join(", ", SetColumnNames.Select(col => $"ut.{QB}{col}{QE} = vt.{QB}{col}{QE}"))}");
}
else if (ConnectionType == ConnectionManagerType.Db2)
{
QueryText.AppendLine($") vt ( { string.Join(",", SourceColumnNames.Select(col => $"{QB}{col}{QE}"))} )");
QueryText.Append(" ON ( ");
QueryText.AppendLine(string.Join(Environment.NewLine + " AND ",
JoinColumnNames.Select(col => $@"( ( ut.{QB}{col}{QE} IS NULL AND vt.{QB}{col}{QE} IS NULL) OR ( ut.{QB}{col}{QE} = vt.{QB}{col}{QE} ) )")));
QueryText.AppendLine(") WHEN MATCHED THEN UPDATE SET");
QueryText.AppendLine($@"{string.Join(", ", SetColumnNames.Select(col => $"ut.{QB}{col}{QE} = vt.{QB}{col}{QE}"))}");
}
else if (ConnectionType == ConnectionManagerType.MySql)
{
if (IsMariaDb)
{
QueryText.AppendLine($" ) SELECT * FROM ws ) vt");
}
else
{
QueryText.AppendLine($") vt ( { string.Join(",", SourceColumnNames.Select(col => $"{QB}{col}{QE}"))} )");
}
QueryText.Append(" ON ");
QueryText.AppendLine(string.Join(Environment.NewLine + " AND ",
JoinColumnNames.Select(col => $@"( ( ut.{QB}{col}{QE} IS NULL AND vt.{QB}{col}{QE} IS NULL) OR ( ut.{QB}{col}{QE} = vt.{QB}{col}{QE} ) )")));
QueryText.AppendLine($@"SET {string.Join(", ", SetColumnNames.Select(col => $"ut.{QB}{col}{QE} = vt.{QB}{col}{QE}"))}");
}
else if (ConnectionType == ConnectionManagerType.Postgres)
{
QueryText.AppendLine($") vt ( { string.Join(",", SourceColumnNames.Select(col => $"{QB}{col}{QE}"))} )");
QueryText.Append(" WHERE ");
AppendMatchConditionWithJsonbConversion(JoinColumnNames, "ut");
}
else
{
QueryText.AppendLine($") vt ( { string.Join(",", SourceColumnNames.Select(col => $"{QB}{col}{QE}"))} )");
QueryText.Append(" ON ");
QueryText.AppendLine(string.Join(Environment.NewLine + " AND ",
JoinColumnNames.Select(col => $@"( ( ut.{QB}{col}{QE} IS NULL AND vt.{QB}{col}{QE} IS NULL) OR ( ut.{QB}{col}{QE} = vt.{QB}{col}{QE} ) )")));
}
}
