All Provider Specific Types (e.g. TdDecimal) implement the INullable interface. Furthermore all Provider Specific Types exhibit the same behavior when a Null is an operand in a comparison or equality operation.

Retrieving a Null Value

The TdDataReader class has Provider Specific Typed accessor methods, for example the GetTdDate method returns a TdDate instance. .NET applications must use the read-only IsNull property of the Provider Specific Type to check for Null.

The following example shows how to use TdDate.IsNull property to check for Null value.

using (TdDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        TdDate orderDate = reader.GetTdDate(0);
        if (orderDate.IsNull)
        {
            Console.WriteLine("Order date is NULL.");
        }
        else
        {
            Console.WriteLine("Order date is {0}", orderDate);
        }
    }
}

Sending a Null Value

The TdParameter.Value property can be set to a Null Provider Specific Type instance. All Provider Specific Types implement a Null property, for example TdDate.Null, which returns a singleton Null instance.

The following example illustrates how to send a Null value targeting a Date column.

TdCommand cmd = cn.CreateCommand();
cmd.CommandText = "UPDATE Order SET OrderDate = ? WHERE OrderID='XYZ'";
TdParameter orderDate = new TdParameter("orderDate", TdType.Date);
orderDate.Value = TdDate.Null;
cmd.Parameters.Add(orderDate);

Comparison Operators(>, >=, <, <=)

Comparison operators always return false if one of the operands is Null. Therefore, you must not assume that the reverse condition is true. For example, you cannot assume that the left operand is greater than or equal the right operand if the LessThan operator returns false.

When both operands are not Null, the reverse condition is true. The following example uses the TdPeriodDate provider type to demonstrate the results when one of the operands is null.

    TdPeriodDate x = TdPeriodDate.Null;
    TdPeriodDate y = new TdPeriodDate (new TdDate(2007, 10, 10), new TdDate(2007, 11, 01));

    if (x > y)
    {
         Console.WriteLine("X is greater than Y");
    }
    else
    {
         // This is wrong. One cannot assume that X is less than Y
         // because x can be Null or Y can be Null.
         Console.WriteLine("X is less than or equal Y");
    }

    if (X.IsNull == false && Y.IsNull == false)
    {
         if (x > y)
         {
            Console.WriteLine("X is greater than Y");
         }
         else
         {
            // this is right because the outside if statement guarantees that left and right
            // operands are not Null.
            Console.WriteLine("X is less than or equal to Y");
         }
    }

Equality Operators (==, !=)

Two Nulls are considered to be equal. Therefore equality can safely be used in if-then-else statements.  The example uses the TdDecimal provider type to demonstrate this.

TdDecimal x = TdDecimal.Null;
TdDecimal y = new TdDecimal(99.12345M);

if (x == y)
{
    Console.WriteLine("x is equal to y");
}
else
{
    // This is right because two Nulls are equal.
    // and furthermore Null is not equal to any number including zero.
    Console.WriteLine("x is not equal to y");
}

Object Equality (System.Object.Equals)

All the Provider Specific Types override the System.ObjectEquals(Object) method. It returns false when a null reference is passed into this method. It also returns false if the passed in parameter is not the corresponding Provider type. Otherwise it behaves exactly like the Equality operator mentioned above.

IComparable<Provider Specific Type>

Two Nulls are considered equal. Null is less than a non-null value and a non-null value is greater than a Null. IComparable<T> is unlike the Comparison Operators in that it can compare a Null of a specific Provider type to another of the same type that is not null.  Or a Null can be compared to another Null of the same type. IComparable<T> is usually used to sort generic collections of the same Provider Type (e.g. List<TdPeriodTime>).

The following is an example of calling the implemented method of IComparable<Provider Specific Type> when the type is TdTimestamp.

TdTimestamp x = TdTimestamp.Null;
TdTimestamp y = TdTimestamp.Parse("2007-12-25 12:00:00.0005");

if (x.CompareTo(y) == 0)
{
    Console.WriteLine(@"x is equal to y");
}
else if (x.CompareTo(y) > 0)
{
    Console.WriteLine(@"x is greater than y");
}
else if (x.CompareTo(y) < 0)
{
    Console.WriteLine(@"x is less than y");
}

The following example creates a List that contains TdPeriodTimestamp values, and then sorts the list. The implementation of the IComparable<T> interface is used to sort the list.

List<TdPeriodTimestamp> list = new List<TdPeriodTimestamp>();

TdPeriodTimestamp s = TdPeriodTimestamp.Null;

TdTimestamp m = new TdPeriodTimestamp(
    new TdTimestamp(2007, 09, 11, 14, 21, 0),
    new TdTimestamp(2007, 10, 15, 32, 0, 0));

TdTimestamp l = new TdPeriodTimestamp(
    new TdTimestamp(2007, 10, 31, 11, 14, 0),
    new TdTimestamp(2007, 11, 15, 0, 0, 0));

TdTimestamp xl = new TdPeriodTimestamp(
    new TdTimestamp(2007, 11, 22, 15, 32, 0),
    new TdTimestamp(2007, 11, 31, 43, 01, 0));

TdTimestamp xxl = new TdPeriodTimestamp(
    new TdTimestamp(2007, 12, 30, 21, 01, 0),
    new TdTimestamp(2008, 2, 1, 20, 21, 0));

list.Add(xl);
list.Add(s);
list.Add(xxl);
list.Add(m);
list.Add(l);

list.Sort();

IComparable

A null reference is less than the Null value of a Period Type. Otherwise it behaves exactly like IComparable<T>.

See Also

Numeric Types Overview

Date And Time Types Overview

Period Types Overview

Interval Types Overview