how to handle null values for SQLDecimal datatype in FileHelper classes

Question

Hi,

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

I've never worked with FileHelpers, but you could try instead:

[FileHelpers.FieldNullValue(null)]
public SqlDecimal? Rate;

In addition to your other attributes, of course.

Note the big change here - I added a question mark to SqlDecimal to make it a nullable type.

Then you can use Rate.HasValue to se whether it's null or not and Rate.Value to access the SqlDecimal if HasValue is true.

Something like this will properly use SqlDecimal.Null after you parse the file:

SqlDecimal trueRate = (Rate.HasValue ? Rate.Value : SqlDecimal.Null);

Edit - It's possible that if you make Rate nullable (SqlDecimal?) you don't even need the "FieldNullValue" attribute. Again, I'm not familiar with the library. :(

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.

Answer 3

Why can't you use Decimal or an appropriate .net datatype instead of SqlDecimal?
I am suggesting that you should change the code to public Decimal? Rate; instead.

Answer 4

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 :)