Extract from Diffgram

[wancherng]

Hi all, i'm new to vb.net and i meet some problem regarding with some coding.

The case is:
I have a datagrid on a wins form to let end-user to do some data maintenance and update the database, and i have no problem with that.
Along the way, i need to do a audit-trail to log all the changes to the table, and that's still ok to me.
i'm using diffgram of the dataset to trap the chances, and i convert the diffgram into a long string and store in my auditTrailTable as text.

But the problem is, the diffgram text is in so unreadable format, that makes me wanna do some extraction from the diffgram so that i can get only the value like "column name", "new value", and "before value", and with all the diffgram tags shreded off.
And also, i want the audittrail function to be a more generic(global function to cater the data maintenance) to handle any different tables.

I'd search everywhere from the net and i 'd tried all the xml-related method but i jz dun get the way to achieve this.
So i would like to see is there anyone here know how to do this, and if u guys think there are any ways to perform the auditTrail in a better way, pls let me know ok??

So, i hope u guys are able to help me out in this matter. Million thanks in advanced.


p/s:i am quite a newbie, so if possible, pls explain in a more dummy way. Sorry for this


regards
wancherng

 

[JohnH]

Could you post a diffgram xml file for two different tables, attach them to the post as xml files or zip them. Make each of them contain a few delete and change and insert. I have made some successful tests with diffgrams of xml data (which can get a little complicated to describe because of the level of childnodes) but I would like to see the working sets for your situation also.

 

[wancherng]

Glad to get response from u guys!!
And hope that the solution u got is really what i want....thanx 1st

Here attached is the samples of the diffgram i have export to my mssql database, and i save the string as "text type" field.

Ohya! by the way, why the insert diffgram got both before and after changes?? Cant we only the after value if it is just a insert record?

Pls do let me know if u guys know/found a good mathod to handle this. Thanx!!

 

[JohnH]

You got two 'modified' and one 'deleted' there.. I have attached the merged test set (made an 'inserted' too).

So, started analyzing: first checked the 'before' node, if there is a corresponding dataset node it's a 'modified' else a 'deleted', then got all 'inserted' nodes. The 'modified' could be found directly using same method as 'inserted', but there isn't anything to save in processing this compared to what I did; the xml file have to be searced 'forward + backward' either way (since deleted only occurs in 'before' node and inserted only occurs in dataset node).

Below is the code, it hopyfully fits others kinds of diffgram data than those tested on, you can modify the ReportChanges sub to your needs.Take the attached xml file test data and run this code to see how it looks, just run the analyzeXmlFile sub.

Enum audittype
    modified
    deleted
    inserted
    errored
End Enum
 
Class audit
    Public id As String
    Public type As audittype
    Public newnode, oldnode As Xml.XmlNode
    Public Sub New()
    End Sub
    Public Sub New(ByVal id As String, ByVal type As audittype, ByVal oldnode As Xml.XmlNode)
        Me.id = id
        Me.type = type
        Me.oldnode = oldnode
    End Sub
End Class
 
Class auditSort
    Implements IComparer(Of audit)
    Public Function Compare(ByVal x As audit, ByVal y As audit) As Integer _
    Implements System.Collections.Generic.IComparer(Of audit).Compare
        Return x.id < y.id
    End Function
End Class
 
Sub analyzeXmlString(ByVal XmlString As String)
    Dim xdoc As New Xml.XmlDocument
    xdoc.LoadXml(XmlString)
    analyzeXml(xdoc)
End Sub
 
Sub analyzeXmlFile(ByVal filename As String)
    Dim xdoc As New Xml.XmlDocument
    xdoc.Load(filename)
    analyzeXml(xdoc)
End Sub
 
