Creazione di applicazioni client per dati FILESTREAMCreate Client Applications for FILESTREAM Data

In questo argomento si applica a: SìSQL ServernonDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse Le API Win32 consentono di leggere e scrivere dati in un oggetto BLOB FILESTREAM. You can use Win32 APIs to read and write data to a FILESTREAM BLOB. È necessario effettuare le operazioni seguenti:The following steps are required:

  • Leggere il percorso del file FILESTREAM.Read the FILESTREAM file path.

  • Leggere il contesto di transazione corrente.Read the current transaction context.

  • Ottenere un handle Win32 e utilizzarlo per leggere e scrivere dati in un oggetto BLOB FILESTREAM.Obtain a Win32 handle and use the handle to read and write data to the FILESTREAM BLOB.

Nota

Gli esempi riportati in questo argomento richiedono il database e la tabella abilitati per FILESTREAM creati in Creare un database abilitato per FILESTREAM e Creare una tabella per archiviare dati FILESTREAM.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.

Funzioni per l'utilizzo di Dati FILESTREAMFunctions for Working with FILESTREAM Data

Se si utilizza FILESTREAM per archiviare dati di oggetti binari di grandi dimensioni (BLOB), è possibile utilizzare API Win32 per usare i file.When you use FILESTREAM to store binary large object (BLOB) data, you can use Win32 APIs to work with the files. Per supportare l'utilizzo dei dati BLOB FILESTREAM nelle applicazioni Win32, SQL ServerSQL Server fornisce le seguenti funzioni e API:To support working with FILESTREAM BLOB data in Win32 applications, SQL ServerSQL Server provides the following functions and API:

  • PathName restituisce un percorso come token a un BLOB.PathName returns a path as a token to a BLOB. In un'applicazione viene utilizzato questo token per ottenere un handle Win32 e operare sui dati BLOB.An application uses this token to obtain a Win32 handle and operate on BLOB data.

    Quando il database che contiene dati FILESTREAM appartiene a un gruppo di disponibilità Always On e la funzione PathName restituire un nome di rete virtuale (VNN) invece di un nome computer.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() restituisce un token che rappresenta la transazione corrente di una sessione.GET_FILESTREAM_TRANSACTION_CONTEXT() returns a token that represents the current transaction of a session. In un'applicazione viene utilizzato questo token per associare le operazioni di flusso file system FILESTREAM alla transazione.An application uses this token to bind FILESTREAM file system streaming operations to the transaction.

  • Un handle di file Win32 è ottenuto in API OpenSqlFilestream .The OpenSqlFilestream API obtains a Win32 file handle. L'applicazione usa l'handle per trasmettere i dati FILESTREAM e può passare l'handle alle seguenti API Win32: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFileo FlushFileBuffers.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. Se l'applicazione chiama qualsiasi altra API utilizzando l'handle, viene restituito un errore ERROR_ACCESS_DENIED.If the application calls any other API by using the handle, an ERROR_ACCESS_DENIED error is returned. L'applicazione deve chiudere l'handle usando CloseHandle.The application should close the handle by using CloseHandle.

    L'accesso al contenitore di tutti i dati FILESTREAM viene eseguito in una transazione SQL ServerSQL Server .All FILESTREAM data container access is performed in a SQL ServerSQL Server transaction. Transact-SQLTransact-SQL possono essere eseguite nella stessa transazione per mantenere la coerenza tra i dati SQL e i dati FILESTREAM. statements can be executed in the same transaction to maintain consistency between SQL data and FILESTREAM data.

Passaggi per l'accesso a dati FILESTREAMSteps for Accessing FILESTREAM Data

Lettura del percorso del file FILESTREAMReading the FILESTREAM File Path

A ogni cella di una tabella FILESTREAM è associato un percorso del file.Each cell in a FILESTREAM table has a file path that is associated with it. Per leggere il percorso, usare la proprietà PathName di una colonna varbinary(max) in un'istruzione Transact-SQLTransact-SQL .To read the path, use the PathName property of a varbinary(max) column in a Transact-SQLTransact-SQL statement. L'esempio seguente illustra come leggere il percorso del file di una colonna varbinary(max) .The following example shows how to read the file path of a varbinary(max) column.

DECLARE @filePath varchar(max)

