If you've ever starred an address on google maps and wanted to rename it from "1600 Pennsylvania Avenue Northwest, Washington D.C., DC" to "The White House", just go to https://www.google.com/bookmarks/l. The site lists all of your google maps stars, and you click the 'edit' link and rename them.

I ran in to a strange issue when adding a hyperlink to a docx file generated using the OpenXML SDK 2.0. After adding the hyperlink, opening the file resulted in the following error:

The file <filename> cannot be opened because there are problems with the contents.

Details
Microsoft Office cannot open this file because some parts are missing or invalid.

Location: Part: /word/document.xml, Line: 1, Column: 265

Turns out the problem was the way that I had generated the code. I was calling AddHyperlinkRelationship on the wrong object. I was calling it on the WordprocessingDocument, when I should have been calling it on the WordprocessingDocument.MainDocumentPart object, or in other words:

using (var doc = WordprocessingDocument.Create(@"c:\temp\test.docx", WordprocessingDocumentType.Document, true))
{
     doc.AddMainDocumentPart();
     doc.MainDocumentPart.Document = new Document
     {
         Body = new Body(),
     };

     var link = "http://www.google.com";

     //var hr = doc.AddHyperlinkRelationship(new Uri(link), true); // this was the problem
     var hr = doc.MainDocumentPart.AddHyperlinkRelationship(new Uri(link), true); // this works properly
     doc.MainDocumentPart.Document.Body.AppendChild(new Paragraph(new Hyperlink(new Run(new Text("link"))) { Id = hr.Id, TargetFrame = "_blank" }));

     doc.Close();
}

The problem

Creating an audit log is often a tricky proposal. Whether it’s for legal purposes or you just want to give a history of modifications made to your data, Ensuring that your business objects properly keep track of what’s going on often creates a lot of extra work, and if your application isn’t designed properly (or you’re handed legacy code) it can be nearly impossible to track down every instance of CRUD statements.

The proposal

The easiest way to consistently audit every action is via a trigger in the database. Even in poorly written legacy code, developers are very unlikely to have found a reason to disable triggers before running insert/update/delete statements. Using an “INSTEAD OF“ trigger to capture modifications and log it to a single xml column gives you the consistency of a trigger and the ease of data transformation that xml provides.

Limitations

• Developers need to be cognizant of the fact that the magic is being performed by a database trigger.
• T-SQL is not a normal language for developers to be fluent in.

The solution

The XML Schema. This is simply here to ensure that the format of the log stays exactly what we expected.

CREATE XML SCHEMA COLLECTION LogSchema AS N'<xs:schema 
		attributeFormDefault="unqualified" 
		elementFormDefault="qualified" 
		xmlns:xs="http://www.w3.org/2001/XMLSchema" 
		xmlns="http://djeebus.net/schemas/xml-log" 
		targetNamespace="http://djeebus.net/schemas/xml-log">
    <xs:simpleType name="entryType">
        <xs:restriction base="xs:string">
            <xs:enumeration value="insert" />
            <xs:enumeration value="update" />
            <xs:enumeration value="delete" />
            <xs:enumeration value="comment" />
        </xs:restriction>
    </xs:simpleType>
    
    <xs:element name="log">
        <xs:complexType>
            <xs:sequence>
                <xs:element minOccurs="0" maxOccurs="unbounded" name="entry">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="column" minOccurs="0" maxOccurs="unbounded">
                                <xs:complexType>
                                    <xs:simpleContent>
                                        <xs:extension base="xs:string">
                                            <xs:attribute name="name" type="xs:string" use="required" />
                                        </xs:extension>
                                    </xs:simpleContent>
                                </xs:complexType>
                            </xs:element>                            
                        </xs:sequence>
                        <xs:attribute name="type"     type="entryType"   use="required" />
                        <xs:attribute name="date"     type="xs:dateTime" use="required" />
                        <xs:attribute name="user"     type="xs:string"   use="required" />
                        <xs:attribute name="comment"  type="xs:string"   use="optional" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';
GO

The test table. Just a simple table that describes users.

CREATE TABLE Users
(
    UserId          UNIQUEIDENTIFIER    NOT NULL PRIMARY KEY DEFAULT (NEWID()),
    Username        NVARCHAR(MAX)       NOT NULL,
    Email           NVARCHAR(MAX)       NOT NULL,
    PasswordHash    VARBINARY(128)      NOT NULL,
    IsDisabled      BIT                 NOT NULL DEFAULT (0),
    IsActivated     BIT                 NOT NULL DEFAULT (0),
    Address1        NVARCHAR(MAX)           NULL,
    Address2        NVARCHAR(MAX)           NULL,
    City            NVARCHAR(MAX)           NULL,
    State           NVARCHAR(MAX)           NULL,
    Country         NVARCHAR(MAX)           NULL,

    -- columns necessary for logging
    ModifiedBy      UNIQUEIDENTIFIER        NULL,
    Comment         NVARCHAR(MAX)           NULL,
    Log             XML(LogSchema)      NOT NULL DEFAULT (''),

    UNIQUE (Username)
)
GO

The trigger. Here’s where the magic happens.

