Modificando dados de grande valor (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 max tipos de dados podem ser buscados por um , e também podem ser especificados como valores de DataReaderparâmetros de entrada e 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. Eles também podem ser usados nas cláusulas DISTINCT, ORDER BY, GROUP BY de uma instrução SELECT, bem como em agregados, junções e subconsultas.
Para obter mais informações, consulte Usando tipos de dados de grande valor.
Restrições de tipo de valor grande
As seguintes restrições se aplicam aos tipos de max dados, que não existem para tipos de dados menores:
A
sql_variantnão pode conter um tipo de dados grandevarchar.Colunas grandes
varcharnã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
varcharnão podem ser usadas como colunas de chave de particionamento.
Trabalhando com tipos de grande valor 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 referenciado na cláusula FROM de uma consulta como se fosse um nome de tabela. Ele também pode ser referenciado como a tabela de destino de uma instrução INSERT, UPDATE ou DELETE, sujeito aos recursos do provedor OLE DB.
A OPENROWSET função inclui o BULK provedor de conjunto de linhas, que permite ler dados diretamente de um arquivo sem carregar os dados em uma tabela de destino. Isso permite que você use OPENROWSET em uma instrução INSERT SELECT simples.
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, você pode especificar que o arquivo de dados seja lido como um conjunto de linhas de linha única e coluna do tipo varbinary, varcharou 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
Atualizando dados usando UPDATE . ESCREVER
A instrução UPDATE Transact-SQL tem uma nova sintaxe WRITE para modificar o conteúdo de varchar(max), nvarchar(max)ou varbinary(max) colunas. Isso permite que você execute atualizações parciais dos dados. A ATUALIZAÇÃO . A sintaxe WRITE é mostrada aqui de forma abreviada:
ATUALIZAR
<{ objeto> }
SET
{ column_name = { . ESCREVER ( expressão , @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 é o @Offset ponto inicial no qual a expressão será escrita e o @Length argumento é o comprimento da seção na coluna.
| Se | Então |
|---|---|
| A expressão é definida como NULL | @Lengthé ignorado e o valor em column_name é truncado no .@Offset |
@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 final do column_name valor. |
Nota
Nem @Offset @Length pode ser um número negativo.
Exemplo
Este exemplo Transact-SQL atualiza um valor parcial em DocumentSummary, uma nvarchar(max) coluna na tabela Document no banco 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 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 grande valor no ADO.NET
Você pode trabalhar com tipos de valor grande em ADO.NET especificando tipos de valor grande como SqlParameter objetos em um SqlDataReader para retornar um conjunto de resultados ou usando um SqlDataAdapter para preencher um DataSet/DataTable. Não há diferença entre a maneira como você trabalha com um tipo de valor grande e seu tipo de dados de valor menor relacionado.
Usando 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 a seguir assume um objeto chamado cmd que seleciona varbinary(max) dados de uma tabela e um SqlDataReader objeto chamado reader que recupera os dados como SqlBytes.SqlCommand
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 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 a seguir assume um SqlCommand objeto chamado cmd que seleciona nvarchar(max) dados de uma tabela e um SqlDataReader objeto 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 GetSqlBinary método de a SqlDataReader pode ser usado para recuperar o conteúdo de uma varbinary(max) coluna. O fragmento de código a seguir pressupõe um SqlCommand objeto chamado cmd que seleciona varbinary(max) dados de uma tabela e um SqlDataReader objeto chamado reader que recupera os dados como um SqlBinary fluxo.
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 de um SqlDataReader lê um fluxo de bytes do deslocamento de coluna especificado em uma matriz de bytes começando no deslocamento de matriz especificado. O fragmento de código a seguir assume um SqlDataReader objeto chamado reader que recupera bytes em uma matriz de bytes. Observe que, ao contrário GetSqlBytesdo , GetBytes requer um tamanho para o buffer de 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 GetValue método de um SqlDataReader lê o valor do deslocamento de coluna especificado em uma matriz. O fragmento de código a seguir assume um SqlDataReader objeto chamado reader que recupera dados binários do deslocamento da primeira coluna e, em seguida, dados de 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 grande valor para tipos CLR
Você pode converter o conteúdo de uma ou nvarchar(max) coluna usando qualquer um varchar(max) dos métodos de conversão de cadeia de caracteres, como ToString. O fragmento de código a seguir assume um SqlDataReader objeto 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 LargePhoto objeto da tabela no banco de ProductPhoto dados e o salva em AdventureWorks um arquivo. O assembly precisa ser compilado System.Drawing com uma referência ao namespace. O GetSqlBytes método do SqlDataReader retorna um SqlBytes objeto que expõe uma Stream propriedade. O código usa isso para criar um novo Bitmap objeto e, em seguida, salva-o 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 valor grande
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 passados @DocumentSummary de volta no parâmetro de saída na janela 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