It's about time...

Time and space. Two of my favorite things. Time keeps everything from happening all at once. Space keeps everything from happening to you. Time AND Space keep everything from happening all at once to you!
The problem to be solved today is this: Create a .net object that has it's XML serialization saved to an XML column in a database table. Now, have a trigger (add/update) that takes that XML field and denormalizes its content to some additional columns. In this way, you have the denormalized data that you might want to use for joins and filtering without having to parse the XML field over and over.
You'd create a class something like this:

public class Foo {
Guid _id = Guid.NewGuid(); //This is our object's database identifier
DateTime _updated= DateTime.Now; //This was the last time the object was changed
string _description = string.empty;

public Guid Id{ get{return _id;} set{_id = value;} }
public string Description { 
get{return _description;} 
set{_description = value; Update();} }
public DateTime Updated { get{return _updated;} set{} }
private void Update(){ _updated = DateTime.Now; }
}

You remember how to serialize an object as XML, right? Ok, just for reference, here is how we would get our object as XML text:

XmlSerializer serializer = new XmlSerializer(typeof(Foo));
System.Text.StringBuilder sb = new System.Text.StringBuilder();
TextWriter writer = new StringWriter(sb);
serializer.Serialize(writer, this);
writer.Close();
string XMLText = sb.ToString();

You would also have a table that looks like this:

CREATE TABLE [dbo].[TableXML](
[Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT 
[XML] [xml] NOT NULL,
[Description] [nvarchar](max) )

Then you'd add a trigger to the table that looks like this:

CREATE TRIGGER [TableXML_InsUpd] ON  [dbo].[TableXML] AFTER INSERT,UPDATE AS 
BEGIN
SET NOCOUNT ON;
IF UPDATE([XML]) -- Only update the table if the XML field was updated.
BEGIN
UPDATE [MWI].[dbo].[TableXML] SET 
[Description] = INSERTED.XML.value('(//Description)[1]','nVarchar(MAX)'),
[Updated] = INSERTED.XML.value('xs:dateTime((//Updated)[1])','DateTime')
FROM INSERTED WHERE [PromotionXML].PromotionId = INSERTED.PromotionId
END
END

With these things in place, you could serialize the class to XML with code that looks like this:

public static void SaveFooDB(Foo foo) {
// Put the XML representation of the object into the database
SqlConnection conn = GetDataConnection(); //you'll have to write GetDataConnection
SqlCommand cmd = new SqlCommand("foo_InsertOrUpdate", conn);
using (cmd) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fooXML", promotion.ToString());
cmd.ExecuteNonQuery();
}
}

The stored procedure "foo_InsertOrUpdate" simply checks if the id already exists in the database and updates the existing record if it does, otherwise it is a new record, so it is inserted. Why SQL server doesn't have this sort of query is a mystery to me. Notice, however that we only have to pass one parameter (the serialized XML from our object) and we extract the Id from that XML.

CREATE PROCEDURE [dbo].[foo_InsertOrUpdate]( @FooXML XML ) AS
BEGIN 
DECLARE @IdUniqueIdentifier
select @Id= @FooXml.value('(//Id)[1]','UniqueIdentifier')
IF Exists (Select FooId from [MWI].[dbo].[FooXML] WHERE Id= @Id) 
BEGIN
UPDATE [MWI].[dbo].[FooXML] SET [XML] = @FooXML WHERE Id= @Id
END
ELSE 
BEGIN 
INSERT INTO [MWI].[dbo].[PromotionXML]([PromotionID],[XML])
VALUES (@PromotionID, @PromotionXML)
END
END

Now you might argue that since we have our insert/update procedure, we could do all the work of populating the additional columns here rather than in the trigger. You are right of course IF all of your table updates come through this procedure. Me, I'd rather not assume that everyone will play nicely, especially since it is possible to update the xml directly and in bulk. So, the trigger works for my needs, your mileage may vary.
Now everything works very well until we introduce another DateTime property in our class. If we (for example) bind that value to a DateTimePicker component, the XML extraction formatting will break. What is up with that?
It turns out that the DateTime format in .NET is far more robust than in many other systems (notibly SQL Server's DateTime). In fact, there is a property called "kind" on .net DateTime objects. The DateTimePicker uses the DateTimeKind.Unspecified kind which doesn't have time zone information. We can remedy this little problem by creating a new DateTime object in the set accessor of our new FooDate Property like this:

DateTime _fooDate = DateTime.Today;

public DateTime FooDate{
get { return _fooDate; }
set { _fooDate = new DateTime(value.Year, value.Month, value.Day, 0, 0, 0, 0, DateTimeKind.Local);}
}

Another thing that we can do here is to strip out the time infomation that is often provided by a calendar picker or other unreliable DateTime mechanism. One of my favorite tricks here is to specify the end of the day when people are picking a date range, so that we don't end up with that annoying "pick the next day really mean the end of today. Do something like this:

DateTime _fooEndDate= DateTime.Today;

public DateTime FooEndDate{
get { return _fooEndDate; }
set { _fooEndDate= new DateTime(value.Year, value.Month, value.Day, 23, 59, 59, 0, DateTimeKind.Local);}
}

And finally (wow, this was a long post) you don't want to put in 999 for the milliseconds if you are going to put this value into SQL server, someplace along the way, the milliseconds are rounded up to tomorrow and our SQL column representation becomes different from the XML representation.