internal void SetupCommand(SqliteCommand cmd, ChangeType itemChangeType, Dictionary <string, SyncItemValue> syncItemValues)
{
//take values only for existing columns (server table schema could be not in sync with local table schema)
var valuesForValidColumns = syncItemValues
.Where(value => Columns.Any(_ => StringComparer.OrdinalIgnoreCase.Compare(_.Key, value.Key) == 0))
.ToList();
switch (itemChangeType)
{
case ChangeType.Insert:
cmd.CommandText = $@"INSERT OR IGNORE INTO [{Name}] ({string.Join(", ", valuesForValidColumns.Select(_ => "[" + _.Key + "]"))})
VALUES ({string.Join(", ", valuesForValidColumns.Select(_ => "@" + _.Key.Replace(' ', '_')))});";
break;
case ChangeType.Update:
cmd.CommandText = $@"UPDATE [{Name}]
SET {string.Join(", ", valuesForValidColumns.Select(_ => "[" + _.Key + "] = @" + _.Key.Replace(' ', '_')))}
WHERE [{Name}].[{PrimaryColumnName}] = @{PrimaryColumnName.Replace(' ', '_')}
AND (@sync_force_write = 1 OR (SELECT MAX(ID) FROM __CORE_SYNC_CT WHERE PK_{PrimaryColumnType} = @{PrimaryColumnName.Replace(' ', '_')} AND TBL = '{Name}') <= @last_sync_version)";
break;
case ChangeType.Delete:
cmd.CommandText = $@"DELETE FROM [{Name}]
WHERE [{Name}].[{PrimaryColumnName}] = @{PrimaryColumnName.Replace(' ', '_')}
AND (@sync_force_write = 1 OR (SELECT MAX(ID) FROM __CORE_SYNC_CT WHERE PK_{PrimaryColumnType} = @{PrimaryColumnName.Replace(' ', '_')} AND TBL = '{Name}') <= @last_sync_version)";
break;
}
foreach (var valueItem in valuesForValidColumns)
{
cmd.Parameters.Add(new SqliteParameter("@" + valueItem.Key.Replace(" ", "_"), valueItem.Value.Value ?? DBNull.Value));
}
}
internal void SetupCommand(SqlCommand cmd, ChangeType itemChangeType, Dictionary <string, SyncItemValue> syncItemValues)
{
var allColumnsExceptSkipColumns = Columns.Keys.Except(SkipColumns).ToArray();
//take values only for existing columns (server table schema could be not in sync with local table schema)
var allSyncItems = syncItemValues
.Where(value => allColumnsExceptSkipColumns.Any(_ => StringComparer.OrdinalIgnoreCase.Compare(_, value.Key) == 0))
.ToList();
var allSyncItemsExceptPrimaryKey = allSyncItems.Where(_ => !PrimaryKeyColumns.Any(kc => kc == _.Key)).ToArray();
switch (itemChangeType)
{
case ChangeType.Insert:
cmd.CommandText = $@"{(HasTableIdentityColumn ? $"SET IDENTITY_INSERT {NameWithSchema} ON" : string.Empty)}
BEGIN TRY
INSERT INTO {NameWithSchema} ({string.Join(", ", allSyncItems.Select(_ => "[" + _.Key + "]"))})
VALUES ({string.Join(", ", allSyncItems.Select(_ => "@" + _.Key.Replace(' ', '_')))});
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
{(HasTableIdentityColumn ? $"SET IDENTITY_INSERT {NameWithSchema} OFF" : string.Empty)}";
break;
case ChangeType.Update:
cmd.CommandText = $@"BEGIN TRY
UPDATE {NameWithSchema}
SET {string.Join(", ", allSyncItemsExceptPrimaryKey.Select(_ => "[" + _.Key + "] = @" + _.Key.Replace(' ', '_')))}
WHERE {NameWithSchema}.[{PrimaryColumnName}] = @{PrimaryColumnName.Replace(" ", "_")}
AND (@sync_force_write = 1 OR (SELECT MAX(ID) FROM __CORE_SYNC_CT WHERE PK_{PrimaryColumnType} = @{PrimaryColumnName.Replace(" ", "_")} AND TBL = '{NameWithSchema}') <= @last_sync_version)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH";
break;
case ChangeType.Delete:
cmd.CommandText = $@"BEGIN TRY
DELETE FROM {NameWithSchema}
WHERE {NameWithSchema}.[{PrimaryColumnName}] = @{PrimaryColumnName.Replace(" ", "_")}
AND (@sync_force_write = 1 OR (SELECT MAX(ID) FROM __CORE_SYNC_CT WHERE PK_{PrimaryColumnType} = @{PrimaryColumnName.Replace(" ", "_")} AND TBL = '{NameWithSchema}') <= @last_sync_version)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH";
break;
}
foreach (var valueItem in allSyncItems)
{
cmd.Parameters.Add(new SqlParameter("@" + valueItem.Key.Replace(" ", "_"), Columns[valueItem.Key].DbType)
{
Value = Utils.ConvertToSqlType(valueItem.Value, Columns[valueItem.Key].DbType)
});
//cmd.Parameters.AddWithValue("@" + valueItem.Key.Replace(" ", "_"), valueItem.Value.Value ?? DBNull.Value);
}
}
