Valnuke (9) [Avatar] Offline
#1
Hello, I would like to implement something to log the updates in my database (to check which user updated what)

Since I'm quite a newbie programmer, I thought that you coul give me some good advice or point evident mistake in my code.

(I've also taken parts of code around on the internet(so thanks to these guys) but most of the examples were more complicated and written in c#)

I found 2 major issues on the internet:
1. none of the code I've seen around was dealing with the problem of avoiding logging if the item was not correctly updated... thing that I tried to solve by first adding the log information in a separate list
2. most of them were dealing with definied types of objects (customer, product) while what I want is a method that can deal with all of them


thanks


Partial Class myDataContext

Sub SubmitLogChanges()
'List of AuditLog objects, taken from the AuditLogs table in the database,
'a table with information about
'Username, date, table_name, modified item type, oldvalue, newvalue and ID

Dim AuditLogList As New List(Of AuditLog)

'For each update ready to be submitted
For Each item In GetChangeSet.Updates

'Get the table, object type, primary key
Dim myUpdatedTable = GetTable(item.GetType)
Dim pkeyfield = Mapping.GetTable(item.GetType).RowType.DataMembers.Where(Function(x) x.IsPrimaryKey)

Dim mmi = myUpdatedTable.GetModifiedMembers(item)
For Each mi In mmi

Dim newAuditRecord As New AuditLog
With newAuditRecord
'Get information about the logged user, date and values
.Username = myUserID
.Date = Now
.DbTable = mi.Member.Name
.Type = mi.Member.ReflectedType.Name ' same as item.GetType.Name
.OldValue = mi.OriginalValue
.NewValue = mi.CurrentValue
End With



'This is the only way I found (for now) to identify the object, looking for its
'primary key value....
Dim properties() = mi.Member.ReflectedType.GetProperties
If pkeyfield.Count = 1 Then
For Each pr In properties
If pr.Name = pkeyfield.FirstOrDefault.Name Then
With newAuditRecord
.ID = pr.GetValue(item, Nothing)
End With
End If
Next
Else
'If there are more than 1 PK, for example a combined PK....
If pkeyfield.Count > 1 Then
'to do, suggestion appreciated
End If
End If

'This add the Log object to a list
AuditLogList.Add(newAuditRecord)
Next
Next

Try
'Modify Database
SubmitChanges()

'insert audit values, if no errors showed up
AuditLogs.InsertAllOnSubmit(AuditLogList)
'Save also the changes in the AuditLogs Table
SubmitChanges()

Catch ex As Exception
Finally
AuditLogList.Clear()
End Try
End Sub
End class


If I get to have this correctly working, I'll work on a delete-logger method in the future