Exemplo n.º 1
0
        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 {scopeTableName.ObjectNameNormalized} 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 {scopeTableName.ObjectNameNormalized} set sync_scope_name=@sync_scope_name, scope_timestamp={SqliteObjectNames.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.ObjectNameNormalized} (sync_scope_name, scope_timestamp, scope_is_local, scope_last_sync, scope_last_sync_duration, scope_last_sync_timestamp, sync_scope_id) values (@sync_scope_name, {SqliteObjectNames.TimestampValue}, @scope_is_local, @scope_last_sync, @scope_last_sync_duration, @scope_last_sync_timestamp, @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 = "@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())
                {
                    while (reader.Read())
                    {
                        scopeInfo.Name      = reader["sync_scope_name"] as String;
                        scopeInfo.Id        = reader.GetGuid(reader.GetOrdinal("sync_scope_id"));
                        scopeInfo.Timestamp = SqliteManager.ParseTimestamp(reader["scope_timestamp"]);
                        scopeInfo.IsLocal   = (bool)reader["scope_is_local"];
                        scopeInfo.LastSync  = reader["scope_last_sync"] != DBNull.Value
                                    ? (DateTime?)reader.GetDateTime(reader.GetOrdinal("scope_last_sync"))
                                    : null;
                        scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_sync_timestamp")) : 0L;
                        scopeInfo.LastSyncDuration  = reader["scope_last_sync_duration"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_sync_duration")) : 0L;
                    }
                }

                return(scopeInfo);
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"Error during CreateTableScope : {ex}");
                throw;
            }
            finally
            {
                if (!alreadyOpened && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }

                if (command != null)
                {
                    command.Dispose();
                }
            }
        }
Exemplo n.º 2
0
        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.ObjectNameNormalized}
                    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())
                {
                    // read only the first one
                    while (reader.Read())
                    {
                        ScopeInfo scopeInfo = new ScopeInfo();
                        scopeInfo.Name      = reader["sync_scope_name"] as String;
                        scopeInfo.Id        = reader.GetGuid(reader.GetOrdinal("sync_scope_id"));
                        scopeInfo.Timestamp = SqliteManager.ParseTimestamp(reader["scope_timestamp"]);
                        scopeInfo.LastSync  = reader["scope_last_sync"] != DBNull.Value
                                        ? (DateTime?)reader.GetDateTime(reader.GetOrdinal("scope_last_sync"))
                                        : null;
                        scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_sync_timestamp")) : 0L;
                        scopeInfo.LastSyncDuration  = reader["scope_last_sync_duration"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_sync_duration")) : 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();
                }
            }
        }