Modification de données de valeurs élevées (max) dans ADO.NET

Les types de données LOB (Large Object) sont ceux dont la taille maximale de ligne dépasse huit kilo-octets (Ko). SQL Server fournit un spécificateur max pour les types de données varchar, nvarchar et varbinary afin de 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 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 de valeur élevée, les données peuvent être récupérées et mises à jour de façon incrémentielle.

Les types de données max peuvent être utilisés pour effectuer des comparaisons, en tant que variables Transact-SQL, ainsi que des concaténations. Elles peuvent également être utilisées dans les clauses DISTINCT, ORDER BY et GROUP BY d’une instruction SELECT, ainsi que dans les agrégats, jointures et sous-requêtes.

Pour plus d’informations, consultez Utilisation de types de données à grande valeur.

Restrictions relatives aux types de valeur élevée

Les restrictions suivantes s’appliquent aux types de données max, et n’existent pas pour les types de données plus petits :

• Un sql_variant ne peut pas contenir un type de données varchar de valeur élevée.

• Les colonnes varchar de valeur élevée ne peuvent pas être spécifiées en tant que colonne clé dans un index. Elles sont autorisées dans une colonne incluse dans un index non cluster.

• Les colonnes varchar de valeur élevée ne peuvent pas être utilisées en tant que colonnes clés de partitionnement.

Utilisation de types de valeur élevée dans Transact-SQL

La fonction Transact-SQL OPENROWSET est une méthode unique de connexion et d’accès aux données distantes. Elle inclut toutes les informations de connexion nécessaires pour accéder à des données distantes à partir d'une source de données OLE DB. Il est possible de référencer OPENROWSET dans la clause FROM d’une requête comme s’il s’agissait du nom d’une 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 comprend le 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 OPENROWSET BULK 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. Vous pouvez par exemple spécifier que le fichier de données soit lu comme un ensemble d’une seule ligne et d’une seule colonne du type varbinary, varchar ou nvarchar.

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 BULK OPENROWSET, 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 d’identité et peut être omise dans la liste des colonnes. Notez que vous devez également fournir un nom de corrélation à la fin de l’instruction OPENROWSET, dans ce cas ThumbnailPhoto. Cela correspond à 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 possède une nouvelle syntaxe WRITE permettant de modifier le contenu des colonnes varchar(max), nvarchar(max) ou varbinary(max). Cela vous permet d’effectuer des mises à jour partielles des données. La syntaxe UPDATE .WRITE est présentée ici sous forme abrégée :

UPDATE

{ <objet> }

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
L’expression est définie sur NULL	@Length est ignoré et la valeur de column_name est tronquée à l’emplacement spécifié par @Offset.
@Offset est 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 est NULL	La mise à jour supprime toutes les données de @Offset jusqu’à la fin de la valeur de column_name.

Notes

Ni @Offset ni @Length ne peuvent être un nombre négatif.

Exemple

Cet exemple Transact-SQL met à jour une valeur partielle dans DocumentSummary, une colonne nvarchar(max) dans la table Document de la base de données AdventureWorks. Le terme « components » est remplacé par le terme « features », en spécifiant le terme de remplacement, la position de départ (décalage) du terme à remplacer dans les données existantes et le nombre de caractères à remplacer (longueur). L’exemple comprend des instructions SELECT avant et après l’instruction UPDATE pour 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 des types de valeur élevée comme 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 aucune différence entre la façon dont vous travaillez avec un type de valeur élevée et le type de données de plus petite valeur associé.

Utilisation de GetSqlBytes pour récupérer des données

La méthode GetSqlBytes du SqlDataReader peut être utilisée pour récupérer le contenu d’une colonne varbinary(max). Le fragment de code suivant suppose un objet SqlCommand nommé cmd qui sélectionne des données varbinary(max) d’une table et un objet SqlDataReader nommé reader qui récupère les données en tant que 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 du SqlDataReader peut être utilisée pour récupérer le contenu d’une colonne varchar(max) ou nvarchar(max). Le fragment de code suivant suppose un objet SqlCommand nommé cmd qui sélectionne des données nvarchar(max) d’une table et un objet SqlDataReader nommé reader qui récupère 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 SqlDataReader peut être utilisée pour récupérer le contenu d’une colonne varbinary(max). Le fragment de code suivant suppose un objet SqlCommand nommé cmd qui sélectionne des données varbinary(max) d’une table et un objet SqlDataReader nommé reader qui récupère les données en tant que 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'offset de colonne spécifié dans un tableau d’octets commençant à l’offset de tableau donné. Le fragment de code suivant suppose un objet SqlDataReader nommé reader qui récupère les octets dans un tableau d’octets. Notez que, contrairement à GetSqlBytes, GetBytes nécessite une taille pour la mémoire tampon du 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 SqlDataReader lit la valeur à partir de l’offset de colonne spécifié dans un tableau. Le fragment de code suivant suppose un objet SqlDataReader nommé reader qui récupère les données binaires à partir de l’offset de la première colonne, puis les données de chaîne à partir du deuxième offset 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 d’une des méthodes de conversion de chaînes, comme ToString. Le fragment de code suivant suppose un objet SqlDataReader nommé reader qui récupère 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 récupère le nom et l’objet LargePhoto à partir de la table ProductPhoto dans la base de données AdventureWorks et l’enregistre dans un fichier. L’assembly doit être compilé avec une référence à l’espace de noms System.Drawing. La méthode GetSqlBytes du 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 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

Utilisation de paramètres de types de valeur élevée

Les types de valeur élevée peuvent être utilisés dans des objets SqlParameter de la même façon que vous utilisez des types de valeur plus petits 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 suppose 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 des objets SqlConnection et SqlCommand pour exécuter la procédure stockée GetDocumentSummary et récupérer le résumé du document, qui est stocké en tant que 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.

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

Voir aussi

• Données binaires et à valeurs élevées SQL Server
• Mappages de types de données SQL Server
• Opérations sur les données SQL Server dans ADO.NET
• Vue d'ensemble d’ADO.NET