SELECT @filePath = Chart.PathName()
FROM Archive.dbo.Records
WHERE SerialNumber = 3

PRINT @filepath

Lettura del contesto di transazioneReading the Transaction Context

Per ottenere il contesto di transazione corrente, usare la funzione Transact-SQLTransact-SQL GET_FILESTREAM_TRANSACTION_CONTEXT() .To obtain the current transaction context, use the Transact-SQLTransact-SQL GET_FILESTREAM_TRANSACTION_CONTEXT() function. Nell'esempio seguente si illustra come iniziare una transazione e come leggere il contesto di transazione corrente.The following example shows how to begin a transaction and read the current transaction context.

DECLARE @txContext varbinary(max)

BEGIN TRANSACTION
SELECT @txContext = GET_FILESTREAM_TRANSACTION_CONTEXT()
PRINT @txContext
COMMIT

Ottenere un handle di file Win32Obtaining a Win32 File Handle

Per ottenere un handle di file Win32, chiamare l'API OpenSqlFilestream.To obtain a Win32 file handle, call the OpenSqlFilestream API. Tale API viene esportata dal file sqlncli.dll.This API is exported from the sqlncli.dll file. L'handle restituito può essere passato a una qualsiasi delle API Win32 seguenti: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFileo FlushFileBuffers.The returned handle can be passed to any of the following Win32 APIs: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile, or FlushFileBuffers. Negli esempi seguenti si illustra come ottenere un handle di file Win32 e come utilizzarlo per leggere e scrivere dati in un oggetto BLOB FILESTREAM.The following examples show you how to obtain a Win32 File handle and use it to read and write data to a FILESTREAM BLOB.

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;
        }
    }
}
Imports System.IO
Imports System 
Imports System.Collections.Generic 
Imports System.Text 
Imports System.Data 
Imports System.Data.SqlClient 
Imports System.Data.SqlTypes 

Module Module1
    Public Sub Main(ByVal args As String())
        '        Dim sqlConnection As New SqlConnection("Integrated Security=true;server=(local)")
        Dim sqlConnection As New SqlConnection("Integrated Security=true;server=kellyreyue\MSSQL1")

        Dim sqlCommand As 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"

            Dim filePath As String = Nothing

            Dim pathObj As Object = sqlCommand.ExecuteScalar()
            If Not pathObj.Equals(DBNull.Value) Then
                filePath = DirectCast(pathObj, String)
            Else
                Throw New System.Exception("Chart.PathName() failed" + " to read the path name " + " for the Chart column.")
            End If

            '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. 

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

            sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"

            Dim obj As Object = sqlCommand.ExecuteScalar()
            Dim txContext As Byte() = Nothing

            Dim contextLength As UInteger

            If Not obj.Equals(DBNull.Value) Then
                txContext = DirectCast(obj, Byte())
                contextLength = txContext.Length()
            Else
                Dim message As String = "GET_FILESTREAM_TRANSACTION_CONTEXT() failed"
                Throw New System.Exception(message)
            End If

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

            Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.ReadWrite)

            Dim buffer As Byte() = New Byte(511) {}

            Dim numBytes As Integer = 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. 

            Dim someData As String = "EKG data."
            Dim unicode As Encoding = Encoding.GetEncoding(0)

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

            'Read the data from the FILESTREAM 
            'BLOB. 

            sqlFileStream.Seek(0, SeekOrigin.Begin)

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

            Dim readData As String = unicode.GetString(buffer)

            If numBytes <> 0 Then
                Console.WriteLine(readData)
            End If

            '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 ex As System.Exception
            Console.WriteLine(ex.ToString())
        Finally
            sqlConnection.Close()
        End Try
        Return
    End Sub
End Module
#include <windows.h>
#include <sql.h>
#include<sqltypes.h>
#include<sqlext.h>
#include <stdio.h>
#include <sqlncli.h>

#define COPYBUFFERSIZE 4096

/// <summary>
///This class iterates though the ODBC error queue and prints all of the
///accumulated error messages to the console.
/// </summary>

class ODBCErrors
{
private:
    int         m_iLine;    //Source code line on which the error occurred
    SQLSMALLINT m_type;     //Type of handle on which the error occurred
    SQLHANDLE   m_handle;   //ODBC handle on which the error occurred

public:
    /// <summary>
    ///Default constructor for the ODBCErrors class
    ///</summary>

