Modifica di dati con valori elevati (massimi) in ADO.NET
I tipi di dati LOB (Large Object) sono quelli che superano la dimensione massima di 8 kilobyte (KB) per le righe. SQL Server include l'identificatore max per i tipi di dati varchar, nvarchar e varbinary per consentire l'archiviazione di valori di dimensioni fino a 2^32 byte. Le colonne della tabella e le variabili Transact-SQL possono specificare tipi di dati varchar(max), nvarchar(max)o varbinary(max). In ADO.NET i tipi di dati max possono essere recuperati da un DataReader e possono inoltre essere specificati come parametri di input e di output senza richiedere una gestione speciale. Per i tipi di dati varchar di grandi dimensioni, è possibile recuperare e aggiornare i dati in modo incrementale.
I tipi di dati max possono essere usati per eseguire confronti, ad esempio per le variabili Transact-SQL, e per le concatenazioni. Si possono usare anche nelle clausole DISTINCT, ORDER BY, GROUP BY di un'istruzione SELECT, nonché in aggregazioni, join e sottoquery.
Per altre informazioni, vedere Utilizzo di tipi di dati per valori di grandi dimensioni.
Restrizioni per i tipi di valori di grandi dimensioni
Per i tipi di dati max vengono applicate le restrizioni seguenti, che non esistono per i tipi di dati più piccoli:
Un
sql_variantnon può contenere un tipo di dativarchardi grandi dimensioni.Non è possibile specificare colonne
varchardi grandi dimensioni come colonna chiave in un indice. Sono consentite in una colonna inclusa in un indice non cluster.Le colonne
varchardi grandi dimensioni non possono essere usate come colonne chiave di partizionamento.
Uso di tipi di valori di grandi dimensioni in Transact-SQL
La funzione OPENROWSET di Transact-SQL è un metodo monouso per la connessione e l'accesso ai dati remoti. Include tutte le informazioni di connessione necessarie per l'accesso remoto ai dati da un'origine dati OLE DB. È possibile fare riferimento a OPENROWSET nella clausola FROM di una query come se fosse un nome di tabella. È inoltre possibile farvi riferimento come tabella di destinazione di un'istruzione INSERT, UPDATE o DELETE, soggetta alle funzionalità del provider OLE DB.
La funzione OPENROWSET include il provider di set di righe BULK, che consente di leggere i dati direttamente da un file senza caricare i dati in una tabella di destinazione. Ciò consente di usare OPENROWSET in una semplice istruzione INSERT SELECT.
Gli argomenti dell'opzione OPENROWSET BULK forniscono un controllo notevole sul punto in cui iniziare e terminare la lettura dei dati, sulla gestione degli errori e sull'interpretazione dei dati. Ad esempio, è possibile specificare che il file di dati venga letto come riga singola, set di righe a colonna singola di tipo varbinary, varchar o nvarchar.
Nell'esempio seguente viene inserita una foto nella tabella ProductPhoto del database di esempio AdventureWorks. Se si usa il provider BULK OPENROWSET, è necessario fornire l'elenco di colonne denominato anche se non si inseriscono valori in ogni colonna. La chiave primaria in questo caso è definita come colonna Identity e può essere omessa dall'elenco di colonne. Si noti che è necessario specificare anche un nome di correlazione alla fine dell'istruzione OPENROWSET, che in questo caso è ThumbnailPhoto. Questo è correlato alla colonna della tabella ProductPhoto in cui viene caricato il file.
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
Aggiornamento di dati tramite UPDATE .WRITE
L'istruzione UPDATE di Transact-SQL usa una nuova sintassi di WRITE per la modifica del contenuto delle colonne varchar(max), nvarchar(max) o varbinary(max). In questo modo è possibile eseguire aggiornamenti parziali dei dati. La sintassi di UPDATE.WRITE è illustrata di seguito in forma abbreviata:
UPDATE
{ <object> }
SET
{ column_name = { .WRITE ( espressione , @Offset , @Length ) }
Il metodo WRITE specifica che una sezione del valore di column_name verrà modificato. L'espressione corrisponde al valore che verrà copiato in column_name, l'argomento @Offset al punto di inizio in cui verrà scritta l'espressione e l'argomento @Length alla lunghezza della sezione nella colonna.
| If | Risultato |
|---|---|
| L'espressione è impostata su NULL. | Il valore di @Length viene ignorato e il valore di column_name viene troncato in base al valore specificato di @Offset. |
@Offset è NULL |
L'operazione di aggiornamento aggiunge l'espressione alla fine del valore di column_name esistente e il valore di @Length viene ignorato. |
@Offset è maggiore della lunghezza del valore column_name |
SQL Server restituisce un errore. |
@Length è NULL |
L'operazione di aggiornamento rimuove tutti i dati da @Offset fino al termine del valore column_name. |
Nota
Né @Offset né @Length possono essere un numero negativo.
Esempio
Questo esempio di Transact-SQL aggiorna un valore parziale in DocumentSummary, una colonna nvarchar(max) nella tabella Document del database AdventureWorks. La parola 'components' viene sostituta con la parola 'features' specificando la parola sostitutiva, la posizione iniziale (offset) della parola da sostituire nei dati esistenti e il numero di caratteri da sostituire (lunghezza). Nell'esempio sono incluse istruzioni SELECT prima e dopo l'istruzione UPDATE per confrontare i risultati.
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.
Uso di tipi di valori di grandi dimensioni in ADO.NET
È possibile usare tipi di valore di grandi dimensioni in ADO.NET specificandoli come oggetti SqlParameter in un oggetto SqlDataReader per restituire un set di risultati oppure usando un oggetto SqlDataAdapter per compilare un oggetto DataSet/DataTable. Non esiste alcuna differenza tra l'uso di un tipo di dati per valori di grandi dimensioni e l'uso del tipo di dati correlato per valori più piccoli.
Uso di GetSqlBytes per recuperare i dati
È possibile usare il metodo GetSqlBytes dell'oggetto SqlDataReader per recuperare il contenuto di una colonna varbinary(max). Il seguente frammento di codice presuppone un oggetto SqlCommand denominato cmd che seleziona dati varbinary(max) da una tabella e un oggetto SqlDataReader denominato reader che recupera i dati come 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);
}
Uso di GetSqlChars per il recupero di dati
È possibile usare il metodo GetSqlChars dell'oggetto SqlDataReader per recuperare il contenuto di una colonna varchar(max) o nvarchar(max). Il seguente frammento di codice presuppone un oggetto SqlCommand denominato cmd che seleziona dati nvarchar(max) da una tabella e un oggetto SqlDataReader denominato reader che recupera i dati.
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);
}
Uso di GetSqlBinary per il recupero di dati
È possibile usare il metodo GetSqlBinary di un oggetto SqlDataReader per recuperare il contenuto di una colonna varbinary(max). Il seguente frammento di codice presuppone un oggetto SqlCommand denominato cmd che seleziona dati varbinary(max) da una tabella e un oggetto SqlDataReader denominato reader che recupera i dati come flusso 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);
}
Uso di GetBytes per il recupero di dati
Il metodo GetBytes di un oggetto SqlDataReader legge un flusso di byte dall'offset di colonna specificato in una matrice di byte, a partire dall'offset di matrice specificato. Il frammento di codice seguente presuppone l'uso di un oggetto SqlDataReader denominato reader che recupera i byte in una matrice di byte. Si noti che, a differenza di GetSqlBytes, GetBytes richiede una dimensione per il buffer della matrice.
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);
}
Uso di GetValue per il recupero di dati
Il metodo GetValue di un oggetto SqlDataReader legge il valore dall'offset di colonna specificato in una matrice. Il frammento di codice seguente presuppone l'uso di un oggetto SqlDataReader denominato reader che recupera i dati binari dall'offset della prima colonna, quindi i dati di tipo stringa dall'offset della seconda colonna.
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);
}
Conversione da tipi di valore di grandi dimensioni a tipi CLR
È possibile convertire il contenuto di una colonna varchar(max) o nvarchar(max) usando uno qualsiasi dei metodi di conversione stringhe, ad esempio ToString. Il frammento di codice seguente presuppone l'uso di un oggetto SqlDataReader denominato reader che recupera i dati.
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);
}
Esempio
Il codice seguente recupera il nome e l'oggetto LargePhoto dalla tabella ProductPhoto nel database AdventureWorks e li salva in un file. L'assembly deve essere compilato con un riferimento allo spazio dei nomi System.Drawing. Il metodo GetSqlBytes di SqlDataReader restituisce un oggetto SqlBytes che espone una proprietà Stream. Questa viene usata nel codice per creare un nuovo oggetto Bitmap, che verrà quindi salvato come ImageFormat 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("{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 Bitmap = _
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
Utilizzo dei parametri di tipi di valore di grandi dimensioni
I tipi di valore di grandi dimensioni possono essere usati negli oggetti SqlParameter nello stesso modo in cui si usano i tipi di valore più piccoli negli oggetti SqlParameter. È possibile recuperare tipi di valore di grandi dimensioni come valori SqlParameter, come illustrato nell'esempio seguente. Il codice presuppone che la seguente stored procedure GetDocumentSummary esista nel database di esempio AdventureWorks. La stored procedure accetta un parametro di input denominato @DocumentID e restituisce il contenuto della colonna DocumentSummary nel parametro di output @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
Esempio
Il codice ADO.NET crea oggetti SqlConnection e SqlCommand per eseguire la stored procedure GetDocumentSummary e recuperare il riepilogo del documento, archiviato come tipo di valore di grandi dimensioni. Il codice passa un valore per il parametro di input @DocumentID e i risultati restituiti nel parametro di output @DocumentSummary vengono visualizzati nella finestra della 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
{
// 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("@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