XPO and SQL Server 2008 FILESTREAM support

14 August 2008

In a recent forum post, Reinhold Erlacher asked me whether we supported the new FILESTREAM feature in SQL Server 2008 yet. I was not familiar with the feature, not being a SQL Server expert really, but I decided to have a look.

First, I had to set up SQL Server to actually support the feature in question. A well documented process, but a bit weird nonetheless - why do they introduce features that are only available after jumping through so many additional hoops? Ah well... Here are the instructions on MSDN - there's a lot of confusing information around in blogs, apparently from pre-release versions of SQL Server 2008, so be sure to use these to get it to work with your database.

Now, first I played around a bit to try and get XPO to create a database structure automatically that would be compatible with the FILESTREAM feature. That is not currently possible, as it turns out, since there are a few requirements:

  • The database must have a file group for the file streams
  • A table that has associated file streams must also have a column marked ROWGUIDCOL
  • The ROWGUIDCOL column must have a unique index applied

Finally, of course, the blob column that will store the stream data must be marked FILESTREAM. We don't support any of these things directly - i.e. through a built-in feature - in XPO, and I don't think it's likely that we will. Fortunately, it's quite easy to make the necessary changes either manually (configuring the file group in SQL Server Management Studio) or automatically (using a bit of SQL code that's executed after the default schema creation process from the XPO application). The process shouldn't be hard to integrate with a typical deployment scenario, and of course we can't help the fact that Microsoft have made it quite hard to benefit from the new feature.

I'm using this basic persistent class for my demo:

 

  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;

    public byte[] StreamData {

      get { return streamData; }

      set { SetPropertyValue("StreamData", ref streamData, value); }

    }

 

    private Guid streamId;

    public Guid StreamId {

      get { return streamId; }

      set { SetPropertyValue("StreamId", ref streamId, value); }

    }

  }

I experimented briefly with the DbTypeAttribute and I had some success with it, but since custom SQL code was still required, I decided to make all relevant changes in one place instead of spreading them out. My test application is this:

 

  class Program {

    static void Main(string[] args) {

      XpoDefault.DataLayer = XpoDefault.GetDataLayer(

        MSSqlConnectionProvider.GetConnectionString(".", "XPOFileStream"),

        AutoCreateOption.DatabaseAndSchema);

 

      // This sets up the DB structure. Of course we would only do this

      // once in a real app. Since we're boring in this sample, we're just

      // going to kill the db and go from scratch.

      using (UnitOfWork uow = new UnitOfWork( )) {

        uow.ClearDatabase( );

      }

      CreateDBStructure( );

 

      // Create some test data

      using (UnitOfWork uow = new UnitOfWork( )) {

        Something something = new Something(uow);

        something.StrVal = "Entry";

        something.StreamData = UTF8Encoding.Default.GetBytes("Stuff in my stream");

        uow.CommitChanges( );

      }

      // Read the object back and show the stream data

 

      using (UnitOfWork uow = new UnitOfWork( )) {

        Something something = uow.FindObject<Something>(null);

        Console.WriteLine("Stream says: " + UTF8Encoding.Default.GetString(something.StreamData));

      }

    }

 

    private static void CreateDBStructure( ) {

      // Get the basic db structure created

      using (UnitOfWork uow = new UnitOfWork( )) {

        uow.UpdateSchema(typeof(Something));

      }

 

      // Apply schema modifications

      Action<string> execute = CommandExecutor(XpoDefault.DataLayer.Connection);

      execute("alter table Something alter column StreamId uniqueidentifier not null");

      execute("alter table Something alter column StreamId add rowguidcol");

      execute("alter table Something add constraint streamid_unique unique(StreamId)");

      execute("alter table Something drop column StreamData");

      execute("alter table Something add StreamData varbinary(max) FILESTREAM");

    }

    static Action<string> CommandExecutor(IDbConnection connection) {

      return delegate(string command) {

        SqlCommand sqlCommand = new SqlCommand(command, (SqlConnection) connection);

        sqlCommand.ExecuteNonQuery( );

      };

    }

  }

You can download the complete sample from here (VS 2008 SP1): XPOSQL2008FileStream.zip (4267 bytes)

Note: If you try to run the sample out of the box, you will be in trouble because the database is not configured to support file streams! You should therefore create a database called "XPOFileStream" manually and set it up to support file streams correctly (again, as described here), and then run the sample.

Tags
5 comment(s)
XPO

Yesterday I wrote about using the new SQL Server 2008 FILESTREAM feature with XPO , and this morning

15 August, 2008
Steven Rasmussen

I'm thinking of another solution... but I'm not certain about all the details and if its even possible.  Maybe someone from DevExpress could comment.

Anyway, I know that you can create your own DBProvider in XPO.  Could we create our own SQLProvider such that if it detected that the database was an SQL Server 2008 database, when it went to create the schema it would alter the statement such that if the column were of type byte() that it would automatically change the type to 'VARBINARY(MAX) FILESTREAM' datatype?  This is assuming that a unique identifier of type GUID is already used on the table(which is the case in my scenario)  If its possible would you be able to shed a little light on the detials and how it could be accomplished?

Thanks,

Steve

20 August, 2008
Oliver Sturm (DevExpress)

Steve,

Sure you could do that - it should actually be quite easy. If you look at the source code for XPO, in particular the file MsSql.cs in the Providers folder, you'll find a method in there called GetSqlCreateColumnTypeForByteArray. As you can see, this method already implements a distinction for SQL Server 2005 and older versions. If you derive your own provider from MSSqlConnectionProvider and override that method, you can easily add an additional case for SQL Server 2008. The code we're using to distinguish SQL Server versions is also in that same file, look for a method called ReadDbVersion.

20 August, 2008
Steven Rasmussen

For anyone reading this post.  I have created a suggestion for XPO to support this natively.  It sounds like this could be very easily implemented as Oliver has suggested.  Anyway, the suggestion is located here: www.devexpress.com/.../S92203.aspx

20 August, 2008
Lars Corneliussen
20 January, 2009

Please login or register to post comments.