Utiliser le nouveau type FILESTREAM de SQL Server 2008 – Partie 2

Dans un billet précédent, je vous expliquais comment configurer une instance SQL Server 2008 afin de pouvoir utiliser le nouveau type FILESTREAM, permettant de stocker des fichiers sur le système de fichier NTFS au travers d'une base de données et ce pour outrepasser la limite de fichier de taille de 2GB maximum, imposée par le type varbinary(Max).

Dans cette partie, nous allons voir comment stocker des fichiers dans la base de données configuré dans le billet suscité.

Stockage des fichiers

Selon que vous travaillez avec une version antérieure au .NET Framework SP1, la marche à suivre ne sera pas la même pour stocker et récupérer les fichiers dans la base de données. En effet, pour les versions pré SP1, vous devrez utiliser la technique de P/Invoke afin de faire appel à une fonction non-managée – OpenSqlFilestream – définie dans la librairie « sqlncli10.dll ». Un wrapper managé de cette méthode – la classe SqlFileStream, décrite dans la suite – a été intégré au .NET Framework 3.5 SP1. Nous détaillerons dans cette partie les deux techniques.

Nous avons vu dans la première partie de cet article que l'accès aux FileStream SQL Server 2008 pouvait se faire de deux manières : en T-SQL, ou via l'API Win32. Nous ne nous intéresserons qu'à la seconde.

Stockage des fichiers à l'aide de P/Invoke (pré .NET Framework 3.5 SP1)

La première chose à faire dans ce cas de figure, est de créer un point d'entrée statique vers la fonction OpenSqlFilestrem de la librairie « sqlncli10.dll ». Cette méthode nous retournera alors une instance de SafeFileHandle, classe définie dans l'espace de noms System.Microsoft.Win32.SafeFileHandles, représentant un pointeur (au sens Win32 du terme) vers un fichier du système d'exploitation.

Voici la déclaration de ce point d'entrée statique dans notre application .NET :

[DllImport("sqlncli10.dll", SetLastError = true, CharSet = CharSet.Unicode)]

static extern SafeFileHandle OpenSqlFilestream(

string FilestreamPath,

UInt32 DesiredAccess,

UInt32 OpenOptions,

byte[] FilestreamTransactionContext,

UInt32 FilestreamTransactionContextLength,

Int64 AllocationSize);

• Le premier argument correspond au chemin d'accès du fichier pour lequel nous souhaitons récupérer le handle Win32.
• Le second argument, dont la valeur peut prendre 0 (Lecture), 1 (Ecriture) ou 2 (Lecture/Ecriture) permet d'indiquer le mode d'accès que nous souhaitons pour le fichier dont nous allons récupérer un handle.
• Le troisième argument permet de préciser des options pour l'ouverture du fichier
• Le quatrième argument représente le contexte de la transaction. Nous nous intéresserons plus en détails à ce dernier dans la suite
• Le cinquième argument représente la longueur du contexte de la transaction
• Le dernier argument précise la taille par défaut du fichier qui sera crée. Ce paramètre importe peu dans le cas de la lecture.

Avant de pouvoir appeler la méthode OpenSqlFilestream et récupérer le SafeFileHandle de notre fichier, nous allons devoir exécuter deux requêtes SQL :

• La première aura pour but de créer une entrée dans la table de la base de données contenant une colonne de type FILESTREAM, ainsi que d'initialiser le fichier sur le filesystem. C'est lors de cette requête qu'il est important que notre champs PhotoFile soit initialisé, afin qu'un fichier (vide) soit crée dans le répertoire Mediatheque_Photos.
• La seconde aura pour objectif de récupérer le path du fichier à écrire à passer en première argument de la méthode ci-dessus, ainsi que le tableau d'octets représentant le contexte de la transaction.

Le chemin du fichier à écrire est récupérable via l'appel de la méthode « PathName() » sur la colonne de type FILESTREAM, au sein de la requête SQL. Le contexte de la transaction est retourné par l'appel de la fonction sql GET_FILESTREAM_TRANSACTION_CONTEXT.

Il est important de préciser que ces deux requêtes, ainsi que l'écriture du fichier (vu par la suite) doivent être réaliser au sein d'une même transaction SQL, afin d'être en mesure d'effectuer un Rollback en cas d'échec de l'une des procédures.

Dans un premier temps, nous allons devoir établir une connexion avec la base de données et récupérer une transaction :

public static void WriteFileInDb(string sourceFile)

{

ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["MediathequeCnxString"];

using (SqlConnection con = new SqlConnection(settings.ConnectionString))

{

con.Open();

SqlTransaction transaction = con.BeginTransaction();

}

}

