Hi,

I found summary of .NET types based on the NUMBER setting in Oracle but that was from 10 years ago. Is the code below still valid for Oracle driver to produce .NET types based on the NUMBER setting?

/// <summary> /// Converts the specified DbType to its equivalent .NET type. This routine is used for NUMBER types, to convert them /// to .NET types based on the precision and scale. /// </summary> /// <param name="dbType">dbtype to convert</param> /// <param name="precision">precision of type</param> /// <param name="scale">scale of type</param> /// <returns>.NET type which is the equivalent of the passed in dbtype with the passed in precision and scale.</returns> public Type DBType2NETType(int dbType, int precision, int scale) { if(dbType!=(int)OracleDbTypes.Number) { // not a number throw new ArgumentException("DbType is not of type NUMBER.", "dbType"); }

if(scale != 0 && scale != 255 ) 
{
    // floating point. Filter out singles and doubles
    if(precision > 15) 
    {
        return typeof(System.Decimal);
    }
    if(precision > 7)
    {
        return typeof(System.Double);
    }
    else 
    {
        return typeof(System.Single);
    }
}
else 
{
    // number without a fraction.
    if(precision < 5) 
    {
        return typeof(System.Int16);
    } 
    if(precision < 10)
    {
        return typeof(System.Int32);
    } 
    if(precision < 19) 
    {
        return typeof(System.Int64);
    }
    else
    {
        return typeof(System.Decimal);
    }
}

}

Thanks, sabina

Hi Sabina,

Which LLBLGen version are you using? Oracle version? Driver version? Are you modifying the driver code?

LLBLGen Version : 4.2

Oracle version : 11.1.0.7.0

Oracle Driver : 11.2.0.3.0

No, I am not modifying the driver code.

Basically, we have some columns in Oracle database with NUMBER(10,2) and SQL Server equivalent DECIMAL(10,2) . In LLBLGen field mappings, .NET Type is set to decimal but I am afraid that the value stored in Oracle database is float and not decimal.

In LLBLGen field mappings, .NET Type is set to decimal but I am afraid that the value stored in Oracle database is float and not decimal.

Could you please give an example of a float number stored in the Number(10,2)? AFAIK, decimal would work for Number(10,2)

Hi Walaa,

Thanks for the response to Sabina's question.

As some additional information for her question, we have been using the following post as our guideline of how to specify the size of Oracle fields, so they properly come up as the specific .NET data types we need (e.g. decimal, int32, int 16, float).

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=897&HighLight=1

A couple reasons we need to make sure we get this right: *Due much of our data being monetary, we really need to use decimal data types, instead of floating datatype. *We create parallel LLBLGen layers for Oracle and SQL Server (customer option which they want to use on their server), so we need to make sure we have the matching datatypes in both the Oracle and SQL Server database.

From what I understand from this posting, the Oracle source code fragment Frans posted is how the Oracle ODP.NET driver determines which datatype to return the data as.

Sure, we can run some tests if needed. But our question is really if you guys know if this posting information is still accurate, or has changed since 2004? If it's changed, might you have an idea of which version it changed in...or if you think (like was available 10 years ago) Oracle might still give access to seeing how their ODP.NET driver is deciding in which datatype their driver returns the data.

Thanks!

To my knowledge they're is still valid: ODP.NET still converts number(x, y) this way on their ADO.NET classes.

If you see differences in types based on precision/scale, you can always use a type converter to make sure the .net type in the entity is the one you expect, and assign the type converter to the Oracle mapping and leave the sql server mapping as-is

We rely on the conversion for the .net type mappings, so if they've changed, we'd change them too But I don't expect them to change this as it would break a lot of applications. They did change it for the Entity Framework code they added to ODP.NET (so in entity framework, the values are a bit different, not necessarily equal to sql server btw, it's a mess)

Yes, thanks for the info! ...and for remiding that we can use the type converter, but as I'd think you might agree, it seems a bit cleaner implementation if we can avoid them when not needed.

Glad you guys are dealing with the data type mess for us!