Gestion des valeurs null

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 DateHeure), ni une valeur zéro (pour les types de données numériques). La spécification SQL-92 ANSI indique qu’une valeur Null doit être la même pour tous les types de données, de sorte que toutes les valeurs Null sont gérées de manière cohérente. L’espace de noms System.Data.SqlTypes fournit une sémantique Null en implémentant l’interface INullable. Chacun des types de données de System.Data.SqlTypes a sa propre propriété IsNull et une valeur Null qui peut être assignée à une instance de ce type de données.

Valeurs null et logique à trois valeurs

L’autorisation de valeurs Null dans les définitions de colonne introduit une logique ternaire dans votre application. Une comparaison peut évaluer entre une et trois conditions :

• True

• False

• Unknown

Étant donné que la valeur Null est considérée comme inconnue, deux valeurs Null comparées l’une avec l’autre ne sont pas considérées comme égales. Dans les expressions utilisant des opérateurs arithmétiques, si l’un des opérandes a la valeur Null, le résultat est également Null.

Valeurs Null et SqlBoolean

La comparaison entre tout System.Data.SqlTypes retourne une SqlBoolean. La fonction IsNull pour 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 dans la présence d’une valeur Null. (T = true (vrai), F = false (faux) et U = unknown (inconnu) ou Null.)

Compréhension de l'option ANSI_NULLS

System.Data.SqlTypes fournit la même sémantique que lorsque l’option ANSI_NULLS est activée dans SQL Server. Tous les opérateurs arithmétiques (+, -, *, /, %), les opérateurs de bits (~, &, |) et la plupart des fonctions retournent une valeur Null si un des opérandes ou des 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 fois la possibilité de valeur Null par défaut dans la base de données et l’évaluation des comparaisons par rapport aux valeurs Null. Si ANSI_NULLS est activé (valeur par défaut), l’opérateur IS NULL doit être utilisé dans les expressions lors du test des valeurs Null. Par exemple, la comparaison suivante donne toujours un résultat inconnu quand ANSI_NULLS est activé :

colname > NULL  

La comparaison avec une variable contenant une valeur Null génère également Inconnu :

colname > @MyVariable  

Utilisez le prédicat IS NULL ou IS NOT NULL pour tester si une valeur est NULL. Ceci peut compliquer encore la clause WHERE. Par exemple, la colonne TerritoryID de la table AdventureWorks Customer autorise les valeurs Null. Si une instruction SELECT doit rechercher 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 avec la valeur Null. Toutefois, 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 les valeurs Null fonctionne toujours, quels que soient les paramètres de ANSI_NULLS pour une connexion.

La désactivation de ANSI_NULLS n’est pas prise en charge dans un DataSet, qui suit toujours la norme ANSI SQL-92 pour la gestion des valeurs Null dans System.Data.SqlTypes.

Assignation de valeurs null

Les valeurs Null sont spéciales et leur sémantique de stockage et d’affectation diffère selon les systèmes de stockage et systèmes de type. Un Dataset est conçu pour être utilisé avec différents systèmes de type et de stockage.

Cette section décrit la sémantique Null pour l’affectation de valeurs Null à un DataColumn dans un DataRow sur les différents systèmes de type.

DBNull.Value
Cette affectation est valide pour un DataColumn de n’importe quel type. Si le type implémente INullable, DBNull.Value est forcé 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 de données de la colonne à l’aide d’opérateurs de forçage de type implicite, l’affectation doit y être soumise. Sinon, une exception de forçage de type non valide est levée.

null
Si « Null » est une valeur autorisée pour le type de données DataColumn donné, il est converti en DbNull.Value approprié ou Null associé au type de INullable (SqlType.Null)

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

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

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

Les valeurs Null peuvent être attribuées à un DataColumn comme indiqué dans l’exemple de code suivant. Vous pouvez attribuer directement des valeurs Null à des variables SqlTypes sans déclencher d’exception.

Exemple

L’exemple de code suivant crée une 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 au sein de la DataTable, en attribuant les valeurs aux variables et en affichant le résultat dans la fenêtre de console.

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

    // 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();
    }

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

Cet exemple produit les résultats suivants :

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

Assignation de plusieurs colonnes (lignes)

DataTable.Add, DataTable.LoadDataRow ou d’autres API acceptant une ItemArray mappée à une ligne, mappez « 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 ci-dessus sont appliquées.

En outre, les règles suivantes s’appliquent à une instance des affectations Null DataRow.["columnName"] :

1. La valeur par défaut 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 lors de la sérialisation vers des fichiers XML (comme dans « xsi:nil »).

3. Toutes les valeurs non Null, y compris les valeurs par défaut, sont toujours écrites lors de la sérialisation en XML. Contrairement à la sémantique XSD/XML, où une valeur Null (xsi:nil) est explicite et que la valeur par défaut est implicite (si elle n’est pas présente dans XML, un analyseur de validation peut l’obtenir à partir d’un schéma XSD associé). Le contraire est vrai pour une DataTable : une valeur Null est implicite et la valeur par défaut est explicite.

4. La Valeur Null est attribué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 attribuée aux lignes créées à l’aide de NewRow ou à des méthodes similaires.

5. La méthode IsNull renvoie true pour DbNull.Value et INullable.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 façon dont la méthode Equals évalue les valeurs Null dans les System.Data.SqlTypes par rapport à la façon dont elle fonctionne avec les types CLR. Toutes les méthodes System.Data.SqlTypesEquals utilisent la sémantique de base de données pour évaluer les valeurs Null : si une des valeurs ou les deux sont Null, la comparaison génère la valeur Null. En revanche, l’utilisation de la méthode Equals CLR sur deux System.Data.SqlTypes produira la valeur 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 String.Equals CLR et l’utilisation de la méthode statique/partagée, SqlString.Equals.

L’exemple suivant illustre la différence de résultats entre la méthode SqlString.Equals et la méthode String.Equals quand chacun reçoit une paire de valeurs Null, puis une paire de chaînes vides.

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

        // 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));
    }

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

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

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

Ce code produit 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

• Types de données SQL Server et ADO.NET
• Vue d'ensemble d’ADO.NET