Tratamiento de valores NULL (ADO.NET)
Actualización: November 2007
Los valores NULL se utilizan en bases de datos relacionales cuando el valor de una columna se desconoce o falta. Un NULL no es ni una cadena vacía (en los tipos de datos de caracteres o de fecha y hora) ni un valor cero (en los tipos de datos numéricos). La especificación ANSI SQL-92 afirma que un NULL debe ser igual en todos los tipos de datos; por lo tanto, todos los NULL se tratan de forma coherente. El espacio de nombres System.Data.SqlTypes proporciona semántica para valores NULL mediante la implementación de la interfaz INullable. Cada uno de los tipos de datos de System.Data.SqlTypes tiene su propia propiedad IsNull y un valor Null que se puede asignar a una instancia de ese tipo de datos.
.gif "Nota") Nota: |
|---|
En la versión 2.0 de .NET Framework se introduce la compatibilidad con tipos que admiten valores NULL, lo que permite a los programadores ampliar un tipo de valor para representar todos los valores del tipo subyacente. Estos tipos CLR que admiten valores NULL representan una instancia de la estructura Nullable. Esta capacidad es especialmente útil cuando a los valores tipo se les ha aplicado la conversión boxing o la conversión unboxing, lo que proporciona una compatibilidad mejorada con tipos de objeto. Los tipos CLR que admiten valores NULL no están pensados para el almacenamiento de valores NULL de base de datos porque un valor NULL ANSI SQL no se comporta del mismo modo que una referencia null (o Nothing, en Visual Basic). Para trabajar con valores NULL ANSI SQL de base de datos, utilice valores NULL System.Data.SqlTypes en lugar de Nullable. Para obtener más información sobre el trabajo con tipos CLR que admiten valores NULL en Visual Basic, vea Tipos de valor que aceptan valores NULL y para C#, vea Utilizar tipos que aceptan valores NULL (Guía de programación de C#). |
Valores NULL y la lógica de tres valores
Permitir valores NULL en definiciones de columna introduce la lógica de tres valores en la aplicación. Una comparación puede evaluarse en función de una de tres condiciones:
True
False
Unknown
Como un valor NULL se considera que es desconocido, dos valores NULL comparados entre sí no se consideran iguales. En expresiones que utilizan operadores aritméticos, si alguno de los operandos es nulo, el resultado también es nulo.
Valores NULL y SqlBoolean
La comparación entre cualquier System.Data.SqlTypes devolverá un SqlBoolean. La función IsNull de cada SqlType devuelve un SqlBoolean y se puede utilizar para comprobar si hay valores NULL. En las siguientes tablas de tipo truth se muestra cómo funcionan los operadores AND, OR y NOT en presencia de un valor NULL. (T=true, F=false y U=unknown, o NULL.)
.gif)
Descripción de la opción ANSI_NULLS
System.Data.SqlTypes proporciona la misma semántica que cuando se establece la opción ANSI_NULLS en ON en SQL Server. Todos los operadores aritméticos (+, -, *, /, %), operadores a nivel de bit (~, &, |) y la mayoría de las funciones devuelven NULL si alguno de los operandos o argumentos es nulo, excepto en el caso de la propiedad IsNull.
El estándar ANSI SQL-92 no admite columnName = NULL en una cláusula WHERE. En SQL Server, la opción ANSI_NULLS controla la posibilidad de aceptar NULL predeterminada en la base de datos y la evaluación de comparaciones con respecto a valores NULL. Si ANSI_NULLS está activado (el valor predeterminado), al comprobar la existencia de valores NULL se debe utilizar el operador IS NULL en las expresiones. Por ejemplo, la siguiente comparación siempre produce UNKNOWN cuando ANSI_NULLS está activado:
colname > NULL
La comparación con una variable que contiene un valor NULL también produce UNKNOWN:
colname > @MyVariable
Utilice el predicado IS NULL o IS NOT NULL para comprobar si un valor es NULL. Esto puede agregar complejidad a la cláusula WHERE. Por ejemplo, la columna TerritoryID de la tabla Customer de AdventureWorks permite valores NULL. Si se utiliza una instrucción SELECT para comprobar si hay valores NULL además de otros, debe incluir un predicado IS NULL:
SELECT CustomerID, AccountNumber, TerritoryID
FROM AdventureWorks.Sales.Customer
WHERE TerritoryID IN (1, 2, 3)
OR TerritoryID IS NULL
Si establece ANSI_NULLS en OFF en SQL Server, puede crear expresiones que utilicen el operador de igualdad para comparar con NULL. Sin embargo, no puede evitar que diferentes conexiones establezcan opciones nulas para esa conexión. El uso de IS NULL para probar si los valores son NULL siempre funciona, con independencia de la configuración de ANSI_NULLS en una conexión.
No se admite el establecimiento de ANSI_NULLS en OFF en un DataSet, ya que siempre sigue el estándar ANSI SQL-92 para el tratamiento de valores NULL en System.Data.SqlTypes.
Asignación de valores NULL
Los valores NULL son especiales y su semántica de almacenamiento y asignación varía según los diversos sistemas de tipos y sistemas de almacenamiento. Un Dataset se ha diseñado para su uso con diferentes sistemas de tipos y de almacenamiento.
En esta sección se describe la semántica de valores NULL para la asignación de dichos valores a una DataColumn de una DataRow entre sistemas de tipos diferentes.
DBNull.Value
Esta asignación es válida para una DataColumn de cualquier tipo. Si el tipo implementa INullable, DBNull.Value se convierte en el valor NULL adecuado con establecimiento inflexible de tipos.SqlType.Null
Todos los tipos de datos System.Data.SqlTypes implementan INullable. Si el valor NULL con establecimiento inflexible de tipos se puede convertir en el tipo de datos de la columna mediante operadores de conversión implícitos, la asignación debería realizarse. De lo contrario, se produce una excepción de conversión no válida.null
Si 'null' es un valor válido para el tipo de datos DataColumn dado, se convierte en el DbNull.Value o Null asociado con el tipo INullable (SqlType.Null)derivedUdt.Null
En columnas de tipos definidos por el usuario, los valores NULL se almacenan siempre en el tipo asociado con la DataColumn. Considere el caso de un tipo definido por el usuario asociado con una DataColumn que no implementa INullable, mientras su subclase sí lo hace. En este caso, si se asigna un valor NULL con establecimiento inflexible de tipos asociado a la clase derivada, se almacenará como DbNull.Value sin tipo, porque el almacenamiento de valores NULL es siempre coherente con el tipo de datos de DataColumn.
| Nota: |
|---|
La estructura Nullable<T> o Nullable no se admite actualmente en DataSet. |
Asignación de varias columnas (filas)
DataTable.Add, DataTable.LoadDataRow u otras API que aceptan ItemArray que se asigna a una fila, asignan 'null' al valor predeterminado de DataColumn. Si un objeto de la matriz contiene DbNull.Value o su equivalente con establecimiento inflexible de tipos, se aplican las mismas reglas que se han descrito anteriormente.
Además, las siguientes reglas se aplican para una instancia de asignaciones de NULL de DataRow.["columnName"]:
El valor predeterminado default es DbNull.Value para todas las columnas excepto aquellas NULL con establecimiento inflexible de tipos en las que se encuentra el valor NULL adecuado con establecimiento inflexible de tipos.
Los valores NULL nunca se escriben durante la serialización a archivos XML (como en "xsi:nil").
Todos los valores que no son NULL, incluidos los predeterminados, siempre se escriben durante la serialización a XML. Esto no es característico de la semántica de XSD/XML en la que un valor NULL (xsi:nil) es explícito y el valor predeterminado es implícito (si no está presente en XML, un analizador de validación puede obtenerlo de un esquema XSD asociado). Lo contrario se cumple para DataTable: un valor NULL es implícito y el valor predeterminado es explícito.
A todos los valores de columna que faltan en las filas leídas de la información XML introducida se les asigna NULL. A las filas creadas mediante NewRow o métodos similares se les asigna el valor predeterminado de DataColumn.
El método IsNull devuelve true para DbNull.Value y INullable.Null.
Asignación de valores NULL
El valor predeterminado de cualquier instancia de System.Data.SqlTypes es NULL.
Los valores NULL de System.Data.SqlTypes son específicos del tipo y no se pueden representar con un único valor, como DbNull. Utilice la propiedad IsNull para comprobar si hay valores NULL.
Es posible asignar valores NULL a una DataColumn, como se muestra en el siguiente código de ejemplo. Puede asignar directamente valores NULL a variables SqlTypes sin desencadenar una excepción.
Ejemplo
El siguiente código de ejemplo crea una DataTable con dos columnas definidas como SqlInt32 y SqlString. El código agrega una fila de valores conocidos, una fila de valores NULL y, luego, establece una iteración en la DataTable, de modo que los valores se asignan a variables y el resultado se muestra en la ventana de la consola.
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();
}
Este ejemplo muestra el siguiente resultado:
isColumnNull=False, ID=123, Description=Side Mirror
isColumnNull=True, ID=Null, Description=Null
Comparación de valores NULL con SqlTypes y tipos CLR
Al comparar valores NULL, es importante comprender la diferencia entre la forma en que el método Equals evalúa los valores NULL en System.Data.SqlTypes por contraposición a cómo funciona con tipos CLR. Todos los métodos de System.Data.SqlTypesEquals usan semántica de base de datos para evaluar valores NULL: si alguno de los valores es NULL, o lo son ambos, la comparación produce NULL. Por otra parte, el uso del método Equals de CLR en dos System.Data.SqlTypes producirá TRUE si ambos son NULL. Esto refleja la diferencia entre el uso de un método de instancia como el método String.Equals de CLR y el uso del método estático o compartido SqlString.Equals.
En el siguiente ejemplo se muestra la diferencia entre los resultados del método SqlString.Equals y del método String.Equals cuando se pasa a cada uno un par de valores NULL y, a continuación, un par de cadenas vacías.
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;
}
}
El código produce el siguiente resultado:
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