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.
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.