Modificando dados de valores grandes (max) no ADO.NET

Os tipos de dados de objetos grandes (LOB) são os que excedem o tamanho de linha máximo de 8 KB. O SQL Server apresenta um especificador max para tipos de dados varchar, nvarchar e varbinary para permitir o armazenamento de valores tão grandes quanto 2^32 bytes. Colunas de tabela e variáveis Transact-SQL podem especificar tipos de dados varchar(max), nvarchar(max) ou varbinary(max). No ADO.NET, os tipos de dados max podem ser encontrados por DataReader e também podem ser especificados como valores de parâmetro de entrada e saída sem nenhuma manipulação especial. Para tipos de dados varchar grandes, os dados podem ser recuperados e atualizados incrementalmente.

Os tipos de dados max podem ser usados para comparações, como variáveis Transact-SQL, e para concatenação. Eles também podem ser usados nas cláusulas DISTINCT, ORDER BY, GROUP BY de uma instrução SELECT e também em agregações, ingressos e subconsultas.

Para obter mais informações, confira Usar tipos de valor grande.

Restrições de tipo de valores grandes

As seguintes restrições se aplicam aos tipos de dados max que não existem para tipos de dados menores:

• Um sql_variant não pode conter um tipo de dados varchar grande.

• As colunas varchar grandes não podem ser especificadas como uma coluna de chave em um índice. Elas são permitidas em uma coluna incluída em um índice não clusterizado.

• As colunas varchar grandes não podem ser usadas como colunas de chave de particionamento.

Trabalhando com tipos de valores grandes no Transact-SQL

A função OPENROWSET do Transact-SQL é um método único de conectar e acessar dados remotos. Ela inclui todas as informações de conexão necessárias para acessar dados remotos de uma fonte de dados OLE DB. OPENROWSET pode ser referenciada na cláusula FROM de uma consulta como se fosse um nome de tabela. Ela também pode ser referenciada como a tabela de destino de uma instrução INSERT, UPDATE ou DELETE, sujeita aos recursos do provedor OLE DB.

A função OPENROWSET inclui o provedor de conjunto de linhas BULK que permite ler dados diretamente de um arquivo sem carregar os dados em uma tabela de destino. Isso o habilita a usar OPENROWSET com uma instrução INSERT SELECT simples.

Os argumentos de opção OPENROWSET BULK fornecem o controle significativo sobre os pontos de início e término da leitura de dados, como tratar erros e como os dados são interpretados. Por exemplo, você pode especificar que o arquivo de dados seja lido como uma única linha, um conjunto de linhas de coluna única do tipo varbinary, varchar ou nvarchar.

O exemplo a seguir insere uma foto na tabela ProductPhoto do banco de dados de amostra do AdventureWorks. Ao usar o provedor BULK OPENROWSET, você deverá fornecer a lista de colunas nomeada mesmo se não estiver inserindo valores em cada coluna. A chave primária nesse caso é definida como uma coluna de identidade e pode ser omitida da lista de colunas. Observe que você também deve fornecer um nome de correlação no final da instrução OPENROWSET que, nesse caso, é ThumbnailPhoto. Isso se correlaciona com a coluna na tabela ProductPhoto na qual o arquivo está sendo carregado.

INSERT Production.ProductPhoto (  
    ThumbnailPhoto,
    ThumbnailPhotoFilePath,
    LargePhoto,
    LargePhotoFilePath)  
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'  
FROM OPENROWSET
    (BULK 'c:\images\tricycle.jpg', SINGLE_BLOB) ThumbnailPhoto  

Atualizando dados usando UPDATE .WRITE

A instrução Transact-SQL UPDATE tem uma nova sintaxe WRITE para modificar o conteúdo das colunas varchar(max), nvarchar(max) ou varbinary(max). Isso permite que você execute atualizações parciais dos dados. A sintaxe UPDATE .WRITE é mostrada aqui na forma abreviada:

UPDATE

{ <object> }

SET

