The ADO.NET specification requires that the SQL NULL value be mapped to the System.DBNull class. For the purpose of this discussion the ADO.NET specification is specified by the System.Data.Common.DbDataReader and System.Data.Common.DbParameter classes.

Note
The .NET Data Provider for Teradata extends the ADO.NET specification. It has Provider Specific Types that implement the INullabe interface. For additional information refer to How Provider Specific Types Handle NULLs.

Retrieving a Null Value

The Typed Accessor methods (e.g. GetInt32) of the TdDataReader class will throw an exception when the specified field is NULL. .NET Applications must invoke TdDataReader.IsDBNull method to detect a NULL value before invoking a Typed Accessor to retrieve a .NET Framework primitive type. Alternatively .NET applications can invoke the TdDataReader.GetValue method which returns a System.Object.

Following example illustrates how to use IsDBNull method:

C#	Copy Code
using (TdDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (reader.IsDBNull(0)) { Console.WriteLine("Order date is NULL."); } else { Console.WriteLine("Order date is {0}", reader.GetDate(0)); } } }

Copy Code

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

This next example shows how to detect DBNull when using GetValue method instead of a Typed-Accessor method:

C#	Copy Code
using (TdDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Object orderDate = reader.GetValue(0); if (orderDate is DBNull) { Console.WriteLine("Order date is NULL."); } else { Console.WriteLine("Order date is {0}", orderDate); } } }

Copy Code

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

The DBNull class is a singleton object. Therefore Object.ReferenceEquals method can also be used to detect a DBNull:

C#	Copy Code
using (TdDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Object orderDate = reader.GetValue(0); if (Object.ReferenceEquals(orderDate, DBNull.Value)) { Console.WriteLine("Order date is NULL."); } else { Console.WriteLine("Order date is {0}", orderDate); } } }

Copy Code

using (TdDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        Object orderDate = reader.GetValue(0);
        if (Object.ReferenceEquals(orderDate, DBNull.Value))
        {
            Console.WriteLine("Order date is NULL.");
        }
        else
        {
            Console.WriteLine("Order date is {0}", orderDate);
        }
    }
}

Sending a Null Value

The TdParameter.Value property must be set to DBNull.Value in order to send NULL value to the SQL Engine.

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

C#	Copy Code
TdCommand cmd = cn.CreateCommand(); cmd.CommandText = "UPDATE OrderItems SET Quantity = ? WHERE OrderID='XYZ'"; TdParameter quantity = new TdParameter("quantity", TdType.Integer); quantity.Value = DBNull.Value; cmd.Parameters.Add(quantity);

Copy Code

TdCommand cmd = cn.CreateCommand();
cmd.CommandText = "UPDATE OrderItems SET Quantity = ? WHERE OrderID='XYZ'";
TdParameter quantity = new TdParameter("quantity", TdType.Integer);
quantity.Value = DBNull.Value;
cmd.Parameters.Add(quantity);