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();
                }
            }
        }