public RecordDataPage(RecordDataReader parent)
{
this.parent = parent;
}
public RecordDataPage(RecordDataReader parent)
{
this.parent = parent;
}
public static async Task <int> InsertIntoSqlDbAsync(IEnumerable <UsageRecord> usageRecords, Guid subscriptionId, DateTime startDate, DateTime endDate, CancellationToken token = default(CancellationToken))
{
int recordCount;
DateTime startTime;
TimeSpan processingTime;
SqlConnection connection = new SqlConnection(SqlConnectionString);
// note: it is important to specify at least repeatableread transaction isolation level - otherwise other transaction could simultaneously manipulate same record range
SqlCommand deleteUsageRecordCommand = new SqlCommand(@"delete from dbo.AzureUsageRecords with(repeatableread, rowlock)
where SubscriptionId = @subscriptionId and UsageStartTime = @usageStartTime and UsageEndTime = @usageEndTime" , connection);
deleteUsageRecordCommand.CommandType = CommandType.Text;
var deleteParameters = deleteUsageRecordCommand.Parameters;
deleteParameters.Add("@subscriptionId", SqlDbType.UniqueIdentifier).Value = subscriptionId;
deleteParameters.Add("@usageStartTime", SqlDbType.DateTime2).Value = startDate;
deleteParameters.Add("@usageEndTime", SqlDbType.DateTime2).Value = endDate;
SqlTransaction transaction = null;
try {
connection.Open();
transaction = connection.BeginTransaction();
deleteUsageRecordCommand.Transaction = transaction;
recordCount = deleteUsageRecordCommand.ExecuteNonQuery();
transaction.Commit();
if (recordCount > 0)
{
Trace.TraceInformation($"{recordCount} existing record(s) deleted");
}
} catch {
transaction?.Rollback();
} finally {
transaction?.Dispose();
connection.Close();
connection.Dispose();
}
SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlConnectionString, SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.TableLock);
bulkCopy.DestinationTableName = "dbo.AzureUsageRecords";
bulkCopy.BatchSize = 500;
bulkCopy.NotifyAfter = 1000;
bulkCopy.BulkCopyTimeout = 30;
bulkCopy.SqlRowsCopied += BulkCopy_SqlRowsCopied;
startTime = DateTime.UtcNow;
try {
using (RecordDataReader <UsageRecord> recReader = new RecordDataReader <UsageRecord>(usageRecords, x => { return(Sink(x, startDate.AddDays(-1), endDate)); })) {
try {
await bulkCopy.WriteToServerAsync(recReader, token).ConfigureAwait(false);
} catch (Exception ex) {
throw;
}
recordCount = recReader.RecordsAffected;
}
} finally {
bulkCopy.Close();
}
processingTime = DateTime.UtcNow.Subtract(startTime);
return(recordCount);
}
