Manipulando valores nulos

  • Artigo

Um valor nulo em um banco de dados relacional é usado quando o valor em uma coluna é desconhecido ou está ausente. Um valor nulo não é uma cadeia de caracteres vazia (para tipos de dados character ou datetime) nem um valor zero (para tipos de dados numéricos). A especificação ANSI SQL-92 declara que um valor nulo deve ser o mesmo para todos os tipos de dados, para que todos os nulos sejam manipulados consistentemente. O namespace System.Data.SqlTypes fornece semântica nula ao implementar a interface INullable. Cada um dos tipos de dados no System.Data.SqlTypes tem uma propriedade própria IsNull e um valor Null que pode ser atribuído a uma instância desse tipo de dados.

Observação

O .NET Framework versão 2.0 incorporou o suporte a tipos que permitem tipos de valores nulos, proporcionando aos programadores meios de estender um tipo de valor para representar todos os valores do tipo subjacente. Esses tipos CLR que permitem tipos de valores nulos representam uma instância da estrutura Nullable. Essa funcionalidade é especialmente útil quando os tipos de valor são boxed e unboxed, fornecendo compatibilidade aprimorada com tipos de objeto. Os tipos de valores CLR que permitem valores nulos não se destinam ao armazenamento dos valores nulos do banco de dados, pois um valor nulo ANSI SQL não se comporta da mesma maneira que uma referência null (ou Nothing no Visual Basic). Para trabalhar com valores nulos ANSI SQL de banco de dados, use nulos System.Data.SqlTypes em vez de Nullable. Para obter mais informações sobre como trabalhar com tipos anuláveis de valor CLR no Visual Basic, consulte Tipos de Valor Anuláveise, para C#, consulte Tipos de valor anuláveis.

Valores nulos e lógica de três valores

Permitir valores nulos em definições de coluna apresenta uma lógica de três valores em seu aplicativo. Uma comparação pode ser avaliada como uma das três condições:

  • True

  • Falso

  • Unknown (desconhecido)

Como um nulo é considerado desconhecido, dois valores nulos comparados entre si não são considerados iguais. Em expressões que usam operadores aritméticos, se um dos operandos for nulo, o resultado será nulo também.

Nulos e SqlBoolean

A comparação entre um System.Data.SqlTypes retornará um SqlBoolean. A função IsNull para cada SqlType retorna um SqlBoolean e pode ser usada para verificar se há valores nulos. As tabelas da verdade a seguir mostram como os operadores AND, OR e NOT funcionam na presença de um valor nulo. (T = verdadeiro, F = falso e U = desconhecido ou nulo.)

Truth Table

Noções básicas sobre a opção ANSI_NULLS

O System.Data.SqlTypes fornece a mesma semântica que quando a opção ANSI_NULLS é definida no SQL Server. Todos os operadores aritméticos (+, -, *, /, %), operadores bit a bit (~, &, |) e a maioria das funções retornarão nulo se algum operando ou argumento for nulo, exceto na propriedade IsNull.

O padrão ANSI SQL-92 não oferece suporte a columnName = NULL em uma cláusula WHERE. No SQL Server, a opção ANSI_NULLS controla a nulabilidade padrão no banco de dados e a avaliação de comparações com valores nulos. Se ANSI_NULLS estiver ativado (o padrão), o operador IS NULL deverá ser usado em expressões ao testar valores nulos. Por exemplo, a comparação seguinte sempre retorna unknown quando ANSI_NULLS for on:

colname > NULL

A comparação com uma variável que contém um valor nulo também produz valores desconhecidos:

colname > @MyVariable

Use o predicado IS NULL ou IS NOT NULL para testar um valor nulo. Isso pode adicionar complexidade à cláusula WHERE. Por exemplo, a coluna TerritoryID na tabela Cliente AdventureWorks permite valores nulos. Se uma instrução SELECT for testada para obter valores nulos além de outros, ela deverá incluir um predicado IS NULL:

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

Se você desativar ANSI_NULLS no SQL Server, poderá criar expressões que usam o operador de igualdade para comparar com o valor nulo. No entanto, você não pode impedir que diferentes conexões configurem opções nulas para essa conexão. Usar IS NULL para testar valores nulos sempre funciona, independentemente das configurações de ANSI_NULLS para uma conexão.

A desativação de ANSI_NULLS não é compatível em um DataSet, que sempre segue o padrão ANSI SQL-92 para manipular valores nulos no System.Data.SqlTypes.

Atribuindo valores nulos

Os valores nulos são especiais e suas semânticas de armazenamento e atribuição diferem em sistemas de tipos e sistemas de armazenamento diferentes. Um Dataset foi projetado para ser usado com diferentes sistemas de tipo e armazenamento.