{ column_name = { .WRITE ( expression , @Offset , @Length ) }

O método WRITE especifica que uma seção do valor do column_name será modificada. A expressão é o valor que será copiado para o column_name, o @Offset é o ponto inicial no qual a expressão será escrita e o argumento @Length é o comprimento da seção na coluna.

Exemplo

Este exemplo de Transact-SQL atualiza um valor parcial em DocumentSummary, uma coluna nvarchar(max) na tabela de documentos no banco de dados AdventureWorks. A palavra "components" é substituída pela palavra "features" especificando a palavra de substituição, o local de início (deslocamento) da palavra a ser substituída nos dados existentes e o número de caracteres a serem substituídos (comprimento). Esse exemplo inclui instruções SELECT antes e depois da instrução UPDATE para comparar resultados.

USE AdventureWorks;  
GO  
--View the existing value.  
SELECT DocumentSummary  
FROM Production.Document  
WHERE DocumentID = 3;  
GO  
-- The first sentence of the results will be:  
-- Reflectors are vital safety components of your bicycle.  
  
--Modify a single word in the DocumentSummary column  
UPDATE Production.Document  
SET DocumentSummary .WRITE (N'features',28,10)  
WHERE DocumentID = 3 ;  
GO
--View the modified value.  
SELECT DocumentSummary  
FROM Production.Document  
WHERE DocumentID = 3;  
GO  
-- The first sentence of the results will be:  
-- Reflectors are vital safety features of your bicycle.  

Trabalhando com tipos de valores grandes no ADO.NET

Você pode trabalhar com tipos de valores grandes no ADO.NET especificando tipos de valores grandes como objetos SqlParameter em um SqlDataReader para retornar um conjunto de resultados ou usando SqlDataAdapter para preencher um DataSet/DataTable. Não há nenhuma diferença entre a maneira como você trabalha com um tipo de valor grande e seu tipo de dado de valor menor relacionado.

Como usar GetSqlBytes para recuperar dados

O método GetSqlBytes de SqlDataReader pode ser usado para recuperar o conteúdo de uma coluna varbinary(max). O fragmento de código a seguir aceita um objeto SqlCommand chamado cmd que seleciona dados varbinary(max) de uma tabela e um objeto SqlDataReader chamado reader que recupera os dados como SqlBytes.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)  
While reader.Read()  
    Dim bytes As SqlBytes = reader.GetSqlBytes(0)  
End While  

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
    {  
        SqlBytes bytes = reader.GetSqlBytes(0);  
    }  

Usando GetSqlChars para recuperar dados

O método GetSqlChars do SqlDataReader pode ser usado para recuperar o conteúdo de uma coluna varchar(max) ou nvarchar(max). O fragmento de código a seguir aceita um objeto SqlCommand chamado cmd,que seleciona dados nvarchar(max) de uma tabela e um objeto SqlDataReader chamado reader que recupera os dados.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)  
While reader.Read()  
    Dim buffer As SqlChars = reader.GetSqlChars(0)  
End While  

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
{  
    SqlChars buffer = reader.GetSqlChars(0);  
}  

Usando GetSqlBinary para recuperar dados

O método GetSqlBinary de um SqlDataReader pode ser usado para recuperar o conteúdo de uma coluna varbinary(max). O fragmento de código a seguir aceita um objeto SqlCommand chamado cmd que seleciona dados varbinary(max) de uma tabela e um objeto SqlDataReader chamado reader que recupera os dados como um fluxo SqlBinary.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)  
While reader.Read()  
    Dim binaryStream As SqlBinary = reader.GetSqlBinary(0)  
End While  

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
    {  
        SqlBinary binaryStream = reader.GetSqlBinary(0);  
    }  

Usando GetBytes para recuperar dados

O método GetBytes de um SqlDataReader lê um fluxo de bytes do deslocamento de coluna especificado na matriz de bytes começando com o deslocamento de matriz especificado. O fragmento de código a seguir aceita um objeto SqlDataReader chamado reader que recupera bytes em uma matriz de bytes. Observe que, ao contrário de GetSqlBytes, GetBytes requer um tamanho para o buffer da matriz.

While reader.Read()  
    Dim buffer(4000) As Byte  
    Dim byteCount As Integer = _  
    CInt(reader.GetBytes(1, 0, buffer, 0, 4000))  
End While  

while (reader.Read())  
{  
    byte[] buffer = new byte[4000];  
    long byteCount = reader.GetBytes(1, 0, buffer, 0, 4000);  
}  

Usando GetValue para recuperar dados

O método GetValue de um SqlDataReader lê o valor do deslocamento de coluna especificado em uma matriz. O fragmento de código a seguir aceita um objeto SqlDataReader chamado reader que recupera dados binários do deslocamento da primeira coluna e, em seguida, dados da cadeia de caracteres do deslocamento da segunda coluna.

