It turns out that Disabling/Rebuilding the Indexes does work and works within a transaction too, so I added these methods to DataAccessAdapter:
public void DisableIndex(string indexName, string tableName)
{
var commandString = CreateIndexCommandString(indexName, tableName, "DISABLE");
ExecuteSqlScript(commandString);
}
public void RebuildIndex(string indexName, string tableName)
{
var commandString = CreateIndexCommandString(indexName, tableName, "REBUILD");
ExecuteSqlScript(commandString);
}
string CreateIndexCommandString(string indexName, string tableName, string action)
{
return $"USE [{(CatalogNameToUse.Length == 0 ? "TADS" : CatalogNameToUse)}]\r\n" +
$"ALTER INDEX [{indexName}] ON [{tableName}] {action};\r\n";
}
and do my save like this..
(you have to specify individual Index names rather than use ALL otherwise the clustered index gets disabled and nothing works!)
static readonly string[] UniqueIndexNames = { "UC_CitiCode", "UC_FLIBMatch", "UC_FLMatch", "UC_ISIN", "UC_MEX", "UC_SEDOL", "UC_TNMatch" };
using (var adapter = new DataAccessAdapter())
{
adapter.StartTransaction(IsolationLevel.Serializable, "X");
UniqueIndexNames.Visit(uin => adapter.DisableIndex(uin, "Fund"));
updateCount += adapter.SaveEntityCollection(allFundsAndShares, false, false);
updateCount += adapter.SaveEntityCollection(Providers, false, true);
UniqueIndexNames.Visit(uin => adapter.RebuildIndex(uin, "Fund"));
Debugger.Break();
adapter.Commit();
}
Not something for every use I guess but this works for me because I'm the only user of this particular database and doing bulk updates periodically.