We have a import / bulk copy batch that runs in the middle of the night, something like
using (var tx = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromMinutes(1)))
{
//Delete existing
...
//Bulk copy to temp table
...
//Insert
...
tx.Complete();
}
We also have a cache that uses SqlDependency. But it triggers allready on the Delete statement making the Cache empty for a few seconds between the delete nad the reinsert. Can I configure SqlDependency to only listen to commited data?
SqlDep. code
private IEnumerable<TEntity> RegisterAndFetch(IBusinessContext context)
{
var dependency = new SqlDependency();
dependency.OnChange += OnDependencyChanged;
try
{
CallContext.SetData("MS.SqlDependencyCookie", dependency.Id);
var refreshed = OnCacheRefresh(context, data.AsEnumerable());
var result = refreshed.ToArray();
System.Diagnostics.Debug.WriteLine("{0} - CacheChanged<{1}>: {2}", DateTime.Now, typeof(TEntity).Name, result.Length);
return result;
}
finally
{
CallContext.SetData("MS.SqlDependencyCookie", null);
}
}
OnDependencyChanged basily calls above RegisterAndFetch method
Query:
protected override IEnumerable<BankHoliday> OnCacheRefresh(IBusinessContext context, IEnumerable<BankHoliday> currentData)
{
var firstOfMonth = DateTime.Now.FirstDayOfMonth(); // <-- Must be a constant in the query
return context.QueryOn<BankHoliday>().Where(bh => bh.Holiday >= firstOfMonth);
}
