Med SQL Server 2008 forbedrede Microsoft muligheden for at gemme filer i databasen ved at introducere FILESTREAM.  Med FILESTREAM bliver filer gemt som fysiske filer på disk, mens al tilgang til filerne gik gennem T-SQL.  Dette blev gjort for at forbedre performance betragteligt ift. tidligere versioner af SQL Server, hvor BLOBs blev anvendt til at gemme filer.

Med SQL Server 2012 udbygger Microsoft FILESTREAM yderligere ved at tillade, at man tilgår de fysiske filer direkte gennem Windows I/O API’en.  Den nye funktionalitet kaldes FileTables, og det ser ret interessant ud.  FileTables tillader, at man kan tilgå filerne uden at skulle gå den tunge vej gennem transaktioner, samtidig med at filerne administreres af SQL Server, og dermed indgår i den daglige vedligehold med backups etc.  Fordi FileTables bygger på almindelige filer, tillader det også bruge af standard Windows directories.  FileTables bygger ovenpå FILESTREAM, hvillket betyder, at f.eks. FULLTEXT search virker i filerne.

Det er en længere historie at sætte FILESTREAM og derefter FileTables op i SQL Server 2012.  Når det er gjort, har SQL Server oprettet et fil share på serveren, som man skal bruge til at tilgå filerne, hvis man ønsker at bruge Windows I/O API’en.  Funktionen FileTableRootPath bruges til at få stien til dette share:

   1:  SELECT FileTableRootPath();

For at benytte FileTables kræves en speciel tabel databases.  Denne tabel indeholder en række for hver fil, der gemmes i det oprettede fil share.  Det betyder, at SQL Server intercepter alle I/O til dette fil share.  Filsystemet i Windows understøtter som sådan ikke ACID transaktioner, men så vidt jeg kan forstå, benytter SQL Server diverse locks på tabellen til at styre concurrency

Med et share og en FileTable på plads, kan man nu meget nemt skrive filer til databasen uden brug af SQL (men husk – det er uden en transaktion):

   1:  string rootShare = @"\\machine\blabla";
   2:  for (int i = 0; i < 10; i++)
   3:  {
   4:      File.WriteAllLines(rootShare + String.Format("\\TextFile_{0}.txt", i), new string[] { "Hello " + i });
   5:  }

Følgende SQL afslører, at der rent faktisk er dukket 10 rækker op i vores FileTable:

   1:  SELECT file_stream.GetFileNamespacePath()
   2:  FROM MyFileTable

FileTables er tilgængeligt i den SQL Server 2012, der er til download d.d. (RCO hedder den vist) – og det er også tilgængeligt i Express edition. Sweet.

Det kan undertiden være nyttigt at skulle gemme filer i SQL Server.  Før SQL Server 2008 havde man den mulighed at gemme filer i en BLOB, dvs. man gemte filen binært i en kolonne af type VARBINARY.  Flere har talt imod denne løsning, fordi den – i hvert fald hvis man skal tro rygterne – kunne være ekstremt skadelig for performance.  Flere anbefalede i stedet en løsning, hvor filerne fysisk bliver gemt på disk på et fileshare, og man i stedet nøjes med at gemme filens placering i en kolonne på SQL Server.  Det er naturligvis en lidt skrøbelig løsning, fordi man altid skal huske at opdatere fileshare og database som ét.  Det kan gøres i en transaktion, men det er indiskutabelt en lidt besværlig løsning.

Til det formål introducerede SQL Server 2008 type FILESTREAM.  Med FILESTREAM gemmes filerne fysisk på disk et sted, som SQL Server administrerer, men al tilgang til filerne går gennem SQL Server med T-SQL.

FILESTREAM er som default ikke tilgængeligt på en nyinstalleret SQL Server, så der skal en lille smule arbejde til for at kunne benytte det.

Vha. ADO.NET kan man læse og skrive til en FILESTREAM kolonne.  Det er ikke svært, men det kræver lidt arbejde, hvor man skal have fat i mindre fashionable ting som SqlFileStream og GET_FILESTREAM_TRANSACTION_CONTEXT.  Desuden er der krav om, at både læsning og skrivning omkranses af en transaktion, som man selv er ansvarlig for at oprette.  Alt i alt kunne man godt ønske sig, at der var en lidt mere elegant måde at gøre det på.

Heldigvis findes der en sådan elegant løsning (ellers ville jeg ikke have noget at skrive om), og den findes i Entiry Framework.

Givet følgende tabel med en FILESTREAM kolonne,

   1:  CREATE TABLE Ad
   2:  (
   3:    AdId UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL PRIMARY KEY,
   4:    UserId UNIQUEIDENTIFIER NOT NULL,
   5:    Photo VARBINARY(MAX) FILESTREAM NULL,
   6:    Url NVARCHAR(512) NOT NULL,
   7:    ExpiryDate DATETIME2 NOT NULL
   8:  )

vil Entity Framework automatisk bl.a. generere en property Ad.Photo af typen byte[].  Bemærk i øvrigt, at en tabel, der benytter FIILESTREAM, skal have en ROWGUIDCOL kolonne.  Som i gamle dage er kolonnen med filen stadig af typen VARBINARY, men med et ekstra lille FILESTREAM bagefter, som sørger for al den filbaserede magi.

Med Entity Framework kan man nu gemme sine filer i SQL Server meget nemt ved blot at omsætte sin fil til et byte array:

   1:  using (var db = GetContext())
   2:  {
   3:      Stream fs = File.OpenRead(filePath);
   4:      using (var reader = new BinaryReader(fs))
   5:      {
   6:          var ad = new Ad()
   7:          {
   8:              // Set other properties here.
   9:              Photo = reader.ReadBytes((int) fs.Length)
  10:          };
  11:          db.Ad.AddObject(ad);
  12:          db.SaveChanges();
  13:      }
  14:  }

På tilsvarende vis kan filer læses meget simpelt.  Entity Framework sørger for alt det beskidte arbejde.