Utilizzo di UPDATETEXT con dati binari (ADO.NET)
Per l'utilizzo di oggetti binari di grandi dimensioni (BLOB) nelle versioni di SQL Server precedenti a SQL Server 2005 sono disponibili opzioni limitate. È possibile scrivere un oggetto BLOB in un database inserendo o aggiornando un campo con un valore stringa o con una matrice di byte, in base al tipo di campo presente nel database. Le dimensioni di un BLOB possono tuttavia essere piuttosto grandi e pertanto la sua scrittura come valore singolo può comportare un notevole utilizzo 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 mediante il quale un BLOB viene scritto in un database in questo modo dipende dalle caratteristiche del database.
Esempio UPDATETEXT Transact-SQL
Nell'esempio seguente viene illustrato come scrivere un BLOB in blocchi in SQL Server. Nell'esempio viene aggiunto un nuovo record alla tabella Employees del database Northwind, compresa una foto del dipendente, che costituisce un oggetto BLOB. Viene utilizzata la funzione UPDATETEXT di SQL Server per scrivere la foto del nuovo dipendente nel campo Photo in blocchi di dimensioni specificate.
La funzione UPDATETEXT richiede un puntatore al campo BLOB che viene aggiornato. In questo esempio, quando si aggiunge il record per il nuovo dipendente, 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 dipendente e conservare il puntatore al campo Photo è illustrata nell'esempio seguente, 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
Notare 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 inserito. Il valore null non incide sui blocchi di dati che verranno aggiunti.
Dal momento che è stato conservato un puntatore al campo Photo del nuovo record, l'esempio 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 seguente viene mostrata la sintassi della funzione UPDATETEXT (dove @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.
Esempio di aggiornamento ADO.NET
Nell'esempio viene recuperata la foto del dipendente, 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 quindi 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 aggiunto al campo Photo del record del dipendente.
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();
}
}
Vedere anche
Concetti
Mapping dei tipi di dati SQL Server (ADO.NET)
Altre risorse
Dati binari e con valori di grandi dimensioni SQL Server (ADO.NET)