tag:blogger.com,1999:blog-7130587409577087282.post6411301378986415213..comments2024-03-15T23:24:50.847+11:00Comments on David Klein's Corner: Simple Auditing with LINQ to SQL - Date, Time and User StampsDavid Kleinhttp://www.blogger.com/profile/13657389334766989234noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-7130587409577087282.post-4414349926501767082012-01-27T18:38:41.676+11:002012-01-27T18:38:41.676+11:00Hi Dude,
In LINQ to SQL, the data model of a rela...Hi Dude,<br /><br />In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. Thanks!<br /><br /><a href="http://www.stealthbits.com/" rel="nofollow">SMP For SQL</a>stealthbitshttps://www.blogger.com/profile/15764884549574000250noreply@blogger.comtag:blogger.com,1999:blog-7130587409577087282.post-85048971593743361172010-12-12T22:23:06.664+11:002010-12-12T22:23:06.664+11:00I noticed I've written the same just the other...I noticed I've written the same just the other day, yet completely different! It probably is a lot slower too :)<br /><br />private bool updateAuditInformation(IEntity entity, string creationUserEmail, string modificationUserEmail)<br /> {<br /> var table = Context.GetTable();<br /> var mapping = Context.Mapping.GetTable(typeof(T));<br /><br /> // Update SubEntitySets from LinqToSQL entity<br /> foreach (PropertyInfo propertyInfo in entity.GetType().GetProperties())<br /> {<br /> if (propertyInfo.PropertyType.IsGenericType &&<br /> typeof(EntitySet<>).IsAssignableFrom(propertyInfo.PropertyType.GetGenericTypeDefinition()))<br /> {<br /> IEnumerable enumerable = propertyInfo.GetValue(entity, null) as IEnumerable;<br /> IEnumerator enumerator = enumerable.GetEnumerator();<br /><br /> while (enumerator.MoveNext())<br /> {<br /> updateAuditInformation(enumerator.Current as IEntity, creationUserEmail, modificationUserEmail);<br /> }<br /> }<br /> }<br /><br /> // Update current Entity Audit information if present<br /> if (!string.IsNullOrWhiteSpace(creationUserEmail))<br /> {<br /> var creationDateField = mapping.RowType.DataMembers.FirstOrDefault(d => d.Name == "CreationDate");<br /> var creationUserIdField = mapping.RowType.DataMembers.FirstOrDefault(d => d.Name == "CreationUser");<br /> if (creationDateField != null)<br /> entity.GetType().GetProperties().Where(p => p.CanRead && p.Name == creationDateField.Name).SingleOrDefault().SetValue(entity, DateTime.UtcNow, null);<br /> if (creationUserIdField != null)<br /> entity.GetType().GetProperties().Where(p => p.CanRead && p.Name == creationUserIdField.Name).SingleOrDefault().SetValue(entity, creationUserEmail, null);<br /> }<br /> if (!string.IsNullOrWhiteSpace(modificationUserEmail))<br /> {<br /> var modificationDateField = mapping.RowType.DataMembers.FirstOrDefault(d => d.Name == "ModificationDate");<br /> var modificationUserIdField = mapping.RowType.DataMembers.FirstOrDefault(d => d.Name == "ModificationUser");<br /> if (modificationDateField != null)<br /> entity.GetType().GetProperties().Where(p => p.CanRead && p.Name == modificationDateField.Name).SingleOrDefault().SetValue(entity, DateTime.UtcNow, null);<br /> if (modificationUserIdField != null)<br /> entity.GetType().GetProperties().Where(p => p.CanRead && p.Name == modificationUserIdField.Name).SingleOrDefault().SetValue(entity, modificationUserEmail, null);<br /> }<br /> return true;<br /> }Peterhttps://www.blogger.com/profile/03164527364689144492noreply@blogger.comtag:blogger.com,1999:blog-7130587409577087282.post-76085590069502340182009-12-07T14:06:58.332+11:002009-12-07T14:06:58.332+11:00Using a marker interface is also the approach that...Using a marker interface is also the approach that I generally use to implement this. It is a very simple solution.<br /><br /><a href="http://instantdevelopment.blogspot.com/2009/12/generic-entity-auditing-in-linq-to-sql.html" rel="nofollow">Generic auditing in Linq to Sql</a>Anonymoushttps://www.blogger.com/profile/16748920504975752738noreply@blogger.comtag:blogger.com,1999:blog-7130587409577087282.post-6722802841596094492009-05-02T23:30:00.000+10:002009-05-02T23:30:00.000+10:00Your reflection could be simplified (and sped up c...Your reflection could be simplified (and sped up considerably) if you created an interface such as this one:<br />interface IHasRowLevelAuditing <br />{<br /> DataTime Created{get;set;}<br /> String CreatedBy{get;set;}<br /> DataTime Modified{get;set;}<br /> String ModifiedBy{get;set;}<br />}<br /><br />Then, add the interface to all your entity classes like this...<br /><br />partial class Employee <br />: IHasRowLevelAuditing <br />{}<br /><br />And then, in your submit changes code, instead of using the for statements, you use, you could try something like this...<br /><br />//Updates<br />foreach (IHasRowLevelAuditing modifiedEntity in this.GetChangeSet().Updates.OfType<IHasRowLevelAuditing >())<br />{<br /> modifiedEntity.Modified = DateTime.Now;<br /> modifiedEntity.ModifiedBy = username;<br />}<br /><br />and off you go...<br /><br />Thanks for a good post. I like the idea of linking in at the point where you did in order to manage this sort of stuff in a uniform way across all tables.Brenthttps://www.blogger.com/profile/01413051036149434809noreply@blogger.comtag:blogger.com,1999:blog-7130587409577087282.post-76109430231875885002008-06-14T22:39:00.000+10:002008-06-14T22:39:00.000+10:00Wow, really nice, let's see if it works....Amazing...Wow, really nice, let's see if it works....<BR/>Amazing.<BR/>Unfortunately for this project i need VB (its disgusting syntax imho), but the code is:<BR/> Public Overrides Sub SubmitChanges(ByVal failureMode As System.Data.Linq.ConflictMode)<BR/><BR/> 'inserts<BR/> For Each modifiedEntry As Object In Me.GetChangeSet().Inserts<BR/> SetAuditStamp(Me, modifiedEntry, ChangeType.Insert)<BR/> Next<BR/><BR/> 'updates<BR/> For Each modifiedEntry As Object In Me.GetChangeSet().Updates<BR/> SetAuditStamp(Me, modifiedEntry, ChangeType.Update)<BR/> Next<BR/><BR/> MyBase.SubmitChanges(failureMode)<BR/> End Sub<BR/><BR/> Private Enum ChangeType<BR/> Update<BR/> Insert<BR/> End Enum<BR/><BR/> Private Sub SetAuditStamp(ByVal context As DataContext, ByVal modifiedEntry As Object, ByVal changeType As ChangeType)<BR/> Dim user As sys_User = App.User<BR/> Const Created As String = "Felvitel_Datum", Created_By As String = "Felvivo", Modified As String = "Modositas_Datum", Modified_By As String = "Modosito"<BR/><BR/> If changeType = DBDataContext.ChangeType.Insert Then<BR/> SetAuditValue(modifiedEntry, Created, System.DateTime.Now)<BR/> SetAuditValue(modifiedEntry, Created_By, user.User_ID)<BR/> End If<BR/><BR/> If changeType = DBDataContext.ChangeType.Update Then<BR/> SetAuditValue(modifiedEntry, Modified, System.DateTime.Now)<BR/> SetAuditValue(modifiedEntry, Modified_By, user.User_ID)<BR/> End If<BR/><BR/><BR/><BR/> End Sub<BR/><BR/> Private Sub SetAuditValue(ByVal modifiedEntity As Object, ByVal fieldName As String, ByVal propertyValue As Object)<BR/> If Not modifiedEntity.GetType().GetProperty(fieldName) Is Nothing Then<BR/> modifiedEntity.GetType().GetProperty(fieldName).SetValue(modifiedEntity, propertyValue, Nothing)<BR/> End If<BR/> End SubUnknownhttps://www.blogger.com/profile/03008348569757962988noreply@blogger.com