Tout d'abord, quelques explications :
Le champ de type > vous permet de stoker, au sein de votre base, des données binaires pouvant faire jusqu'à 2Go par enregistrement. Ils peuvent donc être utilisés pour y stocker des fichiers.
Si vous tentez d'accéder à une grosse quantité de données à l'aide d'un champ "image", vos requêtes risquent de bloquer votre application, voir mê;me de se terminer sur une erreur de timeout. Il est donc préférable de découper les données en paquets, de taille fixe et pas trop importante, afin d'alléger le traitement et de pouvoir l'arrêter proprement en cas de perte de patience de l'utilisateur.
De plus cette technique vous permettra de gérer un pourcentage d'avancement de votre traitement.
Les données étant stockées dans la base, il est préférable de ne pas l'utiliser pour le stockage d'une grande quantité de fichiers volumineux. Ceci dégradera les performances de votre serveur si la fréquence d'accés à ces fichiers est importante. On l'utilisera donc pour des fichiers de taille raisonnable ou, principalement, pour faciliter la gestion de droits d'accés aux fichiers en fonction des utilisateurs de votre base.
Elle vous permettra aussi de rendre vos fichiers inaccessibles via un explorateur sur le serveur. Il faudra dans tous les cas passer par une connexion à la base.
Bien entendu, la perte de la base entrainera la perte des fichiers.
Passons à la programmation :
Commencez par créer, dans votre base de données, une table nommée "Fichiers" ayant le schéma suivant :
L'insertion va se dérouler en 2 étapes :
Insertion d'un nouvel enregistrement et récupération d'un pointeur vers l'emplacement du fichier dans la base
Pour effectuer cette tache, nous allons utiliser la requête Sql suivante :
INSERT INTO Fichiers (Nom, Taille, Fichier)
VALUES (@Nom, @Taille, 0x0);
SELECT @NewID = SCOPE_IDENTITY();
SELECT @Pointeur = TEXTPTR(Fichier)
FROM Fichiers WHERE id = @NewID;
Analysons cette requête :
INSERT INTO Fichiers (Nom, Taille, Fichier)
VALUES (@Nom, @Taille, 0x0);
Permet de créer un nouvel enregistrement avec le nom et la taille du fichier.
SELECT @NewID = SCOPE_IDENTITY();
Permet de récupérer l'identifiant unique de l'enregistrement inséré à l'instant.
<SCOPE_IDENTITY>
SELECT @Pointeur = TEXTPTR(Fichier)
FROM Fichiers WHERE id = @NewID;
Permet de récupérer un pointeur vers l'emplacement du nouveau fichier à insérer dans la base. <TEXTPTR>
Insertion du fichier
Une fois le pointeur récupéré, nous allons utiliser un objet de type BinaryReader pour lire notre fichier par paquets et utiliser la requête :
UPDATETEXT Fichiers.Fichier @Pointeur @Offset 0 @Bytes
Pour insérer chaque paquet dans la base. >
Mise en oeuvre :
' Private Sub InsertBLOB() 'Chemin du fichier inséré Dim fichierAinserer As String = "C:\MonFichier.jpg" '(Pour exemple de gestion de barre d'avancement) Dim progressBar As New Windows.Forms.ProgressBar() 'Taille en octets des paquets Dim bufferLength As Integer = 1024 'Création d'un stream branché sur notre fichier à insérer Using fs As New FileStream(fichierAinserer, FileMode.Open, FileAccess.Read) 'Création d'un reader binaire nous permettant de découper 'facilement notre fichier en paquets Using reader As New BinaryReader(fs) 'Initialisation de la barre de progression progressBar.Value = 0 'Création de la connexion la base de données Using connection As New SqlConnection("MaChaineDeConnexion") 'Ouverture de la connexion connection.Open() 'Récupération des informations du fichier à insérer Dim fileLength As Int64 = fs.Length Dim fileName As String = Path.GetFileName(fichierAinserer) 'Initialisation de la valeur maxi de la barre de progression progressBar.Maximum = (fileLength \ bufferLength) '******** Etape a. Insertion d'un nouvel enregistrement et récupération '******** d'un pointeur vers l'emplacement du fichier dans la base 'Création de la requête d'ajout d'enregistrement Dim commandAddNew As New SqlCommand() commandAddNew.CommandText = _ "INSERT INTO Fichiers " & _ "(Nom, Taille, Fichier) " & _ "VALUES (@Nom, @Taille, 0x0); " & _ "SELECT @NewID = SCOPE_IDENTITY(); " & _ "SELECT @Pointeur = TEXTPTR(Fichier) " & _ "FROM Fichiers WHERE id = @NewID" commandAddNew.CommandType = CommandType.Text commandAddNew.Connection = connection 'Ajout des paramètres d'entrées commandAddNew.Parameters.Add("@Nom", SqlDbType.VarChar, 100).Value = fileName commandAddNew.Parameters.Add("@Taille", SqlDbType.BigInt).Value = fileLength 'Ajout des paramètres de sorties commandAddNew.Parameters.Add("@NewID", SqlDbType.Int) commandAddNew.Parameters("@NewID").Direction = ParameterDirection.Output commandAddNew.Parameters.Add("@Pointeur", SqlDbType.Binary, 16) commandAddNew.Parameters("@Pointeur").Direction = ParameterDirection.Output 'Exécution de la requête Dim rowsAffected As Integer = commandAddNew.ExecuteNonQuery() 'Si l'enregistrement a été créé avec succès If rowsAffected = 1 Then 'Récupération du pointeur Dim pointeur() As Byte = commandAddNew.Parameters("@Pointeur").Value '******** Etape b. Insertion du fichier 'Création de la requête de mise à jour du fichier Dim commandUpdateFichier As New SqlCommand() commandUpdateFichier.CommandText = _ "UPDATETEXT Fichiers.Fichier @Pointeur @Offset 0 @Bytes" commandUpdateFichier.CommandType = CommandType.Text commandUpdateFichier.Connection = connection 'Ajout des paramètres d'entrées commandUpdateFichier.Parameters.Add("@Pointeur", SqlDbType.Binary, 16).Value = pointeur Dim offset As SqlParameter = commandUpdateFichier.Parameters.Add("@Offset", SqlDbType.Int) Dim bytes As SqlParameter = commandUpdateFichier.Parameters.Add("@Bytes", SqlDbType.Binary, bufferLength) 'Déclaration et initialisation des variables pour le parcours '(Lecture du premier packet) Dim buffer() As Byte = reader.ReadBytes(bufferLength) offset.Value = 0 Dim offset_ctr As Integer = 0 'Tant que le fichier n'a pas été totalement inséré While buffer.Length > 0 'Initialisation du paquet à envoyer bytes.Value = buffer 'Enregistrement du packet commandUpdateFichier.ExecuteNonQuery() 'Incrémentation de la barre de progression progressBar.Increment(1) 'Incrémentation de l'offset offset_ctr += buffer.Length offset.Value = offset_ctr 'Lecture du pacquet suivant buffer = reader.ReadBytes(bufferLength) End While End If End Using End Using End Using End Sub
La lecture se fera à l'aide de la requête :
SELECT id, Nom, Taille, Fichier
FROM Fichiers
WHERE id = @id
Elle sera exécutée à travers un DataReader à accès séquentiel.
Dim reader As New SqlDataReader(CommandBehavior.SequentialAccess)
Nous allons donc lire notre fichier, stocké dans la base, paquet par paquet à l'aide de la fonction GetBytes() du DataReader.
Mise en oeuvre :
' Private Sub LoadBLOB() 'id du fichier à charger Dim idFichier As Integer = 10 'Dossier de récupération du fichier Dim destFolder As String = "C:\MonDossier" '(Pour exemple de gestion de barre d'avancement) Dim progressBar As New Windows.Forms.ProgressBar() 'Taille en octets des paquets Dim bufferLength As Integer = 1024 'Création de la connexion la base de données Using connection As New SqlConnection("MaChaineDeConnexion") 'Ouverture de la connexion connection.Open() 'Création de la requête de chargement Dim loadCommand As New SqlCommand() loadCommand.CommandText = _ "SELECT Nom, Taille, Fichier " & _ "FROM Fichiers WHERE id = @id" loadCommand.CommandType = CommandType.Text loadCommand.Connection = connection 'Ajout des paramètres d'entrées loadCommand.Parameters.Add("@id", SqlDbType.Int).Value = idFichier 'Récupération du résultat de la requête dans un datareader avec accès séquentiel Using reader As SqlDataReader = _ loadCommand.ExecuteReader(CommandBehavior.SequentialAccess) 'Si l'enregistrement existe If reader.Read() Then 'Récupération des informations du fichier Dim fileName As String = reader.GetSqlString(1) Dim fileLength As Int64 = reader.GetSqlInt64(2) 'Création du chemin de destination Dim desPath As String = Path.Combine(destFolder, fileName) 'Création des variables de parcours Dim buffer(bufferLength - 1) As Byte Dim offset As Integer = 0 Dim bufferSize As Integer = 0 'Initialisation de la barre de progression progressBar.Value = 0 progressBar.Maximum = (fileLength \ bufferLength) 'Création d'un stream branché sur notre fichier de destination Using fs As New FileStream(desPath, FileMode.Create, FileAccess.Write) Using writer As New BinaryWriter(fs) 'Lecture d'un paquet bufferSize = reader.GetBytes(3, offset, buffer, 0, bufferLength) 'Tant que le fichier n'est pas complètement chargé While bufferSize > 0 'Ecriture du paquet dans notre fichier de destination writer.Write(buffer, 0, bufferSize) 'Incrémentation de l'offset offset += bufferSize 'Lecture du paquet suivant bufferSize = reader.GetBytes(3, offset, buffer, 0, bufferLength) 'Incrémentation de la barre de progression progressBar.Increment(1) End While End Using End Using End If reader.Close() End Using End Using End Sub