public List <ScopeInfo> GetAllScopes(string scopeName)
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
List <ScopeInfo> scopes = new List <ScopeInfo>();
try
{
if (!alreadyOpened)
{
connection.Open();
}
command.CommandText =
$@"SELECT sync_scope_id
, sync_scope_name
, scope_timestamp
, scope_is_local
, scope_last_sync
, scope_last_sync_timestamp
, scope_last_sync_duration
FROM {scopeTableName.Quoted().ToString()}
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 = command.ExecuteReader())
{
if (reader.HasRows)
{
// read only the first one
while (reader.Read())
{
ScopeInfo scopeInfo = new ScopeInfo();
scopeInfo.Name = reader["sync_scope_name"] as String;
scopeInfo.Id = new Guid((String)reader["sync_scope_id"]);
scopeInfo.Timestamp = MySqlManager.ParseTimestamp(reader["scope_timestamp"]);
scopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
scopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L;
scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L;
scopeInfo.IsLocal = reader.GetBoolean(reader.GetOrdinal("scope_is_local"));
scopes.Add(scopeInfo);
}
}
}
return(scopes);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during GetAllScopes : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public ScopeInfo InsertOrUpdateScopeInfo(ScopeInfo scopeInfo)
{
bool alreadyOpened = connection.State == ConnectionState.Open;
bool exist;
try
{
using (var command = connection.CreateCommand())
{
if (transaction != null)
{
command.Transaction = transaction;
}
if (!alreadyOpened)
{
connection.Open();
}
command.CommandText = $@"Select count(*) from {scopeTableName.Quoted().ToString()} where sync_scope_id = @sync_scope_id";
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
exist = (long)command.ExecuteScalar() > 0;
}
string stmtText = exist
? $"Update {scopeTableName.Quoted().ToString()} set sync_scope_name=@sync_scope_name, scope_timestamp={MySqlObjectNames.TimestampValue}, scope_is_local=@scope_is_local, scope_last_sync=@scope_last_sync, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id"
: $"Insert into {scopeTableName.Quoted().ToString()} (sync_scope_name, scope_timestamp, scope_is_local, scope_last_sync, sync_scope_id, scope_last_sync_timestamp, scope_last_sync_duration) values (@sync_scope_name, {MySqlObjectNames.TimestampValue}, @scope_is_local, @scope_last_sync, @sync_scope_id, @scope_last_sync_timestamp, @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 = scopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_is_local";
p.Value = scopeInfo.IsLocal;
p.DbType = DbType.Boolean;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = scopeInfo.LastSync.HasValue ? (object)scopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = scopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = scopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
scopeInfo.Name = reader["sync_scope_name"] as String;
scopeInfo.Id = new Guid((string)reader["sync_scope_id"]);
scopeInfo.Timestamp = MySqlManager.ParseTimestamp(reader["scope_timestamp"]);
scopeInfo.IsLocal = (bool)reader["scope_is_local"];
scopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L;
scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L;
scopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
}
}
}
return(scopeInfo);
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateTableScope : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public ScopeInfo InsertOrUpdateScopeInfo(ScopeInfo scopeInfo)
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
command.CommandText = @"Select count(*) from scope_info where sync_scope_id = @sync_scope_id";
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
var exist = (long)command.ExecuteScalar() > 0;
string stmtText = exist
? $"Update scope_info set sync_scope_name=@sync_scope_name, scope_timestamp={MySqlObjectNames.TimestampValue}, scope_is_local=@scope_is_local, scope_last_sync=@scope_last_sync where sync_scope_id=@sync_scope_id"
: $"Insert into scope_info (sync_scope_name, scope_timestamp, scope_is_local, scope_last_sync, sync_scope_id) values (@sync_scope_name, {MySqlObjectNames.TimestampValue}, @scope_is_local, @scope_last_sync, @sync_scope_id)";
command = connection.CreateCommand();
command.CommandText = stmtText;
p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_is_local";
p.Value = scopeInfo.IsLocal;
p.DbType = DbType.Boolean;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = scopeInfo.LastSync.HasValue ? (object)scopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
scopeInfo.Name = reader["sync_scope_name"] as String;
scopeInfo.Id = new Guid((string)reader["sync_scope_id"]);
scopeInfo.LastTimestamp = MySqlManager.ParseTimestamp(reader["scope_timestamp"]);
scopeInfo.IsLocal = (bool)reader["scope_is_local"];
scopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
}
}
return(scopeInfo);
}
catch (Exception ex)
{
Logger.Current.Error($"Error during CreateTableScope : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
