views:

117

answers:

1

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.

+1  A: 

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"]);
Anthony Pegram
@Anthony Pegram,Sir,What does mean for `?` and what does `a ?? 0 + b ?? 0` do ?
Harikrishna
@Anthony,Sir it gives error `DataSet does not support System.Nullable<>.`
Harikrishna
I've updated the answer. The original post was not suited for what you were trying to do, I apologize.
Anthony Pegram
@Anthony,Sir,does `ISNULL` replace 0 instead of `null` value or what it does ?
Harikrishna
`ISNULL(x, y)` is a native SQL Server function supported, in this case, as a datatable expression that will return x unless it is null, in which case it will return y. The result in my example is `0 + 3`.
Anthony Pegram
@Mr Anthony,Can we assign DBNull.Value to string variable ?
Harikrishna
You can certainly use DBNull.Value in a string field in a table, provided the field allows nulls.
Anthony Pegram
@Anthony,Thank You Sir.
Harikrishna