Usar UPDATETEXT con datos binarios (ADO.NET)
Hay opciones limitadas para trabajar con objetos binarios de gran tamaño (BLOB) en versiones de SQL Server anteriores a SQL Server 2005. Para escribir un 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. 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.
Ejemplo de UPDATETEXT de Transact-SQL
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.
Ejemplo de actualización de ADO.NET
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(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();
}
}
Vea también
Conceptos
Asignar tipos de datos de SQL Server (ADO.NET)