Verwenden von UPDATETEXT mit Binärdaten (ADO.NET)
Aktualisiert: November 2007
In SQL Server-Versionen vor SQL Server 2005 sind die Optionen zum Arbeiten mit BLOBs (Binary Large Objects) begrenzt. Sie können ein BLOB in eine Datenbank schreiben, indem Sie je nach Feldtyp in der Datenbank einen Zeichenfolgenwert bzw. ein Bytearray in ein Feld einfügen oder ein Feld damit aktualisieren. Ein BLOB kann jedoch sehr umfangreich sein und, sofern es als einzelner Wert geschrieben wird, den Systemspeicher erheblich beanspruchen und damit die Leistung der Anwendung beeinträchtigen.
Eine bewährte Methode zur Reduzierung der Speicherbelegung stellt das Schreiben eines BLOB-Werts in kleinen Blöcken in die Datenbank dar. Das Verfahren, mit dem ein BLOB auf diese Weise in die Datenbank geschrieben wird, hängt von den Funktionen der Datenbank ab.
Beispiel für UPDATETEXT (Transact-SQL)
Im folgenden Beispiel wird das Schreiben eines BLOBs in kleinen Blöcken bei Verwendung von SQL Server veranschaulicht. Im Beispiel wird der Employees-Tabelle der Northwind-Datenbank ein neuer Datensatz mit einem Bild eines Mitarbeiters hinzugefügt, bei dem es sich um ein BLOB handelt. Das Bild des neu hinzugefügten Mitarbeiters wird mit der UPDATETEXT-Funktion von SQL Server in kleinen Blöcken mit festgelegter Größe in das Photo-Feld geschrieben.
Die UPDATETEXT-Funktion erfordert einen Zeiger auf das zu aktualisierende BLOB-Feld. Beim Hinzufügen des neuen Mitarbeiterdatensatzes wird die SQL Server-Funktion TEXTPTR aufgerufen, um einen Zeiger auf das Feld Photo des neuen Datensatzes zurückzugeben. Der zurückgegebene Zeigerwert wird als Ausgabeparameter zurückgegeben. Im Beispielcode wird dieser Zeiger beibehalten und beim Hinzufügen der Datenblöcke an die UPDATETEXT-Funktion übergeben.
Das folgende Beispiel zeigt die zum Einfügen des neuen Mitarbeiterdatensatzes sowie zum Beibehalten des Zeigers auf das Photo-Feld verwendete Transact-SQL-Anweisung (wobei @Identity und @Pointer als Ausgabeparameter für den SqlCommand angegeben sind):
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
Beachten Sie, dass im Photo-Feld der Anfangswert 0x0 (NULL) eintragen wird. Damit wird gewährleistet, dass für das Photo-Feld des neu eingefügten Datensatzes ein Zeigerwert abgerufen werden kann. Der NULL-Wert wirkt sich jedoch nicht auf die angehängten Datenblöcke aus.
Durch Beibehalten eines Zeigers auf das Feld Photo im neu eingefügten Datensatz können im vorliegenden Beispiel mit der SQL Server-Funktion UPDATETEXT an das BLOB-Feld Datenblöcke angefügt werden. Die UPDATETEXT-Funktion verwendet als Eingabe den Feldbezeichner (Employees.Photo), den Zeiger auf das BLOB-Feld, einen Offsetwert zur Anzeige der Position im BLOB, an der der aktuelle Datenblock geschrieben wird, sowie den anzufügenden Datenblock. Das folgende Codebeispiel zeigt die Syntax für die UPDATETEXT-Funktion (wobei @Pointer, @Offset, und @Bytes als Eingabeparameter für den SqlCommand angegeben sind).
UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes
Der Offsetwert richtet sich nach der Größe des Speicherpuffers, der anhand der jeweiligen Anwendungsanforderungen angepasst werden muss. Bei einem großen Puffer wird das BLOB schneller geschrieben, benötigt aber mehr Systemspeicher. In diesem Beispiel wird ein relativ kleiner Puffer mit einer Größe von 128 Byte verwendet. Der Offsetwert für den ersten Datenblock beginnt bei 0 (null) und wird für jeden folgenden Datenblock um die Puffergröße erhöht.
Beispiel für eine Aktualisierung in ADO.NET
In diesem Beispiel wird das Mitarbeiterfoto in kleinen Segmenten aus einem vorgegebenen Dateipfad abgerufen. Jeder Block wird hierbei in ein Bytearray von der Größe des festgelegten Puffers eingelesen. Das Bytearray wird anschließend als Wert des @Bytes des SqlCommand-Eingabeparameters festgelegt. Der Wert des @Offset-Parameters wird aktualisiert, und der SqlCommand wird ausgeführt. Dadurch wird der aktuelle Byteblock an das Photo-Feld des Mitarbeiterdatensatzes angehängt.
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();
}
}
Siehe auch
Konzepte
SQL Server-Datentypmappings (ADO.NET)
Weitere Ressourcen
Binäre Daten und Daten mit großen Werten in SQL Server (ADO.NET)