private static void MergeShardletAndDropTempTables(ShardConnection destinationConnection,
string uniqueProcessString)
{
using (var destinationSqlConnection = new ReliableSqlConnection(destinationConnection.ConnectionString))
{
// sync the header, detail and map data, drop the temp tables
destinationSqlConnection.Open();
// todo: need to check rows affected and return error if none (same on all updates in this method)
MergeSalesOrderHeaders(uniqueProcessString, destinationSqlConnection);
MergeSalesOrderDetails(uniqueProcessString, destinationSqlConnection);
MergeShoppingCartItems(uniqueProcessString, destinationSqlConnection);
// drop the temporary table data
var dropTempTablesCommand =
new SqlCommand("[Shardlets].[DropShardletSalesOrder]", destinationSqlConnection.Current)
{
CommandType = CommandType.StoredProcedure
};
dropTempTablesCommand.Parameters.Add(CreateUniqueProcessIDParameter(uniqueProcessString));
dropTempTablesCommand.ExecuteNonQuery();
}
}
/// <summary>
/// Initializes a new instance of the <see cref="DacPacPublisher"/> class.
/// </summary>
/// <param name="parameters">The dacpac publisher parameters.</param>
public DacPacPublisher(DacPacPublisherParams parameters)
{
_shardSetConfig = parameters.ShardSetConfig;
_shardUserName = parameters.ShardUserName;
_shardUserPassword = parameters.ShardUserPassword;
// create connection object to the shard instance, shard catalog with admin credentials
_shardCatalogConnection =
new ShardConnection
{
ServerInstanceName = parameters.Shard.ServerInstanceName,
Catalog = parameters.Shard.Catalog,
UserName = parameters.ShardAdminUserName,
Password = parameters.ShardAdminPassword,
ShardSetName = parameters.ShardSetConfig.ShardSetName
};
// create connection object to the shard instance, master catalog with admin credentials
_masterCatalogConnection =
new ShardConnection
{
ServerInstanceName = parameters.Shard.ServerInstanceName,
Catalog = "master",
UserName = parameters.ShardAdminUserName,
Password = parameters.ShardAdminPassword,
ShardSetName = parameters.ShardSetConfig.ShardSetName
};
}
public void Publish(ShardBase shard, ShardSetConfig shardSetConfig, string shardUserName,
string shardUserPassword, string sql, CommandType commandType)
{
var shardConnection =
new ShardConnection
{
ServerInstanceName = shard.ServerInstanceName,
Catalog = shard.Catalog,
UserName = shardUserName,
Password = shardUserPassword,
ShardSetName = shardSetConfig.ShardSetName
};
using (var sqlConnection = GetReliableConnection(shardConnection.ConnectionString))
{
try
{
//open the connection
sqlConnection.Open();
// execute the command
var sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandText = sql;
sqlCommand.CommandType = commandType;
sqlCommand.ExecuteNonQuery();
}
catch (Exception)
{
// todo: Add Logging
throw;
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="ReferenceTableUpdater" /> class.
/// </summary>
/// <param name="referenceDataConnectionString">The reference data connection string.</param>
/// <param name="shard">The shard to update.</param>
/// <param name="settings">The settings.</param>
/// <param name="tableGroupName">Name of the shard set.</param>
public ReferenceTableUpdater(string referenceDataConnectionString, ShardBase shard, Settings settings, string tableGroupName)
{
_referenceDataConnectionString = referenceDataConnectionString;
_shardConnection =
new ShardConnection
{
ServerInstanceName = shard.ServerInstanceName,
Catalog = shard.Catalog,
UserName = settings.AdminUser,
Password = settings.AdminPassword,
ShardSetName = tableGroupName
};
}
private static void CopyShardletIntoTempTables(string shardingKey, ShardConnection sourceConnection,
ShardConnection destinationConnection, string uniqueProcessString)
{
// copy the shardlet data to the temporary tables
var copier = new BulkCopier(sourceConnection.ConnectionString, destinationConnection.ConnectionString);
using (var sourceSqlConnection = new ReliableSqlConnection(sourceConnection.ConnectionString))
{
sourceSqlConnection.Open();
CopySalesOrderHeadersIntoTempTable(shardingKey, uniqueProcessString, sourceSqlConnection, copier);
CopySalesOrderDetailsIntoTempTable(shardingKey, uniqueProcessString, sourceSqlConnection, copier);
CopyShoppingCartItemsToTempTable(shardingKey, uniqueProcessString, sourceSqlConnection, copier);
}
}
private static void ResetTempTables(string uniqueProcessString, ShardConnection destinationConnection)
{
// reset the temporary tables in the target for the current process
using (var destinationSqlConnection = new ReliableSqlConnection(destinationConnection.ConnectionString))
{
destinationSqlConnection.Open();
var command =
new SqlCommand("[Shardlets].[ResetShardletCustomer]", destinationSqlConnection.Current)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters.Add(CreateUniqueProcessIDParameter(uniqueProcessString));
command.ExecuteNonQuery();
}
}
private static void AddTestShardlets(ShardBase shard, ShardSetConfig shardSetConfig,
int initialTestCustomerID, int numberOfTestCustomers, int numberOfTestOrders, int numberOfTestSalesLineNums)
{
var shardCatalogConnection =
new ShardConnection
{
ServerInstanceName = shard.ServerInstanceName,
Catalog = shard.Catalog,
UserName = "******",
Password = "******",
ShardSetName = shardSetConfig.ShardSetName
};
var builder = new TestDataBuilder(GetReferenceConnectionString());
builder.AddTestSalesOrdersInDatabase(shardCatalogConnection.ConnectionString, initialTestCustomerID, numberOfTestCustomers, numberOfTestOrders);
builder.AddTestShoppingCartItemsInDatabase(shardCatalogConnection.ConnectionString, initialTestCustomerID, numberOfTestCustomers, numberOfTestSalesLineNums);
}