    ODBCErrors()
    {
        m_iLine  = -1;
        m_type   = 0;
        m_handle = SQL_NULL_HANDLE;
    }

    /// <summary>
    ///Constructor for the ODBCErrors class
    /// </summary>
    /// <param name="iLine">
    /// This parameter is the source code line
    /// at which the error occurred.
    ///</param>
    /// <param name="type">
    /// This parameter is the type of ODBC handle passed in
    /// the next parameter.
    ///</param>
    /// <param name="handle">
    /// This parameter is the handle on which the error occurred.
    ///</param>

    ODBCErrors(int iLine, SQLSMALLINT type, SQLHANDLE handle)
    {
        m_iLine  = iLine;
        m_type   = type;
        m_handle = handle;
    }

    ///<summary>
    /// This method iterates though the error stack for the handle passed
    /// into the constructor and displays those errors on the console.
    ///</summary>

    void Print()
    {
        SQLSMALLINT i = 0, len = 0;
        SQLINTEGER  native;
        SQLTCHAR    state[9], text[256];
        SQLRETURN   sqlReturn = SQL_SUCCESS;

        if ( m_handle == SQL_NULL_HANDLE )
        {
            wprintf_s(TEXT("The error handle is not a valid handle.\n"), m_iLine);
            return;
        }

        wprintf_s(TEXT("Error Line(%d)\n"), m_iLine);

        while( sqlReturn == SQL_SUCCESS )
        {
            len = 0;

            sqlReturn = SQLGetDiagRec(
                m_type,
                m_handle,
                ++i,
                state,
                &native,
                text,
                sizeof(text)/sizeof(SQLTCHAR),
                &len);

            if ( SQL_SUCCEEDED(sqlReturn) )
                wprintf_s(TEXT("Error(%d, %ld, %s) : %s\n"), i, native, state, text);
        }
    }
};


BOOL CopyFileToSQL(LPTSTR srcFilePath, LPTSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken)
{
	BOOL bRetCode = FALSE;

	HANDLE srcHandle = INVALID_HANDLE_VALUE;
	HANDLE dstHandle = INVALID_HANDLE_VALUE;
    BYTE   buffer[COPYBUFFERSIZE] = { 0 };

    TCHAR *szErrMsgSrc   = TEXT("Error opening source file.");
    TCHAR *szErrMsgDst   = TEXT("Error opening destFile file.");
    TCHAR *szErrMsgRead  = TEXT("Error reading source file.");
    TCHAR *szErrMsgWrite = TEXT("Error writing SQL file.");

    try
    {
	    if ( (srcHandle = CreateFile(
            srcFilePath,
            GENERIC_READ,
            FILE_SHARE_READ,
            NULL,
            OPEN_EXISTING,
            FILE_FLAG_SEQUENTIAL_SCAN,
            NULL)) == INVALID_HANDLE_VALUE )
            throw szErrMsgSrc;

    	if ( (dstHandle =  OpenSqlFilestream(
            dstFilePath,
            Write,
            0,
            transactionToken,
            cbTransactionToken,
            0)) == INVALID_HANDLE_VALUE)
            throw szErrMsgDst;

        DWORD bytesRead = 0;
        DWORD bytesWritten = 0;

    	do
        {
            if ( ReadFile(srcHandle, buffer, COPYBUFFERSIZE, &bytesRead, NULL) == 0 )
                throw szErrMsgRead;

    		if (bytesRead > 0)
            {
        		if ( WriteFile(dstHandle, buffer, bytesRead, &bytesWritten, NULL) == 0 )
                    throw szErrMsgWrite;
            }
		} while (bytesRead > 0);

        bRetCode = TRUE;
	}
    catch( TCHAR *szErrMsg )
    {
        wprintf_s(szErrMsg);
        bRetCode = FALSE;
    }

    if ( srcHandle != INVALID_HANDLE_VALUE )
        CloseHandle(srcHandle);

    if ( dstHandle != INVALID_HANDLE_VALUE )
    	CloseHandle(dstHandle);

    return bRetCode;
}

