I’ve got a 2 database tables in parent/child relationship as one-many.
I’ve got three classes representing the data in these two tables:
Parent Class
{
Public int ID {get; set;}
.. other properties
}
Child Class
{
Public int ID {get;set;}
Public int ParentID {get; set;}
.. other properties
}
TogetherClass
{
Public Parent Parent;
Public List<Child> ChildList;
}
Lastly I’ve got a client and server application – I’m in control of both ends so can make changes to both programs as I need to.
Client makes a request for ParentID and receives a Together Class for the matching parent, and all of the child records.
The client app may make changes to the children – add new children, remove or modify existing ones. Client app then sends the Together Class back to the server app.
Server app needs to update the parent and child records in the database.
In addition I would like to be able to log the changes – I’m doing this by having 2 separate tables one for Parent, one for child; each containing the same columns as the original plus date time modified, by whom and a list of the changes.
I’m unsure as to the best approach to detect the changes in records – new records, records to be deleted, records with no fields changed, records with some fields changed.
I figure I need to read the parent & children records and compare those to the ones in the Together Class.
Strategy A:
If Together class’s child record has an ID of say 0, that indicates a new record; insert. Any deleted child records are no longer in the Together Class; see if any of the comparison child records are not found in the Together class and delete if not found (Compare using ID).
Check each child record for changes and if changed log.
Strategy B: Make a new Updated TogetherClass
UpdatedClass
{
Public Parent Parent {get; set}
Public List<Child> ListNewChild {get;set;}
Public List<Child> DeletedChild {get;set;}
Public List<Child> ExistingChild {get;set;} // used for no changes and modified rows
}
And then process as per the list.
The reason why I’m asking for ideas is that both of these solutions don’t seem optimal to me and I suspect this problem has been solved already – some kind of design pattern ?
I am aware of one potential problem in this general approach – that where Client App A requests a record; App B requests same record; A then saves changes; B then saves changes which may overwrite changes A made. This is a separate locking issue which I’ll raise a separate question for if I’ve got trouble implementing.
The actual implementation is c#, SQL Server and WCF between client and server - sharing a library containing the class implementations.
Apologies if this is a duplicate post – I tried searching various terms without finding a match though.
Update based on comments from svick and TimG:
To track change would the base class be something like:
public class ChangedRowBase
{
public enum StatusState
{
New, Unchanged, Updated, Deleted
};
protected StatusState _Status;
public StatusState Status
{
get
{
return _Status;
}
}
public void SetUnchanged()
{
_Status = StatusState.Unchanged;
}
public void SetDeleted()
{
_Status = StatusState.Deleted;
}
}
Then my Child class would have something like:
public int SomeDumbProperty
{
get;
set
{
base.Status = StatusState.Updated;
// Missing line here to set the property itself
}
}
private int _SomeIntelligentProperty;
public int SomeIntelligentProperty
{
get { return _SomeIntelligentProperty; }
set
{
if (value != _SomeIntelligentProperty)
{
_SomeIntelligentProperty = value;
base.Status = StatusState.Updated;
}
}
}
The idea being that the intelligent one detects if there has indeed been a change; otherwise using the dumb one the client app would need to either detect change and only update if there has been a change, or use the setter anyway flagging a change which actually didn't occur.
I also know that my dumbproperty doesn't work (or compile) right now. If I want code in the getter or setter, does this mean I need to define a separate private variable myself ? I know the short hand {get; set;} does this behind the scenes.