Riduzione dell'impiego di risorse durante la scrittura di valori BLOB in SQL Server
È possibile scrivere un oggetto binario di grandi dimensioni (BLOB, Binary Large Object) in un database inserendo o aggiornando un campo con un valore di tipo stringa o matrice di byte, a seconda del tipo di campo presente nel database (vedere Scrittura di valori BLOB in un database). È tuttavia possibile che il BLOB presenti considerevoli dimensioni e che la sua memorizzazione come valore singolo comporti un consistente impiego della memoria di sistema, con la conseguente riduzione delle prestazioni dell'applicazione.
Una pratica comunemente adottata per ridurre la memoria utilizzata durante la scrittura di un valore BLOB consiste nello scrivere il BLOB nel database in "blocchi". Il processo attraverso cui un BLOB viene scritto in un database in questo modo dipende dalle caratteristiche del database.
Nell'esempio che segue viene illustrato come scrivere un BLOB in blocchi in SQL Server. Nell'esempio viene aggiunto un nuovo record alla tabella Employees del database Northwind, comprensivo di una foto dell'impiegato, che costituisce il BLOB. Viene utilizzata la funzione UPDATETEXT di SQL Server per scrivere l'immagine del nuovo impiegato nel campo Photo in blocchi di una grandezza specificata.
La funzione UPDATETEXT richiede un puntatore al campo BLOB che si sta aggiornando. In questo esempio, quando si aggiunge il record per il nuovo impiegato, viene chiamata la funzione TEXTPTR di SQL Server, che restituisce un puntatore al campo Photo del nuovo record. Il valore del puntatore viene restituito come parametro di output. Nel codice dell'esempio il puntatore viene conservato e passato a UPDATETEXT durante l'aggiunta dei blocchi di dati.
La procedura Transact-SQL utilizzata per inserire il record del nuovo impiegato e conservare il puntatore al campo Photo è illustrata nell'esempio che segue, in cui @Identity e @Pointer sono identificati come parametri di output per 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
Si noti che il campo Photo viene inizializzato con il valore 0x0 (null). In questo modo sarà possibile recuperare un puntatore al campo Photo del nuovo record. Il valore null non altera i blocchi di dati che verranno aggiunti.
Nell'esempio il puntatore al campo Photo del nuovo record potrà essere utilizzato per aggiungere blocchi di dati al campo BLOB tramite la funzione UPDATETEXT di SQL Server. La funzione UPDATETEXT accetta come input l'identificatore di campo (Employees.Photo), il puntatore al campo BLOB, un valore di offset che rappresenta la posizione del BLOB in cui verrà scritto il blocco corrente e il blocco di dati da aggiungere. Nell'esempio di codice che segue viene mostrata la sintassi della funzione UPDATETEXT (@Pointer, @Offset e @Bytes sono identificati come parametri di input di SqlCommand).
UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes
Il valore di offset è determinato dalla dimensione del buffer di memoria, che va definito in base alle esigenze della propria applicazione. Con un buffer di grandi dimensioni sarà possibile completare la scrittura del BLOB più rapidamente, ma si utilizzerà più memoria di sistema. In questo esempio viene utilizzato un buffer di soli 128 byte. Il valore di offset è 0 per il primo blocco di dati e viene incrementato della dimensione del buffer per ogni blocco successivo.
Nell'esempio viene recuperata la foto dell'impiegato in blocchi da un percorso di file specificato. Ogni blocco viene letto in una matrice di byte per la dimensione di buffer specificata. La matrice di byte viene poi impostata come valore del parametro di input @Bytes di SqlCommand. Il valore del parametro @Offset viene aggiornato e SqlCommand viene eseguito. In tal modo, il blocco di byte corrente viene accodato al campo Photo del record del nuovo impiegato.
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();
}
}
Vedere anche
Scrittura di valori BLOB in un database | Scenari ADO.NET di esempio | Accesso ai dati tramite ADO.NET | Utilizzo di provider di dati .NET Framework per accedere ai dati