Sub analyzeXml(ByVal xdoc As Xml.XmlDocument)
    Dim changes As New List(Of audit)
    Dim ns As New Xml.XmlNamespaceManager(xdoc.NameTable)
    ns.AddNamespace("diffgr", "urn:schemas-microsoft-com:xml-diffgram-v1")
    Dim diff As Xml.XmlNode = xdoc.SelectSingleNode("/diffgr:diffgram", ns)
    ns.AddNamespace("sch", diff.FirstChild.NamespaceURI)
    'get modified and deleted
    Dim before As Xml.XmlNode = diff.SelectSingleNode("diffgr:before", ns)
    If Not before Is Nothing Then
        For Each node As Xml.XmlNode In before.ChildNodes
            Dim change As New audit(node.Attributes("diffgr:id").Value, audittype.deleted, node)
            Dim xpath As String = getNewnodeXpath(node)
            Dim newnode As Xml.XmlNode = diff.FirstChild.SelectSingleNode(xpath, ns)
            If Not newnode Is Nothing Then
                change.type = audittype.modified
                change.newnode = newnode
            End If
            changes.Add(change)
        Next
    End If
    'get errors
    Dim errors As Xml.XmlNode = diff.SelectSingleNode("diffgr:errors", ns)
    If Not errors Is Nothing Then
        For Each node As Xml.XmlNode In errors.ChildNodes
            Dim change As New audit(node.Attributes("diffgr:id").Value, audittype.errored, node)
            Dim xpath As String = getnewnodeXpath(node)
            Dim newnode As Xml.XmlNode = diff.FirstChild.SelectSingleNode(xpath, ns)
            If Not newnode Is Nothing Then
                change.newnode = newnode
            End If
            changes.Add(change)
        Next
    End If
    'get inserted
    getInserted(changes, diff.FirstChild.ChildNodes)
    'sort by change id
    changes.Sort(New auditSort)
    'report changes
    reportChanges(changes)
End Sub
 
Function getNewnodeXpath(ByVal node As Xml.XmlNode) As String
    Dim xpath As String = "descendant::"
    If node.LocalName = node.Name Then xpath &= "sch:"
    xpath &= node.Name & "[@diffgr:id='" & node.Attributes("diffgr:id").Value & "']"
    Return xpath
End Function
 
Sub getInserted(ByRef changes As List(Of audit), ByVal nodes As Xml.XmlNodeList)
    Dim attr As Xml.XmlAttribute
    For Each node As Xml.XmlNode In nodes
        Dim found As Boolean
        If Not node.Attributes Is Nothing Then
            attr = node.Attributes("diffgr:hasChanges")
            If Not attr Is Nothing Then
                If attr.InnerText = "inserted" Then
                    Dim change As New audit
                    change.type = audittype.inserted
                    change.id = node.Attributes("diffgr:id").Value
                    change.newnode = node
                    changes.Add(change)
                    found = True 'skip search childs of this node
                End If
            End If
        End If
        If found = False AndAlso node.ChildNodes.Count > 0 Then getInserted(changes, node.ChildNodes)
    Next
End Sub
 
Sub reportChanges(ByVal changes As List(Of audit))
    Dim sb As New System.Text.StringBuilder
    For Each change As audit In changes
        sb.AppendFormat("ChangeId {0}: ", change.id)
        Dim reportnode As Xml.XmlNode = IIf(change.type = audittype.deleted, change.oldnode, change.newnode)
        sb.AppendFormat("Row in {0} {1}:", reportnode.Name, change.type.ToString.ToUpper)
        sb.AppendLine()
        Select Case change.type
            Case audittype.modified                  
                For i As Integer = 0 To change.newnode.ChildNodes.Count - 1
                    If change.newnode.ChildNodes(i).InnerText <> change.oldnode.ChildNodes(i).InnerText Then
                        sb.AppendFormat("  {0} changed from '{1}' to '{2}'", change.newnode.ChildNodes(i).Name, _
                            change.oldnode.ChildNodes(i).InnerText, change.newnode.ChildNodes(i).InnerText)
                        sb.AppendLine()
                    End If
                Next
            Case audittype.errored
                sb.AppendFormat("  error message: {0}", change.oldnode.Attributes("diffgr:Error").InnerText)
                sb.AppendLine()
                For Each xn As Xml.XmlNode In reportnode.ChildNodes
                    sb.AppendFormat("  {0}:'{1}' ", xn.Name, xn.InnerText)
                Next
                sb.AppendLine()
            Case Else
                For Each xn As Xml.XmlNode In reportnode.ChildNodes
                    sb.AppendFormat("  {0}:'{1}' ", xn.Name, xn.InnerText)
                Next
                sb.AppendLine()
        End Select
        sb.AppendLine()
    Next
    MsgBox(sb.ToString)
End Sub

It may seem like a lot of operations and processing but it's quite fast, analyzing the merged xml with the three different changes takes only around 6 milliseconds.

Update: added audit for errors.

 

[JohnH]

Also have a look at this (http://www.vbdotnetforums.com/showthread.php?t=15038) for the database-way of doing it, but I don't think the order of events is possible to extract from that approach should that matter, easier to handle code though.