Gestion des valeurs null (ADO.NET)

Mise à jour : November 2007

Une valeur null dans une base de données relationnelle est utilisée lorsque la valeur d'une colonne est inconnue ou manquante. Une valeur null n'est ni une chaîne vide (pour les types de données caractère ou datetime) ni une valeur zéro (pour les types de données numériques). La spécification ANSI SQL-92 stipule qu'une valeur null doit être la même pour tous les types de données afin que toutes les valeurs null soient traitées de manière cohérente. L'espace de noms System.Data.SqlTypes fournit des sémantiques de valeurs null en implémentant l'interface INullable. Chacun des types de données de l'espace de noms System.Data.SqlTypes possède sa propre propriété IsNull et une valeur Null qui peut être assignée à une instance de ce type de données.

Remarque :

L'une des nouveautés de la version 2.0 du .NET Framework est la prise en charge des types nullables qui permettent aux programmeurs d'étendre un type de valeur pour représenter toutes les valeurs du type sous-jacent. Ces types nullables CLR représentent une instance de la structure Nullable. Cette fonctionnalité est tout particulièrement utile lorsque des types de valeurs sont boxed et unboxed, ce qui améliore la compatibilité avec les types d'objets. Les types nullables CLR ne sont pas destinés au stockage de valeurs de base de données null, car une valeur null SQL ANSI ne se comporte pas de la même manière qu'une référence null (ou Nothing en Visual Basic). Pour travailler avec des valeurs de base de données null SQL ANSI , utilisez des valeurs null System.Data.SqlTypes plutôt que Nullable. Pour plus d'informations sur l'utilisation de types nullables CLR dans Visual Basic, consultez Types valeur nullables et pour le langage C#, consultez Utilisation de types nullables (Guide de programmation C#).

Valeurs null et logique à trois valeurs

Permettre des valeurs null dans des définitions de colonnes introduit une logique à trois valeurs dans votre application. Une comparaison peut correspondre à l'une des trois conditions :

• True

• False

• Inconnu

Comme la valeur null est considérée comme inconnue, les deux valeurs null comparées l'une à l'autre ne sont pas considérées comme égales. Dans des expressions utilisant des opérateurs arithmétiques, si l'un des opérandes est une valeur null, le résultat est une valeur null également.

Valeurs null et SqlBoolean

La comparaison entre tout System.Data.SqlTypes retournera un SqlBoolean. La fonction IsNull de chaque SqlType retourne un SqlBoolean et peut être utilisée pour vérifier les valeurs null. Les tables de vérité suivantes montrent comment les opérateurs AND, OR et NOT fonctionnent en présence d'une valeur null (T=true, F=false et U=inconnu ou null).

Compréhension de l'option ANSI_NULLS

System.Data.SqlTypes offre les mêmes sémantiques que lorsque l'option ANSI_NULLS est définie dans SQL Server. Tous les opérateurs arithmétiques (+, -, *, /, %), opérateurs de bits (~, &, |) et la plupart des fonctions retournent une valeur null si l'un des opérandes ou arguments est une valeur null, excepté pour la propriété IsNull.

La norme ANSI SQL-92 ne prend pas en charge columnName = NULL dans une clause WHERE. Dans SQL Server, l'option ANSI_NULLS contrôle la possibilité de nullité par défaut dans la base de données et l'évaluation des comparaisons par rapport à des valeurs null. Si ANSI_NULLS est activée (par défaut), l'opérateur IS NULL doit être utilisé dans des expressions lors du test des valeurs null. Par exemple, la comparaison suivante entraîne toujours une valeur inconnue si ANSI_NULLS est activée :

            colname > NULL

La comparaison avec une variable contenant une valeur null entraîne également une valeur inconnue :

            colname > @MyVariable

Utilisez le prédicat IS NULL ou IS NOT NULL pour tester une valeur null. Cela peut compliquer la clause WHERE. Par exemple, la colonne TerritoryID de la table AdventureWorks Customer autorise les valeurs null. Si une instruction SELECT doit tester des valeurs null en plus des autres, elle doit inclure un prédicat IS NULL :

