Search code examples
c#winformsnulldatacolumn

A null value should be considered as 0 when addition of it is done with decimal value

There are three column in the datatable A,B and C. Now each column is type of decimal. Now I am doing like dt.Columns["A"].Expression="B+C"; to make addition of Column B's record and column C's record. Now if there is any value of B or C is null then addition of B and C will be null like B's value is 3 and C's value is null for first row then B+C(3+null) will be null which is not appropriate, the result of addition should be 3.If I replace 0 instead of null then it will be ok.But whereever there is null value in the records it should be remain it is and it should not be replaced by 0.That is null value should not be replaced by 0 and when addition of null value is done with any decimal value null value should be considered as 0.

Is it possible,how can we do this ?

EDIT See the following answer where dt.Rows[0]["B"] = DBNull.Value; but I have problem like according to my code I can not assign DBNull.Value directly dt.Rows[0]["B"] = DBNull.Value; I have to store it in one variable of type object like Object obj; obj=DBNull.Value and then that value goes to the table.Then it gives error like null value can not be stored to the column B.

Solution

  • Try something like this 

    dt.Columns["A"].Expression = "ISNULL(B, 0) + ISNULL(C, 0)";

    Full working test:

    DataTable dt = new DataTable();
dt.Columns.Add("A", typeof(decimal));
dt.Columns.Add("B", typeof(decimal));
dt.Columns.Add("C", typeof(decimal));

dt.Rows.Add();
dt.Rows[0]["B"] = DBNull.Value;
dt.Rows[0]["C"] = 3;

dt.Columns["A"].Expression = "ISNULL(B, 0) + ISNULL(C, 0)";
Console.WriteLine(dt.Rows[0]["A"]);