private static void InsertRow(Table sourceTable, Table targetTable, SqlDataReader reader, SqlConnection targetDatabaseConnection, SqlTransaction transaction, string identityInsertSetup, ISqlCommandFactory commandFactory)
{
SqlCommand insertCommand = commandFactory.CreateSqlCommand("", targetDatabaseConnection, transaction);
string insertCommandText = $@"USE [{targetTable.Database}]
{identityInsertSetup}
INSERT INTO {targetTable.Schema}.{targetTable.Name} ( ";
for (int i = 0; i < sourceTable.Columns.Length; i++)
insertCommandText += $"[{sourceTable.Columns[i].Name}] {((i < sourceTable.Columns.Length - 1) ? "," : ") VALUES (")}";
for (int i = 0; i < sourceTable.Columns.Length; i++)
{
string paramName = $"prm{i}";
insertCommandText += $"@{paramName} {((i < sourceTable.Columns.Length - 1) ? "," : ")")}";
insertCommand.Parameters.AddWithValue(paramName, reader[sourceTable.Columns[i].Name]);
}
insertCommand.CommandText = insertCommandText;
if (insertCommand.ExecuteNonQuery() != 1)
throw new ReplicationException("Replication error: Failed to insert row into target database");
}
private static int ExecuteNonQuerySqlCommand(string sqlCommandText, SqlConnection targetDatabaseConnection, SqlTransaction transaction, ISqlCommandFactory commandFactory)
{
SqlCommand deleteForeignKeysConstraintsCmd = commandFactory.CreateSqlCommand(sqlCommandText, targetDatabaseConnection);
deleteForeignKeysConstraintsCmd.Transaction = transaction;
return deleteForeignKeysConstraintsCmd.ExecuteNonQuery();
}