Gestione di valori null (ADO.NET)

  • Articolo

Un valore null in un database relazionale viene utilizzato quando il valore in una colonna è sconosciuto o mancante. Un valore null non è né una stringa vuota (per tipi di dati carattere o data-ora) né un valore zero (per tipi di dati numerici). Nella specifica ANSI SQL-92 si afferma che un valore null deve essere uguale per tutti i tipi di dati, in modo da gestire coerentemente tutti i valori null. Lo spazio dei nomi System.Data.SqlTypes offre una semantica di tipo null tramite l'implementazione dell'interfaccia INullable. Ciascun tipo di dati nello spazio dei nomi System.Data.SqlTypes dispone di una proprietà IsNull e di un valore Null che può essere assegnato a un'istanza di quel tipo di dati.

NotaNota

In .NET Framework versione 2.0 è stato introdotto il supporto per i tipi nullable, che consentono ai programmatori di estendere un tipo di valore in modo da rappresentare tutti i valori del tipo sottostante.I tipi nullable di CLR rappresentano un'istanza della struttura Nullable.Questa funzionalità risulta particolarmente utile quando i tipi di valore sono boxed e unboxed e garantisce una maggior compatibilità con i tipi di oggetto.I tipi nullable di CLR non devono essere utilizzati per l'archiviazione di valori null di database perché il comportamento di un valore null SQL ANSI è diverso da quello di un riferimento a null (o Nothing in Visual Basic).Per gestire valori null SQL ANSI di database, utilizzare valori null di System.Data.SqlTypes anziché Nullable.Per ulteriori informazioni sull'utilizzo di tipi nullable CLR in Visual Basic, vedere Nullable Value Types, mentre per C# vedere Utilizzo dei tipi nullable (Guida per programmatori C#).

Valori null e logica con tre valori

La concessione di valori null nelle definizioni di colonna introduce una logica con tre valori nell'applicazione. Un confronto può restituire una delle tre condizioni indicate di seguito:

  • True

  • False

  • Sconosciuto

Poiché il valore null è considerato come sconosciuto, due valori null confrontati tra loro non vengono considerati uguali. In espressioni che utilizzano operatori aritmetici, se uno degli operandi è un valore null, anche il risultato sarà null.

Valori null e SqlBoolean

Un confronto tra qualsiasi spazio dei nomi System.Data.SqlTypes restituirà un tipo SqlBoolean. La funzione IsNull per ogni SqlType restituisce un tipo SqlBoolean e può essere utilizzata per controllare eventuali valori null. Nelle seguenti tabelle reali viene mostrato come funzionano gli operatori AND, OR e NOT in presenza di un valore null. (T=true, F=false e U=sconosciuto [unknown] o null.)

Tabella Truth

Nozioni di base sull'opzione ANSI_NULLS

Lo spazio dei nomi System.Data.SqlTypes offre la stessa semantica dell'opzione ANSI_NULLS quando è attivata 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 consente di controllare sia i valori null predefiniti nel database sia la valutazione dei confronti rispetto a valori null. Se l'opzione ANSI_NULLS è attivata (impostazione predefinita), è necessario utilizzare l'operatore IS NULL nelle espressioni quando si verifica la presenza di valori null. Ad esempio, il confronto che segue restituisce sempre Sconosciuto quando l'opzione ANSI_NULLS è attiva:

            colname > NULL

Anche il confronto con una variabile che contiene un valore null restituisce Sconosciuto:

            colname > @MyVariable

Utilizzare il predicato IS NULL o IS NOT NULL per verificare la presenza di un valore null. In tal modo si può rendere più complessa la clausola WHERE. Ad esempio, la colonna TerritoryID nella tabella Customer di AdventureWorks consente valori null. Se con un'istruzione SELECT si intende verificare la presenza di valori in aggiunta ad altri valori, è necessario che l'istruzione includa un predicato IS NULL:

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

Se si disattiva l'opzione ANSI_NULLS in SQL Server, è possibile creare espressioni che utilizzano l'operatore di uguaglianza per eseguire confronti con valori null. Tuttavia, non è possibile impedire che altre connessioni impostino opzioni null per tale connessione. L'utilizzo di IS NULL per verificare la presenza di valori null funziona sempre, indipendentemente dall'impostazione dell'opzione ANSI_NULLS per una connessione.

La disattivazione dell'opzione ANSI_NULLS non è supportata in un tipo DataSet, che segue sempre lo standard ANSI SQL-92 per la gestione di valori null nello spazio dei nomiSystem.Data.SqlTypes.

Assegnazione di valori null

