private void InsertItems(string tableName, string pkName, List <ItemToReplicate> dataForTable, Action <DbCommand> commandCallback = null)
{
var sqlReplicationTableMetrics = sqlReplicationMetrics.GetTableMetrics(tableName);
var replicationInsertActionsMetrics = sqlReplicationTableMetrics.SqlReplicationInsertActionsMeter;
var replicationInsertActionsHistogram = sqlReplicationTableMetrics.SqlReplicationInsertActionsHistogram;
var replicationInsertDurationHistogram = sqlReplicationTableMetrics.SqlReplicationInsertActionsDurationHistogram;
var sp = new Stopwatch();
foreach (var itemToReplicate in dataForTable)
{
sp.Restart();
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
var sb = new StringBuilder("INSERT INTO ")
.Append(GetTableNameString(tableName))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(pkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == pkName)
{
continue;
}
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, pkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, pkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == pkName)
{
continue;
}
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value, stringParserList);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile)
{
sb.Append(" OPTION(RECOMPILE)");
}
var stmt = sb.ToString();
cmd.CommandText = stmt;
if (commandCallback != null)
{
commandCallback(cmd);
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException(
"Failure to replicate changes to relational database for: " + cfg.Name + " (doc: " + itemToReplicate.DocumentId + " ), will continue trying." +
Environment.NewLine + cmd.CommandText, e);
replicationStatistics.RecordWriteError(e, database);
hadErrors = true;
}
finally
{
sp.Stop();
var elapsedMiliseconds = sp.ElapsedMilliseconds;
if (log.IsDebugEnabled)
{
log.Debug(string.Format("Insert took: {0}ms, statement: {1}", elapsedMiliseconds, stmt));
}
var elapsedMicroseconds = (long)(sp.ElapsedTicks * SystemTime.MicroSecPerTick);
replicationInsertDurationHistogram.Update(elapsedMicroseconds);
replicationInsertActionsMetrics.Mark(1);
replicationInsertActionsHistogram.Update(1);
if (elapsedMiliseconds > LongStatementWarnThresholdInMiliseconds)
{
HandleSlowSql(elapsedMiliseconds, stmt);
}
}
}
}
}
private void InsertItems(string tableName, string pkName, List <ItemToReplicate> dataForTable)
{
foreach (var itemToReplicate in dataForTable)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var sb = new StringBuilder("INSERT INTO ")
.Append(commandBuilder.QuoteIdentifier(tableName))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(pkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == pkName)
{
continue;
}
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, pkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, pkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == pkName)
{
continue;
}
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException(
"Failure to replicate changes to relational database for: " + cfg.Name + " (doc: " + itemToReplicate.DocumentId + " ), will continue trying." +
Environment.NewLine + cmd.CommandText, e);
replicationStatistics.RecordWriteError(e, database);
hadErrors = true;
}
}
}
}
private bool WriteToRelationalDatabase(SqlReplicationConfig cfg, DbProviderFactory providerFactory, Dictionary<string, List<ItemToReplicate>> dictionary, SqlReplicationStatistics replicationStatistics)
{
using (var commandBuilder = providerFactory.CreateCommandBuilder())
using (var connection = providerFactory.CreateConnection())
{
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
Database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
bool hadErrors = false;
using (var tx = connection.BeginTransaction())
{
foreach (var kvp in dictionary)
{
// first, delete all the rows that might already exist there
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
cmd.Parameters.Add(dbParameter);
dbParameter.Value = itemToReplicate.DocumentId;
cmd.CommandText = string.Format("DELETE FROM {0} WHERE {1} = {2}",
commandBuilder.QuoteIdentifier(kvp.Key),
commandBuilder.QuoteIdentifier(itemToReplicate.PkName),
dbParameter.ParameterName
);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name + ", will continue trying." + Environment.NewLine + cmd.CommandText, e);
replicationStatistics.RecordWriteError(e, Database);
hadErrors = true;
}
}
}
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var sb = new StringBuilder("INSERT INTO ")
.Append(commandBuilder.QuoteIdentifier(kvp.Key))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name + ", will continue trying." + Environment.NewLine + cmd.CommandText, e);
replicationStatistics.RecordWriteError(e, Database);
hadErrors = true;
}
}
}
}
tx.Commit();
}
return hadErrors == false;
}
}
private bool WriteToRelationalDatabase(SqlReplicationConfig cfg, DbProviderFactory providerFactory, Dictionary<string, List<ItemToReplicate>> dictionary, SqlReplicationStatistics replicationStatistics)
{
using (var commandBuilder = providerFactory.CreateCommandBuilder())
using (var connection = providerFactory.CreateConnection())
{
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
Database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
bool hadErrors = false;
using (var tx = connection.BeginTransaction())
{
const string cmdText = "DELETE FROM {0} WHERE {1} IN ({2})";
foreach (var kvp in dictionary)
{
// array of ids to delete
List<string> ids = kvp.Value.Select(x => x.DocumentId).ToList();
foreach (var partitionOfIds in ids.Partition(1000))
{
using (var deleteCmd = connection.CreateCommand())
{
// create an array of param names to store our ids (@id1, @id2 etc)
string[] paramNames = partitionOfIds.Select(
(s, i) => "@id" + i.ToString()
).ToArray();
// add a parameter for each id to our command
string inClause = string.Join(",", paramNames);
for (int i = 0; i < paramNames.Length; i++)
{
var param = deleteCmd.CreateParameter();
param.ParameterName = paramNames[i];
param.Value = partitionOfIds[i];
deleteCmd.Parameters.Add(param);
}
deleteCmd.Transaction = tx;
deleteCmd.CommandText = string.Format(cmdText,
commandBuilder.QuoteIdentifier(kvp.Key),
commandBuilder.QuoteIdentifier(
kvp.Value[0].PkName),
inClause);
try
{
deleteCmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException(
"Failure to replicate changes to relational database for: " + cfg.Name +
" (delete), will continue trying." + Environment.NewLine +
deleteCmd.CommandText, e);
replicationStatistics.RecordWriteError(e, Database);
hadErrors = true;
}
}
}
foreach (var itemToReplicate in kvp.Value)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
var sb = new StringBuilder("INSERT INTO ")
.Append(commandBuilder.QuoteIdentifier(kvp.Key))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, itemToReplicate.PkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == itemToReplicate.PkName)
continue;
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.WarnException("Failure to replicate changes to relational database for: " + cfg.Name + " (insert), will continue trying." + Environment.NewLine + cmd.CommandText, e);
replicationStatistics.RecordWriteError(e, Database);
hadErrors = true;
}
}
}
}
tx.Commit();
}
return hadErrors == false;
}
}
private IEnumerable <DbCommand> InsertItems(string tableName, string pkName, List <ItemToReplicate> dataForTable, bool exportCommands = false)
{
foreach (var itemToReplicate in dataForTable)
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
database.WorkContext.CancellationToken.ThrowIfCancellationRequested();
var sb = new StringBuilder("INSERT INTO ")
.Append(GetTableNameString(tableName))
.Append(" (")
.Append(commandBuilder.QuoteIdentifier(pkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == pkName)
{
continue;
}
sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, pkName);
pkParam.Value = itemToReplicate.DocumentId;
cmd.Parameters.Add(pkParam);
sb.Append(") \r\nVALUES (")
.Append(GetParameterName(providerFactory, commandBuilder, pkName))
.Append(", ");
foreach (var column in itemToReplicate.Columns)
{
if (column.Key == pkName)
{
continue;
}
var colParam = cmd.CreateParameter();
colParam.ParameterName = column.Key;
SetParamValue(colParam, column.Value, stringParserList);
cmd.Parameters.Add(colParam);
sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile)
{
sb.Append(" OPTION(RECOMPILE)");
}
cmd.CommandText = sb.ToString();
if (exportCommands)
{
yield return(cmd);
}
try
{
cmd.ExecuteNonQuery();;
}
catch (Exception e)
{
log.WarnException(
"Failure to replicate changes to relational database for: " + cfg.Name + " (doc: " + itemToReplicate.DocumentId + " ), will continue trying." +
Environment.NewLine + cmd.CommandText, e);
replicationStatistics.RecordWriteError(e, database);
hadErrors = true;
}
}
}
}
