- Linq to SQL
jak wyklikasz sobie diagram linq to sql bedziesz mial klase, ktora dziedziczy po System.Data.Linq.DataContext (zalozmy MyDataContext)
najlepiej zrobic kolejna klase dziedziczaca po twojej (np. My2DataContext : MyDataContext) i przeciazyc w niej metode SubmitChanges
private HashSet<Type> _logFilter = new HashSet<Type>(new[] {
typeof(SomeType1),
typeof(SomeType2),
...
});
public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
{
ChangeSet cs = this.GetChangeSet();
#if DEBUG
int insCount = 0, updCount = 0, delCount = 0;
#endif
List<DbAction> actionsToLog = new List<DbAction>(cs.Inserts.Count + cs.Updates.Count + cs.Deletes.Count);
for (int i = 0; i < cs.Updates.Count; i++)
{
Type entityType = cs.Updates[i].GetType();
if (!_logFilter.Contains(entityType))
{
#if DEBUG
++updCount;
#endif
object originalEntity = this.GetTable(entityType).GetOriginalEntityState(cs.Updates[i]);
ModifiedMemberInfo[] changes = this.GetTable(entityType).GetModifiedMembers(cs.Updates[i]);
DbAction act = new DbAction();
act.User = ...
act.Date = DateTime.Now;
act.ActionType = DbAction.ActionTypeEnum.Upd;
act.EntityTypeName = entityType.Name;
act.Changes = new List<ModifiedMemberInfo>(changes);
act.OldData = originalEntity;
act.NewData = cs.Updates[i];
actionsToLog.Add(act);
}
}
for (int i = 0; i < cs.Inserts.Count; i++)
{
Type entityType = cs.Inserts[i].GetType();
if (!_logFilter.Contains(entityType))
{
#if DEBUG
++insCount;
#endif
DbAction act = new DbAction();
act.User = ...
act.Date = DateTime.Now;
act.ActionType = DbAction.ActionTypeEnum.Ins;
act.EntityTypeName = entityType.Name;
act.OldData = null;
act.NewData = cs.Inserts[i];
actionsToLog.Add(act);
}
}
for (int i = 0; i < cs.Deletes.Count; i++)
{
Type entityType = cs.Deletes[i].GetType();
if (!_logFilter.Contains(entityType))
{
#if DEBUG
++delCount;
#endif
DbAction act = new DbAction();
act.User = ...
act.Date = DateTime.Now;
act.ActionType = DbAction.ActionTypeEnum.Del;
act.EntityTypeName = entityType.Name;
act.OldData = cs.Deletes[i];
act.NewData = null;
actionsToLog.Add(act);
}
}
#if DEBUG
if (insCount + updCount + delCount > 0)
Console.WriteLine("ChangeSet: Ins: {0} | Upd: {1} | Del: {2} ", insCount, updCount, delCount);
#endif
// bazowe wywolanie, ktore wykona akcje na bazie
base.SubmitChanges(failureMode);
// bazowy SubmitChanges wewnetrznie wykonuje wszystko w tranzakcji
// wiec jesli zakonczy sie wyjatkiem, to takze akcje nie powinny sie zalogowac
actionsToLog.ForEach(a => DbActionsLogger.LogAction(a));
actionsToLog.Clear();
actionsToLog = null;
}
public static void LogAction(DbAction act)
{
Thread.BeginCriticalRegion();
lock (((ICollection)_q).SyncRoot) // synchronizacja dostepu do kolejki
{
_q.Enqueue(act);
}
_sem.Release();
Thread.EndCriticalRegion();
}
private static void LogJob()
{
while (_sem.WaitOne())
{
Thread.BeginCriticalRegion();
try
{
DbAction actToLog = null;
lock (((ICollection)_q).SyncRoot) // synchronizacja dostepu do kolejki
{
if (_q.Count <= 0)
continue;
actToLog = _q.Dequeue();
}
if (actToLog != null)
{
// zmiany sa tylko dla akcji Update
if (actToLog.Changes != null && actToLog.Changes.Count > 0)
actToLog.Changes = actToLog.Changes.Where(mm => mm.Member.Name != "UpdatedBy" && mm.Member.Name != "UpdatedDate").ToList();
// loguj akcje Update tylko jesli faktycznie sa zmiany danych
if (!(actToLog.ActionType == DbAction.ActionTypeEnum.Upd && (actToLog.Changes == null || actToLog.Changes.Count == 0)))
{
LogDbAction entry = new LogDbAction()
{
User = actToLog.User,
ActionType = Enum.GetName(typeof(DbAction.ActionTypeEnum), actToLog.ActionType),
ObjectName = actToLog.EntityTypeName,
Date = actToLog.Date,
Changes = actToLog.GetChanges(),
OldData = DbAction.GetSerializedEntity(actToLog.OldData),
NewData = DbAction.GetSerializedEntity(actToLog.NewData)
};
//if (entry.ActionType.Length > 3)
// entry.ActionType = entry.ActionType.Substring(0, 3);
_db.LogDbActions.InsertOnSubmit(entry);
_db.SubmitChanges();
}
}
}
catch (Exception ex)
{
...
}
Thread.EndCriticalRegion();
}
}
public class DbAction
{
public enum ActionTypeEnum
{
Ins, Upd, Del
}
public string User { get; set; }
public DateTime Date { get; set; }
public ActionTypeEnum ActionType { get; set; }
public List<ModifiedMemberInfo> Changes { get; set; }
public object OldData { get; set; }
public object NewData { get; set; }
public string EntityTypeName { get; set; }
public DbAction()
{
Changes = new List<ModifiedMemberInfo>();
}
/// <summary>Serialize changes collection.</summary>
/// <returns>Serialized changes collection or null if collection is null or empty.</returns>
public XElement GetChanges()
{
if (Changes == null || Changes.Count == 0)
return null;
XElement el = null;
XmlWriter xw = null;
try
{
el = new XElement("Changes");
xw = el.CreateWriter();
Changes.ForEach(mm =>
{
xw.WriteStartElement("Field");
xw.WriteElementString("Name", mm.Member != null ? mm.Member.Name : "NULL");
xw.WriteElementString("OldValue", mm.OriginalValue != null ? mm.OriginalValue.ToString() : string.Empty);
xw.WriteElementString("NewValue", mm.CurrentValue != null ? mm.CurrentValue.ToString() : string.Empty);
xw.WriteEndElement();
});
}
catch (Exception ex)
{
...
}
finally
{
if (xw != null) xw.Close();
}
return el;
}
/// <summary>Serialize linq to sql entity. Serialize only properties witch attribute ColumnAttribute.</summary>
/// <param name="o">Object to serialize</param>
/// <returns>Serialized object or null if object is null or error occured during serialization.</returns>
public static XElement GetSerializedEntity(object o)
{
if (o == null)
return null;
Type entityType = o.GetType();
XElement el = null;
XmlWriter xw = null;
try
{
el = new XElement(entityType.Name);
xw = el.CreateWriter();
PropertyInfo[] props = entityType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
for (int i = 0; i < props.Length; i++)
{
object[] attrs = props[i].GetCustomAttributes(typeof(System.Data.Linq.Mapping.ColumnAttribute), false);
if (attrs != null && attrs.Length > 0 && props[i].CanRead)
{
object value = props[i].GetValue(o, null);
xw.WriteElementString(props[i].Name, value != null ? value.ToString() : string.Empty);
}
}
}
catch (Exception ex)
{
...
}
finally
{
if (xw != null) xw.Close();
}
return el;
}
}
CREATE TABLE [dbo].[LogDbActions](
[Id] [int] IDENTITY(1,1) NOT NULL,
[User] [nvarchar](50) NOT NULL,
[Date] [datetime] NOT NULL,
[ActionType] [char](3) NOT NULL,
[ObjectName] [nvarchar](100) NOT NULL,
[Changes] [xml] NULL,
[OldData] [xml] NULL,
[NewData] [xml] NULL,
CONSTRAINT [PK_LogDbActions] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
coz jak widzisz nie jest to najprostsze, ale swietnie dziala, umozliwia odtworzenie pelnej historii rekordu
nie moglem calosci ci podac, ale to co napisalem powinno wystarczyc
LogJob chodzi w osobnym watku i zapisuje wszelkie zmiany do bazy (nie za czesto, zeby nie obciazac jej specjalnie)
w entity framework zasada bedzie podobna
jesli swtorzysz jakas autorska DAL, to generalna zasada jest ta sama, musisz miec stan oryginalny i zmieniony i wykryc gdzie nastapily modyfikacje, a nastepnie je zapisac