Préservation des ressources lors de l'écriture des valeurs BLOB dans SQL Server
Vous pouvez écrire un objet binaire volumineux (BLOB) dans une base de données en insérant ou en remplaçant dans un champ une valeur de chaîne ou un tableau d'octets en fonction du type de ce champ (consultez Écriture de valeurs BLOB dans une base de données). Toutefois, un BLOB peut parfois être très volumineux et donc consommer une grande quantité de mémoire système lorsqu'il est écrit en tant que valeur unique, ce qui a une incidence négative sur les performances de l'application.
Pour réduire la quantité de mémoire utilisée, il est donc courant d'écrire le BLOB dans la base de données en segments. La procédure d'écriture dépend dans ce cas des fonctionnalités de la base de données.
L'exemple suivant montre comment écrire un BLOB en segments dans SQL Server. Dans cet exemple, un nouvel enregistrement est ajouté à la table Employees de la base de données Northwind ; cet enregistrement contient un BLOB, à savoir la photo de l'employé. La fonction UPDATETEXT de SQL Server est utilisée pour écrire la photo de l'employé dans le champ Photo en segments d'une taille spécifiée.
Cette fonction requiert un pointeur vers le champ BLOB mis à jour. Dans cet exemple, lorsque l'enregistrement du nouvel employé est ajouté, la fonction SQL Server TEXTPTR est appelée pour retourner un pointeur vers le champ Photo du nouvel enregistrement. La valeur de pointeur retournée est repassée sous forme de paramètre de sortie. Le code de cet exemple conserve ce pointeur et le passe à la fonction UPDATETEXT lors de l'ajout des segments de données.
L'instruction Transact-SQL utilisée pour insérer le nouvel enregistrement d'employé et conserver le pointeur vers le champ Photo est illustrée dans l'exemple suivant (où @Identity et @Pointer sont identifiés comme paramètres de sortie de SqlCommand).
INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo)
Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0)
SELECT @Identity = SCOPE_IDENTITY()
SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity
Notez que la valeur initiale de 0x0 (nulle) est insérée dans le champ Photo. Une valeur de pointeur peut ainsi être extraite pour le champ Photo du nouvel enregistrement inséré. Toutefois, cette valeur nulle n'a pas d'incidence sur les segments de données ajoutés.
Le pointeur vers le champ Photo du nouvel enregistrement inséré étant conservé, les segments de données peuvent être ajoutés au champ BLOB par la fonction UPDATETEXT de SQL Server. Cette fonction accepte en entrée l'identificateur de champ (Employees.Photo), le pointeur vers le champ BLOB, une valeur de décalage représentant l'emplacement dans le BLOB auquel le segment actuel est écrit et le segment de données à ajouter. L'exemple de code suivant illustre la syntaxe de la fonction UPDATETEXT (où @Pointer, @Offset et @Bytes sont identifiés comme paramètres d'entrée de SqlCommand).
UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes
La valeur de décalage est déterminée par la taille de la mémoire tampon, laquelle est définie en fonction des besoins de votre application. Si la taille de la mémoire tampon est élevée, l'écriture du BLOB est plus rapide, mais la quantité de mémoire système utilisée plus importante. Dans cet exemple, la mémoire tampon est de taille assez modeste : 128 octets. La valeur de décalage démarre à 0 pour le premier segment de données et est ensuite incrémentée de la taille de la mémoire tampon pour chacun des segments suivants.
Dans cet exemple, la photo de l'employée est extraite sous forme de segments d'un fichier dont le chemin d'accès est spécifié. Chaque segment est lu dans un tableau d'octets compte tenu de la taille de mémoire tampon spécifiée. Le tableau d'octets est alors défini comme valeur du paramètre d'entrée @Bytes de SqlCommand. La valeur du paramètre @Offset est mise à jour et SqlCommand exécutée, ce qui a pour effet d'ajouter le segment actuel d'octets au champ Photo de l'enregistrement de l'employé.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Public Class EmployeeData
Public Shared Sub Main()
Dim hireDate As DateTime = DateTime.Parse("4/27/98")
Dim newID As Integer = _
AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "smith.bmp")
Console.WriteLine("New Employee added. EmployeeID = " & newID)
End Sub
Public Shared Function AddEmployee(lastName As String, firstName As String, title As String, hireDate As DateTime, _
reportsTo As Integer, photoFilePath As String) As Integer
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;")
Dim addEmp As SqlCommand = New SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " & _
"Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" & _
"SELECT @Identity = SCOPE_IDENTITY();" & _
"SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", nwindConn)
addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName
addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName
addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title
addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hireDate
addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo
Dim idParm As SqlParameter = addEmp.Parameters.Add("@Identity", SqlDbType.Int)
idParm.Direction = ParameterDirection.Output
Dim ptrParm As SqlParameter = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
ptrParm.Direction = ParameterDirection.Output
nwindConn.Open()
addEmp.ExecuteNonQuery()
Dim newEmpID As Integer = CType(idParm.Value, Integer)
StorePhoto(photoFilePath, ptrParm.Value, nwindConn)
nwindConn.Close()
Return newEmpID
End Function
Public Shared Sub StorePhoto(fileName As String, pointer As Byte(), nwindConn As SqlConnection)
Dim bufferLen As Integer = 128 ' The size of the "chunks" of the image.
Dim appendToPhoto As SqlCommand = New SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", nwindConn)
Dim ptrParm As SqlParameter = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
ptrParm.Value = pointer
Dim photoParm As SqlParameter = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen)
Dim offsetParm As SqlParameter = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int)
offsetParm.Value = 0
''''''''''''''''''''''''''''''''''''
'' Read the image in and write it to the database 128 (bufferLen) bytes at a time.
'' Tune bufferLen for best performance. Larger values write faster, but
'' use more system resources.
Dim fs As FileStream = New FileStream(fileName, FileMode.Open, FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)
Dim buffer() As Byte = br.ReadBytes(bufferLen)
Dim offset_ctr As Integer = 0
Do While buffer.Length > 0
photoParm.Value = buffer
appendToPhoto.ExecuteNonQuery()
offset_ctr += bufferLen
offsetParm.Value = offset_ctr
buffer = br.ReadBytes(bufferLen)
Loop
br.Close()
fs.Close()
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
public class EmployeeData
{
public static void Main()
{
DateTime hireDate = DateTime.Parse("4/27/98");
int newID = AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "smith.bmp");
Console.WriteLine("New Employee added. EmployeeID = " + newID);
}
public static int AddEmployee(string lastName, string firstName, string title, DateTime hireDate , int reportsTo, string photoFilePath)
{
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
SqlCommand addEmp = new SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " +
"Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" +
"SELECT @Identity = SCOPE_IDENTITY();" +
"SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", nwindConn);
addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName;
addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName;
addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title;
addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hireDate;
addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo;
SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int);
idParm.Direction = ParameterDirection.Output;
SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
ptrParm.Direction = ParameterDirection.Output;
nwindConn.Open();
addEmp.ExecuteNonQuery();
int newEmpID = (int)idParm.Value;
StorePhoto(photoFilePath, (byte[])ptrParm.Value, nwindConn);
nwindConn.Close();
return newEmpID;
}
public static void StorePhoto(string fileName, byte[] pointer, SqlConnection nwindConn)
{
int bufferLen = 128; // The size of the "chunks" of the image.
SqlCommand appendToPhoto = new SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", nwindConn);
SqlParameter ptrParm = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
ptrParm.Value = pointer;
SqlParameter photoParm = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen);
SqlParameter offsetParm = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int);
offsetParm.Value = 0;
//''''''''''''''''''''''''''''''''''
// Read the image in and write it to the database 128 (bufferLen) bytes at a time.
// Tune bufferLen for best performance. Larger values write faster, but
// use more system resources.
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
byte[] buffer = br.ReadBytes(bufferLen);
int offset_ctr = 0;
while (buffer.Length > 0)
{
photoParm.Value = buffer;
appendToPhoto.ExecuteNonQuery();
offset_ctr += bufferLen;
offsetParm.Value = offset_ctr;
buffer = br.ReadBytes(bufferLen);
}
br.Close();
fs.Close();
}
}
Voir aussi
Écriture de valeurs BLOB dans une base de données | Exemples de scénarios ADO.NET | Accès aux données avec ADO.NET | Utilisation des fournisseurs de données .NET Framework pour l'accès aux données