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