Share via

Access FILESTREAM Data with OpenSqlFilestream

  • Article

The OpenSqlFilestream API obtains a Win32 compatible file handle for a FILESTREAM binary large object (BLOB) that is stored in the file system. The handle can be passed to any of the following Win32 APIs: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile, or FlushFileBuffers. If you pass this handle to any other Win32 API, the error ERROR_ACCESS_DENIED is returned. The handle must be closed by passing it to the Win32 CloseHandle API before the transaction is committed or rolled back. Failing to close the handle will cause server-side resource leaks.

All FILESTREAM data container access must be performed in a SQL Server transaction. Transact-SQL statements can also be executed in the same transaction. This maintains consistency between the SQL data and FILESTREAM BLOB data.

To access the FILESTREAM BLOB by using Win32, Windows Authorization must be enabled.

Important

When the file is opened for write access, the transaction is owned by the FILESTREAM agent. Only Win32 file I/O is allowed until the transaction is released. To release the transaction, the write handle must be closed.

Syntax


HANDLE OpenSqlFilestream (
    LPCWSTR FilestreamPath,
    SQL_FILESTREAM_DESIRED_ACCESS DesiredAccess,
    ULONG OpenOptions,
    LPBYTE FilestreamTransactionContext,
    SIZE_T FilestreamTransactionContextLength,
    PLARGE_INTEGER AllocationSize);

Parameters

  • FilestreamPath
    [in] Is the nvarchar(max) path that is returned by the PathName function. PathName must be called from the context of an account that has SQL Server SELECT or UPDATE permissions on the FILESTREAM table and column.

  • DesiredAccess
    [in] Sets the mode used to access FILESTREAM BLOB data. This value is passed to the DeviceIoControl Function.

    Name

    Value

    Meaning

    SQL_FILESTREAM_READ

    0

    Data can be read from the file.

    SQL_FILESTREAM_WRITE

    1

    Data can be written to the file.

    SQL_FILESTREAM_READWRITE

    2

    Data can be read and written from the file.

    Note

    These values are defined in the SQL_FILESTREAM_DESIRED_ACCESS enumeration in sqlncli.h.

  • OpenOptions
    [in] The file attributes and flags. This parameter can also include any combination of the following flags.

    Flag

    Value

    Meaning

    SQL_FILESTREAM_OPEN_NONE

    0x00000000:

    The file is being opened or created with no special options.

    SQL_FILESTREAM_OPEN_FLAG_ASYNC

    0x00000001L

    The file is being opened or created for asynchronous I/O.

    SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING

    0x00000002L

    The system opens the file by using no system caching.

    SQL_FILESTREAM_OPEN_FLAG_NO_WRITE_THROUGH 

    0x00000004L

    The system does not write through an intermediate cache. Writes go directly to disk.

    SQL_FILESTREAM_OPEN_FLAG_SEQUENTIAL_SCAN

    0x00000008L

    A file is accessed sequentially from beginning to end. The system can use this as a hint to optimize file caching. If an application moves the file pointer for random access, optimal caching may not occur.

    SQL_FILESTREAM_OPEN_FLAG_RANDOM_ACCESS

    0x00000010L

    A file is accessed randomly. The system can use this as a hint to optimize file caching.

  • FilestreamTransactionContext
    [in] The value that is returned by the GET_FILESTREAM_TRANSACTION_CONTEXT function.

  • FilestreamTransactionContextLength
    [in] Number of bytes in the varbinary(max) data that is returned by the GET_FILESTREAM_TRANSACTION_CONTEXT function. The function returns an array of N bytes. N is determined by the function and is a property of the byte array that is returned.

  • AllocationSize
    [in] Specifies the initial allocation size of the data file in bytes. It is ignored in read mode. This parameter can be NULL, in which case the default file system behavior is used.

Return Value

If the function succeeds, the return value is an open handle to a specified file. If the function fails, the return value is INVALID_HANDLE_VALUE. For extended error information, call GetLastError().

Examples

The following examples show you how to use the OpenSqlFilestream API to obtain a Win32 handle.

using System.IO;
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace FILESTREAM
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection sqlConnection = new SqlConnection(
                "Integrated Security=true;server=(local)");

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;

            try
            {
                sqlConnection.Open();

                //The first task is to retrieve the file path
                //of the SQL FILESTREAM BLOB that we want to
                //access in the application.

                sqlCommand.CommandText =
                      "SELECT Chart.PathName()"
                    + " FROM Archive.dbo.Records"
                    + " WHERE SerialNumber = 3";

                String filePath = null;

                Object pathObj = sqlCommand.ExecuteScalar();
                if (DBNull.Value != pathObj)
                    filePath = (string)pathObj;
                else
                {
                    throw new System.Exception(
                        "Chart.PathName() failed"
                      + " to read the path name "
                      + " for the Chart column.");
                }

                //The next task is to obtain a transaction
                //context. All FILESTREAM BLOB operations
                //occur within a transaction context to
                //maintain data consistency.

                //All SQL FILESTREAM BLOB access must occur in 
                //a transaction. MARS-enabled connections
                //have specific rules for batch scoped transactions,
                //which the Transact-SQL BEGIN TRANSACTION statement
                //violates. To avoid this issue, client applications 
                //should use appropriate API facilities for transaction management, 
                //management, such as the SqlTransaction class.

                SqlTransaction transaction = sqlConnection.BeginTransaction("mainTranaction");
                sqlCommand.Transaction = transaction;

                sqlCommand.CommandText =
                    "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";

                Object obj = sqlCommand.ExecuteScalar();
                byte[] txContext = (byte[])obj;

                //The next step is to obtain a handle that
                //can be passed to the Win32 FILE APIs.

                SqlFileStream sqlFileStream = new SqlFileStream(filePath, txContext, FileAccess.ReadWrite);

                byte[] buffer = new byte[512];

                int numBytes = 0;

                //Write the string, "EKG data." to the FILESTREAM BLOB.
                //In your application this string would be replaced with
                //the binary data that you want to write.

                string someData = "EKG data.";
                Encoding unicode = Encoding.GetEncoding(0);

                sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()),
                    0,
                    someData.Length);

                //Read the data from the FILESTREAM
                //BLOB.

                sqlFileStream.Seek(0L, SeekOrigin.Begin);

                numBytes = sqlFileStream.Read(buffer, 0, buffer.Length);

                string readData = unicode.GetString(buffer);

                if (numBytes != 0)
                    Console.WriteLine(readData);

                //Because reading and writing are finished, FILESTREAM 
                //must be closed. This closes the c# FileStream class, 
                //but does not necessarily close the the underlying 
                //FILESTREAM handle. 
                sqlFileStream.Close();

                //The final step is to commit or roll back the read and write
                //operations that were performed on the FILESTREAM BLOB.

                sqlCommand.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                sqlConnection.Close();
            }
            return;
        }
    }
}

Remarks

The SQL Server Native Client must be installed to use this API. The SQL Server Native Client is installed with SQL Server or SQL Server client tools. For more information, see Installing SQL Server Native Client.

See Also

Concepts

Binary Large Object (Blob) Data (SQL Server)

Make Partial Updates to FILESTREAM Data

Avoid Conflicts with Database Operations in FILESTREAM Applications