Bless large objects (BLOB) can be written to the database by inserting or updating fields with string values or byte arrays (depending on field types in the database). However, BLOB may be quite large, so a large number of system memory may be used when writing as a single value, thereby reducing the performance of the application.
This example adds a new record containing the employee image to the Employees table of the Northwind database. This image is a blob. This example uses SQL Server's UpdateText function to write images of newly added employees to the PHOTO field in the block area specified size.
The UpdateText function requires a pointer to the updated BLOB field. In this example, after adding a new employee's record, the SQL Server TextPtr function will be called to return a pointer to the new recorded Photo field. The returned pointer value will be passed as an output parameter. The code in the example reserves this pointer and passes it to the UpdateText when adding the data block area.
Transact-SQL for inserting new employee records and reserves the pointer to the PHOTO field will be displayed in the following example (where @IDENTITY and @Point are identified as the output parameters of 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 the initial value 0x0 (empty) is inserted in the Photo field. This ensures that the pointer value of the newly inserted PHOTO field can be retrieved. However, null values do not affect the appended data block area.
After retaining a pointer to the PHOTO field in the newly inserted record, the example can then add the data block area to the BLOB field using the SQL Server's UpdateText function. The UPDATETEXT function accepts the following objects as input: field identifier (Employees.Photo), pointing to the BLOB field, indicating the offset value of the location of the current block area, and the data block area to be added. The following code sample shows the syntax of the UpdateText function (where @ Pointer, @ Offset, and @BYTES are identified as the input parameters of SQLCommand).
UpdateText Employees.photo @point @offset 0 @bytes
The offset value is determined by the size of the memory buffer, and the size depends on the needs of the application. The big buffer is written in Blob faster, but more system memory will be used. The buffer used in this example is quite small, only 128 bytes. The offset value assigned to the first data block area is 0, and then the offset value is incremented by the size of the buffer size of each consecutive block.
<% @ Import namespace = "system"%> <% @ import name "%> <% @ import name"%> "system.data.sqlclient"%> <% @ import namespace = "system.io" %>
