XPO and FILESTREAM - update

15 August 2008

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.

Tags
3 comment(s)
James

For the record this isn't real file stream support.

Real file stream support would be a property that returns a stream that can be read.  The whole point to file streams asside from the fact that they don't impact the 4 gig limit in express edition, is that you can STREAM them. A Byte array is not that, it's a big ugly blob that eats memory to read on both the server side and the client side.

Once you have it so that this is native with a real stream behind this that doesn't blob data, then you'll have real support, not until.

17 August, 2008
Malisa

Am just wondering and thinking that it would help the users if you create some ASP.NET starter kits (VSI) for different types of sites. e.g. Using XPO for a simple portal application - Job Site, Web 2.0 app, WebStore, Blogs and others.

Something like www.binaryintellect.net/.../a203c824-aec1-41b7-b3ec-49a15d5c9ebb.aspx

18 August, 2008
Oliver Sturm (DevExpress)

Hi James,

I'm afraid I don't agree with your definition of "real file stream support". XPO is an object persistence framework and it interfaces with the database in certain ways - it creates relational structures, it adds, modifies and deletes data. My samples have shown how to make XPO work with the new file stream feature, stressing particularly the point of setting up a database with support for that feature, since the adding/modifying/deleting works pretty much out of the box once that has happened.

Now you're saying there are other ways of accessing the data in a stream once it has been stored there. I agree, and that's great - this is API functionality delivered with SQL Server 2008, and a field of type VARBINARY(MAX) FILESTREAM that has been created by XPO can be accessed using these approaches just like any other such field. I don't think we will be introducing any special SQL Server specific functionality in XPO for this, unless, as usual, we find reasons why this is absolutely necessary.

As far as I can see - and I haven't used the API in question myself - there's absolutely nothing specific to XPO about implementing a property or other point of access to the stream data in a FILESTREAM field in SQL Server 2008.

18 August, 2008

Please login or register to post comments.