public async Task <List <ServerHistoryScopeInfo> > GetAllServerHistoryScopesAsync(string scopeName, DbConnection connection, DbTransaction transaction)
{
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
List <ServerHistoryScopeInfo> scopes = new List <ServerHistoryScopeInfo>();
var commandText =
$@"SELECT sync_scope_id
, sync_scope_name
, scope_last_sync_timestamp
, scope_last_sync_duration
, scope_last_sync
FROM `{tableName}`
WHERE sync_scope_name = @sync_scope_name";
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeName;
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
// read only the first one
while (reader.Read())
{
var serverScopeInfo = new ServerHistoryScopeInfo();
serverScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
serverScopeInfo.Name = reader["sync_scope_name"] as string;
serverScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
serverScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0;
serverScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0;
scopes.Add(serverScopeInfo);
}
}
}
return(scopes);
}
}
public Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo, DbConnection connection, DbTransaction transaction) => Task.FromResult(serverHistoryScopeInfo);
public async Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo)
{
bool alreadyOpened = connection.State == ConnectionState.Open;
bool exist;
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
try
{
using (var command = connection.CreateCommand())
{
if (transaction != null)
{
command.Transaction = transaction;
}
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.CommandText = $@"Select count(*) from `{tableName}` where sync_scope_id = @sync_scope_id";
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0;
}
string stmtText = exist
? $"Update `{tableName}` set sync_scope_name=@sync_scope_name, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync=@scope_last_sync, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id"
: $"Insert into `{tableName}` (sync_scope_id, sync_scope_name, scope_last_sync_timestamp, scope_last_sync, scope_last_sync_duration) values (@sync_scope_id, @sync_scope_name, @scope_last_sync_timestamp, @scope_last_sync, @scope_last_sync_duration)";
using (var command = connection.CreateCommand())
{
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandText = stmtText;
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = serverHistoryScopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = serverHistoryScopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = serverHistoryScopeInfo.LastSync.HasValue ? (object)serverHistoryScopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = serverHistoryScopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
serverHistoryScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
serverHistoryScopeInfo.Name = reader["sync_scope_name"] as string;
serverHistoryScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L;
serverHistoryScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L;
serverHistoryScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
}
}
}
return(serverHistoryScopeInfo);
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during InsertOrUpdateServerHistoryScopeInfoAsync : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public async Task <List <ServerHistoryScopeInfo> > GetAllServerHistoryScopesAsync(string scopeName)
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
List <ServerHistoryScopeInfo> scopes = new List <ServerHistoryScopeInfo>();
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.CommandText =
$@"SELECT sync_scope_id
, sync_scope_name
, scope_last_sync_timestamp
, scope_last_sync_duration
, scope_last_sync
FROM `{tableName}`
WHERE sync_scope_name = @sync_scope_name";
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeName;
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
// read only the first one
while (reader.Read())
{
var serverScopeInfo = new ServerHistoryScopeInfo();
serverScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
serverScopeInfo.Name = reader["sync_scope_name"] as string;
serverScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
serverScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0;
serverScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0;
scopes.Add(serverScopeInfo);
}
}
}
return(scopes);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during GetAllServerHistoryScopes : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public virtual async Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo, DbConnection connection, DbTransaction transaction)
{
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
var commandText = $@"
MERGE ""{tableName}"" AS ""base""
USING (
SELECT @sync_scope_id AS sync_scope_id,
@sync_scope_name AS sync_scope_name,
@scope_last_sync_timestamp AS scope_last_sync_timestamp,
@scope_last_sync_duration AS scope_last_sync_duration,
@scope_last_sync AS scope_last_sync
) AS ""changes""
ON ""base"".""sync_scope_id"" = ""changes"".""sync_scope_id""
WHEN NOT MATCHED THEN
INSERT (""sync_scope_name"", ""sync_scope_id"", ""scope_last_sync_timestamp"", ""scope_last_sync"", ""scope_last_sync_duration"")
VALUES (""changes"".""sync_scope_name"", ""changes"".""sync_scope_id"", ""changes"".""scope_last_sync_timestamp"",""changes"".""scope_last_sync"", ""changes"".""scope_last_sync_duration"")
WHEN MATCHED THEN
UPDATE SET ""sync_scope_name"" = ""changes"".""sync_scope_name"",
""scope_last_sync_timestamp"" = ""changes"".""scope_last_sync_timestamp"",
""scope_last_sync"" = ""changes"".""scope_last_sync"",
""scope_last_sync_duration"" = ""changes"".""scope_last_sync_duration""
OUTPUT INSERTED.""sync_scope_name"",
INSERTED.""sync_scope_id"",
INSERTED.""scope_last_sync_timestamp"",
INSERTED.""scope_last_sync"",
INSERTED.""scope_last_sync_duration"";";
using (var command = new NpgsqlCommand(commandText, (NpgsqlConnection)connection, (NpgsqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = serverHistoryScopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = serverHistoryScopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = serverHistoryScopeInfo.LastSync.HasValue ? (object)serverHistoryScopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = serverHistoryScopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
while (reader.Read())
{
serverHistoryScopeInfo.Name = reader["sync_scope_name"] as String;
serverHistoryScopeInfo.Id = (Guid)reader["sync_scope_id"];
serverHistoryScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0;
serverHistoryScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
serverHistoryScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0;
}
}
}
return(serverHistoryScopeInfo);
}
}
public async Task <List <ServerHistoryScopeInfo> > GetAllServerHistoryScopesAsync(string scopeName)
{
using (var command = connection.CreateCommand())
{
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
var scopes = new List <ServerHistoryScopeInfo>();
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.CommandText =
$@"SELECT ""sync_scope_id""
, ""sync_scope_name""
, ""scope_last_sync_timestamp""
, ""scope_last_sync_duration""
, ""scope_last_sync""
FROM ""{tableName}""
WHERE ""sync_scope_name"" = @sync_scope_name";
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeName;
p.DbType = DbType.String;
command.Parameters.Add(p);
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
// read only the first one
while (reader.Read())
{
var serverScopeInfo = new ServerHistoryScopeInfo();
serverScopeInfo.Id = (Guid)reader["sync_scope_id"];
serverScopeInfo.Name = reader["sync_scope_name"] as string;
serverScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
serverScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0;
serverScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0;
scopes.Add(serverScopeInfo);
}
}
}
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
return(scopes);
}
}
public virtual async Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo)
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
command.CommandText = $@"
MERGE ""{tableName}"" AS ""base""
USING (
SELECT @sync_scope_id AS sync_scope_id,
@sync_scope_name AS sync_scope_name,
@scope_last_sync_timestamp AS scope_last_sync_timestamp,
@scope_last_sync_duration AS scope_last_sync_duration,
@scope_last_sync AS scope_last_sync
) AS ""changes""
ON ""base"".""sync_scope_id"" = ""changes"".""sync_scope_id""
WHEN NOT MATCHED THEN
INSERT (""sync_scope_name"", ""sync_scope_id"", ""scope_last_sync_timestamp"", ""scope_last_sync"", ""scope_last_sync_duration"")
VALUES (""changes"".""sync_scope_name"", ""changes"".""sync_scope_id"", ""changes"".""scope_last_sync_timestamp"",""changes"".""scope_last_sync"", ""changes"".""scope_last_sync_duration"")
WHEN MATCHED THEN
UPDATE SET ""sync_scope_name"" = ""changes"".""sync_scope_name"",
""scope_last_sync_timestamp"" = ""changes"".""scope_last_sync_timestamp"",
""scope_last_sync"" = ""changes"".""scope_last_sync"",
""scope_last_sync_duration"" = ""changes"".""scope_last_sync_duration""
OUTPUT INSERTED.""sync_scope_name"",
INSERTED.""sync_scope_id"",
INSERTED.""scope_last_sync_timestamp"",
INSERTED.""scope_last_sync"",
INSERTED.""scope_last_sync_duration"";
";
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = serverHistoryScopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = serverHistoryScopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = serverHistoryScopeInfo.LastSync.HasValue ? (object)serverHistoryScopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = serverHistoryScopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
while (reader.Read())
{
serverHistoryScopeInfo.Name = reader["sync_scope_name"] as String;
serverHistoryScopeInfo.Id = (Guid)reader["sync_scope_id"];
serverHistoryScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0;
serverHistoryScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
serverHistoryScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0;
}
}
}
return(serverHistoryScopeInfo);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateTableScope : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public async Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo, DbConnection connection, DbTransaction transaction)
{
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
var commandText = $@"Select count(*) from `{tableName}` where sync_scope_id = @sync_scope_id";
bool exist;
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0;
}
string stmtText = exist
? $"Update `{tableName}` set sync_scope_name=@sync_scope_name, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync=@scope_last_sync, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id"
: $"Insert into `{tableName}` (sync_scope_id, sync_scope_name, scope_last_sync_timestamp, scope_last_sync, scope_last_sync_duration) values (@sync_scope_id, @sync_scope_name, @scope_last_sync_timestamp, @scope_last_sync, @scope_last_sync_duration)";
using (var command = new MySqlCommand(stmtText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = serverHistoryScopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = serverHistoryScopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = serverHistoryScopeInfo.LastSync.HasValue ? (object)serverHistoryScopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = serverHistoryScopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
serverHistoryScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
serverHistoryScopeInfo.Name = reader["sync_scope_name"] as string;
serverHistoryScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L;
serverHistoryScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L;
serverHistoryScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
}
}
}
return(serverHistoryScopeInfo);
}
}
public Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo) => Task.FromResult(serverHistoryScopeInfo);
/// <summary> /// Http Client is not authorized to save server scope history on the server /// </summary> public override Task <ServerHistoryScopeInfo> SaveServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) => throw new NotImplementedException();
public Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo) => throw new NotImplementedException();
public Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo, DbConnection connection, DbTransaction transaction) => throw new NotImplementedException();
