Gestione dei valori null

Se il valore di una colonna è sconosciuto o mancante, viene usato un valore Null in un database relazionale. Un valore Null non è né una stringa vuota (per i tipi di dati character o datetime) né un valore zero (per i tipi di dati numerici). La specifica ANSI SQL-92 indica che un valore Null deve essere lo stesso per tutti i tipi di dati, in modo che tutti i valori Null vengano gestiti in modo coerente. Lo spazio dei nomi System.Data.SqlTypes specifica la semantica Null implementando l'interfaccia INullable. Ogni tipo di dati in System.Data.SqlTypes ha una propria proprietà IsNull e un valore Null che può essere assegnato a un'istanza di tale tipo di dati.

Valori null e logica con tre valori

Consentendo i valori Null nelle definizioni di colonna si introduce una logica a tre valori nell'applicazione. Un confronto può restituire una delle tre condizioni seguenti:

• Vero

• False

• Sconosciuto

Poiché il valore Null è considerato sconosciuto, due valori Null confrontati tra loro non sono considerati uguali. Nelle espressioni che usano operatori aritmetici, se uno degli operandi è Null, anche il risultato è Null.

Valori Null e SqlBoolean

Il confronto tra qualsiasi System.Data.SqlTypes restituisce un elemento SqlBoolean. La funzione IsNull per ogni SqlType restituisce un elemento SqlBoolean e può essere usata per verificare la presenza di valori Null. Le seguenti tabelle di veridicità illustrano la funzione degli operatori AND, OR e NOT in presenza di un valore Null. (T=true, F=false e U=unknown o Null)

Nozioni di base sull'opzione ANSI_NULLS

System.Data.SqlTypes offre la stessa semantica di quando si imposta l'opzione ANSI_NULLS in SQL Server. Tutti gli operatori aritmetici (+, -, *, /, %), gli operatori bit per bit (~, &, |) e la maggior parte delle funzioni restituiscono Null se uno degli operandi o degli argomenti è Null, ad eccezione della proprietà IsNull.

Lo standard ANSI SQL-92 non supporta columnName = NULL in una clausola WHERE. In SQL Server l'opzione ANSI_NULLS controlla sia il supporto di valori Null predefinito nel database, sia la valutazione dei confronti con i valori Null. Se l'opzione ANSI_NULLS è attivata (impostazione predefinita), è necessario usare l'operatore IS NULL nelle espressioni quando si effettuano test dei valori Null. Ad esempio, se l'opzione ANSI_NULLS è impostata su ON, il confronto seguente restituisce sempre UNKNOWN:

colname > NULL  

Il confronto con una variabile che contiene un valore Null restituisce anche Unknown:

colname > @MyVariable  

Per verificare la presenza di un valore Null, usare il predicato IS NULL o IS NOT NULL. La clausola WHERE potrebbe risultare in tal modo più complessa. Ad esempio, la colonna TerritoryID nella tabella Customer di AdventureWorks consente i valori Null. Un'istruzione SELECT che oltre ad altri valori deve verificare i valori Null deve includere il predicato IS NULL:

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

Se si imposta ANSI_NULLS come disattivata in SQL Server, è possibile creare espressioni che usano l'operatore di uguaglianza per eseguire il confronto con il valore Null. Tuttavia, non è possibile impedire a diverse connessioni di impostare opzioni Null per tale connessione. L'uso di IS NULL per testare i valori Null funziona sempre, indipendentemente dalle impostazioni di ANSI_NULLS per una connessione.

L'impostazione di ANSI_NULLS come disattivata non è supportata in un oggetto DataSet, che segue sempre lo standard ANSI SQL-92 per la gestione dei valori Null in System.Data.SqlTypes.

Assegnazione di valori null

I valori Null sono speciali e la relativa semantica di archiviazione e assegnazione varia tra sistemi di tipi e sistemi di archiviazione diversi. Un oggetto Dataset è progettato per essere usato con sistemi di tipi e di archiviazione diversi.

In questa sezione viene descritta la semantica Null per l'assegnazione di valori Null a un DataColumn in un DataRow nei diversi sistemi di tipi.

DBNull.Value
Questa assegnazione è valida per un DataColumn di qualsiasi tipo. Se il tipo implementa INullable, DBNull.Value viene assegnato forzatamente al valore Null fortemente tipizzato appropriato.