I valori null sono speciali e la loro semantica di archiviazione e assegnazione differisce nei diversi sistemi di tipi e di archiviazioni. Un tipo Dataset è progettato per essere utilizzato con diversi sistemi di tipi e di archiviazioni.

In questa sezione viene descritta la semantica del valore null per assegnare valori null a un tipo DataColumn in un tipo DataRow nei diversi sistemi di tipi.

  • DBNull.Value
    Questa assegnazione è valida per un oggetto DataColumn di qualunque tipo. Se il tipo implementa INullable, DBNull.Value viene convertito nel valore null fortemente tipizzato appropriato.

  • SqlType.Null
    Tutti i tipi di dati System.Data.SqlTypes implementano INullable. Se è possibile convertire il valore null fortemente tipizzato nel tipo di dati della colonna utilizzando operatori di cast impliciti, l'assegnazione verrà eseguita. 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 convertito nel valore DbNull.Value o Null appropriato associato al tipo INullable (SqlType.Null).

  • derivedUdt.Null
    Per le colonne UDT i valori null sono sempre archiviati in base al tipo associato al DataColumn. Si prenda in considerazione il caso di una colonna UDT associata a un DataColumn che non implementa INullable mentre lo fa la relativa sottoclasse. In questo caso, se viene associato alla classe derivata, un valore null fortemente tipizzato viene archiviato come DbNull.Value non tipizzato, perché l'archiviazione di valori null è sempre coerente con il tipo di dati di DataColumn.

NotaNota

Attualmente la struttura Nullable<T> o Nullable non è supportata nel DataSet.

Assegnazione di più colonne (riga)

Per DataTable.Add, DataTable.LoadDataRow o altre API che accettano una proprietà ItemArray di cui viene eseguito il mapping a una riga, viene associato "null" al valore predefinito di DataColumn. Se un oggetto nella matrice contiene DbNull.Value oppure la controparte fortemente tipizzata, si applicano le stesse regole descritte in precedenza.

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

  1. Il valore predefinito di default è 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 in file XML (come in "xsi:nil").

  3. Tutti i valori non null, inclusi i valori predefiniti, vengono sempre scritti durante la serializzazione in file XML. Questo caso differisce dalla semantica XSD/XML, dove un valore null (xsi:nil) è esplicito e il valore predefinito è implicito (se non è presente in XML, un parser di convalida lo può recuperare da uno schema XSD associato). Per un oggetto DataTable vale il concetto opposto: 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 il valore NULL. Alle righe create utilizzando il metodo 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.

Assegnazione di valori null

Il valore predefinito per qualsiasi istanza System.Data.SqlTypes è null.

I valori null nell'istanza System.Data.SqlTypes sono specifici dei tipi e non è possibile rappresentarli con un unico valore, quale DbNull. Utilizzare la proprietà IsNull per controllare la presenza di valori null.

È possibile assegnare valori null a un tipo DataColumn, come mostrato nel seguente esempio di codice. È possibile assegnare direttamente valori null a variabili SqlTypes senza generare un'eccezione.

Esempio

Nell'esempio di codice seguente viene creato un tipo DataTable con due colonne definite SqlInt32 e SqlString. Il codice consente di aggiungere una riga di valori noti e una riga di valori, quindi di scorrere la DataTable, assegnando i valori alle variabili e visualizzando l'output nella finestra della 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();
    }

In questo esempio vengono visualizzati i seguenti risultati:

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

Confronto di valori null con SqlTypes e tipi CLR

Quando si confrontano valori null, è importante comprendere la differenza tra il modo in cui il metodo Equals valuta i valori null nello spazio dei nomi System.Data.SqlTypes rispetto al modo in cui funziona con i tipi CLR. Tutti i metodi Equals di System.Data.SqlTypes utilizzano la semantica del database per valutare valori null: se uno o entrambi i valori sono null, il confronto restituisce null. D'altra parte, se si utilizza il metodo CLR Equals su due spazi dei nomi System.Data.SqlTypes, verrà restituito true se entrambi gli spazi dei nomi sono null. Questo riflette la differenza tra l'utilizzo di un metodo di istanza come il metodo CLR String.Equals e l'utilizzo del metodo statico/condiviso SqlString.Equals.

Nell'esempio seguente viene illustrata la differenza di risultato tra il metodo SqlString.Equals e il metodo String.Equals quando a ciascuno vengono passate una coppia di valori null e successivamente una coppia di stringhe vuote.

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

L'output del codice è il 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

Vedere anche

Altre risorse

Tipi di dati SQL Server e ADO.NET