SELECT CustomerID, AccountNumber, TerritoryID
FROM AdventureWorks.Sales.Customer
WHERE TerritoryID IN (1, 2, 3)
   OR TerritoryID IS NULL

Si vous désactivez ANSI_NULLS dans SQL Server, vous pouvez créer des expressions qui utilisent l'opérateur d'égalité pour effectuer une comparaison par rapport à une valeur null. Cependant, vous ne pouvez pas empêcher des connexions différentes de définir des options null pour cette connexion. L'utilisation de IS NULL pour tester des valeurs null fonctionne toujours, indépendamment des paramètres ANSI_NULLS pour une connexion.

La désactivation de ANSI_NULLS n'est pas prise en charge dans un DataSet, qui respecte toujours la norme ANSI SQL-92 relative à la gestion des valeurs null dans System.Data.SqlTypes.

Assignation de valeurs null

Les valeurs null sont spéciales et leurs sémantiques de stockage et d'assignation varient selon les systèmes de types et de stockage. Un Dataset est conçu pour être utilisé avec différents systèmes de types et de stockage.

Cette section décrit la sémantique des valeurs null pour l'assignation des valeurs null à un DataColumn dans un DataRow dans différents systèmes de types.

• DBNull.Value
  Cette assignation est valide pour un DataColumn de n'importe quel type. Si le type implémente INullable, DBNull.Value est converti dans la valeur null fortement typée appropriée.

• SqlType.Null
  Tous les types de données System.Data.SqlTypes implémentent INullable. Si la valeur null fortement typée peut être convertie dans le type données de la colonne à l'aide des opérateurs cast implicites, l'assignation doit continuer. Si tel n'est pas le cas, une exception cast non valide est levée.

• null
  Si 'null' est une valeur autorisée pour le type de données DataColumn, elle est convertie en DbNull.Value ou Null approprié en association avec le type INullable (SqlType.Null)

• derivedUdt.Null
  Pour les colonnes UDT, les valeurs null sont toujours stockées en fonction du type associé à DataColumn. Considérons le cas d'un UDT associé à un DataColumn qui n'implémente pas INullable contrairement à sa sous-classe. Dans ce cas, si une valeur null fortement typée associée à la classe dérivée est assignée, elle est stockée en tant que DbNull.Value non typé, car le stockage de valeurs null est toujours cohérent avec le type de données de DataColumn.

Remarque :
La structure Nullable<T> ou Nullable n'est actuellement pas prise en charge dans le DataSet.

Assignation de plusieurs colonnes (lignes)

DataTable.Add, DataTable.LoadDataRow ou toute autre API acceptant une propriété ItemArray qui obtient le mappage à une ligne, mappe 'null' à la valeur par défaut de DataColumn. Si un objet du tableau contient DbNull.Value ou son équivalent fortement typé, les mêmes règles que celles décrites précédemment s'appliquent.

Par ailleurs, les règles suivantes s'appliquent à une instance des assignations null DataRow.["columnName"] :

1. La valeur par défaut default est DbNull.Value pour tout, à l'exception des colonnes null fortement typées contenant la valeur null fortement typée appropriée.

2. Les valeurs null ne sont jamais écrites durant la sérialisation en fichiers XML (comme dans « xsi:nil »).

3. Toutes les valeurs non null, y compris les valeurs par défaut, sont toujours écrites durant la sérialisation en XML. Cela est contraire à la sémantique XSD/XML où une valeur null (xsi:nil) est explicite et la valeur par défaut est implicite (s'il n'est pas présent dans XML, un analyseur validant peut l'obtenir d'un schéma XSD associé). Le contraire est vrai pour un DataTable : une valeur null est implicite et la valeur par défaut est explicite.

4. La valeur NULL est assignée à toutes les valeurs de colonne manquantes pour les lignes lues à partir de l'entrée XML. La valeur par défaut de DataColumn est assignée aux lignes créées à l'aide de NewRow ou de méthodes similaires.

5. La méthode IsNull retourne true pour DbNull.Value et INullable.Null.

Assignation de valeurs null

La valeur par défaut pour toute instance System.Data.SqlTypes est null.

