Modificando dados de Large-Value (máx.) no ADO.NET

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

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

Para obter mais informações, consulte Usando Large-Value tipos de dados.

Large-Value Restrições de tipo

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

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

• Colunas grandes varchar não podem ser especificadas como uma coluna chave em um índice. Eles são permitidos em uma coluna incluída em um índice não clusterizado.

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

Trabalhando com tipos de Large-Value no Transact-SQL

A função Transact-SQL OPENROWSET é um método único de conexão e acesso a dados remotos. Ele inclui todas as informações de conexão necessárias para acessar dados remotos de uma fonte de dados OLE DB. OPENROWSET pode ser referido na cláusula FROM de uma consulta como se se tratasse de um nome de tabela. Também pode ser referenciada como tabela alvo de uma instrução INSERT, UPDATE ou DELETE, sujeita às capacidades do fornecedor OLE DB.

A função OPENROWSET inclui o provedor de conjunto de linhas BULK, que permite ler dados diretamente de um ficheiro sem carregar os dados numa tabela de destino. Isto permite-lhe usar OPENROWSET numa instrução simples INSERT SELECT.

Os OPENROWSET BULK argumentos de opção fornecem controle significativo sobre onde começar e terminar a leitura de dados, como lidar com erros e como os dados são interpretados. Por exemplo, pode especificar que o arquivo de dados seja lido como um conjunto de uma linha e uma coluna do tipo varbinary, varchar ou nvarchar.

O exemplo a seguir insere uma foto na tabela ProductPhoto no banco de dados de exemplo AdventureWorks. Ao usar o BULK OPENROWSET provedor, você deve fornecer a lista nomeada de colunas, mesmo que não esteja inserindo valores em todas as colunas. A chave primária, neste 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 OPENROWSET instrução, que neste caso é ThumbnailPhoto. Isso se correlaciona com a coluna na tabela na qual o ProductPhoto 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

Atualizar dados usando UPDATE .WRITE

A instrução Transact-SQL UPDATE tem uma nova WRITE sintaxe 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 UPDATE .WRITE sintaxe é apresentada aqui em forma abreviada:

UPDATE

<{ objeto> }

SET

{ column_name = { .WRITE ( expressão , @Offset , @Length ) }

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

Se o	Então
A expressão está definida para NULL	@Length é ignorado e o valor em column_name é truncado no @Offset especificado.
@Offset é NULL	A operação de atualização acrescenta a expressão no final do valor de column_name existente e @Length é ignorada.
@Offset é maior do que o comprimento do valor de column_name	O SQL Server retorna um erro.
@Length é NULL	A operação de atualização remove todos os dados do @Offset até ao final do valor column_name.

Observação

Nem @Offset@Length pode ser um número negativo.

Exemplo

Este exemplo Transact-SQL atualiza um valor parcial em DocumentSummary, uma coluna nvarchar(max) na tabela Document na base de dados AdventureWorks. A palavra «componentes» é substituída pela palavra «características», especificando a palavra de substituição, a localização inicial (deslocação) da palavra a substituir nos dados existentes e o número de carateres a substituir (comprimento). O exemplo inclui SELECT instruções antes e depois da UPDATE instrução 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 Large-Value no ADO.NET

Utilize SqlParameter objetos com SqlCommand para enviar valores grandes. Recupere resultados com SqlDataReader, ou preencha objetos DataSet ou DataTable com SqlDataAdapter. Lida com tipos de valor grandes, como os seus tipos de dados relacionados e de menor valor.

Usando GetSqlBytes para recuperar dados

Para recuperar o conteúdo de uma varbinary(max) coluna como SqlBytes, chame GetSqlBytes. O fragmento seguinte assume que cmd é o SqlCommand que seleciona os dados binários, e reader é o SqlDataReader que os recupera.

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

Para recuperar o conteúdo de uma coluna varchar(max) ou nvarchar(max), chame GetSqlChars. O fragmento seguinte assume que cmd é o SqlCommand que seleciona nvarchar(max) os dados, e reader é o SqlDataReader que os recupera.

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

Para recuperar o conteúdo de uma coluna varbinary(max) como o valor SqlBinary, chame GetSqlBinary. O fragmento seguinte assume que cmd é o SqlCommand que seleciona os dados binários, e reader é o SqlDataReader que os recupera.

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 GetBytes método lê bytes do deslocamento da coluna especificado para um array de bytes no deslocamento do array especificado. O fragmento seguinte assume que reader é o SqlDataReader que recupera os bytes. Ao contrário de GetSqlBytes, GetBytes requer um tamanho para o buffer do array.

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 GetValue método devolve o valor da coluna como um objeto. O fragmento seguinte assume que reader é o SqlDataReader. Em C#, a coluna binária devolve byte[] e a coluna de caracteres devolve string. No Visual Basic, os tipos correspondentes são Byte() e String.

While reader.Read()
    ' Read the data from varbinary(max) column
    Dim binaryData() As Byte = CType(reader.GetValue(0), Byte())

    ' 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);
}

Conversão de Tipos de Grande Valor para Tipos CLR

Pode converter o conteúdo de uma coluna varchar(max) ou nvarchar(max) com métodos de conversão de cadeias, como ToString. O fragmento seguinte assume que reader é o SqlDataReader 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 seguinte recupera o nome e o valor LargePhoto da tabela ProductPhoto na base de dados AdventureWorks e guarda-o num ficheiro. Este exemplo de conversão de imagem requer o Windows e o System.Drawing.Common pacote NuGet. Essa restrição aplica-se à conversão de imagem, não à obtenção de dados binários do fornecedor. O GetSqlBytes método devolve SqlBytes, que expõe a propriedade Stream . O código usa o fluxo para criar o Bitmap objeto e guarda-o em formato GIF.

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($"{photoName} is unavailable.");
                    }
                    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 {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 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 grandes parâmetros de tipo de valor

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

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 SqlConnection e SqlCommand objetos para executar o procedimento armazenado GetDocumentSummary e recuperar o resumo do documento, que é armazenado como um tipo de valor grande. O código passa um valor para o @DocumentID parâmetro de entrada e exibe os resultados retornados no @DocumentSummary parâmetro de saída 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

Ver também

• Dados binários e Large-Value do SQL Server
• Mapeamentos de tipo de dados do SQL Server
• Operações de dados do SQL Server no ADO.NET
• Visão geral ADO.NET