Conservar recursos al escribir valores BLOB en SQL Server

Artículo
04/11/2008

Para escribir un objeto binario grande (Binary Large Object, BLOB) en una base de datos, inserte o actualice un campo con un valor de cadena o con una matriz de bytes, según el tipo de campo de la base de datos (vea Escribir valores BLOB en una base de datos). No obstante, un BLOB puede ser bastante grande y, por lo tanto, es posible que utilice una gran cantidad de memoria del sistema cuando se escribe como un único valor, lo que produce una reducción del rendimiento de la aplicación.

Una práctica frecuente para reducir la cantidad de memoria utilizada al escribir un valor BLOB consiste en escribir el BLOB en la base de datos en "fragmentos". El proceso de escritura de un BLOB en una base de datos de esta forma depende de las características de la base de datos.

En el siguiente ejemplo se muestra cómo escribir un BLOB en fragmentos en SQL Server. En el ejemplo se agrega un registro nuevo a la tabla Employees de la base de datos Northwind, y se incluye una imagen del empleado, que es un BLOB. En el ejemplo se utiliza la función UPDATETEXT de SQL Server para introducir la imagen del empleado recién agregado en el campo Photo en fragmentos de un tamaño especificado.

La función UPDATETEXT requiere un puntero al campo BLOB que se está actualizando. En este ejemplo, al agregar el registro del nuevo empleado, se llama a la función TEXTPTR de SQL Server para devolver un puntero al campo Photo del nuevo registro. El valor de puntero devuelto se vuelve a pasar como parámetro de salida. En el código del ejemplo, este puntero se mantiene y se pasa a la función UPDATETEXT al agregar los fragmentos de datos.

En el siguiente ejemplo se muestra la instrucción Transact-SQL que se utiliza para insertar el registro del nuevo empleado y mantener el puntero al campo Photo (donde @Identity y @Pointer se identifican como parámetros de salida para 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

Tenga en cuenta que se inserta un valor inicial de 0x0 (null) en el campo Photo. De este modo, queda garantizada la recuperación de un valor de puntero para el campo Photo del registro recién insertado. Sin embargo, el valor null no afectará a los fragmentos de datos agregados.

Si se ha mantenido un puntero al campo Photo del registro recién insertado, como en el ejemplo, entonces se pueden agregar fragmentos de datos al campo BLOB mediante la función UPDATETEXT de SQL Server. La función UPDATETEXT toma como entrada el identificador de campo (Employees.Photo), el puntero al campo BLOB, un valor de desplazamiento que representa la ubicación del BLOB en la que se escribirá el fragmento actual y el fragmento de datos que se va a agregar. En el siguiente ejemplo de código se muestra la sintaxis de la función UPDATETEXT (donde @Pointer, @Offset y @Bytes se identifican como parámetros de entrada para SqlCommand).

UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes

El valor de desplazamiento viene determinado por el tamaño del búfer de memoria que, a su vez, viene determinado por el usuario en función de las necesidades de la aplicación. Si el tamaño del búfer es grande, el BLOB se escribirá con mayor rapidez, pero se utilizará más espacio de memoria del sistema. En este ejemplo se utiliza un tamaño de búfer bastante pequeño, de 128 bytes. El valor de desplazamiento comienza en 0 para el primer fragmento de datos y se va incrementando por el tamaño del búfer para cada fragmento consecutivo.

En este ejemplo se recupera la foto del empleado en fragmentos desde la ruta de acceso del archivo proporcionada. Cada fragmento se lee en una matriz de bytes por el tamaño de búfer especificado. La matriz de bytes se establece entonces como el valor del parámetro de entrada @Bytes de SqlCommand. El valor de parámetro @Offset se actualiza y se ejecuta SqlCommand, que agrega el fragmento de bytes actual al campo Photo del registro del empleado.

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();
  }
}

Vea también

Escribir valores BLOB en una base de datos | Ejemplo de escenarios de ADO.NET | Acceso a datos con ADO.NET | Utilizar proveedores de datos de .NET Framework para obtener acceso a datos

Compartir a través de

Conservar recursos al escribir valores BLOB en SQL Server

Vea también

Recursos adicionales