En fråga jag får ganska ofta är om det är möjligt att lagra binära filer i SQL Server, och i så fall hur det går till.

Ja, det är möjligt att lagra binära filer. Det är till och med ganska lätt. Därmed inte sagt att det alltid är en bra idé. Diskussioner om HUR man lagrar bilder i SQL Server brukar spåra ur i en religiös diskussion om det är en bra idé eller inte. Vissa hävdar att det alltid är bättre att lagra en sökväg i SQL Server, och själva filen i filsystemet.

Själv försöker jag vara lite mer pragmatisk, och lagrar filer direkt i en SQL Server-databas när det är en bra idé att göra det. Vill man ha versionshantering av sina filer är det perfekt att lagra dem direkt i databasen. För att nämna ett användningsområde.

Jag har i mitt jobb byggt en bild-databas, med cirka 12000 högupplösta bilder, som även lagras i ett antal lågupplösta format. Versionshantering är inbyggd, så varje gång en bild laddas upp sparas den som en ny version, och alla gamla versioner finns kvar. Det blir rätt stora datamängder - 80 GB närmare bestämt. Och tidsmätningar visar att det är perfekt att lagra de här bilderna i SQL Server. Ett av de lågupplösta formaten läses nämligen mycket regelbundet, eftersom de bilderna visas i en webb-katalog som öppnas flera tusen gånger om dagen. Det formatet utgör mindre än 1 GB data. SQL Server är bra på att cacha data. Faktum är att SQL Server cachar allt data den läser. Data som läses ofta behåller SQL Server i sin cache hellre än data som läses sällan. Det innebär att de lågupplösta bilderna som läses flera tusen gånger om dagen nästan helt säkert redan finns i SQL Servers cache-minne när de ska visas. Vid en jämförelse mellan lagring i filsystem och lagring i SQL Server, blir just det exemplet snabbare vid lagring i SQL Server, eftersom bilderna nästan varje gång levereras som IO-läsning direkt från SQL Servers primärminne istället för att levereras som en IO-läsning från disk.

Hur gör man då?

Mitt exempel är gjort i VB.NET 1.1 och SQL Server 2000. Att göra det i .NET 2.0 eller 3.0, och SQL Server 2005 är ingen större skillnad.

Till att börja med behöver vi en tabell där vi kan lagra våra filer i databasen:

CREATE TABLE files(
filename nvarchar(255),
version int,
blob image)

Jag föredrar (nästan) alltid att jobba med Stored Procedures för SELECT och INSERT i databaser. Därför skapar jag också två Stored Procedures:

CREATE PROC GetFile (@filename nvarchar(255))
AS
SELECT TOP 1 *
FROM     files
WHERE     filename = @filename
ORDER BY version DESC

CREATE PROC InsertFile (@filename nvarchar(255), @file image)
AS
SET NOCOUNT ON
INSERT INTO files (filename,version,blob)
SELECT @filename, COALESCE((SELECT MAX(version) FROM files WHERE filename = @filename),0)+1 ,@file

Sådär. Då har vi en Stored procedure för att spara filer, och en för att lägga in nya filer. Klart förenklad datamodell, men den räcker för att visa mitt exempel. Jag använder en sub-select i InsertFile för att få fram det högsta versionsnumret som redan finns i databasen för den fil jag ska spara. Finns ingen sådan fil sparad redan returnerar sub-selecten NULL. Därför använder jag COALESCE för att istället returnera 0. Slutligen lägger jag till 1 till resultatet. Det gör att jag får versionsnummer 1 första gången en fil sparas, och MAX(versionsnummer)+1 för kommande versioner.

Så till VB.NET-koden. I mitt exempel visar jag en metod för att läsa ut en fil från databasen och lägga den i en katalog i filsystemet, och en metod för att ta en fil från filsystemet och spara den i databasen.

' Filename är ett filnamn. path är sökvägen till katalogen där filen ligger.
' Först öppnas filen, och läses in i en array. Därefter anropas vår Stored Procedure InsertFile, med filnamnet och arrayen med filinnehållet.
Private Sub saveInDB(filename As String, path As String, connString As String)
Dim cn As New SqlClient.SqlConnection(sConnString)
Dim fs As System.IO.FileStream = System.IO.File.OpenRead(path & filename)
Dim a(fs.Length - 1) As Byte
fs.Read(a, 0, fs.Length - 1)
fs.Close()
Dim cmd As New SqlClient.SqlCommand("InsertFile", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@file", SqlDbType.Image, a.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, a))
cmd.Parameters.Add(New SqlParameter("@filename", filename))
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
End Sub

' filename är filen som ska läsas ut, path är katalogen där den ska läggas.
' Först anropas proceduren getFile. Därefter, om proceduren returnerar några rader, läses blob-kolumnen ut till en array,
' som skrivs till disk med hjälp av en FileStream.
'Vill man streama ut en fil till en webbsida kan man göra Response.BinaryWrite med arrayen istället.
Private Sub getFile(filename As String, path As String, connString As String)
 Dim cn As New SqlClient.SqlConnection(connString)
 Dim cmd As New SqlClient.SqlCommand("getFile", cn)
 cmd.CommandType = CommandType.StoredProcedure
 cmd.Parameters.Add(New SqlClient.SqlParameter("@filename", filename))
 Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 Dim b() As Byte
 Dim f As System.IO.FileStream
 If dr.Read() Then
  f = New System.IO.FileStream(path & filename, IO.FileMode.Create)
  b = dr("blob")
  f.Write(b, 0, b.Length)
  f.Close()
  f = Nothing
 End If
 dr.Close()
End Sub

Inte alltför mycket kod alltså, och inte alltför komplicerat. Självklart behövs mer än såhär för att bygga en lagringsfunktion som är användbar, men principerna ändras inte - man behöver en tabell för lagring, man behöver kunna läsa och lägga in i tabellen, och man behöver applikationskod för att använda databaskoden.

Image-datatypen är alltså vad jag har använt för lagringen - populärt kallad BLOB (Binary Large OBjects).

Är koden användbar - skriv gärna en kommentar och berätta det. Är något fel i koden, berätta självklart det också!