public async Task EnumerateDataAsync(string dataType, DateTime startTime, DateTime endTimeExclusive,
Action <IStatisticalDataRecord> aggregatorCallback,
CancellationToken cancellation)
{
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(EnumerateDataScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
ctx.CreateParameter("@StartTime", DbType.DateTime2, startTime),
ctx.CreateParameter("@EndTimeExclusive", DbType.DateTime2, endTimeExclusive),
});
}, async (reader, cancel) =>
{
while (await reader.ReadAsync(cancel))
{
cancel.ThrowIfCancellationRequested();
var item = GetStatisticalDataRecordFromReader(reader);
aggregatorCallback(item);
}
return(true);
}).ConfigureAwait(false);
}
}
public async Task <IEnumerable <Aggregation> > LoadAggregatedUsageAsync(string dataType, TimeResolution resolution, DateTime startTime, DateTime endTimeExclusive,
CancellationToken cancellation)
{
var aggregations = new List <Aggregation>();
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(LoadAggregatedUsageScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
ctx.CreateParameter("@Resolution", DbType.String, resolution.ToString()),
ctx.CreateParameter("@StartTime", DbType.DateTime2, startTime),
ctx.CreateParameter("@EndTimeExclusive", DbType.DateTime2, endTimeExclusive),
});
}, async (reader, cancel) => {
while (await reader.ReadAsync(cancel))
{
aggregations.Add(GetAggregationFromReader(reader));
}
return(true);
}).ConfigureAwait(false);
}
return(aggregations);
}
public async Task CleanupRecordsAsync(string dataType, DateTime retentionTime, CancellationToken cancellation)
{
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteNonQueryAsync(CleanupRecordsScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", SqlDbType.NVarChar, 50, dataType),
ctx.CreateParameter("@RetentionTime", SqlDbType.DateTime2, retentionTime),
});
}).ConfigureAwait(false);
}
}
protected virtual async Task <byte[][]> GetRawDataAsync(int fileId)
{
using (var ctx = new MsSqlDataContext(RepositoryConnectionString, new DataOptions(), CancellationToken.None))
{
var script = "SELECT [Stream] FROM Files WHERE FileId = @FileId";
var scalar = await ctx.ExecuteScalarAsync(script, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@FileId", SqlDbType.Int, fileId)
});
}).ConfigureAwait(false);
if (scalar == DBNull.Value)
{
return(new byte[0][]);
}
var buffer = (byte[])scalar;
if (buffer.Length == 0)
{
return(new byte[0][]);
}
return(new [] { buffer });
}
}
public async Task CleanupAggregationsAsync(string dataType, TimeResolution resolution, DateTime retentionTime,
CancellationToken cancellation)
{
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteNonQueryAsync(CleanupAggregationsScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
ctx.CreateParameter("@Resolution", DbType.AnsiString, resolution.ToString()),
ctx.CreateParameter("@RetentionTime", SqlDbType.DateTime2, retentionTime),
});
}).ConfigureAwait(false);
}
}
public async Task WriteAggregationAsync(Aggregation aggregation, CancellationToken cancellation)
{
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteNonQueryAsync(WriteAggregationScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, aggregation.DataType),
ctx.CreateParameter("@Resolution", DbType.String, aggregation.Resolution.ToString()),
ctx.CreateParameter("@Date", DbType.DateTime2, aggregation.Date),
ctx.CreateParameter("@Data", DbType.String, (object)aggregation.Data ?? DBNull.Value),
});
}).ConfigureAwait(false);
}
}
public async Task <DateTime?[]> LoadFirstAggregationTimesByResolutionsAsync(string dataType, CancellationToken cancellation)
{
var result = new DateTime?[4];
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(LoadFirstAggregationTimesByResolutionsScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
});
}, async (reader, cancel) => {
if (await reader.ReadAsync(cancel))
{
result[0] = reader.GetDateTimeUtcOrNull("Minute");
result[1] = reader.GetDateTimeUtcOrNull("Hour");
result[2] = reader.GetDateTimeUtcOrNull("Day");
result[3] = reader.GetDateTimeUtcOrNull("Month");
}
return(true);
}).ConfigureAwait(false);
}
return(result.ToArray());
}
private async Tasks.Task SaveSecretAsync(string clientId, ClientSecret secret, bool deleteBefore,
MsSqlDataContext ctx)
{
var sql = deleteBefore
? @"-- MsSqlClientStoreDataProvider.SaveSecret (delete and insert)
DELETE FROM [ClientSecrets] WHERE Id = @Id
INSERT INTO [ClientSecrets] ([Id], [ClientId], [Value], [CreationDate], [ValidTill])
VALUES( @Id, @ClientId, @Value, @CreationDate, @ValidTill)
"
: @"-- MsSqlClientStoreDataProvider.SaveSecret (insert only)
INSERT INTO [ClientSecrets] ([Id], [ClientId], [Value], [CreationDate], [ValidTill])
VALUES( @Id, @ClientId, @Value, @CreationDate, @ValidTill)
";
await ctx.ExecuteNonQueryAsync(sql, cmd =>
{
cmd.Parameters.Add(ctx.CreateParameter("@ClientId", DbType.AnsiString, 50, clientId));
cmd.Parameters.Add(ctx.CreateParameter("@Id", DbType.String, 450, secret.Id));
cmd.Parameters.Add(ctx.CreateParameter("@Value", DbType.String, 450, secret.Value));
cmd.Parameters.Add(ctx.CreateParameter("@CreationDate", DbType.DateTime2, secret.CreationDate));
cmd.Parameters.Add(ctx.CreateParameter("@ValidTill", DbType.DateTime2, secret.ValidTill));
}).ConfigureAwait(false);
}
public async Task <IEnumerable <IStatisticalDataRecord> > LoadUsageListAsync(string dataType, int[] relatedTargetIds, DateTime endTimeExclusive, int count, CancellationToken cancellation)
{
string sql;
if (relatedTargetIds == null || relatedTargetIds.Length == 0)
{
sql = LoadUsageListScript;
}
else
{
var ids = string.Join(", ", relatedTargetIds.Select(x => x.ToString()));
sql = string.Format(LoadUsageListByTargetIdsScript, ids);
}
var records = new List <IStatisticalDataRecord>();
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(sql, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@Take", DbType.Int32, count),
ctx.CreateParameter("@DataType", DbType.String, dataType),
ctx.CreateParameter("@EndTimeExclusive", DbType.DateTime2, endTimeExclusive),
});
}, async (reader, cancel) =>
{
while (await reader.ReadAsync(cancel))
{
records.Add(GetStatisticalDataRecordFromReader(reader));
}
return(true);
}).ConfigureAwait(false);
}
return(records);
}
protected override async Task <byte[][]> GetRawDataAsync(int fileId)
{
string blobProvider = null;
string blobProviderData = null;
byte[] buffer = null;
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString,
new DataOptions(), CancellationToken.None))
{
var script = "SELECT BlobProvider, BlobProviderData, [Stream] FROM Files WHERE FileId = @FileId";
var _ = await ctx.ExecuteReaderAsync(script, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@FileId", SqlDbType.Int, fileId)
});
}, async (reader, cancel) =>
{
if (await reader.ReadAsync(cancel))
{
blobProvider = reader.GetSafeString("BlobProvider");
blobProviderData = reader.GetSafeString("BlobProviderData");
buffer = reader.GetSafeByteArray("Stream");
}
return(true);
}).ConfigureAwait(false);
}
if (blobProvider == null)
{
if (buffer.Length == 0)
{
return(new byte[0][]);
}
return(new [] { buffer });
}
return(GetRawDataAsync(blobProvider, blobProviderData));
}
public async Task WriteDataAsync(IStatisticalDataRecord data, CancellationToken cancellation)
{
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteNonQueryAsync(WriteDataScript, cmd =>
{
var now = DateTime.UtcNow;
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", SqlDbType.NVarChar, 50, data.DataType),
ctx.CreateParameter("@WrittenTime", SqlDbType.DateTime2, now),
ctx.CreateParameter("@CreationTime", SqlDbType.DateTime2, data.CreationTime ?? now),
ctx.CreateParameter("@Duration", SqlDbType.BigInt, (object)data.Duration?.Ticks ?? DBNull.Value),
ctx.CreateParameter("@RequestLength", SqlDbType.BigInt, (object)data.RequestLength ?? DBNull.Value),
ctx.CreateParameter("@ResponseLength", SqlDbType.BigInt, (object)data.ResponseLength ?? DBNull.Value),
ctx.CreateParameter("@ResponseStatusCode", SqlDbType.Int, (object)data.ResponseStatusCode ?? DBNull.Value),
ctx.CreateParameter("@Url", SqlDbType.NVarChar, 1000, (object)data.Url ?? DBNull.Value),
ctx.CreateParameter("@TargetId", SqlDbType.Int, (object)data.TargetId ?? DBNull.Value),
ctx.CreateParameter("@ContentId", SqlDbType.Int, (object)data.ContentId ?? DBNull.Value),
ctx.CreateParameter("@EventName", SqlDbType.NVarChar, 50, (object)data.EventName ?? DBNull.Value),
ctx.CreateParameter("@ErrorMessage", SqlDbType.NVarChar, 500, (object)data.ErrorMessage ?? DBNull.Value),
ctx.CreateParameter("@GeneralData", SqlDbType.NVarChar, (object)data.GeneralData ?? DBNull.Value),
});
}).ConfigureAwait(false);
}
}
