Skip to content

create-client-applications-for-filestream-data.md

Latest commit

 

History

History
84 lines (54 loc) · 8.5 KB

create-client-applications-for-filestream-data.md

File metadata and controls

84 lines (54 loc) · 8.5 KB
title Create Client Applications for FILESTREAM Data
description Learn how to use Win32 APIs to create client applications that access FILESTREAM data. See available functions, required steps, examples, and best practices.
author MikeRayMSFT
ms.author mikeray
ms.date 03/14/2017
ms.service sql
ms.subservice filestream
ms.topic conceptual
helpviewer_keywords
FILESTREAM [SQL Server], Win32

Create Client Applications for FILESTREAM Data

[!INCLUDE SQL Server] You can use Win32 APIs to read and write data to a FILESTREAM BLOB. The following steps are required:

  • Read the FILESTREAM file path.

  • Read the current transaction context.

  • Obtain a Win32 handle and use the handle to read and write data to the FILESTREAM BLOB.

Note

The examples in this topic require the FILESTREAM-enabled database and table that are created in Create a FILESTREAM-Enabled Database and Create a Table for Storing FILESTREAM Data.

Functions for Working with FILESTREAM Data

When you use FILESTREAM to store binary large object (BLOB) data, you can use Win32 APIs to work with the files. To support working with FILESTREAM BLOB data in Win32 applications, [!INCLUDEssNoVersion] provides the following functions and API:

  • PathName returns a path as a token to a BLOB. An application uses this token to obtain a Win32 handle and operate on BLOB data.

    When the database that contains FILESTREAM data belongs to an Always On availability group, then the PathName function returns a virtual network name (VNN) instead of a computer name.

  • GET_FILESTREAM_TRANSACTION_CONTEXT() returns a token that represents the current transaction of a session. An application uses this token to bind FILESTREAM file system streaming operations to the transaction.

  • The OpenSqlFilestream API obtains a Win32 file handle. The application uses the handle to stream the FILESTREAM data, and can then pass the handle to the following Win32 APIs: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile, or FlushFileBuffers. If the application calls any other API by using the handle, an ERROR_ACCESS_DENIED error is returned. The application should close the handle by using CloseHandle.

All FILESTREAM data container access is performed in a [!INCLUDEssNoVersion] transaction. [!INCLUDEtsql] statements can be executed in the same transaction to maintain consistency between SQL data and FILESTREAM data.

Steps for Accessing FILESTREAM Data

Reading the FILESTREAM File Path

Each cell in a FILESTREAM table has a file path that is associated with it. To read the path, use the PathName property of a varbinary(max) column in a [!INCLUDEtsql] statement. The following example shows how to read the file path of a varbinary(max) column.

:::code language="sql" source="codesnippet/tsql/create-client-applicatio_1.sql":::

Reading the Transaction Context

To obtain the current transaction context, use the [!INCLUDEtsql] GET_FILESTREAM_TRANSACTION_CONTEXT() function. The following example shows how to begin a transaction and read the current transaction context.

:::code language="sql" source="codesnippet/tsql/create-client-applicatio_2.sql":::

Obtaining a Win32 File Handle

To obtain a Win32 file handle, call the OpenSqlFilestream API. This API is exported from the sqlncli.dll file. The returned handle can be passed to any of the following Win32 APIs: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile, or FlushFileBuffers. The following examples show you how to obtain a Win32 File handle and use it to read and write data to a FILESTREAM BLOB.

[!code-csFILESTREAM#FS_CS_ReadAndWriteBLOB]

[!code-vbFILESTREAM#FS_VB_ReadAndWriteBLOB]

[!code-cppFILESTREAM#FS_CPP_WriteBLOB]

Best Practices for Application Design and Implementation

  • When you are designing and implementing applications that use FILESTREAM, consider the following guidelines:

  • Use NULL instead of 0x to represent a non-initialized FILESTREAM column. The 0x value causes a file to be created, and NULL does not.

  • Avoid insert and delete operations in tables that contain nonnull FILESTREAM columns. Insert and delete operations can modify the FILESTREAM tables that are used for garbage collection. This can cause an application's performance to decrease over time.

  • In applications that use replication, use NEWSEQUENTIALID() instead of NEWID(). NEWSEQUENTIALID() performs better than NEWID() for GUID generation in these applications.

  • The FILESTREAM API is designed for Win32 streaming access to data. Avoid using [!INCLUDEtsql] to read or write FILESTREAM binary large objects (BLOBs) that are larger than 2 MB. If you must read or write BLOB data from [!INCLUDEtsql], make sure that all BLOB data is consumed before you try to open the FILESTREAM BLOB from Win32. Failure to consume all the [!INCLUDEtsql] data might cause any successive FILESTREAM open or close operations to fail.

  • Avoid [!INCLUDEtsql] statements that update, append or prepend data to the FILESTREAM BLOB. This causes the BLOB data to be spooled into the tempdb database and then back into a new physical file.

  • Avoid appending small BLOB updates to a FILESTREAM BLOB. Each append causes the underlying FILESTREAM files to be copied. If an application has to append small BLOBs, write the BLOBs into a varbinary(max) column, and then perform a single write operation to the FILESTREAM BLOB when the number of BLOBs reaches a predetermined limit.

  • Avoid retrieving the data length of lots of BLOB files in an application. This is a time-consuming operation because the size is not stored in the [!INCLUDEssDEnoversion]. If you must determine the length of a BLOB file, use the [!INCLUDEtsql] DATALENGTH() function to determine the size of the BLOB if it is closed. DATALENGTH() does not open the BLOB file to determine its size.

  • If an application uses Message Block1 (SMB1) protocol, FILESTREAM BLOB data should be read in 60-KB multiples to optimize performance.

See Also

Avoid Conflicts with Database Operations in FILESTREAM Applications
Access FILESTREAM Data with OpenSqlFilestream
Binary Large Object (Blob) Data (SQL Server)
Make Partial Updates to FILESTREAM Data