The Microsoft SQL Server 2008 FILESTREAM type can be a bit tricky to figure out. Hopefully this post will help those interested in using it. The first thing you need to do is enable FILESTREAM in your SQL Server 2008 instance. The instructions can be found HERE.
Our next step was to create our SQL table for testing.
CREATE TABLE [dbo].[FileStreamEmployees]
(
[EmployeeId] [int] NOT NULL,
[Photo] [varbinary](max) FILESTREAM NULL,
[RowGuid] [uniqueidentifier] ROWGUIDCOL NOT NULL
)
Notice that we have our Photo column set as our FILESTREAM column (shown above).
Our next step was of course to create our “Gnerated” and “Custom” classes. These classes are generated for you in seconds, see this video if you are new to EntitySpaces.
One of the tricks when working with SqlFileStream (which is located in the System.Data.SqlTypes namespace) is that it requires a transaction. So, what we did was make a tiny little class called esSqlFileStream that would hold onto both the esTransactionScope instance and the SqlFileStream instance. Let’s look at how all this works.
First, we created a sample record to play with.
// Create a sample record just for the demo
FileStreamEmployees emp = new FileStreamEmployees();
emp.EmployeeId = 1;
emp.Photo = new byte[] { 0x00, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07 };
emp.Save();
Now, let’s execute some code and see if we can read back the Photo column using streaming and not the entity itself.
//---------------------------------
// Let's test reading
//---------------------------------
esSqlFileStream stream = FileStreamEmployees.GetFileStream(1, FileAccess.Read);
int length = (int)stream.Length;
byte[] data = new byte[length + 1];
// Fill our data buffer
stream.Read(data, 0, length);
stream.Dispose();
Notice that we call a static method on our FileStreamEmployees entity named GetFileStream(). We added that to our Custom class. We’ll take a look at that method in just a moment. Also, we could have used the using() syntax on the esSqlFileStream object but we wanted to demonstrate how you could create an esSqlFileStream object and then pass it around to other methods if need be. This is why we needed to manually call Dispose on it in the sample above.
Now, let’s execute some code and see if we can write to the Photo column using streaming (and without calling Save on the entity).
//---------------------------------
// Let's test writing
//---------------------------------
esSqlFileStream stream = FileStreamEmployees.GetFileStream(1, FileAccess.Write);
data = new byte[] { 0x08, 0x09, 0x10, 0x11, 0x12, 0x13, 0x14, 0x15 };
stream.Write(data, 0, data.Length);
stream.Dispose();
Pretty simple looking really. There is one very important thing to note here. As long as you’re holding onto an esSqlFileStream there is a transaction open. So, you want to get in and out as fast as you can just as you would whenever you are holding a transaction open. Now, let’s take a look at our static GetFileStream() method which is located in our FileStreamEmployees Custom class.
using System;
using System.IO;
using System.Data.SqlTypes;
using EntitySpaces.Core;
using EntitySpaces.Interfaces;
using EntitySpaces.DynamicQuery;
namespace BusinessObjects
{
// This is in our “Custom Class”
public partial class FileStreamEmployees : esFileStreamEmployees
{
static public esSqlFileStream GetFileStream(int id, FileAccess access)
{
esSqlFileStream fileStream = new esSqlFileStream();
FileStreamEmployeesQuery q = new FileStreamEmployeesQuery();
q.Select("<Photo.PathName() as [Path]>", "<GET_FILESTREAM_TRANSACTION_CONTEXT() as [Context]>");
q.Where(q.EmployeeId == id);
FileStreamEmployees fsEmp = new FileStreamEmployees();
fsEmp.Load(q);
fileStream.Create((string)fsEmp.GetColumn("Path"),
(byte[])fsEmp.GetColumn("Context"), access, FileOptions.SequentialScan, 0);
return fileStream;
}
}
}
Notice that we use the “Raw SQL” feature to inject this very SQL Server specific code into our Select() statement. Remember that EntitySpaces is database independent and we try to avoid polluting the core with DBMS specific functions. Typically, you use very little or no “Raw SQL” in EntitySpaces. However, this mechanism is a really nice escape hatch when needed. Basically, the method above fetches the path to our Photo blob and uses the esTransactionScope transaction which is created in the constructor of the esSqlFileStream class.
Finally, let’s look at the esSqlFileStream class.
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Text;
using EntitySpaces.Interfaces;
namespace BusinessObjects
{
public class esSqlFileStream : IDisposable
{
public esSqlFileStream()
{
scope = new esTransactionScope();
}
public void Create(string path, byte[] transactionContext, FileAccess access, FileOptions options, long allocationSize)
{
stream = new SqlFileStream(path, transactionContext, access, options, 0);
}
public int Read(byte[] buffer, int offset, int count)
{
return stream.Read(buffer, offset, count);
}
public void Write(byte[] buffer, int offset, int count)
{
stream.Write(buffer, offset, count);
}
public long Length
{
get
{
return stream != null ? stream.Length : 0;
}
}
public void Dispose()
{
stream.Close();
stream.Dispose();
scope.Complete();
}
private esTransactionScope scope;
private SqlFileStream stream;
}
}
Notice that there is no error handling in this sample code. We wanted to keep this simple so it could be easily understood. Basically, the esSqlFileStream class wraps the SqlFileStream and holds onto the esTransactionScope during its lifetime. You only need to remember to call Dispose() or use it within a using() statement.
This really isn’t a class that we’ll include in the EntitySpaces architecture. This post is merely to show one way to potentially work with SqlFileStream objects in your application, if you plan to use them. Comments are welcome …
From Mobile Devices to large scale enterprise solutions in need of serious transaction support, EntitySpaces can meet your needs. Whether you’re writing an ASP.NET application with Medium Trust requirements, a Silverlight/WCF application, a Mono application, or a Windows.Forms application, the EntitySpaces architecture is there for you. EntitySpaces is provider independent, which means that you can run the same binary code against any of the supported databases. EntitySpaces is available in both C# and VB.NET. EntitySpaces uses no reflection, no XML files, and sports a tiny foot print of less than 300k. Pound for pound, EntitySpaces is one tough, dependable .NET architecture.
EntitySpaces LLC
Persistence Layer and Business Objects for Microsoft .NET
http://www.entityspaces.net