public async Task BaseOrchestrator_StoredProcedures_ShouldCreate()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" })
{
StoredProceduresPrefix = "sp_",
StoredProceduresSuffix = "_sp"
};
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
await localOrchestrator.CreateStoredProceduresAsync(setup.Tables["Product", "SalesLT"]);
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.BulkDeleteRows));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.BulkTableType));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.BulkUpdateRows));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.DeleteMetadata));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.DeleteRow));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.Reset));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChanges));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectInitializedChanges));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectRow));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.UpdateRow));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChangesWithFilters));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectInitializedChangesWithFilters));
// Adding a filter to check if stored procedures "with filters" are also generated
setup.Filters.Add("Product", "ProductCategoryID", "SalesLT");
await localOrchestrator.CreateStoredProceduresAsync(setup.Tables["Product", "SalesLT"], false);
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChangesWithFilters));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectInitializedChangesWithFilters));
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task StoredProcedure_Drop_All()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnStoredProcedureCreating(tca => onCreating++);
localOrchestrator.OnStoredProcedureCreated(tca => onCreated++);
localOrchestrator.OnStoredProcedureDropping(tca => onDropping++);
localOrchestrator.OnStoredProcedureDropped(tca => onDropped++);
var isCreated = await localOrchestrator.CreateStoredProceduresAsync(setup.Tables["Product", "SalesLT"]);
Assert.True(isCreated);
Assert.Equal(10, onCreating);
Assert.Equal(10, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
onCreating = 0;
onCreated = 0;
onDropping = 0;
onDropped = 0;
var isDropped = await localOrchestrator.DropStoredProceduresAsync(setup.Tables["Product", "SalesLT"]);
Assert.True(isCreated);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(10, onDropping);
Assert.Equal(10, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task StoredProcedure_Create_All()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnStoredProcedureCreating(tca => onCreating++);
localOrchestrator.OnStoredProcedureCreated(tca => onCreated++);
localOrchestrator.OnStoredProcedureDropping(tca => onDropping++);
localOrchestrator.OnStoredProcedureDropped(tca => onDropped++);
var isCreated = await localOrchestrator.CreateStoredProceduresAsync(setup.Tables["Product", "SalesLT"]);
Assert.True(isCreated);
Assert.Equal(10, onCreating);
Assert.Equal(10, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_bulkdelete").ConfigureAwait(false);
Assert.True(check);
check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_bulkupdate").ConfigureAwait(false);
Assert.True(check);
check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_changes").ConfigureAwait(false);
Assert.True(check);
check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_delete").ConfigureAwait(false);
Assert.True(check);
check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_deletemetadata").ConfigureAwait(false);
Assert.True(check);
check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_initialize").ConfigureAwait(false);
Assert.True(check);
check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_reset").ConfigureAwait(false);
Assert.True(check);
check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_selectrow").ConfigureAwait(false);
Assert.True(check);
check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_update").ConfigureAwait(false);
Assert.True(check);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