void main()
{
    TCHAR *sqlDBQuery =
       TEXT("INSERT INTO Archive.dbo.Records(Id, SerialNumber, Chart)")
       TEXT(" OUTPUT GET_FILESTREAM_TRANSACTION_CONTEXT(), inserted.Chart.PathName()")
       TEXT("VALUES (newid (), 5, CONVERT(VARBINARY, '**Temp**'))");

	SQLCHAR transactionToken[32];
    
    SQLHANDLE henv = SQL_NULL_HANDLE;
    SQLHANDLE hdbc              = SQL_NULL_HANDLE;
    SQLHANDLE hstmt             = SQL_NULL_HANDLE;

    try
    {
        //These statements Initialize ODBC for the client application and
        //connect to the database.

        if ( SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);

        if ( SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3, NULL) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);

        if ( SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);

	    //This code assumes that the dataset name "Sql Server FILESTREAM"
	    //has been previously created on the client computer system. An
        //ODBC DSN is created with the ODBC Data Source item in
        //the Windows Control Panel.

	    if ( SQLConnect(hdbc, TEXT("Sql Server FILESTREAM"),
                SQL_NTS, NULL, 0, NULL, 0) <= 0 )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);

        //FILESTREAM requires that all read and write operations occur
        //within a transaction.
        if ( SQLSetConnectAttr(hdbc,
            SQL_ATTR_AUTOCOMMIT,
            (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
            SQL_IS_UINTEGER) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);

        if ( SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);

        if ( SQLExecDirect(hstmt, sqlDBQuery, SQL_NTS) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        //Retrieve the transaction token.
        if ( SQLFetch(hstmt) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        SQLINTEGER cbTransactionToken = sizeof(transactionToken);

    	if ( SQLGetData(hstmt, 1,
            SQL_C_BINARY,
            transactionToken,
            sizeof(transactionToken),
            &cbTransactionToken) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        //Retrieve the file path for the inserted record.

        TCHAR dstFilePath[1024];
        SQLINTEGER cbDstFilePath;

        if ( SQLGetData(hstmt, 2, SQL_C_TCHAR, dstFilePath, sizeof(dstFilePath), &cbDstFilePath) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

    	if ( SQLCloseCursor(hstmt) != SQL_SUCCESS )
            throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);

        SQLUSMALLINT mode = SQL_ROLLBACK;

        if ( CopyFileToSQL(
            TEXT("C:\\Users\\Data\\chart1.jpg"),
            dstFilePath,
            transactionToken,
            cbTransactionToken) == TRUE )
            mode = SQL_COMMIT;

        SQLTransact(henv, hdbc, mode);
    }
    catch(ODBCErrors *pErrors)
    {
        pErrors->Print();
        delete pErrors;
    }

    if ( hstmt != SQL_NULL_HANDLE )
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    if ( hdbc != SQL_NULL_HANDLE )
        SQLDisconnect(hdbc);

    if ( hdbc != SQL_NULL_HANDLE )
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc); 

    if ( henv != SQL_NULL_HANDLE )
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

Procedure consigliate per la progettazione e l'implementazione di applicazioniBest Practices for Application Design and Implementation

  • Quando si progettano e implementano applicazioni che utilizzano FILESTREAM, tenere presenti le linee guida seguenti:When you are designing and implementing applications that use FILESTREAM, consider the following guidelines:

  • Utilizzare NULL anziché 0x per rappresentare una colonna FILESTREAM non inizializzata.Use NULL instead of 0x to represent a non-initialized FILESTREAM column. Il valore 0x provoca la creazione di un file, a differenza di NULL.The 0x value causes a file to be created, and NULL does not.

  • Evitare di eseguire operazioni di inserimento ed eliminazione nelle tabelle che contengono colonne FILESTREAM non Null.Avoid insert and delete operations in tables that contain nonnull FILESTREAM columns. Le operazioni di inserimento ed eliminazione possono modificare le tabelle FILESTREAM utilizzate per Garbage Collection.Insert and delete operations can modify the FILESTREAM tables that are used for garbage collection. In questo caso, è possibile che le prestazioni dell'applicazione si riducano nel corso del tempo.This can cause an application's performance to decrease over time.

  • Nelle applicazioni che utilizzano la replica, utilizzare NEWSEQUENTIALID() anziché NEWID().In applications that use replication, use NEWSEQUENTIALID() instead of NEWID(). NEWSEQUENTIALID() offre prestazioni migliori di NEWID() per la generazione dei GUID in queste applicazioni.NEWSEQUENTIALID() performs better than NEWID() for GUID generation in these applications.

  • L'API FILESTREAM è progettata per l'accesso ai dati tramite flusso Win32.The FILESTREAM API is designed for Win32 streaming access to data. Evitare di usare Transact-SQLTransact-SQL per la lettura o la scrittura di oggetti BLOB di FILESTREAM di dimensioni maggiori di 2 MB.Avoid using Transact-SQLTransact-SQL to read or write FILESTREAM binary large objects (BLOBs) that are larger than 2 MB. Se è necessario leggere o scrivere dati BLOB da Transact-SQLTransact-SQL, verificare che tutti i dati BLOB vengano utilizzati prima di tentare di aprire l'oggetto BLOB di FILESTREAM da Win32.If you must read or write BLOB data from Transact-SQLTransact-SQL, make sure that all BLOB data is consumed before you try to open the FILESTREAM BLOB from Win32. Se non vengono utilizzati tutti i dati Transact-SQLTransact-SQL , è possibile che le successive operazioni di apertura o chiusura di FILESTREAM non riescano.Failure to consume all the Transact-SQLTransact-SQL data might cause any successive FILESTREAM open or close operations to fail.

  • Evitare di utilizzare istruzioni Transact-SQLTransact-SQL per aggiornare, aggiungere o anteporre dati all'oggetto BLOB di FILESTREAM.Avoid Transact-SQLTransact-SQL statements that update, append or prepend data to the FILESTREAM BLOB. In caso contrario, si verificherà lo spooling dei dati BLOB nel database tempdb e quindi in un nuovo file fisico.This causes the BLOB data to be spooled into the tempdb database and then back into a new physical file.

  • Evitare di aggiungere piccoli aggiornamenti BLOB a un oggetto BLOB di FILESTREAM.Avoid appending small BLOB updates to a FILESTREAM BLOB. Con ogni operazione di aggiunta, vengono copiati i file FILESTREAM sottostanti.Each append causes the underlying FILESTREAM files to be copied. Se un'applicazione deve aggiungere piccoli oggetti BLOB, scrivere tali oggetti in una colonna varbinary(max) , quindi eseguire una sola operazione di scrittura nell'oggetto BLOB di FILESTREAM quando il numero di oggetti BLOB raggiunge un limite predeterminato.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.

  • Evitare di recuperare la lunghezza dei dati di molti file BLOB in un'applicazione.Avoid retrieving the data length of lots of BLOB files in an application. Si tratta di un'operazione dispendiosa in termini di tempo perché le dimensioni non vengono archiviate nel Motore di database di SQL ServerSQL Server Database Engine.This is a time-consuming operation because the size is not stored in the Motore di database di SQL ServerSQL Server Database Engine. Se è necessario determinare la lunghezza di un file BLOB, usare la funzione Transact-SQLTransact-SQL DATALENGTH() per determinare le dimensioni dell'oggetto BLOB, se è chiuso.If you must determine the length of a BLOB file, use the Transact-SQLTransact-SQL DATALENGTH() function to determine the size of the BLOB if it is closed. DATALENGTH() non apre il file BLOB per determinarne le dimensioni.DATALENGTH() does not open the BLOB file to determine its size.

  • Se in un'applicazione viene utilizzato il protocollo Message Block1 (SMB1), i dati BLOB di FILESTREAM devono essere letti in multipli di 60 KB per ottimizzare le prestazioni.If an application uses Message Block1 (SMB1) protocol, FILESTREAM BLOB data should be read in 60-KB multiples to optimize performance.

Vedere ancheSee Also

Evitare conflitti con le operazioni del database nelle applicazioni di FILESTREAM Avoid Conflicts with Database Operations in FILESTREAM Applications
Accesso ai dati FILESTREAM con OpenSqlFilestream Access FILESTREAM Data with OpenSqlFilestream
Dati BLOB (Binary Large Object) (Blob) Data (SQL Server) Binary Large Object (Blob) Data (SQL Server)
Esecuzione di aggiornamenti parziali di dati FILESTREAMMake Partial Updates to FILESTREAM Data