Large BLOBs and UPDATETEXT (ADO.NET)

Large BLOBs and UPDATETEXT

Switch on low bandwidth view

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

.NET Framework Developer's Guide

Large BLOBs and UPDATETEXT (ADO.NET)

There are limited options for working with binary large objects (BLOBs) in versions of SQL Server earlier than SQL Server 2005. You can write a BLOB to a database by inserting or updating a field with a string value or byte array, depending on the type of field in your database. However, a BLOB can be quite large and therefore might consume significant system memory when written as a single value, decreasing application performance.

A common practice to reduce the amount of memory used when writing a BLOB value is to write the BLOB to the database in "chunks". The process of writing a BLOB to a database in this way depends on the capabilities of your database.

Transact-SQL UPDATETEXT Example

The following sample demonstrates how to write a BLOB in chunks to SQL Server. The sample adds a new record to the Employees table of the Northwind database, including an image of the employee, which is a BLOB. The sample uses the UPDATETEXT function of SQL Server to write the image of the newly added employee to the Photo field in chunks of a specified size.

The UPDATETEXT function requires a pointer to the BLOB field being updated. In this sample, when the new employee record is added, the SQL Server TEXTPTR function is called to return a pointer to the Photo field of the new record. The returned pointer value is passed back as an output parameter. The code in the sample retains this pointer and passes it to UPDATETEXT when appending the chunks of data.

The Transact-SQL used to insert the new employee record and retain the pointer to the Photo field is shown in the following example (where @Identity and @Pointer are identified as output parameters for the 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

Note that an initial value of 0x0 (null) is inserted into the Photo field. This ensures that a pointer value can be retrieved for the Photo field of the newly inserted record. However, the null value will not affect the appended chunks of data.

Having retained a pointer to the Photo field in the newly inserted record, the sample can then append chunks of data to the BLOB field using the UPDATETEXT function of SQL Server. The UPDATETEXT function takes as input the field identifier (Employees.Photo), the pointer to the BLOB field, an offset value that represents the location in the BLOB where the current chunk will be written, and the chunk of data to append. The following code example shows the syntax for the UPDATETEXT function (where @Pointer, @Offset, and @Bytes are identified as input parameters for the SqlCommand).

UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes

The offset value is determined by the size of the memory buffer, which you determine based on the needs of your application. A large buffer size will write the BLOB faster, but will use more system memory. This sample uses a rather small buffer size of 128 bytes. The offset value starts at 0 for the first chunk of data, and is incremented by the size of the buffer for each consecutive chunk.

ADO.NET Updating Example

This example retrieves the employee photo, from a supplied file path, in chunks. Each chunk is read into a byte array per the specified buffer size. The byte array is then set as the value of the @Bytes input parameter of the SqlCommand. The @Offset parameter value is updated and the SqlCommand is executed, which appends the current chunk of bytes to the Photo field of the employee record.

Visual Basic

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

Concepts

SQL Server Data Type Mappings (ADO.NET)

Other Resources

Binary and Large-Value Data (ADO.NET)

Retrieving and Modifying Data in ADO.NET