Using the File Management Schema from my SQL Server 2008 FILESTREAM post, I created a simple .net application that uses LINQ to SQL in order to access the file contents.
Just a reminder of how the Files table looks like:
CREATE TABLE [dbo].[Files]
(
FileID uniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,
FileContents varbinary(max) FILESTREAM DEFAULT(0x)
)
and the corresponding LINQ to SQL model and generated code look like:
[Table(Name="dbo.Files")]
{
private System.Guid _FileID;
private System.Data.Linq.Binary _FileContents;
[Column(Storage="_FileID",
DbType="UniqueIdentifier NOT NULL",
IsPrimaryKey=true)]
public System.Guid FileID
{
get { ... }
set { ... }
}
[Column(Storage="_FileContents",
DbType="VarBinary(MAX)",
UpdateCheck=UpdateCheck.Never)]
public System.Data.Linq.Binary FileContents
{
get { ... }
set { ... }
}
}
Notice that the FileContents field is defined as System.Data.Linq.Binary.
Reading a File Content
FileManagementDataContext db = new FileManagementDataContext();
var query = db.Files;
foreach (var file in query)
{
byte[] buffer = file.FileContents.ToArray();
System.IO.File.WriteAllBytes(file.FileID + ".txt", buffer);
}
In the above code block I create a new instance of the data context and query for all the files. Going over the files, I take out the file contents as a byte array from the System.Data.Linq.Binary field and write it to another file in the file system.
Adding a new File
byte[] inputBuffer = System.IO.File.ReadAllBytes("TextFile1.txt");
File newFile = new File();
newFile.FileID = Guid.NewGuid();
newFile.FileContents = new System.Data.Linq.Binary(inputBuffer);
db.Files.InsertOnSubmit(newFile);
db.SubmitChanges();
In the above code I read all the contents of a file and get it as a byte array. Then, I create a new File object, assign a new ID and create a new instance of System.Data.Linq.Binary and pass the contents. Then, I use standard LINQ to SQL methods to add the file into the table and submit the changes to the database.
Conclusion
LINQ to SQL adds the support of using SQL Server 2008 FILESTREAM in Visual Studio 2008 and .Net Framework 3.5 SP1. The FILESTREAM columns is represented as a System.Data.Linq.Binary field that we can access it and get the file contents as a byte array.
Enjoy!