Esta seção descreve a semântica nula para atribuir valores nulos a um DataColumn em um DataRow entre os diferentes sistemas de tipos.

DBNull.Value
Essa atribuição é válida para um DataColumn de qualquer tipo. Se o tipo implementa INullable, o DBNull.Value é forçado no valor nulo fortemente tipado apropriado.

SqlType.Null
Todos os tipos de dados System.Data.SqlTypes implementam INullable. Se o valor nulo fortemente tipado puder ser convertido no tipo de dados da coluna usando operadores de conversão implícitos, a atribuição deverá passar. Caso contrário, uma exceção de conversão inválida será lançada.

null
Se nulo for um valor válido para o tipo de dados DataColumn especificado, ele será forçado no DbNull.Value ou no Null apropriado associado ao tipo INullable (SqlType.Null)

derivedUdt.Null
Para colunas UDT, os nulos são sempre armazenados com base no tipo associado à DataColumn. Considere o caso de um UDT associado a um DataColumn que não implementa INullable, enquanto sua subclasse faz a implementação. Nesse caso, se um valor nulo fortemente tipado associado à classe derivada for atribuído, ele será armazenado como um DbNull.Value não tipado, porque o armazenamento nulo é sempre consistente com o tipo de dados de DataColumn.

Observação

No momento, a estrutura Nullable<T> ou Nullable não é compatível no DataSet.

O valor padrão para uma instância System.Data.SqlTypes é nulo.

Os valores nulos em System.Data.SqlTypes são específicos do tipo e não podem ser representados por um valor, como DbNull. Use a propriedade IsNull para verificar valores nulos.

Os valores nulos podem ser atribuídos a um DataColumn, conforme mostrado no exemplo de código a seguir. Você pode atribuir diretamente valores nulos a variáveis SqlTypes sem disparar uma exceção.

Exemplo

O exemplo de código a seguir cria um DataTable com duas colunas definidas como SqlInt32 e SqlString. O código adiciona uma linha de valores conhecidos, uma linha de valores nulos e, em seguida, itera por DataTable, atribuindo os valores a variáveis e exibindo a saída na janela do 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

Esse exemplo mostra os seguintes resultados:

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

Atribuição de várias colunas (linha)

DataTable.Add, DataTable.LoadDataRow ou outras APIs que aceitam um ItemArray que é mapeado para uma linha, mapeiam "nulo" para o valor padrão da DataColumn. Se um objeto na matriz contiver DbNull.Value ou sua contraparte fortemente tipada, as mesmas regras descritas acima serão aplicadas.

Além disso, as seguintes regras se aplicam a uma instância de atribuições nulas de DataRow.["columnName"]:

  1. O valor padrão é DbNull.Value para todos, exceto as colunas nulas fortemente tipada em que é o valor nulo fortemente tipado apropriado.

  2. Os valores nulos nunca são gravados durante a serialização para arquivos XML (como em "xsi:nil").

  3. Todos os valores não nulos, incluindo os padrões, sempre são gravados durante a serialização para XML. Isso é diferente da semântica XSD/XML em que um valor nulo (xsi:nil) é explícito e o valor padrão é implícito (se não estiver presente em XML, um analisador de validação poderá obtê-lo de um esquema XSD associado). O oposto é verdadeiro para um DataTable: um valor nulo é implícito e o valor padrão é explícito.

  4. Todos os valores de coluna ausentes para linhas lidas da entrada XML são atribuídos como NULL. As linhas criadas usando NewRow ou métodos semelhantes recebem o valor padrão de DataColumn.

  5. O método IsNull retorna true para DbNull.Value e INullable.Null.

Comparando valores nulos com SqlTypes e os tipos CLR

Ao comparar valores nulos, é importante entender a diferença entre a maneira como o método Equals avalia valores nulos em System.Data.SqlTypes em comparação com o modo como ele funciona com tipos CLR. Todos os métodos System.Data.SqlTypesEquals usam semânticas de banco de dados para avaliar valores nulos: se um ou ambos os valores forem nulos, a comparação produzirá um valor nulo. Por outro lado, usar o método CLR Equals em dois System.Data.SqlTypes produzirá true se ambos forem valores nulos. Isso reflete a diferença entre usar um método de instância, como o método CLR String.Equals, e usar o método estático/compartilhado, SqlString.Equals.

O exemplo a seguir demonstra a diferença nos resultados entre o método SqlString.Equals e o método String.Equals quando cada um é passado por um par de valores nulos e, em seguida, um par de cadeias de caracteres vazias.

    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

O código produz a seguinte saída:

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

Confira também