Sparsames Verwenden von Ressourcen beim Schreiben von BLOB-Werten in SQL Server

Sie können ein BLOB (Binary Large Object) in eine Datenbank schreiben, indem Sie je nach Feldtyp in der Datenbank einen Zeichenfolgenwert bzw. ein Bytearray in ein Feld einfügen bzw. dieses aktualisieren (siehe Schreiben von BLOB-Werten in eine Datenbank). Ein BLOB kann u. U. jedoch sehr umfangreich sein und, sofern es als einzelner Wert geschrieben wird, den Systemspeicher erheblich beanspruchen und damit die Leistung der Anwendung verringern.

Eine bewährte Methode zur Reduzierung der Speicherbelegung stellt das Schreiben eines BLOB-Wertes in kleinen Blöcken in die Datenbank dar. Auf welche Weise ein BLOB-Wert bei Verwendung dieser Methode in eine Datenbank geschrieben wird, richtet sich nach der Funktionalität der jeweiligen Datenbank.

Das folgende Beispiel veranschaulicht das Schreiben eines BLOBs in kleinen Blöcken bei Verwendung von SQL Server. In dem Beispiel wird der Tabelle Employees der Northwind-Datenbank ein neuer Datensatz hinzufügt. Der Datensatz enthält ein Bild des Mitarbeiters in Form eines BLOBs. Das Bild des neu hinzugefügten Mitarbeiters wird mit der SQL Server-Funktion UPDATETEXT in kleinen Blöcken mit einer festgelegten Größe in das Feld Photo geschrieben.

Die Funktion UPDATETEXT erfordert einen Zeiger auf das zu aktualisierende BLOB-Feld. Nach dem Hinzufügen des neuen Mitarbeiterdatensatzes wird die SQL Server-Funktion TEXTPTR aufgerufen, um einen Zeiger auf das Photo-Feld des neuen Datensatzes zurückzugeben. Der Zeigerwert wird als Ausgabeparameter zurückgegeben. Im Beispielcode wird dieser Zeiger beibehalten und beim Hinzufügen der Datenblöcke an die UPDATETEXT-Funktion übergeben.

Die zum Einfügen des neuen Mitarbeiterdatensatzes sowie zum Beibehalten des Zeigers auf das Feld Photo verwendete Transact-SQL-Anweisung wird im folgenden Beispiel veranschaulicht (wobei @Identity und @Pointer als Ausgabeparameter für 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 Feld Photo ein Anfangswert von 0x0 (NULL) eintragen wird. Diese Eingabe stellt sicher, dass ein Zeigerwert für das Feld Photo des neu eingefügten Datensatzes abgerufen werden kann. Der NULL-Wert wirkt sich jedoch nicht auf die angehängten Datenblöcke aus.

Nachdem die Beibehaltung eines Zeigers auf das Feld Photo des neu eingefügten Datensatz realisiert ist, können dem BLOB-Feld mit der SQL Server-Funktion UPDATETEXT Datenblöcke angefügt werden. Die UPDATETEXT-Funktion verwendet als Eingabe den Feldbezeichner (Employees.Photo), den Zeiger auf das BLOB-Feld, einen Offsetwert als Position im BLOB, an die 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 SqlCommand angegeben sind).

UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes

Der Offsetwert richtet sich nach der Größe des Speicherpuffers, der an die jeweiligen Anwendungsanforderungen angepasst ist. Bei einem umfangreichen 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 Bytes 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.

In diesem Beispiel wird das Mitarbeiterphoto in kleinen Blöcken aus einem vorgegebenen Dateipfad abgerufen. Jeder Block wird hierbei in ein Bytearray von der Größe des festgelegten Puffers gelesen. Das Bytearray wird anschließend als Wert des @Bytes-Eingabeparameters von SqlCommand festgelegt. Der @Offset-Parameterwert wird aktualisiert, und SqlCommand wird ausgeführt. Damit wird der aktuelle Byteblock an den Inhalt des Photo-Feldes des Mitarbeiterdatensatzes anhä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(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();
  }
}

Siehe auch

Schreiben von BLOB-Werten in eine Datenbank | Beispielszenarien zu ADO.NET | Zugreifen auf Daten mit ADO.NET | Datenzugriff mit .NET Framework-Datenprovidern

Last updated on 2008-03-31

Freigeben über

Sparsames Verwenden von Ressourcen beim Schreiben von BLOB-Werten in SQL Server

Siehe auch

Zusätzliche Ressourcen