c# Write null field to a datatable

Question

`I'm having a problem writing a null result to a datatable. My linq query is returning values with which I'm populating a new instance of a class. My datatable is being created generically and being populated with a generically created datarow.

What happens is my datatable is being created succesfully, the query runs, but when I hit the VAR statement it fails because one of the decimal fields is null. I cannot change this in the class because then I cannot create the datatable.

I need to change this one line I think to make it accept a null value:

moneyvalue = result.moneyvalue,

This is my table definition:

[Table(Name = "t_sdi_traded_product")]
public class t_sdi_traded_product
{
    [Column]
    public string deal_position_id;
    [Column]
    public decimal moneyvalue;
    [Column]
    public string cost_centre;
}

This is my class

 public class traded_product
{
    public string Deal { get; set; }
    public decimal moneyvalue { get; set; }
    public string InvolvedPartyId { get; set; }
}

This is my query

 var query =
            from result in t_sdi_traded_product_hsbc.AsQueryable()
            where result.sdi_control_id == current_control_id
            select new traded_product() 
            { 
                Deal = result.deal_position_id, 
                moneyvalue = result.moneyvalue,
                InvolvedPartyId = result.involved_party_id
             }

Here is how I create my datatable and datarow

public static DataTable CreateDataTable(Type animaltype)
    {
        DataTable return_Datatable = new DataTable();
        foreach (PropertyInfo info in animaltype.GetProperties())
        {
            return_Datatable.Columns.Add(new DataColumn(info.Name, info.PropertyType));
        }
        return return_Datatable;
    }

    public static DataRow makeRow(object input, DataTable table)
    {
        Type inputtype = input.GetType();
        DataRow row = table.NewRow();
        foreach (PropertyInfo info in inputtype.GetProperties())
        {
            row[info.Name] = info.GetValue(input, null);
        }
        return row;
    }

Now as soon as it hits this part of the code after the "var query" I get the problem:

foreach (var results in query)
        {
            foreach (PropertyInfo result in results.GetType().GetProperties())
            {
                string name = result.Name;

                foreach (PropertyInfo info in used.GetType().GetProperties())
                {
                    if (result.Name == info.Name)
                    {
                        try
                        {
                            info.SetValue(used, result.GetValue(results, null), null);
                        }
                        catch (NoNullAllowedException e)
                        {
                        }
                        finally
                        {
                            info.SetValue(used, DBNull.Value, null);
                        }
                        //Console.WriteLine("Result {0} matches class {1} and the value is {2}", result.Name, info.Name, result.GetValue(results,null));
                    }
                }
            }
            tp_table.Rows.Add(used, tp_table);
        }

It fails as soon as it hits the foreach, because the value returned from the database for moneyvalue is null.

I cannot change the class piece to decimal? otherwise the CreateDatable method fails because it says DataTable cannot have a nullable value.

Squirrel5853 · Accepted Answer · 2013-09-13 12:09:13Z

1

I think your issue is in

select new traded_product() 
{ 
    Deal = result.deal_position_id, 
    moneyvalue = result.moneyvalue, <-- here you need some handling for DBNULL.Value
    InvolvedPartyId = result.involved_party_id
}

select new traded_product() 
{ 
    Deal = result.deal_position_id, 
    moneyvalue = result.moneyvalue == DBNull.Value ? 0m : result.moneyvalue,
    InvolvedPartyId = result.involved_party_id
}

* Update *

Why not construct your datatable using traded_product and as @user65439 mentioned change your DB class (t_sdi_traded_product) to have a nullable column

[Column]
public decimal? moneyvalue;

Then you just have to handle nulls being returned and converting them to 0 for your not-nullable decimal in your traded_product class

edited Sep 13, 2013 at 12:09

answered Sep 13, 2013 at 11:51

Squirrel5853

2,4061 gold badge19 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vwdewaal Over a year ago

I'm trying that as well. I have the [COLUMN] set to decimal? and I have the following going outval(result.funding_rate.ToString()) ? (object)result.funding_rate : (object)DBNull.Value, where outval is a bool method to check if it is a decimal Still getting an error saying cannot convert type object to decimal @SecretSquirrel @user65439

Squirrel5853 Over a year ago

that would be because you are casting to an object.

Squirrel5853 Over a year ago

outval = result.funding_rate == DBNull.Value;

Andre Lombaard · Accepted Answer · 2013-09-13 11:49:26Z

1

If it is allowed to write NULL values to the database you should make your variable types nullable, for example

[Column]
public decimal? moneyvalue;

instead of

[Column]
public decimal moneyvalue;

answered Sep 13, 2013 at 11:49

Andre Lombaard

7,13513 gold badges62 silver badges98 bronze badges

Collectives™ on Stack Overflow

c# Write null field to a datatable

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related