CodeS-SourceS
Rechercher un code, un tuto, une réponse

Accès séquentiel à une donnée de type « image » d'une base de données sql server 2005

Juin 2017


Introduction


Tout d'abord, quelques explications :

Qu'est-ce qu'un champ de type "image" dans une base de données Sql Server


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.

Pourquoi accéder à ces données de manière séquentielle


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.

Quand utiliser le type "image"


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 :

Programmation

La base de données


Commencez par créer, dans votre base de données, une table nommée "Fichiers" ayant le schéma suivant :
  • Une colonne "id" de type "int" - Incrément auto - clé primaire de votre table
  • Une colonne "Nom" de type "String" (taille à votre convenance)
  • Une colonne "Taille" de type "Bigint"
  • Une colonne "Fichier" de type "Image"

L'insertion séquentielle


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

Lecture séquentielle


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

A voir également

Publié par foliv57.
Ce document intitulé «  Accès séquentiel à une donnée de type « image » d'une base de données sql server 2005  » issu de CodeS-SourceS (codes-sources.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.
Gestion de données avec les classes métier
Démarrer votre application avec les droits administrateur