Datatable handle DB NULL

Question

I made connection to a SQL Server database with Azure functions and I am trying to make a datatable

string GetAllItems = String.Format("SELECT * FROM dbo.vw_SO_Leg WHERE Customer=" + Customer);

using (var adp = new SqlDataAdapter(GetAllItems, str))
using (DataTable datatable = new DataTable())
{
    adp.Fill(datatable);
    var temp = datatable;

    DataSet ds = new DataSet();
    DataTable dt = new DataTable();

    dt.Columns.Add("Customer");
    dt.Columns.Add("Amount");

    foreach (DataRow item in datatable.Rows)
    {
        double FixedPrice = Convert.ToDouble(item["FixedPrice"]);
        double ExtraCostsAmount = Convert.ToDouble(item["ExtraCostsAmount"]);
        double Amount = FixedPrice + ExtraCostsAmount

        dt.Rows.Add(Customer, Amount);
    }

    ds.Tables.Add(dt);
}

The problem is that the FixedPrice and ExtraCostsAmount columns in SQL Server can contain Null values, and I get an error when I try to run this. I tried a try/catch and if statement. But then the variable aren't available outside of the statements. Anyone that can help me with this? Thanks in advance

Answer 1

The problem with your code is that doubles are primitive data type and the can't be null. One solution would be to declare nullable doubles by using nullable operator '?' at the type definition. This way, your code will not crash, as it will tolerate null values for that variable.

Basically what you could do is

 foreach (DataRow item in datatable.Rows)
                {
                    double? FixedPrice = Convert.ToDouble(item["FixedPrice"]);
                    double? ExtraCostsAmount = Convert.ToDouble(item["ExtraCostsAmount"]);
                    double? Amount = FixedPrice + ExtraCostsAmount

                    dt.Rows.Add(Customer, Amount);
                }
                ds.Tables.Add(dt);

However, for your business needs, you might want to handle those null values somehow, and simply avoid nulls by checking them first.

if(FixedPrice is null || ExtraCostsAmount is null  || Amount is null) { //skip }
else { //do stuff } 

Answer 2

To handle DB null you can try this:

foreach (DataRow item in dt.Rows)
{
    double FixedPrice = Convert.ToDouble(item["FixedPrice"] == DBNull.Value ? 0 : item["FixedPrice"]);
    double ExtraCostsAmount = Convert.ToDouble(item["ExtraCostsAmount"] == DBNull.Value ? 0 : item["ExtraCostsAmount"]);
    double Amount = FixedPrice + ExtraCostsAmount;
    dt.Rows.Add(Customer, Amount);
}

Answer 3

You may try to use Double.TryParse method for this.

Here is documentation https://learn.microsoft.com/en-us/dotnet/api/system.double.tryparse?view=net-5.0