CODEWITHSUNDEEP
c#asp.netsql-serveroracle-database
Platus
Platus

Reputation: 1497

Converting data types from Oracle to SQL Server in an ASP.NET project

I'm working on the database migration of an ASP.NET project (from Oracle to SQL Server) and part of this work if to modify a connection helper class.

This class contains in particular a piece of code that performs a matching between C# variable types and members of the OracleDbType enum. So I have to transform this matching into a matching between C# variable types and members of the SqlDbType enum.

The first approach I used to do that was to transform a member of the OracleDbType into a member of the SqlDbType using the following steps:

-Find the Oracle SQL type matching the OracleDbType member using this table in Oracle's official documentation

-Find the corresponding Sql Server data type using this table in microsoft's official documentation

-Find the corresponding SqlDbType enum member using this table in microsoft's official documenation

However this approach led me to some problems. For example, in my original code, there is a distinction between a 'short' variable (translated into OracleDbType.Int16) and an 'int' variable (translated into OracleDbType.Int32). Using the method I described above, I have to transform both OracleDbType.Int16 and OracleDbType.Int32 into SqlDbType.Decimal which is weird and doesn't seem correct.

So I have chosen to only use this table and focus on the columns '.NET Framework type' and 'SqlDbType enumeration' to do the transformation, which leads to transform OracleDbType.Int16 into SqlDbType.SmallInt and OracleDbType.Int32 into SqlDbType.BigInt.

I would like to know which one of my methods is correct and why.

Upvotes: 3

Views: 1146

Answers (1)

Jaques
Jaques

Reputation: 2287

This is an old question, but maybe I can help you shed some light on this. One of your assumptions in your question that is wrong is that a OracleDbType.Int32 is a BigInt in SQLServer. is is actually a SqlDbType.Int. OracleDbType.Int64 is a SqlDbType.BigInt. So If I have to create a comparing table of the types of Oracle to Framework to SqlServer I would put it like this.

Oracle          .Net Type       SqlServerType
BFile           byte[]          varbinary(max) With FileStream
Blob            byte[]          varbinary
Byte            byte            tinyint
Char            Char/String     char
Clob            String          varchar(max)
Date            DateTime        Date
Decimal         decimal         decimal/numeric
Double          double          float
Int16           Int16           smallint
Int32           int             int
Int64           long/Int64      bigint
Long            String          (n)varchar(max)
LongRaw         byte[]          varbinary(max)
NChar           String          nchar
NClob           String          nvarchar
NVarchar2       String          nvarchar
Raw             byte[]          varbinary
Single          single          real
TimeStamp       DateTime        datetime2
TimeStampLTZ    DateTime        datetimeoffset (i think)
TimeStampTZ     DateTime        datetimeoffset
Varchar2        String          varchar
XmlType         String          XML

Also look at this link that also provide the CLR types.

In my opinion I would create a dictionary for your transformations which links a OracleDbType to a SqlDbType etc. I have a similar dictionary that I use between SqlDbTypes and .Net types. 

public static Dictionary<Type, SqlDbType> typeMap = 
    new Dictionary<Type, SqlDbType>() 
{ 
    { typeof(byte), SqlDbType.TinyInt}, { typeof(sbyte), SqlDbType.TinyInt },
    { typeof(short), SqlDbType.SmallInt}, { typeof(ushort), SqlDbType.SmallInt },
    { typeof(int), SqlDbType.Int }, {typeof(uint), SqlDbType.Int },
    { typeof(long), SqlDbType.BigInt },   {typeof(ulong), SqlDbType.BigInt },               
    { typeof(float), SqlDbType.Float }, { typeof(double), SqlDbType.Float },
    { typeof(decimal), SqlDbType.Decimal }, {typeof(bool),  SqlDbType.Bit },
    { typeof(string), SqlDbType.VarChar },  {typeof(char), SqlDbType.Char },
    { typeof(Guid),  SqlDbType.UniqueIdentifier }, { typeof(DateTime), SqlDbType.DateTime}, 
    { typeof(DateTimeOffset), SqlDbType.DateTimeOffset }, { typeof(byte[]), SqlDbType.VarBinary },
    //Nullable fields
    { typeof(byte?), SqlDbType.TinyInt }, { typeof(sbyte?), SqlDbType.TinyInt },  
    { typeof(short?), SqlDbType.SmallInt}, { typeof(ushort?), SqlDbType.SmallInt }, 
    { typeof(int?), SqlDbType.Int }, { typeof(uint?), SqlDbType.Int }, 
    { typeof(long?), SqlDbType.BigInt }, { typeof(ulong?), SqlDbType.BigInt },
    { typeof(float?), SqlDbType.Float }, { typeof(double?), SqlDbType.Float },
    { typeof(decimal?), SqlDbType.Decimal}, { typeof(bool?), SqlDbType.Bit },
    { typeof(Guid?), SqlDbType.UniqueIdentifier}, { typeof(DateTime?), SqlDbType.DateTime },
    { typeof(DateTimeOffset?), SqlDbType.DateTimeOffset }
};

Hope this helps.

Upvotes: 1

Related Questions