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
– Lamak, Commented May 11, 2017 at 16:18

Andrei · Accepted Answer · 2017-05-11 16:44:36Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related