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