SqlType.Null
Tutti i tipi di dati System.Data.SqlTypes implementano INullable. Se il valore Null fortemente tipizzato può essere convertito nel tipo di dati della colonna usando gli operatori di cast impliciti, l'assegnazione ha esito positivo. In caso contrario, viene generata un'eccezione di cast non valido.

null
Se "null" è un valore valido per il tipo di dati DataColumn specificato, viene assegnato forzatamente all'oggetto DbNull.Value o Null appropriato associato al tipo INullable (SqlType.Null)

derivedUdt.Null
Per le colonne con tipo definito dall'utente (UDT), i valori Null vengono sempre archiviati in base al tipo associato a DataColumn. Si consideri il caso di un UDT associato a un DataColumn che non implementa un elemento INullable mentre la relativa sottoclasse lo implementa. In questo caso, se viene assegnato un valore Null fortemente tipizzato associato alla classe derivata, viene archiviato come DbNull.Value non tipizzato, perché l'archiviazione Null è sempre coerente con il tipo di dati di DataColumn.

Il valore predefinito per qualsiasi istanza di System.Data.SqlTypes è Null.

I valori Null in System.Data.SqlTypes sono specifici del tipo e non possono essere rappresentati da un singolo valore, ad esempio DbNull. Usare la proprietà IsNull per verificare la presenza di valori Null.

È possibile assegnare valori Null a un DataColumn come illustrato nell'esempio di codice seguente. È possibile assegnare direttamente i valori Null alle variabili SqlTypes senza generare un'eccezione.

Esempio

Nell'esempio di codice seguente viene creato un DataTable con due colonne definite come SqlInt32 e SqlString. Il codice aggiunge una riga di valori noti, una riga di valori Null e quindi scorre esegue l'iterazione in DataTable, assegnando i valori alle variabili e visualizzando l'output nella finestra della 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

L'esempio visualizza i risultati seguenti:

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

Assegnazione di più colonne (riga)

DataTable.Add, DataTable.LoadDataRow o altre API che accettano un elemento ItemArray che viene mappato a una riga, eseguono il mapping di "null" al valore predefinito di DataColumn. Se un oggetto nella matrice contiene DbNull.Value o la relativa controparte fortemente tipizzata, vengono applicate le stesse regole descritte sopra.

Per un'istanza di assegnazioni di valori Null di DataRow.["columnName"] si applicano inoltre le regole seguenti:

1. Il valore predefinito è DbNull.Value per tutte le colonne ad eccezione di quelle null fortemente tipizzate, alle quali si applica il valore null fortemente tipizzato appropriato.

2. I valori Null non vengono mai scritti durante la serializzazione nei file XML (come in "xsi:nil").

3. Tutti i valori non Null, incluse le impostazioni predefinite, vengono sempre scritti durante la serializzazione in XML, a differenza della semantica XSD/XML in cui un valore Null (xsi:nil) è esplicito e il valore predefinito è implicito (se non è presente in XML, un parser di convalida può ottenerlo da uno schema XSD associato). Per un oggetto DataTable è vero il contrario: un valore Null è implicito e il valore predefinito è esplicito.

4. A tutti i valori di colonna mancanti per le righe lette dall'input XML viene assegnato NULL. Alle righe create con NewRow o metodi simili viene assegnato il valore predefinito di DataColumn.

5. Il metodo IsNull restituisce true sia per DbNull.Value che per INullable.Null.

Confronto di valori null con SqlTypes e tipi CLR

Quando si confrontano i valori Null, è importante comprendere la differenza tra il modo in cui il metodo Equals valuta i valori Null in System.Data.SqlTypes e il modo in cui funziona con i tipi CLR. Tutti i metodi System.Data.SqlTypesEquals usano la semantica del database per valutare i valori Null: se uno o entrambi i valori sono Null, il confronto restituisce Null. D'altra parte, l'uso del metodo CLR Equals per due System.Data.SqlTypes genera True se entrambi i valori sono Null. Ciò riflette la differenza tra l'uso di un metodo di istanza, ad esempio il metodo CLR String.Equals, e l'uso del metodo statico/condiviso, SqlString.Equals.

Nell'esempio seguente viene illustrata la differenza nei risultati tra il metodo SqlString.Equals e il metodo String.Equals quando a ognuno viene passata una coppia di valori Null e quindi una coppia di stringhe vuote.

    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

Il codice produce l'output seguente:

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

Vedi anche

• Tipi di dati SQL Server e ADO.NET
• Panoramica di ADO.NET