Les valeurs null dans System.Data.SqlTypes sont spécifiques aux types et ne peuvent pas être représentées par une simple valeur, telle que DbNull. Utilisez la propriété IsNull pour vérifier les valeurs null.

Les valeurs null peuvent être assignées à un DataColumn comme illustré dans l'exemple de code suivant. Vous pouvez assigner directement des valeurs null à des variables SqlTypes sans lever une exception.

Exemple

L'exemple de code suivant crée un DataTable avec deux colonnes définies comme SqlInt32 et SqlString. Le code ajoute une ligne de valeurs connues, une ligne de valeurs null, puis itère sur le DataTable, assignant les valeurs à des variables et affichant la sortie dans la fenêtre de console.

Private Sub WorkWithSqlNulls()
    Dim table As New DataTable()

    ' Specify the SqlType for each column.
    Dim idColumn As DataColumn = _
      table.Columns.Add("ID", GetType(SqlInt32))
    Dim descColumn As DataColumn = _
      table.Columns.Add("Description", GetType(SqlString))

    ' Add some data.
    Dim row As DataRow = table.NewRow()
    row("ID") = 123
    row("Description") = "Side Mirror"
    table.Rows.Add(row)

    ' Add null values.
    row = table.NewRow()
    row("ID") = SqlInt32.Null
    row("Description") = SqlString.Null
    table.Rows.Add(row)

    ' Initialize variables to use when
    ' extracting the data.
    Dim isColumnNull As SqlBoolean = False
    Dim idValue As SqlInt32 = SqlInt32.Zero
    Dim descriptionValue As SqlString = SqlString.Null

    ' Iterate through the DataTable and display the values.
    For Each row In table.Rows
        ' Assign values to variables. Note that you 
        ' do not have to test for null values.
        idValue = CType(row("ID"), SqlInt32)
        descriptionValue = CType(row("Description"), SqlString)

        ' Test for null value with ID column
        isColumnNull = idValue.IsNull

        ' Display variable values in console window.
        Console.Write("isColumnNull={0}, ID={1}, Description={2}", _
          isColumnNull, idValue, descriptionValue)
        Console.WriteLine()
    Next row
End Sub

