Utilisation de UPDATETEXT avec des données binaires (ADO.NET)
Mise à jour : November 2007
Les versions de SQL Server antérieures à SQL Server 2005 n'offrent qu'un nombre limité d'options d'utilisation pour les objets binaires volumineux (BLOB). Vous pouvez écrire un objet BLOB dans une base de données en insérant ou en mettant à jour une valeur de chaîne ou un tableau d'octets dans un champ, selon le type de champ utilisé dans la base de données. Toutefois, un objet BLOB peut être très volumineux et donc consommer une grande quantité de mémoire système lorsqu'il est écrit en tant que valeur unique, limitant alors 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.
Exemple UPDATETEXT Transact-SQL
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 l'objet 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 0x0 (null) 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 null 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 est 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.
Exemple de mise à jour ADO.NET
Dans cet exemple, la photo de l'employé est récupérée sous forme de segments à partir du chemin d'accès du fichier 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é, 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(ByVal lastName As String, _
ByVal firstName As String, ByVal title As String, ByVal hireDate As DateTime, _
ByVal reportsTo As Integer, ByVal photoFilePath As String) As Integer
Using connection As SqlConnection = New SqlConnection( _
"Data Source=(local);Integrated Security=true;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", _
connection)
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
connection.Open()
addEmp.ExecuteNonQuery()
Dim newEmpID As Integer = CType(idParm.Value, Integer)
StorePhoto(photoFilePath, ptrParm.Value, connection)
Return newEmpID
End Using
End Function
Public Shared Sub StorePhoto(ByVal fileName As String, _
ByVal pointer As Byte(), ByVal connection As SqlConnection)
' The size of the "chunks" of the image.
Dim bufferLen As Integer = 128
Dim appendToPhoto As SqlCommand = New SqlCommand( _
"UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", _
connection)
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
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)
{
using (SqlConnection connection = new SqlConnection(
"Data Source=(local);Integrated Security=true;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",
connection);
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;
connection.Open();
addEmp.ExecuteNonQuery();
int newEmpID = (int)idParm.Value;
StorePhoto(photoFilePath, (byte[])ptrParm.Value, connection);
return newEmpID;
}
}
public static void StorePhoto(string fileName, byte[] pointer,
SqlConnection connection)
{
// The size of the "chunks" of the image.
int bufferLen = 128;
SqlCommand appendToPhoto = new SqlCommand(
"UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes",
connection);
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
Concepts
Mappages de types de données SQL Server (ADO.NET)