CREATE TRIGGER Users_Log
ON Users
INSTEAD OF INSERT,UPDATE
AS
    -- avoid screwing up counts on queries
    SET NOCOUNT ON

    -- if nothing is updated or inserted, bail
    IF (SELECT COUNT(*) FROM Inserted) = 0
    BEGIN
        RETURN -- nothing to do here
    END

    -- determine whether this is an update or delete query
    DECLARE @is_update  BIT     
    SET @is_update = CASE WHEN (SELECT COUNT(*) FROM Deleted) = 0 THEN 0 ELSE 1 END
    
    IF @is_update = 1 -- all columns show as updated on inserts
    BEGIN   
        IF UPDATE(Log) -- if log column is updated, assume a comment was added
        BEGIN
            -- update only the log column, throw out other updates and return immediately
            UPDATE attendee.Attendees
            SET Log = i.Log
            FROM attendee.Attendees a
                JOIN Inserted i ON i.Id = a.Id

            RETURN
        END
    END
    
    
    -- ensure that every row has a user assigned for logging purposes
    IF (SELECT COUNT(*) - COUNT(ModifiedBy) FROM Inserted) != 0
    BEGIN
        RAISERROR (N'Must specify a ModifiedBy for every modification of a record.', 10, 1)
        RETURN
    END
        
    -- perform the actual data operations
    IF (SELECT COUNT(*) FROM Deleted) = 0
    BEGIN
        -- insert new records
        INSERT INTO Users (UserId, Username, Email, PasswordHash, IsDisabled, IsActivated, Address1, Address2, City, State, Country)
        SELECT UserId, Username, Email, PasswordHash, IsDisabled, IsActivated, Address1, Address2, City, State, Country
        FROM Inserted
    END
    ELSE
    BEGIN
        -- update existing records
        UPDATE Users
        SET 
            UserId = i.UserId, 
            Username = i.UserId, 
            Email = i.Email,
            PasswordHash = i.PasswordHash, 
            IsDisabled = i.IsDisabled, 
            IsActivated = i.IsActivated, 
            Address1 = i.Address1, 
            Address2 = i.Address2, 
            City = i.City, 
            State = i.State, 
            Country = i.Country
        FROM Users u
            JOIN Inserted i ON i.Id = u.Id
    END

    -- create xml-compatible date string
    DECLARE @date NVARCHAR(50) 
    SET @date = CONVERT(NVARCHAR(50), GETDATE(), 126) + '-07:00'
    
    UPDATE Users
    SET Log.modify('
        declare namespace a="http://djeebus.net/schemas/xml-log";
        insert 
            element a:entry
            {
                attribute type { if (empty(sql:column("d.UserId"))) then "insert" else "update" },
                attribute date { xs:dateTime(sql:variable("@date")) },

                attribute user { sql:column("i.ModifiedBy") }

                attribute usertype { sql:column("i.ModifiedByType") },
                
                if (not(empty(sql:column("i.Comment")))) then
                    attribute comment { sql:column("i.Comment") } else (),

                if (not((empty(sql:column("d.Username")) and empty(sql:column("i.Username"))) or (sql:column("d.Username") eq sql:column("i.Username")))) then
                    element a:column { attribute name { "Username" }, text { xs:string(sql:column("i.Username")) } } else (),

                if (not((empty(sql:column("d.PasswordHash")) and empty(sql:column("i.PasswordHash"))) or (sql:column("d.PasswordHash") eq sql:column("i.PasswordHash")))) then
                    element a:column { attribute name { "PasswordHash" }, text { xs:string(sql:column("i.PasswordHash")) } } else (),

                if (not((empty(sql:column("d.Email")) and empty(sql:column("i.Email"))) or (sql:column("d.Email") eq sql:column("i.Email")))) then
                    element a:column { attribute name { "Email" }, text { xs:string(sql:column("i.Email")) } } else (),

                if (not((empty(sql:column("d.IsDisabled")) and empty(sql:column("i.IsDisabled"))) or (sql:column("d.IsDisabled") eq sql:column("i.IsDisabled")))) then
                    element a:column { attribute name { "IsDisabled" }, text { xs:string(sql:column("i.IsDisabled")) } } else (),

                if (not((empty(sql:column("d.IsActivated")) and empty(sql:column("i.IsActivated"))) or (sql:column("d.IsActivated") eq sql:column("i.IsActivated")))) then
                    element a:column { attribute name { "IsActivated" }, text { xs:string(sql:column("i.IsActivated")) } } else (),

                if (not((empty(sql:column("d.Address1")) and empty(sql:column("i.Address1"))) or (sql:column("d.Address1") eq sql:column("i.Address1")))) then
                    element a:column { attribute name { "Address1" }, text { xs:string(sql:column("i.Address1")) } } else (),

                if (not((empty(sql:column("d.Address2")) and empty(sql:column("i.Address2"))) or (sql:column("d.Address2") eq sql:column("i.Address2")))) then
                    element a:column { attribute name { "Address2" }, text { xs:string(sql:column("i.Address2")) } } else (),

                if (not((empty(sql:column("d.City")) and empty(sql:column("i.City"))) or (sql:column("d.City") eq sql:column("i.City")))) then
                    element a:column { attribute name { "City" }, text { xs:string(sql:column("i.City")) } } else (),

                if (not((empty(sql:column("d.State")) and empty(sql:column("i.State"))) or (sql:column("d.State") eq sql:column("i.State")))) then
                    element a:column { attribute name { "State" }, text { xs:string(sql:column("i.State")) } } else (),

                if (not((empty(sql:column("d.Country")) and empty(sql:column("i.Country"))) or (sql:column("d.Country") eq sql:column("i.Country")))) then
                    element a:column { attribute name { "Country" }, text { xs:string(sql:column("i.Country")) } } else ()
            }
        as last into (/a:log)[1]')
    FROM Users u
        JOIN Inserted i ON a.Id = i.Id
        LEFT JOIN Deleted d ON i.Id = d.Id

    -- go back to counting the number of records affected
    SET NOCOUNT OFF
GO

The only maintenance-intensive part is that every time you add a new column you’ll have to add it to the update statement and the list of new elements at the bottom of the query, but I think the trade off is well worth getting fool-proof logging done properly.

For more information on the .modify() method (and on generaly using xml in sql server 2008) see http://msdn.microsoft.com/en-us/library/ms187093.aspx.