Modificando dados de valor grande (máx) 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 .NET, os tipos de dados max podem ser buscados por um DataReader e também podem ser especificados como valores de parâmetro de entrada e saída sem qualquer administraçã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.
Confira Usar tipos de dados de valores grandes nos Manuais Online do SQL Server para obter mais detalhes sobre os tipos de valores grandes.
Restrições de tipos 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_variantnão pode conter um tipo de dadosvarchargrande.As colunas
varchargrandes 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
varchargrandes não podem ser usadas como colunas de chave de particionamento.
Trabalhando com tipos de valores grandes em Transact-SQL
A função OPENROWSET do Transact-SQL é um método único de conectar e acessar dados remotos. OPENROWSET pode ser referenciada na cláusula FROM de uma consulta como se fosse um nome de tabela. Também pode ser referenciada como a tabela de destino de uma instrução INSERT, UPDATE ou DELETE.
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. Para obter a sintaxe e as opções completas, confira Manuais Online do SQL Server.
O exemplo a seguir insere uma foto na tabela ProductPhoto do banco de dados de exemplo AdventureWorks2022. 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
Atualização de dados que usam 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.
| If | Então |
|---|---|
| A expressão é definida como NULL | @Length é ignorado, e o valor em column_name é truncado no @Offset especificado. |
@Offset é NULL |
A operação de atualização acrescentará a expressão ao final do valor de column_name existente e @Length será ignorado. |
Se @Offset for maior que o comprimento do valor de column_name, o |
SQL Server retornará um erro. |
@Length é NULL |
A operação de atualização removerá todos os dados de @Offset até o final do valor de column_name. |
Observação
@Offset nem @Length poderá ser um número negativo.
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 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 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())
{
SqlBytes bytes = reader.GetSqlBytes(0);
}
Como usar 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())
{
SqlChars buffer = reader.GetSqlChars(0);
}
Como usar 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())
{
SqlBinary binaryStream = reader.GetSqlBinary(0);
}
Como usar 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())
{
byte[] buffer = new byte[4000];
long byteCount = reader.GetBytes(1, 0, buffer, 0, 4000);
}
Como usar 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
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 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())
{
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.
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";
}
}
Como usar parâmetros de tipos 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 AdventureWorks2022. 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.
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óximas etapas
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de