While reader.Read()  
    ' Read the data from varbinary(max) column  
    Dim binaryData() As Byte = CByte(reader.GetValue(0))  
  
    ' Read the data from varchar(max) or nvarchar(max) column  
    Dim stringData() As String = Cstr((reader.GetValue(1))  
End While  

while (reader.Read())  
{  
    // Read the data from varbinary(max) column  
    byte[] binaryData = (byte[])reader.GetValue(0);  
  
    // Read the data from varchar(max) or nvarchar(max) column  
    String stringData = (String)reader.GetValue(1);  
}  

Convertendo de tipos de valores grandes para tipos CLR

Você pode converter o conteúdo de uma coluna varchar(max) ou nvarchar(max) usando qualquer um dos métodos de conversão de cadeia de caracteres, como ToString. O fragmento de código a seguir aceita um objeto SqlDataReader chamado reader que recupera os dados.

While reader.Read()  
    Dim str as String = reader(0).ToString()  
    Console.WriteLine(str)  
End While  

while (reader.Read())  
{  
     string str = reader[0].ToString();  
     Console.WriteLine(str);  
}  

Exemplo

O código a seguir recupera o nome e o objeto LargePhoto da tabela ProductPhoto no banco de dados AdventureWorks e salva-os em um arquivo. O assembly precisa ser compilado com uma referência ao namespace System.Drawing. O método GetSqlBytes do SqlDataReader retorna um objeto SqlBytes que expõe uma propriedade Stream. O código usa isso para criar um novo objeto Bitmap e o salva no Gif ImageFormat.

static void TestGetSqlBytes(int documentID, string filePath)
{
    // Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new(GetConnectionString()))
    {
        SqlCommand command = connection.CreateCommand();
        SqlDataReader reader = default!;
        try
        {
            // Setup the command
            command.CommandText =
                "SELECT LargePhotoFileName, LargePhoto "
                + "FROM Production.ProductPhoto "
                + "WHERE ProductPhotoID=@ProductPhotoID";
            command.CommandType = CommandType.Text;

            // Declare the parameter
            SqlParameter paramID =
                new("@ProductPhotoID", SqlDbType.Int)
                {
                    Value = documentID
                };
            command.Parameters.Add(paramID);
            connection.Open();

            string photoName = default!;

            reader = command.ExecuteReader(CommandBehavior.CloseConnection);

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    // Get the name of the file.
                    photoName = reader.GetString(0);

                    // Ensure that the column isn't null
                    if (reader.IsDBNull(1))
                    {
                        Console.WriteLine("{0} is unavailable.", photoName);
                    }
                    else
                    {
                        SqlBytes bytes = reader.GetSqlBytes(1);
                        using (Bitmap productImage = new(bytes.Stream))
                        {
                            var fileName = filePath + photoName;

                            // Save in gif format.
                            productImage.Save(fileName, ImageFormat.Gif);
                            Console.WriteLine("Successfully created {0}.", fileName);
                        }
                    }
                }
            }
            else
            {
                Console.WriteLine("No records returned.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            reader?.Dispose();
        }
    }
}

Private Sub GetPhoto( _
  ByVal documentID As Integer, ByVal filePath As String)
    ' Assumes GetConnectionString returns a valid connection string.
    Using connection As New SqlConnection(GetConnectionString())
        Dim command As SqlCommand = connection.CreateCommand()
        Dim reader As SqlDataReader
        Try
            ' Setup the command
            command.CommandText = _
              "SELECT LargePhotoFileName, LargePhoto FROM" _
                & " Production.ProductPhoto" _
                & " WHERE ProductPhotoID=@ProductPhotoID"
            command.CommandType = CommandType.Text

            ' Declare the parameter
            Dim paramID As SqlParameter = _
                New SqlParameter("@ProductPhotoID", SqlDbType.Int)
            paramID.Value = documentID
            command.Parameters.Add(paramID)
            connection.Open()

            Dim photoName As String

            reader = _
             command.ExecuteReader(CommandBehavior.CloseConnection)

            If reader.HasRows Then
                While reader.Read()
                    ' Get the name of the file
                    photoName = reader.GetString(0)

                    ' Ensure that the column isn't null
                    If (reader.IsDBNull(1)) Then
                        Console.WriteLine("{0} is unavailable.", photoName)
                    Else
                        Dim bytes As SqlBytes = reader.GetSqlBytes(1)
                        Using productImage As Bitmap = _
                          New Bitmap(bytes.Stream)
                            Dim fileName As String = filePath & photoName

                            ' Save in gif format.
                            productImage.Save( _
                              fileName, ImageFormat.Gif)
                            Console.WriteLine("Successfully created {0}.", fileName)
                        End Using
                    End If
                End While
            Else
                Console.WriteLine("No records returned.")
            End If
        Catch ex As Exception
            Console.WriteLine("Exception: {0}", ex.Message)
        End Try
    End Using
End Sub

Usando parâmetros de tipo de valores grandes

Os tipos de valores grandes podem ser usados em objetos SqlParameter da mesma maneira que você usa tipos de valores menores em objetos SqlParameter. Você pode recuperar tipos de valores grandes como valores SqlParameter, conforme mostrado no exemplo a seguir. O código pressupõe que o seguinte procedimento GetDocumentSummary armazenado exista no banco de dados de exemplo AdventureWorks. O procedimento armazenado tem um parâmetro de entrada denominado @DocumentID e retorna o conteúdo da coluna DocumentSummary no parâmetro de saída @DocumentSummary.

CREATE PROCEDURE GetDocumentSummary
(  
    @DocumentID int,  
    @DocumentSummary nvarchar(MAX) OUTPUT  
)  
AS  
SET NOCOUNT ON  
SELECT  @DocumentSummary=Convert(nvarchar(MAX), DocumentSummary)  
FROM    Production.Document  
WHERE   DocumentID=@DocumentID  

Exemplo

O código ADO.NET cria objetos SqlConnection e SqlCommand para executar o procedimento armazenado GetDocumentSummary e recuperar o resumo do documento, que é armazenado como um tipo de valor grande. O código a seguir transmite um valor para o parâmetro de entrada @DocumentID e exibe os resultados repassados no parâmetro de saída @DocumentSummary na janela do console.

static string? GetDocumentSummary(int documentID)
{
    // Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new(GetConnectionString()))
    {
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        try
        {
            // Set up the command to execute the stored procedure.
            command.CommandText = "GetDocumentSummary";
            command.CommandType = CommandType.StoredProcedure;

            // Set up the input parameter for the DocumentID.
            SqlParameter paramID =
                new("@DocumentID", SqlDbType.Int)
                {
                    Value = documentID
                };
            command.Parameters.Add(paramID);

            // Set up the output parameter to retrieve the summary.
            SqlParameter paramSummary =
                new("@DocumentSummary",
                SqlDbType.NVarChar, -1)
                {
                    Direction = ParameterDirection.Output
                };
            command.Parameters.Add(paramSummary);

            // Execute the stored procedure.
            command.ExecuteNonQuery();
            Console.WriteLine((string)paramSummary.Value);
            return (string)paramSummary.Value;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return null;
        }
    }
}

Private Function GetDocumentSummary( _
  ByVal documentID As Integer) As String

    ' Assumes GetConnectionString returns a valid connection string.
    Using connection As New SqlConnection(GetConnectionString())
        connection.Open()
        Dim command As SqlCommand = connection.CreateCommand()

        ' Setup the command to execute the stored procedure.
        command.CommandText = "GetDocumentSummary"
        command.CommandType = CommandType.StoredProcedure

        ' Set up the input parameter for the DocumentID.
        Dim paramID As SqlParameter = _
            New SqlParameter("@DocumentID", SqlDbType.Int)
        paramID.Value = documentID
        command.Parameters.Add(paramID)

        ' Set up the output parameter to retrieve the summary.
        Dim paramSummary As SqlParameter = _
            New SqlParameter("@DocumentSummary", _
               SqlDbType.NVarChar, -1)
        paramSummary.Direction = ParameterDirection.Output
        command.Parameters.Add(paramSummary)

        ' Execute the stored procedure.
        command.ExecuteNonQuery()
        Console.WriteLine(paramSummary.Value)
        Return paramSummary.Value.ToString
    End Using
End Function

Confira também

• Dados binários e de valor grande do SQL Server
• Mapeamentos de tipo de dados do SQL Server
• SQL Server Data Operations in ADO.NET (Operações de dados do SQL Server no ADO.NET)
• Visão geral do ADO.NET