public Task Upload(WebPage webpage)
{
// 1. wait for previous operation (either CREATE TABLE or EXEC p_ActionWebPage) to complete, then perform TRUNCATE
//await DoAsync(truncateCmd, truncateCmd.ExecuteNonQueryAsync).ConfigureAwait(true); // trash all our #WebPagesStaging table data at Sql Server
AdoWip.WaitBombIf(); // wait for previous operation (e.g. "CREATE TABLE #WebpagesStaging" or "exec dbo.p_ActionWebPage")
#if WIP
LastAdoCmd = AdoWipEnum.Truncate;
AdoWip = Policy.ExecuteAsync(() => truncateCmd.ExecuteNonQueryAsync()); // TRUNCATE TABLE must complete before we can do BULK INSERT
// 2. wait for TRUNCATE, then perform BULK INSERT to upload into #Staging
AdoWip.WaitBombIf(); // wait for previous TRUNCATE operation to complete
LastAdoCmd = AdoWipEnum.Bulk;
AdoWip = _bulk.WriteToServerAsync(dataCaches[ActiveData]); // upload current batch [but no Polly retry as not idempotent and atomic]
#else
AdoWip = Policy.ExecuteAsync(() => // idempotent : if BULKINSERT fails then re-reun TRUNCATE
truncateCmd.ExecuteNonQueryAsync() // TRUNCATE TABLE #WebpagesStaging
.ContinueWith(t => _bulk.WriteToServerAsync(dataCaches[ActiveData]), TaskContinuationOptions.OnlyOnRanToCompletion));
#endif
// 3. advance pointer so caller can continue
ActiveData = ++ActiveData % CACHELEN; // round-robin advance to next DataTable
dataCaches[ActiveData].Clear(); // hose all local data from any previous operation
// the caller is now free to re-use this zeroed dataCache (i.e. invoke AddDataRow)
return(AdoWip);
}
public BulkRepository(Webstore.WebModel dbctx, IAsyncPolicy retryPolicy)
{
Policy = retryPolicy;
Debug.Assert(stagingNames.Length == (int)Staging_enum.NumberOfColumns &&
stagingTypes.Length == (int)Staging_enum.NumberOfColumns,
"Staging_enum metadata is incorrect");
Debug.Assert(p_ActionWebPageParams.Length == (int)Action_enum.NumberOfColumns, "Action_enum metadata is incorrect");
//EF component (single context, but SaveChangesAsync after request[I] can overlap request[I+1] doing its Downloader work in parallel
EfDomain = dbctx;
// var ObjCtx = (EfDomain as IObjectContextAdapter).ObjectContext;
// ObjCtx.SavingChanges += OnSavingChanges;
// ADO component
var csb = new SqlConnectionStringBuilder(EfDomain.Database.Connection.ConnectionString)
{
ApplicationName = "DICKBULKSPROC",
// AsynchronousProcessing = false,
ConnectRetryCount = 10,
ConnectRetryInterval = 2,
ConnectTimeout = 60,
MultipleActiveResultSets = false,
Pooling = false
};
_conn = new SqlConnection(csb.ConnectionString); // independent SPID so EF & ADO can free-run
truncateCmd = new SqlCommand("truncate table " + TGTTABLE, _conn);
addLinksCmd = new SqlCommand("exec dbo.p_ActionWebPage @PageId,@Url", _conn);
addLinksCmd.Parameters.AddRange(p_ActionWebPageParams);
_bulk = new SqlBulkCopy(_conn)
{
DestinationTableName = TGTTABLE, BatchSize = 500, BulkCopyTimeout = 45
};
#if WIP
LastAdoCmd = AdoWipEnum.Open;
#endif
AdoWip = (_conn.State != ConnectionState.Open)
? Policy.ExecuteAsync(() => _conn.OpenAsync()) // start the OPEN handshake async so we can do setup stuff in parallel
: Task.FromResult <bool>(true);
dataCaches[0] = MakeStagingTable(); // does not need _conn to be open yet run
for (var i = 1; i < CACHELEN; i++)
{
dataCaches[i] = dataCaches[0].Clone(); // copy structure (but not data)
}
// wait for OPEN then create remote table on SQL asynchronously
AdoWip = AdoWip.ContinueWith(
t => Policy.ExecuteAsync(() => CreateStagingAsync()),
TaskContinuationOptions.OnlyOnRanToCompletion | TaskContinuationOptions.ExecuteSynchronously);
}
/// <summary>
/// execute the p_ActionWebPage sproc to upsert the data in #Staging table
/// </summary>
/// Upload method has already uploaded the data into #Staging table, but deferred until after SaveChanges completes
/// to ensure redirect WebPage has been persisted (i.e. nz PageId)
/// <remarks>
/// 1. .Wait for previous BULK INSERT operation to complete
/// 2. prepare sproc params (now the PageId value has been assigned by Sql+EF)
/// 3. exec dbo.p_ActionWebPage sproc
/// </remarks>
void SaveLinks(WebPage webpage)
{
AdoWip.WaitBombIf(); // wait for parallel ADO to quiesce (e.g. BULKINSERT)
p_ActionWebPageParams[(int)Action_enum.PageId].Value = webpage.PageId;
p_ActionWebPageParams[(int)Action_enum.Url].Value = webpage.Url;
#if WIP
LastAdoCmd = AdoWipEnum.Action;
#endif
// code is idempotent safe as sproc checks if Depends row pre-exists
AdoWip = Policy.ExecuteAsync(() => addLinksCmd.ExecuteNonQueryAsync()); // import #WebStaging data into WebPages and Depends tables
//AdoWip.WaitBombIf(); // wait for parallel ADO to quiesce (e.g. p_ActionWebPage sproc) TODO: double-check ?
// caller can now populate next buffer in the ring (whilst SqlServer runs sproc)
}
Task CreateStagingAsync() // EF will OPEN() then initiate [but DON'T WAIT for] CREATE
{
#if WIP
LastAdoCmd = AdoWipEnum.CreateStaging;
#endif
#pragma warning disable CA2100 // "Review SQL queries for security vulnerabilities" has been done
var createCmd = new SqlCommand(
$"DROP TABLE IF EXISTS {TGTTABLE};\n" + // drop any pre-existing table (allow Polly to be idempotent)
$"CREATE TABLE {TGTTABLE}\n" +
$"(\t[Url]\t\tnvarchar({WebPage.URLSIZE})\t{COLLATION}\tNOT NULL\tPRIMARY KEY,\n" + // N.B. PKCI on Url for Staging (noPageId column here)
$"\tDraftFilespec\tnvarchar({WebPage.FILESIZE})\t{COLLATION}\tNULL)", _conn);
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
return(createCmd.ExecuteNonQueryAsync());
}