static private void WorkWithSqlNulls()
{
    DataTable table = new DataTable();

    // Specify the SqlType for each column.
    DataColumn idColumn =
        table.Columns.Add("ID", typeof(SqlInt32));
    DataColumn descColumn =
        table.Columns.Add("Description", typeof(SqlString));

    // Add some data.
    DataRow nRow = table.NewRow();
    nRow["ID"] = 123;
    nRow["Description"] = "Side Mirror";
    table.Rows.Add(nRow);

    // Add null values.
    nRow = table.NewRow();
    nRow["ID"] = SqlInt32.Null;
    nRow["Description"] = SqlString.Null;
    table.Rows.Add(nRow);

    // Initialize variables to use when
    // extracting the data.
    SqlBoolean isColumnNull = false;
    SqlInt32 idValue = SqlInt32.Zero;
    SqlString descriptionValue = SqlString.Null;

    // Iterate through the DataTable and display the values.
    foreach (DataRow row in table.Rows)
    {
        // Assign values to variables. Note that you 
        // do not have to test for null values.
        idValue = (SqlInt32)row["ID"];
        descriptionValue = (SqlString)row["Description"];

        // Test for null value in ID column.
        isColumnNull = idValue.IsNull;

        // Display variable values in console window.
        Console.Write("isColumnNull={0}, ID={1}, Description={2}",
            isColumnNull, idValue, descriptionValue);
        Console.WriteLine();
    }

Cet exemple affiche les résultats suivants :

isColumnNull=False, ID=123, Description=Side Mirror
isColumnNull=True, ID=Null, Description=Null

Comparaison de valeurs null à SqlTypes et des types CLR

Lors de la comparaison de valeurs null, il est important de comprendre la différence entre la manière dont la méthode Equals évalue les valeurs null dans System.Data.SqlTypes et celle dont elle fonctionne avec les types CLR. Toutes les méthodes System.Data.SqlTypesEquals utilisent des sémantiques de base de données pour évaluer des valeurs null : si au moins une des valeurs est null, la comparaison produit null. Par ailleurs, l'utilisation de la méthode CLR Equals sur deux System.Data.SqlTypes produira true si les deux sont null. Cela reflète la différence entre l'utilisation d'une méthode d'instance, telle que la méthode CLR String.Equals, et l'utilisation d'une méthode statique/partagée, SqlString.Equals.

L'exemple suivant montre la différence de résultats entre les méthodes SqlString.Equals et String.Equals lorsque chacune passe une paire de valeurs null puis une paire de chaînes vides.

Private Sub CompareNulls()
    ' Create two new null strings.
    Dim a As New SqlString
    Dim b As New SqlString

    ' Compare nulls using static/shared SqlString.Equals.
    Console.WriteLine("SqlString.Equals shared/static method:")
    Console.WriteLine("  Two nulls={0}", SqlStringEquals(a, b))

    ' Compare nulls using instance method String.Equals.
    Console.WriteLine()
    Console.WriteLine("String.Equals instance method:")
    Console.WriteLine("  Two nulls={0}", StringEquals(a, b))

    ' Make them empty strings.
    a = ""
    b = ""

    ' When comparing two empty strings (""), both the shared/static and
    ' the instance Equals methods evaluate to true.
    Console.WriteLine()
    Console.WriteLine("SqlString.Equals shared/static method:")
    Console.WriteLine("  Two empty strings={0}", SqlStringEquals(a, b))

    Console.WriteLine()
    Console.WriteLine("String.Equals instance method:")
    Console.WriteLine("  Two empty strings={0}", StringEquals(a, b))
End Sub

Private Function SqlStringEquals(ByVal string1 As SqlString, _
    ByVal string2 As SqlString) As String

    ' SqlString.Equals uses database semantics for evaluating nulls.
    Dim returnValue As String = SqlString.Equals(string1, string2).ToString()
    Return returnValue
End Function

Private Function StringEquals(ByVal string1 As SqlString, _
    ByVal string2 As SqlString) As String

    ' String.Equals uses CLR type semantics for evaluating nulls.
    Dim returnValue As String = string1.Equals(string2).ToString()
    Return returnValue
End Function

    private static void CompareNulls()
    {
        // Create two new null strings.
        SqlString a = new SqlString();
        SqlString b = new SqlString();

        // Compare nulls using static/shared SqlString.Equals.
        Console.WriteLine("SqlString.Equals shared/static method:");
        Console.WriteLine("  Two nulls={0}", SqlStringEquals(a, b));

        // Compare nulls using instance method String.Equals.
        Console.WriteLine();
        Console.WriteLine("String.Equals instance method:");
        Console.WriteLine("  Two nulls={0}", StringEquals(a, b));

        // Make them empty strings.
        a = "";
        b = "";

        // When comparing two empty strings (""), both the shared/static and
        // the instance Equals methods evaluate to true.
        Console.WriteLine();
        Console.WriteLine("SqlString.Equals shared/static method:");
        Console.WriteLine("  Two empty strings={0}", SqlStringEquals(a, b));

        Console.WriteLine();
        Console.WriteLine("String.Equals instance method:");
        Console.WriteLine("  Two empty strings={0}", StringEquals(a, b));
    }

    private static string SqlStringEquals(SqlString string1, SqlString string2)
    {
        // SqlString.Equals uses database semantics for evaluating nulls.
        string returnValue = SqlString.Equals(string1, string2).ToString();
        return returnValue;
    }

    private static string StringEquals(SqlString string1, SqlString string2)
    {
        // String.Equals uses CLR type semantics for evaluating nulls.
        string returnValue = string1.Equals(string2).ToString();
        return returnValue;
    }
}

Le code génère la sortie suivante :

SqlString.Equals shared/static method:
  Two nulls=Null

String.Equals instance method:
  Two nulls=True

SqlString.Equals shared/static method:
  Two empty strings=True

String.Equals instance method:
  Two empty strings=True

Voir aussi

Autres ressources

Types de données SQL Server et ADO.NET