Introduction

One of the most frequent exceptions developers encounter when working with ADO.NET and databases like Oracle or SQL Server is the System.InvalidCastException. This usually happens when a database field contains a NULL value, and the code tries to convert it directly to a primitive type like int or decimal.

In ADO.NET, a database null is not represented by the C# null keyword but by the DBNull.Value object. Because DBNull.Value cannot be directly cast to an integer, your application crashes.

In this article, we will implement a robust pattern to handle these nulls by defaulting them to 0, and we will look at how to structure your data access code using a safe Try-Catch architecture.

Background

Imagine you are fetching commission rates from an Oracle database. Your table, UTILITIES_COMPANIES_COMM_RATES, has columns for CLIENTID and COMMISSIONRATE. If a specific record has no client assigned (a NULL value), the following code will fail:

C#

// This line will throw an InvalidCastException if CLIENTID is NULL in the DB
ClientId = Convert.ToInt32(reader["CLIENTID"]);

To prevent this, we must verify the data before conversion.

Step-by-Step Implementation

1. Handling DBNull with the Ternary Operator

The simplest way to fix this is to use the ternary operator (? :) to check for DBNull.Value. If the value is null, we assign a default (like 0); otherwise, we perform the conversion.

C#

int clientId = reader["CLIENTID"] == DBNull.Value ? 0 : Convert.ToInt32(reader["CLIENTID"]);

2. Implementing a Robust Repository Method

When writing for a production environment, it is best practice to wrap your database logic in a using statement (to handle connection closing) and a try-catch block (to handle errors).

Here is the full implementation for a GetAllCommissionRates method:

C#

public List<UtilityCommissionRate> GetAllCommissionRates()
{
    var list = new List<UtilityCommissionRate>();
    string query = "SELECT ID, CLIENTID, COMMISSIONRATE, COMPANYID FROM UTILITIES_COMPANIES_COMM_RATES";

    // 'using' ensures the connection is closed even if an error occurs
    using (OracleConnection conn = new OracleConnection(_connectionString))
    {
        OracleCommand cmd = new OracleCommand(query, conn);

        try
        {
            conn.Open();
            using (OracleDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add(new UtilityCommissionRate
                    {
                        // Check each column for DBNull before converting
                        Id = reader["ID"] == DBNull.Value ? 0 : Convert.ToInt32(reader["ID"]),
                        
                        ClientId = reader["CLIENTID"] == DBNull.Value ? 0 : Convert.ToInt32(reader["CLIENTID"]),
                        
                        CommissionRate = reader["COMMISSIONRATE"] == DBNull.Value ? 0m : Convert.ToDecimal(reader["COMMISSIONRATE"]),
                        
                        CompanyId = reader["COMPANYID"] == DBNull.Value ? 0 : Convert.ToInt32(reader["COMPANYID"])
                    });
                }
            }
        }
        catch (OracleException ex)
        {
            // Log database-specific errors (e.g., connection issues)
            Console.WriteLine($"Database Error: {ex.Message}");
            throw; 
        }
        catch (Exception ex)
        {
            // Log general conversion or logic errors
            Console.WriteLine($"General Error: {ex.Message}");
            throw;
        }
    }
    return list;
}

Creating a Cleaner Solution: Extension Methods

If you have a large project with many tables, checking for DBNull.Value in every line makes the code hard to read. We can solve this by creating a Generic Extension Method.

The Utility Class

C#

public static class DataReaderExtensions
{
    public static T GetValueOrDefault<T>(this IDataRecord reader, string columnName)
    {
        object value = reader[columnName];
        return value == DBNull.Value ? default(T) : (T)Convert.ChangeType(value, typeof(T));
    }
}

Clean Usage

Now, your mapping code becomes much shorter and cleaner:

C#

while (reader.Read())
{
    list.Add(new UtilityCommissionRate
    {
        Id = reader.GetValueOrDefault<int>("ID"),
        ClientId = reader.GetValueOrDefault<int>("CLIENTID"),
        CommissionRate = reader.GetValueOrDefault<decimal>("COMMISSIONRATE")
    });
}

Summary

Conclusion

Handling database nulls is a fundamental part of building stable .NET applications. By following the patterns above, you can ensure your application handles missing data gracefully without crashing.