Partilhar via

Modificando dados de grande valor (máx.) no ADO.NET

Baixar ADO.NET

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

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

Consulte Using Large-Value Data Types from SQL Server Books Online para mais detalhes sobre tipos de dados de grande valor.

Restrições de tipo de grande valor

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

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

  • Colunas grandes varchar não podem ser especificadas como uma coluna chave num índice. São permitidas numa coluna incluída num índice não agrupado.

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

Trabalhar com tipos de grande valor em Transact-SQL

A função Transact-SQL OPENROWSET é um método único de ligação e acesso a dados remotos. OPENROWSET pode ser referenciado na cláusula FROM de uma consulta como se fosse um nome de tabela. Também pode ser referenciada como tabela alvo de uma instrução INSERT, UPDATE ou DELETE.

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

Os OPENROWSET BULK argumentos de opção fornecem controlo significativo sobre onde começar e terminar a leitura dos dados, como lidar com erros e como os dados são interpretados. Por exemplo, pode especificar que o ficheiro de dados seja lido como um conjunto de linhas de uma única linha e coluna única do tipo varbinary, varchar, ou nvarchar. Para a sintaxe completa e as opções, consulte SQL Server Books Online.

O exemplo seguinte insere uma foto na tabela ProductPhoto na AdventureWorks2025 base de dados de exemplos. Ao usar o BULK OPENROWSET fornecedor, deve fornecer a lista nomeada de colunas mesmo que não esteja a inserir valores em cada coluna. A chave primária, neste caso, é definida como uma coluna identidade, e pode ser omitida da lista de colunas. Note que também deve fornecer um identificador de correlação ao final da instrução OPENROWSET, sendo, neste caso, ThumbnailPhoto. Isto correlaciona-se com a coluna na ProductPhoto tabela onde o ficheiro está a ser 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

Atualização de dados usando UPDATE .WRITE

A instrução Transact-SQL UPDATE tem nova sintaxe WRITE para modificar o conteúdo de varchar(max), nvarchar(max), ou varbinary(max) colunas. Isto permite-lhe realizar atualizações parciais dos dados. A sintaxe UPDATE .WRITE é apresentada aqui em forma abreviada:

UPDATE

{ <objeto> }

SET

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

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

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

Observação

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

Example

Este Transact-SQL exemplo atualiza um valor parcial no DocumentSummary, uma nvarchar(max) coluna na tabela Document da base de dados AdventureWorks. A palavra 'componentes' é substituída pela palavra 'features' especificando a palavra de substituição, a localização inicial (offset) da palavra a substituir nos dados existentes e o número de caracteres a substituir (comprimento). O exemplo inclui instruções SELECT antes e depois da instrução UPDATE para comparar resultados.

USE AdventureWorks2022;
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.

Trabalhar com tipos de grande valor em ADO.NET

Pode trabalhar com grandes tipos de valor em ADO.NET especificando tipos de valor grande como objetos SqlParameter num(a) SqlDataReader para devolver um conjunto de resultados, ou através de SqlDataAdapter para preencher um DataSet/DataTable. Não há diferença entre a forma como se trabalha com um tipo de valor grande e o seu tipo de dado de valor mais pequeno relacionado.

Utilização do GetSqlBytes para Recuperar Dados

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

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

Utilização do GetSqlChars para recuperar dados

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

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

Usar o GetSqlBinary para recuperar dados

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

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

Utilização do GetBytes para recuperar dados

O GetBytes método de SqlDataReader lê um fluxo de bytes a partir do deslocamento de coluna especificado para um array de bytes começando no deslocamento de array especificado. O fragmento de código seguinte assume um SqlDataReader objeto chamado reader que recupera bytes em um array de bytes. Note que, ao contrário de GetSqlBytes, GetBytes requer um tamanho para o buffer do array.

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

Utilização do GetValue para recuperar dados

O GetValue método do SqlDataReader lê o valor do deslocamento da coluna especificada num array. O fragmento de código seguinte assume um objeto SqlDataReader chamado reader que recupera dados binários do deslocamento da primeira coluna e, em seguida, dados de texto do deslocamento da segunda coluna.

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 dados com grandes valores para tipos CLR

Pode converter o conteúdo de uma varchar(max) coluna ou nvarchar(max) usando qualquer um dos métodos de conversão de cadeias, como ToString. O fragmento de código seguinte assume um SqlDataReader objeto chamado reader que recupera os dados.

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

Example

O código seguinte recupera o nome e o LargePhoto objeto da ProductPhoto tabela na AdventureWorks base de dados e guarda-os num ficheiro. A assembly precisa de ser compilada com uma referência ao System.Drawing namespace. O GetSqlBytes método do SqlDataReader devolve um SqlBytes objeto que expõe uma Stream propriedade. O código usa isto para criar um novo Bitmap objeto e depois guarda-o no Gif ImageFormat.

using Microsoft.Data.SqlClient;
using System.Data.SqlTypes;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

class Program
{
    static void Main()
    {
        // Supply any valid DocumentID value and file path.
        // The value 3 is supplied for DocumentID, and a literal
        // string for the file path where the image will be saved. 1, 60
        TestGetSqlBytes(7, @"c:\temp\");
        Console.ReadLine();
    }
    static private void TestGetSqlBytes(int documentID, string filePath)
    {
        // Assumes GetConnectionString returns a valid connection string.
        using (SqlConnection connection =
                   new SqlConnection(GetConnectionString()))
        {
            SqlCommand command = connection.CreateCommand();
            SqlDataReader reader = null;
            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 SqlParameter("@ProductPhotoID", SqlDbType.Int);
                paramID.Value = documentID;
                command.Parameters.Add(paramID);
                connection.Open();

                string photoName = null;

                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 Bitmap(bytes.Stream))
                            {
                                String 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
            {
                if (reader != null)
                    reader.Dispose();
            }
        }
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property 
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}

Utilização de parâmetros de tipo de grande valor

Tipos de valor grande podem ser usados em SqlParameter objetos da mesma forma que usas tipos de valor mais pequenos em SqlParameter objetos. Você pode recuperar tipos de valores grandes como SqlParameter valores, como é mostrado no exemplo seguinte. O código assume que o seguinte procedimento armazenado GetDocumentSummary existe na AdventureWorks2025 base de dados de exemplo. O procedimento armazenado recebe um parâmetro de entrada nomeado @DocumentID e devolve 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

Example

O código ADO.NET cria SqlConnection e SqlCommand objetos para executar a procédure armazenada 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 mostra os resultados passados de volta no @DocumentSummary parâmetro de saída na janela da Consola.

using Microsoft.Data.SqlClient;
class Program
{
    static void Main()
    {
        // Supply any valid Document ID value.
        // The value 7 is supplied for demonstration purposes.
        string summaryString = GetDocumentSummary(7);
        Console.ReadLine();
    }
    static private string GetDocumentSummary(int documentID)
    {
        //Assumes GetConnectionString returns a valid connection string.
        using (SqlConnection connection =
                   new SqlConnection(GetConnectionString()))
        {
            connection.Open();
            SqlCommand command = connection.CreateCommand();
            try
            {
                // Setup 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 SqlParameter("@DocumentID", SqlDbType.Int);
                paramID.Value = documentID;
                command.Parameters.Add(paramID);

                // Set up the output parameter to retrieve the summary.
                SqlParameter paramSummary =
                    new SqlParameter("@DocumentSummary",
                    SqlDbType.NVarChar, -1);
                paramSummary.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;
            }
        }
    }
    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}

Próximos passos

  • Last updated on