How to handle NULLIF and ISNULL of SQL statements in C# ASP.net?

Question

I have some SQL statements (written by someone else) which I am trying to understand. In this SQL, it calculates 'Weight' as shown below.

ISNULL(NULLIF(CASE WHEN ISNULL(m.Override,0) = 1
    THEN m.OverWeight
    ELSE ISNULL(itemWeight.Weight,groupWeight.Weight) END,0),5) AS Weight

Now I have to use this logic in ASP.net. Since I am new to C#, ASP.net and SQL, I am struggling on it. I have done following so far.

//Calculate weight
    bool override = DataConversion.GetBoolean(row["Override"]);
    decimal overWeight = DataConversion.GetDecimal(row["OverWeight"]);
    decimal itemWeight = DataConversion.GetDecimal(row["ItemWeight"]);
    decimal groupWeight = DataConversion.GetDecimal(row["GroupWeight"]);

    decimal weight= override? DataConversion.GetDecimal(row["OverWeight"]): 5;

    var par = new par(
                Id: DataConversion.GetInt(row["MaterialId"]),
                weight: weight
                );

The issue I am facing is: how do I use ELSE ISNULL(itemWeight.Weight,groupWeight.Weight in my condition statement? i.e.decimal weight= override?DataConversion.GetDecimal(row["OverWeight"]): 5;.

I don't know enough ASP.net to help you with that, but that `CASE` expression is overly complicated. It can be rewritten as: `CASE WHEN m.Override = 1 THEN m.OverWeight WHEN ISNULL(itemWeight.Weight,groupWeight.Weight) = 0 THEN 5 ELSE ISNULL(itemWeight.Weight,groupWeight.Weight) END AS Weight` — Lamak, May 11 '17 at 16:18

Andrei · Answer 1 · 2017-05-11T16:44:36.420

I found it easier to tackle with better formatting - makes it clear what goes where:

ISNULL(
    NULLIF(
        CASE
            WHEN ISNULL(m.Override,0) = 1
                THEN m.OverWeight
            ELSE 
                ISNULL(itemWeight.Weight,groupWeight.Weight)
        END,
        0
    ),
    5
) AS Weight

Now just using definitions of ISNULL and NULLIF I got this code:

decimal weight;                        //  AS Weight

if (override != null && override == 1) // WHEN ISNULL(m.Override,0) = 1
{
    weight = overWeight;               // THEN m.OverWeight
}
else
{
    if (itemWeight != null)            // ELSE ISNULL(...)
        weight = itemWeight;           // itemWeight.Weight
    else
        weight = groupWeight;          // groupWeight.Weight
}

if (weight == 0)                       // NULLIF(..., 0)
{
    weight = 5;                        // ISNULL(..., 5)
}

You way need to test against actual DBNull though, depends on where your data comes from.

How to handle NULLIF and ISNULL of SQL statements in C# ASP.net?

1 Answers1