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