how to handle null values for SQLDecimal datatype in FileHelper classes

Question

I am trying to load a file using FileHelpers (like it already except for this one issue :P ) I have to save CSV file data into the database, and so am using a SqlDecimal datatype to store the decimal values of the CSV files.

[FileHelpers.FieldOptional()]
[FileHelpers.FieldConverter(typeof(SqlDecimalConverter))]
public SqlDecimal Rate;

All this works fine, until I have a blank value for the FixRate1. This is flagged up with an error.

"Warn Exception:Null Value found for the field 'Rate' in the class 'SWTrade'. You must specify a FieldNullValueAttribute because this is a ValueType and can´t be null."

I tried putting [FileHelpers.FieldNullValue(SqlDecimal.Null)] but it obviously throws an error.

An attribute argument must be a constant expression, typeof expression or array creation expression of an attribute parameter type

Even though I override the FieldToString method in the SqlDecimalConverter class, the function is not called, when reading the data.

Well, this being the case, is there a way I can assign any null value or even some other hardcoded value to the Rate data, which I can then replace with a null direclty in my own logic ?

Please do let me know if you would need more details. Thanks in advance.

Answer 1

The other comments are right u need to use

private decimal? internalRate;

And later create a property for the conversion, in fact the library only autoconverts native types and SqlDecimal is not one.

public SqlDecimal? Rate
{
    get 
    {
        if (internalRate.HasValue)
            return new SqlDecimal(internalRate.Value);
        else
            return null;
    }
}

PS: If u want to go to the custom converter way, to use it for read u need to override the method StringToField (FieldToString is called for writing)

Hope this helps :)

Answer 2

You could try using a Nullable type. If you are using a Nullable type then you don't need to specify the FieldNullValue attribute.

[FileHelpers.FieldConverter(typeof(SqlDecimalConverter))]
public SqlDecimal? Rate;

That approach works fine with DateTime using the FileHelpers built in type converter.

If that does not work then you could assign a default value that you could treat as a null. But that can be problematic if there is an actual value that corresponds to the chosen default value:

[FieldNullValue(typeof(SqlDecimal), "-1")]
[FileHelpers.FieldConverter(typeof(SqlDecimalConverter))]
public SqlDecimal Rate;

Update

I just did a test and simply using a decimal? type works fine. Then you can convert it in your code to the proper SqlType to access the database. I think that is the easiest approach.

So the field would just look like:

public decimal? Rate;

no attributes required.