L'étape suivante consiste en l'écriture de la requête permettant l'insertion dans la table Photos ainsi que l'initialisation du fichier :

SqlCommand cmd = new SqlCommand(

@"INSERT INTO Photos (PhotoID,PhotoName,PhotoContentType)

VALUES (@PhotoID,@PhotoName,@PhotoContentType)",

con, transaction

);

 

Guid VideoID = Guid.NewGuid();

String PhotoName = "LogoDotNET.png";

String PhotoContentType = "image/x-png";

 

cmd.Parameters.Add("@VideoID", System.Data.SqlDbType.UniqueIdentifier)

.Value = VideoID;

cmd.Parameters.Add("@PhotoName", System.Data.SqlDbType.VarChar)

.Value = PhotoName;

cmd.Parameters.Add("@PhotoContentType", System.Data.SqlDbType.VarChar)

.Value = PhotoContentType;

 

int nbLine = cmd.ExecuteNonQuery();

 

if (nbLine == 1)

{

 

}

else

{

transaction.Rollback();

}

Pour l'instant, il n'y a aucune difficulté, nous insérons tout simplement un enregistrement dans la table Photos.

L'étape suivante, consiste en l'exécution de la deuxième requête permettant de récupérer les informations à utiliser pour obtenir le SafeFileHandle :

if (nbLine == 1)

{

cmd = new SqlCommand(

@"SELECT PhotoFile.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Photos

WHERE PhotoID = @PhotoID",

con,

transaction

);

 

cmd.Parameters.Add("@PhotoID", System.Data.SqlDbType.UniqueIdentifier).Value = PhotoID;

SqlDataReader reader = cmd.ExecuteReader();

 

if (reader.Read())

{

String filePath = reader[0].ToString();

byte[] transactionContext = (byte[])reader[1];

reader.Close();

}

}

Nous pouvons à présent appeler la méthode OpenSqlFilestream à l'aide des valeurs précédemment récupérées :

SafeFileHandle fileHandle = OpenSqlFilestream(

filePath,

1,//ecriture

0,

transactionContext,

(UInt32)transactionContext.Length,

0L

);

A partir de ce SafeFileHandle, vous pouvez ouvrir un FileStream est commencer à écrire dans le fichier :

FileStream sourceStream = new FileStream(sourceFile, FileMode.Open);

FileStream destStream = new FileStream(fileHandle, FileAccess.Write);

 

byte[] buffer = new byte[4096];

 

int bytes = sourceStream.Read(buffer, 0, buffer.Length);

while (bytes > 0)

{

destStream.Write(buffer, 0, bytes);

bytes = sourceStream.Read(buffer, 0, buffer.Length);

}

 

sourceStream.Close();

destStream.Close();

transaction.Commit();

Stockage des fichiers à l'aide de la classe SqlFileStream

Depuis le .NET Framework 3.5 SP1, nous pouvons nous affranchir du P/Invoke pour manipuler les filestreams et ce par le biais de la classe SqlFileStream définie dans l'espace de noms System.Data.SqlTypes. Cette classe dérive de System.IO.Stream, on y retrouve donc une méthode Write directement.

Dès lors, vous pouvez vous affranchir de le la déclaration de la méthode OpenSqlFileStream, et de l'instanciation d'un FileStream pour le fichier de destination, pour directement manipuler ce type de flux :

SqlFileStream destStream = new SqlFileStream(filePath, transactionContext, FileAccess.Write);

FileStream sourceStream = new FileStream(sourceFile, FileMode.Open);

byte[] buffer = new byte[4096];

 

int bytes = sourceStream.Read(buffer, 0, buffer.Length);

while (bytes > 0)

{

destStream.Write(buffer, 0, bytes);

bytes = sourceStream.Read(buffer, 0, buffer.Length);

}

 

Le code reste inchangé sinon.

Il ne reste plus qu'à appeler l'une de nos deux méthodes, et le tour est joué :

static void Main(string[] args)

{

Sql2008FileStream.Sql2008Wrapper.WriteFileInDb(@"C:\dotnet.png");

//Sql2008FileStream.Sql2008Wrapper.WriteFileInDbWithSqlFileStream(@"C:\dotnet.png");

}

Vous pouvez vérifier dans le dossier Mediatheque_Photos (parcourez son arborescence : premier dossier-GUID pour la table Photos, deuxième dossier-GUID pour la colonne PhotoFile. Vous pouvez rajouter une extension au fichier apparu (correspondant au type que vous avez envoyé) et vous retrouverez votre fichier !

Dans la dernière partie, nous verrons comment récupérer le fichier envoyé dans la base.