Yesterday I wrote about using the new SQL Server 2008 FILESTREAM feature with XPO, and this morning Boris from our XPO team sent me an IM going "na na na, I found an easier way to do this". Okay, maybe he didn't quite put it like that :-) Anyway, here's the code he sent me:
public class Something : XPObject {
public Something(Session session)
: base(session) { }
public override void AfterConstruction( ) {
base.AfterConstruction( );
streamId = Guid.NewGuid( );
}
private string strVal;
public string StrVal {
get { return strVal; }
set { SetPropertyValue("StrVal", ref strVal, value); }
}
private byte[] streamData;
[DbType("VARBINARY(MAX) FILESTREAM")]
public byte[] StreamData {
get { return streamData; }
set { SetPropertyValue("StreamData", ref streamData, value); }
}
private Guid streamId;
[DbType("uniqueidentifier ROWGUIDCOL UNIQUE NOT")]
public Guid StreamId {
get { return streamId; }
set { SetPropertyValue("StreamId", ref streamId, value); }
}
}
This code removes the need for any separate structural updates, which is of course very convenient. Now, in my previous post I had already mentioned I'd been playing with ways of using the DbTypeAttribute, and my solution had looked almost exactly the same - only I hadn't been aware that the UNIQUE keyword could be used in that precise way, and so I wasn't able to get the necessary index created on the ROWGUIDCOL field. Boris' code takes care of that now.
Is this preferable? I'm not entirely sure. I had one other reason to decide not to post my own similar code in my previous article, and that was the fact that the DbType parameter for the StreamId field looks very much like SQL injection. As you can see, it ends in "NOT", and that is to combine with the "NULL" that XPO inserts into the SQL code anyway, so the field ends up being "NOT NULL". Hm... I guess this is SQL injection, seeing how the text from the DbType is inserted into the DML code without any checks. Then again, that's the exact point of having a DbType attribute in the first place - the ability to use types that XPO isn't aware of - and of course it's compile time data that is injected. In the end, that code has the same effect as the other code I published previously. It is important to point out that while this may look similar to code used for SQL injection attacks, it's quite a different thing really, and there doesn't seem to be a way of exploiting this at runtime.
So is it preferable? I don't know - I guess it's more convenient to use in some cases. It's also less dynamic. With the code I published previously, it would be possible for the initialization code to determine whether SQL Server 2008 is being used, and to make the necessary changes only if it is. By decorating the field with the DbType attribute, the class can only be used with SQL Server 2008. I guess in the end everybody will have to decide for themselves.
Update: Another idea from one of our untiring XPO team members - Alex this time - you could use an XPCustomObject and establish a Guid type primary key. This will be marked with ROWGUIDCOL automatically (we introduced this originally to make the databases replication friendly) and of course it will have the required unique index. I actually had a similar idea early on, but I wasn't going to do this because I didn't want to mix the requirements of the FILESTREAM feature with other structure... Of course you'll still have to get the FILESTREAM type set correctly and using DbType on that one still makes your structure specific to SQL Server 2008. I guess my own overall decision might still be to make the modifications through SQL/DML statements, but of course (as Alex also pointed out) I should pay attention whether my table has a Guid type primary key perhaps - in which case SQL Server will not let me establish a second column with the ROWGUIDCOL attribute set.
Finally, I was asked to show how initialization code can be used in the context of an XAF application. That's actually very simple, since XAF has its own mechanism of updating database structure and content with new versions of applications. That mechanism can easily be used to call some structure modification code. It's documented in the XAF help here.
Free DevExpress Products - Get Your Copy Today
The following free DevExpress product offers remain available. Should you have any questions about the free offers below, please submit a ticket via the
DevExpress Support Center at your convenience. We'll be happy to follow-up.