Modification des données de valeur élevée (max) dans ADO.NET
Mise à jour : November 2007
Dans les versions antérieures à SQL Server 2005, l'utilisation de types de données LOB (Large Object) nécessitait une manipulation particulière. Les types de données LOB sont ceux dont la taille maximale de ligne dépasse 8 kilo-octets (Ko). SQL Server 2005 a introduit un spécificateur max pour les types de données varchar, nvarchar et varbinary pour permettre le stockage de valeurs pouvant atteindre 2^32 octets. Les colonnes de table et les variables Transact-SQL peuvent spécifier des types de données varchar(max), nvarchar(max) ou varbinary(max). Dans ADO.NET, les nouveaux types de données max peuvent être extraits par un DataReader et spécifiés comme valeurs de paramètre d'entrée ou de sortie sans que cela nécessite une manipulation particulière. Pour les types de données varchar volumineux, il est possible d'extraire et de mettre à jour les données de façon incrémentielle.
Les types de données max peuvent être utilisés pour effectuer des comparaisons, par exemple des variables Transact-SQL, ainsi que des concaténations. Ils peuvent également être utilisés dans des clauses DISTINCT, ORDER BY et GROUP BY d'une instruction SELECT ainsi que comme agrégats, jointures et sous-requêtes.
Le tableau suivant présente des liens vers les ressources dans la documentation en ligne de SQL Server.
SQL Server 2000 |
SQL Server 2005 |
SQL Server 2008 |
|---|---|---|
Restrictions relatives aux types de valeur élevée
Les restrictions suivantes s'appliquent aux types de données max, qui n'existent pas pour les types de données moins volumineux :
Un sql_variant ne peut pas contenir un type de données varchar volumineux.
Des colonnes varchar volumineuses ne peuvent pas être spécifiées comme colonnes clés dans un index. Elles sont autorisées dans une colonne incluse dans un index sans clusters.
Des colonnes varchar volumineuses ne peuvent pas être utilisées comme colonnes clés de partitionnement.
Utilisation de types de valeur élevée dans Transact-SQL
La fonction Transact-SQL OPENROWSET est une méthode permettant de se connecter et d'accéder à des données distantes en une seule opération. Elle inclut toutes les informations de connexion nécessaires pour accéder à des données distantes depuis une source de données OLE DB. OPENROWSET peut être référencé dans la clause FROM d'une requête comme s'il s'agissait d'un nom de table. Il peut également être référencé comme table cible d'une instruction INSERT, UPDATE ou DELETE, sujette aux capacités du fournisseur OLE DB.
La fonction OPENROWSET a été améliorée dans SQL Server 2005 avec l'ajout du fournisseur de jeu de lignes BULK, qui permet de lire directement les données d'un fichier sans devoir les charger dans une table cible. Cela vous permet d'utiliser OPENROWSET dans une simple instruction INSERT SELECT.
Les arguments de l'option OPENROWSETBULK permettent d'exercer un contrôle important sur l'emplacement où commence et se termine la lecture de données, sur la manière de gérer les erreurs et sur la façon d'interpréter les données. Par exemple, vous pouvez spécifier que le fichier de données doit être lu comme une seule ligne, un jeu de lignes en une seule colonne de type varbinary, varchar ou nvarchar. Pour découvrir la syntaxe complète et les options, voir la documentation en ligne de SQL Server.
L'exemple suivant insère une photo dans la table ProductPhoto de l'exemple de base de données AdventureWorks. Lors de l'utilisation du fournisseur BULKOPENROWSET, vous devez fournir la liste nommée des colonnes même si vous n'insérez pas de valeurs dans chaque colonne. Dans ce cas, la clé primaire est définie comme une colonne identité et peut être omise de la liste des colonnes. Notez que vous devez également fournir un nom de corrélation à la fin de l'instruction OPENROWSET ; en l'occurrence, il s'agit de ThumbnailPhoto. Cela établit une corrélation avec la colonne de la table ProductPhoto dans laquelle le fichier est chargé.
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
Mise à jour de données à l'aide de UPDATE .WRITE
L'instruction Transact-SQL UPDATE utilise une nouvelle syntaxe WRITE pour modifier le contenu de colonnes varchar(max), nvarchar(max) ou varbinary(max). Cela vous permet d'effectuer des mises à jour partielles des données. La syntaxe de UPDATE .WRITE est présentée ici sous forme abrégée :
UPDATE
{ { <object> }
SET
{ { column_name = { .WRITE ( expression , @Offset , @Length ) }
La méthode WRITE spécifie qu'une section de la valeur column_name sera modifiée. L'expression est la valeur qui sera copiée dans column_name, @Offset est le point à partir duquel l'expression sera écrite et l'argument @Length indique la longueur de la section dans la colonne.
Si |
Alors |
|---|---|
La valeur de l'expression est NULL. |
@Length est ignoré et la valeur de column_name est tronquée à l'emplacement spécifié par @Offset. |
@Offset a la valeur NULL |
L'opération de mise à jour ajoute l'expression à la fin de la valeur de column_name existante et @Length est ignoré. |
@Offset est supérieur à la longueur de la valeur column_name. |
SQL Server retourne une erreur. |
@Length a la valeur NULL |
L'opération de mise à jour supprime toutes les données à partir de @Offset jusqu'à la fin de la valeur column_name. |
.gif "Remarque") Remarque : |
|---|
Ni @Offset ni @Length ne peuvent avoir pour valeur un nombre négatif. |
Exemple
Cet exemple Transact-SQL met à jour une valeur partielle dans DocumentSummary, colonne nvarchar(max) dans la table Document de la base de données AdventureWorks. Le mot « components » est remplacé par le mot « features » en spécifiant le mot de remplacement, l'emplacement où commence (offset) le mot à remplacer dans les données existantes et le nombre de caractères à remplacer (length). L'exemple inclut des instructions SELECT devant et derrière l'instruction UPDATE afin de comparer les résultats.
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.
Utilisation de types de valeur élevée dans ADO.NET
Vous pouvez utiliser des types de valeur élevée dans ADO.NET en spécifiant ces types comme des objets SqlParameter d'un objet SqlDataReader afin de retourner un jeu de résultats ou en utilisant un objet SqlDataAdapter pour remplir un DataSet/DataTable. Il n'y a pas de différence d'utilisation entre un type de valeur élevée et le type de données de valeur moins élevée apparenté.
Utilisation de GetSqlBytes pour extraire des données
La méthode GetSqlBytes du SqlDataReader permet d'extraire le contenu d'une colonne varbinary(max). Le fragment de code suivant est basé sur l'hypothèse de l'existence d'un objet SqlCommand nommé cmd qui sélectionne des données varbinary(max) dans une table et d'un objet SqlDataReader nommé reader qui extrait les données comme 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);
}
Utilisation de GetSqlChars pour extraire des données
La méthode GetSqlChars de l'objet SqlDataReader permet d'extraire le contenu d'une colonne varchar(max) ou nvarchar(max). Le fragment de code suivant est basé sur l'hypothèse de l'existence d'un objet SqlCommand nommé cmd qui sélectionne des données nvarchar(max) dans une table et d'un objet SqlDataReader nommé reader qui extrait les données.
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);
}
Utilisation de GetSqlBinary pour extraire des données
La méthode GetSqlBinary d'un objet SqlDataReader permet d'extraire le contenu d'une colonne varbinary(max). Le fragment de code suivant est basé sur l'hypothèse de l'existence d'un objet SqlCommand nommé cmd qui sélectionne des données varbinary(max) dans une table et d'un objet SqlDataReader nommé reader qui extrait les données comme flux 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);
}
Utilisation de GetBytes pour extraire des données
La méthode GetBytes d'un SqlDataReader lit un flux d'octets à partir de l'emplacement de décalage (offset) de colonne spécifié dans un tableau d'octets commençant au décalage de tableau spécifié. Le fragment de code suivant est basé sur l'hypothèse de l'existence d'un objet SqlDataReader nommé reader qui extrait des octets vers un tableau d'octets. Notez que, contrairement à GetSqlBytes, GetBytes requiert une taille pour le tampon de tableau.
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);
}
Utilisation de GetValue pour extraire des données
La méthode GetValue d'un objet SqlDataReader lit la valeur à partir de l'emplacement de décalage (offset) de colonne spécifié dans un tableau. Le fragment de code suivant est basé sur l'hypothèse de l'existence d'un objet SqlDataReader nommé reader qui extrait des données binaires à partir du premier emplacement de décalage (offset) de colonne, puis chaîne les données à partir du second emplacement de décalage de colonne.
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);
}
Conversion de types de valeur élevée en types CLR
Vous pouvez convertir le contenu d'une colonne varchar(max) ou nvarchar(max) à l'aide de n'importe quelle méthode de conversion de chaîne, telle que ToString. Le fragment de code suivant est basé sur l'hypothèse de l'existence d'un objet SqlDataReader nommé reader qui extrait les données.
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);
}
Exemple
Le code suivant extrait le nom et l'objet LargePhoto de la table ProductPhoto dans la base de données AdventureWorks et les enregistre dans un fichier. L'assembly doit être compilé avec une référence à l'espace de noms System.Drawing. La méthode GetSqlBytes de l'objet SqlDataReader retourne un objet SqlBytes qui expose une propriété Stream. Le code utilise celle-ci pour créer un nouvel objet Bitmap, puis l'enregistre dans le ImageFormat Gif.
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
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();
}
}
}
Utilisation de paramètres de types de valeur élevée
Vous pouvez utiliser des types de valeur élevée dans des objets SqlParameter de la même manière que des types de valeur moins élevée dans des objets SqlParameter. Vous pouvez extraire des types de valeur élevée en tant que valeurs SqlParameter, comme illustré dans l'exemple suivant. Le code est basé sur l'hypothèse que la procédure stockée GetDocumentSummary suivante existe dans l'exemple de base de données AdventureWorks. La procédure stockée prend un caractère d'entrée nommé @DocumentID et retourne le contenu de la colonne DocumentSummary dans le paramètre de sortie @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
Exemple
Le code ADO.NET crée les objets SqlConnection et SqlCommand pour exécuter la procédure stockée GetDocumentSummary et extraire le résumé du document qui est stocké comme type de valeur élevée. Le code transmet une valeur pour le paramètre d'entrée @DocumentID et affiche les résultats retournés dans le paramètre de sortie @DocumentSummary dans la fenêtre de console.
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
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;
}
}
}
Voir aussi
Concepts
Mappages de types de données SQL Server (ADO.NET)