/// <summary>
/// return null for all no used commands
/// </summary>
public override (DbCommand, bool) GetCommand(DbCommandType nameType, SyncFilter filter)
{
var command = new SqliteCommand();
switch (nameType)
{
case DbCommandType.UpdateRow:
return(CreateUpdateCommand(), false);
case DbCommandType.InsertRows:
return(CreateInitiliazeRowCommand(), false);
case DbCommandType.DeleteRow:
return(CreateDeleteCommand(), false);
case DbCommandType.DisableConstraints:
command.CommandType = CommandType.Text;
command.CommandText = this.sqliteObjectNames.GetCommandName(DbCommandType.DisableConstraints, filter);
break;
case DbCommandType.EnableConstraints:
command.CommandType = CommandType.Text;
command.CommandText = this.sqliteObjectNames.GetCommandName(DbCommandType.EnableConstraints, filter);
break;
case DbCommandType.Reset:
return(CreateResetCommand(), false);
default:
return(default, default);
}
return(command, false);
}
/// <summary>
/// Create all custom wheres from witing a filter
/// </summary>
protected string CreateFilterCustomWheres(SyncFilter filter)
{
var customWheres = filter.CustomWheres;
if (customWheres.Count == 0)
{
return(string.Empty);
}
var stringBuilder = new StringBuilder();
var and2 = " ";
stringBuilder.AppendLine($"(");
foreach (var customWhere in customWheres)
{
stringBuilder.Append($"{and2}{customWhere}");
and2 = " AND ";
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($")");
return(stringBuilder.ToString());
}
/// <summary>
/// Add all sql parameters
/// </summary>
protected void CreateFilterParameters(MySqlCommand sqlCommand, SyncFilter filter)
{
var parameters = filter.Parameters;
if (parameters.Count == 0)
{
return;
}
foreach (var param in parameters)
{
if (param.DbType.HasValue)
{
// Get column name and type
var columnName = ParserName.Parse(param.Name, "`").Unquoted().Normalized().ToString();
#if MARIADB
var sqlDbType = (MySqlDbType)this.mySqlDbMetadata.TryGetOwnerDbType(null, param.DbType.Value, false, false, param.MaxLength, MariaDB.MariaDBSyncProvider.ProviderType, MariaDB.MariaDBSyncProvider.ProviderType);
#elif MYSQL
var sqlDbType = (MySqlDbType)this.mySqlDbMetadata.TryGetOwnerDbType(null, param.DbType.Value, false, false, param.MaxLength, MySqlSyncProvider.ProviderType, MySqlSyncProvider.ProviderType);
#endif
var customParameterFilter = new MySqlParameter($"in_{columnName}", sqlDbType);
customParameterFilter.Size = param.MaxLength;
customParameterFilter.IsNullable = param.AllowNull;
customParameterFilter.Value = param.DefaultValue;
sqlCommand.Parameters.Add(customParameterFilter);
}
else
{
var tableFilter = this.tableDescription.Schema.Tables[param.TableName, param.SchemaName];
if (tableFilter == null)
{
throw new FilterParamTableNotExistsException(param.TableName);
}
var columnFilter = tableFilter.Columns[param.Name];
if (columnFilter == null)
{
throw new FilterParamColumnNotExistsException(param.Name, param.TableName);
}
// Get column name and type
var columnName = ParserName.Parse(columnFilter, "`").Unquoted().Normalized().ToString();
#if MARIADB
var sqlDbType = (SqlDbType)this.mySqlDbMetadata.TryGetOwnerDbType(columnFilter.OriginalDbType, columnFilter.GetDbType(), false, false, columnFilter.MaxLength, tableFilter.OriginalProvider, MariaDB.MariaDBSyncProvider.ProviderType);
#elif MYSQL
var sqlDbType = (SqlDbType)this.mySqlDbMetadata.TryGetOwnerDbType(columnFilter.OriginalDbType, columnFilter.GetDbType(), false, false, columnFilter.MaxLength, tableFilter.OriginalProvider, MySqlSyncProvider.ProviderType);
#endif
// Add it as parameter
var sqlParamFilter = new MySqlParameter($"in_{columnName}", sqlDbType);
sqlParamFilter.Size = columnFilter.MaxLength;
sqlParamFilter.IsNullable = param.AllowNull;
sqlParamFilter.Value = param.DefaultValue;
sqlCommand.Parameters.Add(sqlParamFilter);
}
}
}
#pragma warning restore CS1998 // Async method lacks 'await' operators and will run synchronously
private async Task DropProcedureAsync(DbCommandType procType, SyncFilter filter = null)
{
var commandName = this.mySqlObjectNames.GetCommandName(procType).name;
var commandText = $"drop procedure if exists {commandName}";
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
using (var command = new MySqlCommand(commandText, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
if (filter != null)
{
using (var command = new MySqlCommand())
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (this.transaction != null)
{
command.Transaction = this.transaction;
}
var commandNameWithFilter = this.mySqlObjectNames.GetCommandName(DbCommandType.SelectChangesWithFilters, filter).name;
command.CommandText = $"DROP PROCEDURE {commandNameWithFilter};";
command.Connection = this.connection;
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during DropProcedureCommand : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
/// <summary>
/// Create all custom joins from within a filter
/// </summary>
protected string CreateFilterCustomJoins(SyncFilter filter)
{
var customJoins = filter.Joins;
if (customJoins.Count == 0)
{
return(string.Empty);
}
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
foreach (var customJoin in customJoins)
{
switch (customJoin.JoinEnum)
{
case Join.Left:
stringBuilder.Append("LEFT JOIN ");
break;
case Join.Right:
stringBuilder.Append("RIGHT JOIN ");
break;
case Join.Outer:
stringBuilder.Append("OUTER JOIN ");
break;
case Join.Inner:
default:
stringBuilder.Append("INNER JOIN ");
break;
}
var filterTableName = ParserName.Parse(filter.TableName, "`").Quoted().ToString();
var joinTableName = ParserName.Parse(customJoin.TableName, "`").Quoted().ToString();
var leftTableName = ParserName.Parse(customJoin.LeftTableName, "`").Quoted().ToString();
if (string.Equals(filterTableName, leftTableName, SyncGlobalization.DataSourceStringComparison))
{
leftTableName = "`base`";
}
var rightTableName = ParserName.Parse(customJoin.RightTableName, "`").Quoted().ToString();
if (string.Equals(filterTableName, rightTableName, SyncGlobalization.DataSourceStringComparison))
{
rightTableName = "`base`";
}
var leftColumName = ParserName.Parse(customJoin.LeftColumnName, "`").Quoted().ToString();
var rightColumName = ParserName.Parse(customJoin.RightColumnName, "`").Quoted().ToString();
stringBuilder.AppendLine($"{joinTableName} ON {leftTableName}.{leftColumName} = {rightTableName}.{rightColumName}");
}
return(stringBuilder.ToString());
}
/// <summary>
/// Overriding adapter since the update metadata is not a stored proc that we can override
/// </summary>
public override DbCommand GetCommand(DbCommandType nameType, SyncFilter filter)
{
if (nameType == DbCommandType.UpdateMetadata)
{
var c = new SqlCommand("Set @sync_row_count = 1;");
c.Parameters.Add("@sync_row_count", SqlDbType.Int);
return(c);
}
return(base.GetCommand(nameType, filter));
}
public DbCommand CreateSelectIncrementalChangesWithFilterCommand(SyncFilter filter, DbConnection connection, DbTransaction transaction)
{
if (filter == null)
{
return(null);
}
var commandName = this.mySqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.SelectChangesWithFilters, filter);
Func <MySqlCommand> cmdWithFilter = () => BuildSelectIncrementalChangesCommand(filter);
return(CreateProcedureCommand(cmdWithFilter, commandName, connection, transaction));
}
public override (DbCommand, bool) GetCommand(DbCommandType commandType, SyncFilter filter = null)
{
var command = new SqliteCommand();
string text;
text = this.sqliteObjectNames.GetCommandName(commandType, filter);
// on Sqlite, everything is text :)
command.CommandType = CommandType.Text;
command.CommandText = text;
return(command, false);
}
public async Task CreateSelectInitializedChangesAsync(SyncFilter filter)
{
var commandName = this.mySqlObjectNames.GetCommandName(DbCommandType.SelectInitializedChanges).name;
Func <MySqlCommand> cmdWithoutFilter = () => BuildSelectInitializedChangesCommand(null);
await CreateProcedureCommandAsync(cmdWithoutFilter, commandName).ConfigureAwait(false);
if (filter != null)
{
commandName = this.mySqlObjectNames.GetCommandName(DbCommandType.SelectInitializedChangesWithFilters, filter).name;
Func <MySqlCommand> cmdWithFilter = () => BuildSelectInitializedChangesCommand(filter);
await CreateProcedureCommandAsync(cmdWithFilter, commandName).ConfigureAwait(false);
}
}
public async Task CreateSelectIncrementalChangesAsync(SyncFilter filter, DbConnection connection, DbTransaction transaction)
{
var commandName = this.mySqlObjectNames.GetCommandName(DbCommandType.SelectChanges).name;
Func <MySqlCommand> cmdWithoutFilter = () => BuildSelectIncrementalChangesCommand(null);
await CreateProcedureCommandAsync(cmdWithoutFilter, commandName, connection, transaction).ConfigureAwait(false);
if (filter != null)
{
commandName = this.mySqlObjectNames.GetCommandName(DbCommandType.SelectChangesWithFilters, filter).name;
Func <MySqlCommand> cmdWithFilter = () => BuildSelectIncrementalChangesCommand(filter);
await CreateProcedureCommandAsync(cmdWithFilter, commandName, connection, transaction).ConfigureAwait(false);
}
}
public override DbCommand GetCommand(DbCommandType nameType, SyncFilter filter)
{
var command = new SqlCommand();
string text;
bool isStoredProc;
(text, isStoredProc) = this.sqlObjectNames.GetCommandName(nameType, filter);
command.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
command.CommandText = text;
return(command);
}
public string GetTriggerCommandName(DbTriggerType objectType, SyncFilter filter = null)
{
if (!triggersNames.ContainsKey(objectType))
{
throw new Exception("Yous should provide a value for all DbCommandName");
}
var commandName = triggersNames[objectType];
//// concat filter name
//if (filter != null)
// commandName = string.Format(commandName, filter.GetFilterName());
return(commandName);
}
public void CreateSelectIncrementalChanges(SyncFilter filter = null)
{
var commandName = this.mySqlObjectNames.GetCommandName(DbCommandType.SelectChanges).name;
Func <MySqlCommand> cmdWithoutFilter = () => BuildSelectIncrementalChangesCommand(null);
CreateProcedureCommand(cmdWithoutFilter, commandName);
if (filter != null)
{
filter.ValidateColumnFilters(this.tableDescription);
commandName = this.mySqlObjectNames.GetCommandName(DbCommandType.SelectChangesWithFilters, filter).name;
Func <MySqlCommand> cmdWithFilter = () => BuildSelectIncrementalChangesCommand(filter);
CreateProcedureCommand(cmdWithFilter, commandName);
}
}
public string GetCommandName(DbCommandType objectType, SyncFilter filter = null)
{
if (!names.ContainsKey(objectType))
{
throw new NotSupportedException($"Sqlite provider does not support the command type {objectType.ToString()}");
}
var commandName = names[objectType];
// concat filter name
//if (filter != null)
// commandName = string.Format(commandName, filter.GetFilterName());
return(commandName);
}
public override DbCommand GetCommand(DbCommandType commandType, SyncFilter filter = null)
{
var command = new MySqlCommand();
string text;
bool isStoredProc;
(text, isStoredProc) = this.mySqlObjectNames.GetCommandName(commandType, filter);
var textName = ParserName.Parse(text, "`");
command.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
command.CommandText = isStoredProc ? textName.Quoted().ToString() : text;
return(command);
}
/// <summary>
/// Returning null for all non used commands (from case default)
/// </summary>
public override (DbCommand, bool) GetCommand(DbCommandType nameType, SyncFilter filter)
{
var isBatch = false;
var command = new SqlCommand();
switch (nameType)
{
case DbCommandType.DisableConstraints:
command.CommandType = CommandType.Text;
command.CommandText = this.SqlObjectNames.GetCommandName(DbCommandType.DisableConstraints, filter);
break;
case DbCommandType.EnableConstraints:
command.CommandType = CommandType.Text;
command.CommandText = this.SqlObjectNames.GetCommandName(DbCommandType.EnableConstraints, filter);
break;
case DbCommandType.BulkTableType:
command.CommandType = CommandType.StoredProcedure;
command.CommandText = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.BulkTableType, filter);
break;
case DbCommandType.UpdateRow:
case DbCommandType.InsertRow:
command.CommandType = CommandType.StoredProcedure;
command.CommandText = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.BulkUpdateRows, filter);
isBatch = true;
break;
case DbCommandType.DeleteRow:
command.CommandType = CommandType.StoredProcedure;
command.CommandText = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.BulkDeleteRows, filter);
isBatch = true;
break;
case DbCommandType.Reset:
command.CommandType = CommandType.StoredProcedure;
command.CommandText = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.Reset, filter);
break;
default:
return(null, false);
}
return(command, isBatch);
}
public override DbCommand GetCommand(DbCommandType commandType, SyncFilter filter = null)
{
var command = this.Connection.CreateCommand();
string text;
text = this.sqliteObjectNames.GetCommandName(commandType, filter);
// on Sqlite, everything is text :)
command.CommandType = CommandType.Text;
command.CommandText = text;
command.Connection = Connection;
if (Transaction != null)
{
command.Transaction = Transaction;
}
return(command);
}
public override DbCommand GetCommand(DbCommandType nameType, SyncFilter filter)
{
var command = this.Connection.CreateCommand() as NpgsqlCommand;
string text;
bool isStoredProc;
(text, isStoredProc) = this.sqlObjectNames.GetCommandName(nameType, filter);
command.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
command.CommandText = text;
command.Connection = Connection as NpgsqlConnection;
if (Transaction != null)
{
command.Transaction = Transaction as NpgsqlTransaction;
}
return(command);
}
public override DbCommand GetCommand(DbCommandType commandType, SyncFilter filter = null)
{
var command = this.Connection.CreateCommand();
string text;
bool isStoredProc;
(text, isStoredProc) = this.mySqlObjectNames.GetCommandName(commandType, filter);
var textName = ParserName.Parse(text, "`");
command.CommandType = isStoredProc ? CommandType.StoredProcedure : CommandType.Text;
command.CommandText = isStoredProc ? textName.Quoted().ToString() : text;
command.Connection = Connection;
if (Transaction != null)
{
command.Transaction = Transaction;
}
return(command);
}
private async Task DropProcedureAsync(DbCommandType procType, SyncFilter filter, DbConnection connection, DbTransaction transaction)
{
var commandName = this.mySqlObjectNames.GetCommandName(procType).name;
var commandText = $"drop procedure if exists {commandName}";
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
if (filter != null)
{
var commandNameWithFilter = this.mySqlObjectNames.GetCommandName(DbCommandType.SelectChangesWithFilters, filter).name;
var commandTextWithFilter = $"DROP PROCEDURE {commandNameWithFilter};";
using (var command = new MySqlCommand(commandTextWithFilter, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
}
/// <summary>
/// Returning null for all non used commands (from case default)
/// </summary>
public override (DbCommand, bool) GetCommand(DbCommandType nameType, SyncFilter filter)
{
var command = new MySqlCommand();
var isBatch = false;
switch (nameType)
{
case DbCommandType.UpdateRows:
case DbCommandType.UpdateRow:
command = CreateUpdateCommand();
break;
case DbCommandType.InsertRows:
command = CreateBulkInitializeCommand(this.bulkBatchMaxLinesCount);
isBatch = true;
break;
case DbCommandType.DeleteRow:
command = CreateDeleteCommand();
break;
case DbCommandType.DisableConstraints:
command.CommandType = CommandType.Text;
command.CommandText = this.MySqlObjectNames.GetCommandName(DbCommandType.DisableConstraints, filter);
break;
case DbCommandType.EnableConstraints:
command.CommandType = CommandType.Text;
command.CommandText = this.MySqlObjectNames.GetCommandName(DbCommandType.EnableConstraints, filter);
break;
default:
return(null, false);
}
return(command, isBatch);
}
/// <summary>
/// Set a stored procedure parameters
/// </summary>
public override async Task SetCommandParametersAsync(DbCommandType commandType, DbCommand command, SyncFilter filter = null)
{
if (command == null)
{
return;
}
if (command.Parameters != null && command.Parameters.Count > 0)
{
return;
}
// special case for constraint
if (commandType == DbCommandType.DisableConstraints || commandType == DbCommandType.EnableConstraints)
{
return;
}
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await this.connection.OpenAsync().ConfigureAwait(false);
}
if (this.transaction != null)
{
command.Transaction = this.transaction;
}
var textParser = ParserName.Parse(command.CommandText).Unquoted().Normalized().ToString();
var source = this.connection.Database;
textParser = $"{source}-{textParser}";
if (derivingParameters.ContainsKey(textParser))
{
foreach (var p in derivingParameters[textParser])
{
command.Parameters.Add(p.Clone());
}
}
else
{
NpgsqlCommandBuilder.DeriveParameters((NpgsqlCommand)command);
var arrayParameters = new List <NpgsqlParameter>();
foreach (var p in command.Parameters)
{
arrayParameters.Add(((NpgsqlParameter)p).Clone());
}
derivingParameters.TryAdd(textParser, arrayParameters);
}
if (command.Parameters.Count > 0 && command.Parameters[0].ParameterName == "@RETURN_VALUE")
{
command.Parameters.RemoveAt(0);
}
}
catch (Exception ex)
{
Debug.WriteLine($"DeriveParameters failed : {ex}");
throw;
}
finally
{
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
foreach (var parameter in command.Parameters)
{
var sqlParameter = (NpgsqlParameter)parameter;
// try to get the source column (from the SchemaTable)
var sqlParameterName = sqlParameter.ParameterName.Replace("@", "");
var colDesc = TableDescription.Columns.FirstOrDefault(c => c.ColumnName.Equals(sqlParameterName, SyncGlobalization.DataSourceStringComparison));
if (colDesc != null && !string.IsNullOrEmpty(colDesc.ColumnName))
{
sqlParameter.SourceColumn = colDesc.ColumnName;
}
}
}
//------------------------------------------------------------------
// Select changes command
//------------------------------------------------------------------
protected override SqlCommand BuildSelectIncrementalChangesCommand(SyncFilter filter)
{
var sqlCommand = new SqlCommand();
var pTimestamp = new SqlParameter("@sync_min_timestamp", SqlDbType.BigInt);
var pScopeId = new SqlParameter("@sync_scope_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add(pTimestamp);
sqlCommand.Parameters.Add(pScopeId);
// Add filter parameters
if (filter != null)
{
CreateFilterParameters(sqlCommand, filter);
}
var stringBuilder = new StringBuilder("");
stringBuilder.AppendLine($";WITH ");
stringBuilder.AppendLine($" {trackingName.Quoted().ToString()} AS (");
stringBuilder.Append($"\tSELECT ");
foreach (var pkColumn in this.tableDescription.PrimaryKeys)
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append($"[CT].{columnName}, ");
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tCAST([CT].[SYS_CHANGE_CONTEXT] as uniqueidentifier) AS [update_scope_id], ");
stringBuilder.AppendLine($"\t[CT].[SYS_CHANGE_VERSION] as [timestamp],");
stringBuilder.AppendLine($"\tCASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]");
stringBuilder.AppendLine($"\tFROM CHANGETABLE(CHANGES {tableName.Schema().Quoted().ToString()}, @sync_min_timestamp) AS [CT]");
stringBuilder.AppendLine($"\t)");
stringBuilder.AppendLine("SELECT DISTINCT");
foreach (var pkColumn in this.tableDescription.PrimaryKeys)
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t[side].{columnName}, ");
}
foreach (var mutableColumn in this.tableDescription.GetMutableColumns())
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t[base].{columnName}, ");
}
stringBuilder.AppendLine($"\t[side].[sync_row_is_tombstone], ");
stringBuilder.AppendLine($"\t[side].[update_scope_id] ");
stringBuilder.AppendLine($"FROM {tableName.Schema().Quoted().ToString()} [base]");
stringBuilder.Append($"RIGHT JOIN {trackingName.Quoted().ToString()} [side]");
stringBuilder.Append($"ON ");
string empty = "";
foreach (var pkColumn in this.tableDescription.PrimaryKeys)
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append($"{empty}[base].{columnName} = [side].{columnName}");
empty = " AND ";
}
// ----------------------------------
// Custom Joins
// ----------------------------------
if (filter != null)
{
stringBuilder.Append(CreateFilterCustomJoins(filter));
}
stringBuilder.AppendLine();
stringBuilder.AppendLine("WHERE (");
string str = string.Empty;
// ----------------------------------
// Where filters on [side]
// ----------------------------------
if (filter != null)
{
var createFilterWhereSide = CreateFilterWhereSide(filter, true);
stringBuilder.Append(createFilterWhereSide);
if (!string.IsNullOrEmpty(createFilterWhereSide))
{
stringBuilder.AppendLine($"AND ");
}
}
// ----------------------------------
// ----------------------------------
// Custom Where
// ----------------------------------
if (filter != null)
{
var createFilterCustomWheres = CreateFilterCustomWheres(filter);
stringBuilder.Append(createFilterCustomWheres);
if (!string.IsNullOrEmpty(createFilterCustomWheres))
{
stringBuilder.AppendLine($"AND ");
}
}
// ----------------------------------
stringBuilder.AppendLine("\t[side].[timestamp] > @sync_min_timestamp");
stringBuilder.AppendLine("\tAND ([side].[update_scope_id] <> @sync_scope_id OR [side].[update_scope_id] IS NULL)");
stringBuilder.AppendLine(")");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public override void SetCommandParameters(DbCommandType commandType, DbCommand command, SyncFilter filter = null)
{
switch (commandType)
{
case DbCommandType.SelectChanges:
case DbCommandType.SelectChangesWithFilters:
this.SetSelecteChangesParameters(command);
break;
case DbCommandType.SelectRow:
this.SetSelectRowParameters(command);
break;
case DbCommandType.DeleteMetadata:
this.SetDeleteMetadataParameters(command);
break;
case DbCommandType.DeleteRow:
this.SetDeleteRowParameters(command);
break;
case DbCommandType.UpdateRow:
this.SetUpdateRowParameters(command);
break;
case DbCommandType.Reset:
this.SetResetParameters(command);
break;
default:
break;
}
}
public override DbCommand GetCommand(DbCommandType commandType, SyncFilter filter = null) => new SqlCommand();
/// <summary>
/// Set a stored procedure parameters or text parameters
/// </summary>
public override async Task AddCommandParametersAsync(DbCommandType commandType, DbCommand command, DbConnection connection, DbTransaction transaction = null, SyncFilter filter = null)
{
if (command == null)
{
return;
}
if (command.Parameters != null && command.Parameters.Count > 0)
{
return;
}
// special case for constraint
if (commandType == DbCommandType.DisableConstraints || commandType == DbCommandType.EnableConstraints)
{
return;
}
// special case for UpdateMetadata
if (commandType == DbCommandType.UpdateMetadata)
{
this.SetUpdateRowParameters(command);
return;
}
// if we don't have stored procedure, return, because we don't want to derive parameters
if (command.CommandType != CommandType.StoredProcedure)
{
return;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.Transaction = transaction;
var textParser = ParserName.Parse(command.CommandText).Unquoted().Normalized().ToString();
var source = connection.Database;
textParser = $"{source}-{textParser}";
if (derivingParameters.ContainsKey(textParser))
{
foreach (var p in derivingParameters[textParser])
{
command.Parameters.Add(p.Clone());
}
}
else
{
// Using the SqlCommandBuilder.DeriveParameters() method is not working yet,
// because default value is not well done handled on the Dotmim.Sync framework
// TODO: Fix SqlCommandBuilder.DeriveParameters
//SqlCommandBuilder.DeriveParameters((SqlCommand)command);
await((SqlConnection)connection).DeriveParametersAsync((SqlCommand)command, false, (SqlTransaction)transaction).ConfigureAwait(false);
var arrayParameters = new List <SqlParameter>();
foreach (var p in command.Parameters)
{
arrayParameters.Add(((SqlParameter)p).Clone());
}
derivingParameters.TryAdd(textParser, arrayParameters);
}
if (command.Parameters.Count > 0 && command.Parameters[0].ParameterName == "@RETURN_VALUE")
{
command.Parameters.RemoveAt(0);
}
}
catch (Exception ex)
{
Debug.WriteLine($"DeriveParameters failed : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
foreach (var parameter in command.Parameters)
{
var sqlParameter = (SqlParameter)parameter;
// try to get the source column (from the SchemaTable)
var sqlParameterName = sqlParameter.ParameterName.Replace("@", "");
var colDesc = TableDescription.Columns.FirstOrDefault(c => c.ColumnName.Equals(sqlParameterName, SyncGlobalization.DataSourceStringComparison));
if (colDesc != null && !string.IsNullOrEmpty(colDesc.ColumnName))
{
sqlParameter.SourceColumn = colDesc.ColumnName;
}
}
}
public override Task AddCommandParametersAsync(DbCommandType commandType, DbCommand command, DbConnection connection, DbTransaction transaction = null, SyncFilter filter = null) => Task.CompletedTask;
private static SyncSet CreateSchema()
{
var set = new SyncSet();
set.StoredProceduresPrefix = "spp";
set.StoredProceduresSuffix = "sps";
set.TrackingTablesPrefix = "ttp";
set.TrackingTablesSuffix = "tts";
set.TriggersPrefix = "tp";
set.TriggersSuffix = "ts";
var tbl = new SyncTable("ServiceTickets", null);
tbl.OriginalProvider = "SqlServerProvider";
tbl.SyncDirection = Enumerations.SyncDirection.Bidirectional;
set.Tables.Add(tbl);
var c = SyncColumn.Create <int>("ServiceTicketID");
c.DbType = 8;
c.AllowDBNull = true;
c.IsAutoIncrement = true;
c.AutoIncrementStep = 1;
c.AutoIncrementSeed = 10;
c.IsCompute = false;
c.IsReadOnly = true;
tbl.Columns.Add(c);
tbl.Columns.Add(SyncColumn.Create <string>("Title"));
tbl.Columns.Add(SyncColumn.Create <string>("Description"));
tbl.Columns.Add(SyncColumn.Create <int>("StatusValue"));
tbl.Columns.Add(SyncColumn.Create <int>("EscalationLevel"));
tbl.Columns.Add(SyncColumn.Create <DateTime>("Opened"));
tbl.Columns.Add(SyncColumn.Create <DateTime>("Closed"));
tbl.Columns.Add(SyncColumn.Create <int>("CustomerID"));
tbl.PrimaryKeys.Add("ServiceTicketID");
// Add Second tables
var tbl2 = new SyncTable("Product", "SalesLT");
tbl2.SyncDirection = SyncDirection.UploadOnly;
tbl2.Columns.Add(SyncColumn.Create <int>("Id"));
tbl2.Columns.Add(SyncColumn.Create <string>("Title"));
tbl2.PrimaryKeys.Add("Id");
set.Tables.Add(tbl2);
// Add Filters
var sf = new SyncFilter("Product", "SalesLT");
sf.Parameters.Add(new SyncFilterParameter {
Name = "Title", DbType = DbType.String, MaxLength = 20, DefaultValue = "'Bikes'"
});
sf.Parameters.Add(new SyncFilterParameter {
Name = "LastName", TableName = "Customer", SchemaName = "SalesLT", AllowNull = true
});
sf.Wheres.Add(new SyncFilterWhereSideItem {
ColumnName = "Title", ParameterName = "Title", SchemaName = "SalesLT", TableName = "Product"
});
sf.Joins.Add(new SyncFilterJoin {
JoinEnum = Join.Right, TableName = "SalesLT.ProductCategory", LeftColumnName = "LCN", LeftTableName = "SalesLT.Product", RightColumnName = "RCN", RightTableName = "SalesLT.ProductCategory"
});
sf.CustomWheres.Add("1 = 1");
set.Filters.Add(sf);
// Add Relations
var keys = new[] { new SyncColumnIdentifier("ProductId", "ServiceTickets") };
var parentKeys = new[] { new SyncColumnIdentifier("ProductId", "Product", "SalesLT") };
var rel = new SyncRelation("AdventureWorks_Product_ServiceTickets", keys, parentKeys);
set.Relations.Add(rel);
return(set);
}
public override Task <DbCommand> GetDropStoredProcedureCommandAsync(DbStoredProcedureType storedProcedureType, SyncFilter filter, DbConnection connection, DbTransaction transaction) => Task.FromResult <DbCommand>(null);
public override Task AddCommandParametersAsync(DbCommandType commandType, DbCommand command, DbConnection connection, DbTransaction transaction = null, SyncFilter filter = null)
{
if (command == null)
{
return(Task.CompletedTask);
}
if (command.Parameters != null && command.Parameters.Count > 0)
{
return(Task.CompletedTask);
}
switch (commandType)
{
case DbCommandType.DeleteRow:
this.SetDeleteRowParameters(command);
return(Task.CompletedTask);
case DbCommandType.UpdateRow:
case DbCommandType.InsertRow:
this.SetUpdateRowParameters(command);
return(Task.CompletedTask);
default:
break;
}
return(base.AddCommandParametersAsync(commandType, command, connection, transaction, filter));
}
