OLE DB Data Types to C# Data Type Mapping
-
I'm struggling with understanding, what I think are difference between the Types used in .NET framework and the OLE DB Data Types used in Access, Excel, etc. For example:
Type indicator OLE DB Name .Net Type Description
DBTYPE_UI8 BigInt Int64 64-bit signed integerWhat's does Int64 map to in C#? Am I using the correct terminology? It's difficult to look up an answer to a question when you don't know what something is called.
-
I'm struggling with understanding, what I think are difference between the Types used in .NET framework and the OLE DB Data Types used in Access, Excel, etc. For example:
Type indicator OLE DB Name .Net Type Description
DBTYPE_UI8 BigInt Int64 64-bit signed integerWhat's does Int64 map to in C#? Am I using the correct terminology? It's difficult to look up an answer to a question when you don't know what something is called.
-
I'm struggling with understanding, what I think are difference between the Types used in .NET framework and the OLE DB Data Types used in Access, Excel, etc. For example:
Type indicator OLE DB Name .Net Type Description
DBTYPE_UI8 BigInt Int64 64-bit signed integerWhat's does Int64 map to in C#? Am I using the correct terminology? It's difficult to look up an answer to a question when you don't know what something is called.
This mapping may be more useful [SQL Server Data Type Mappings | Microsoft Docs](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings)
Never underestimate the power of human stupidity RAH
-
This mapping may be more useful [SQL Server Data Type Mappings | Microsoft Docs](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings)
Never underestimate the power of human stupidity RAH
Well, I just spent the better part of the afternoon yesterday creating this chart myself. LOL Can't seem to get past this. Everything I do is giving me an invalid cast exception. I'm using GetOleDbSchemaTable ( OleDbSchemaGuid.Columns, ...) I thought had a breakthrough, when I read that my cast was failing because it needed unboxed, so this one worked:
long ORDINAL_POSITION = (long)row.Field ( "ORDINAL_POSITION" );
But the Data_Type is not working. The documentation says it's a
UInt16
which I should be able to cast like this:
ushort DATA_TYPE = (ushort)row.Field ( "DATA_TYPE" );
but that gives me the same invalid cast exception. I can't even be sure that the cast that is causing the exception is ushort and UInt16 because I'm not understanding where to look.
-
Well, I just spent the better part of the afternoon yesterday creating this chart myself. LOL Can't seem to get past this. Everything I do is giving me an invalid cast exception. I'm using GetOleDbSchemaTable ( OleDbSchemaGuid.Columns, ...) I thought had a breakthrough, when I read that my cast was failing because it needed unboxed, so this one worked:
long ORDINAL_POSITION = (long)row.Field ( "ORDINAL_POSITION" );
But the Data_Type is not working. The documentation says it's a
UInt16
which I should be able to cast like this:
ushort DATA_TYPE = (ushort)row.Field ( "DATA_TYPE" );
but that gives me the same invalid cast exception. I can't even be sure that the cast that is causing the exception is ushort and UInt16 because I'm not understanding where to look.
Presumably you verified that the exception is coming from that exact line. Extract the column as a generic object and then print that out. That will tell you the exact data type. If that doesn't help then post the output from the above along with the class cast exception text.
-
Well, I just spent the better part of the afternoon yesterday creating this chart myself. LOL Can't seem to get past this. Everything I do is giving me an invalid cast exception. I'm using GetOleDbSchemaTable ( OleDbSchemaGuid.Columns, ...) I thought had a breakthrough, when I read that my cast was failing because it needed unboxed, so this one worked:
long ORDINAL_POSITION = (long)row.Field ( "ORDINAL_POSITION" );
But the Data_Type is not working. The documentation says it's a
UInt16
which I should be able to cast like this:
ushort DATA_TYPE = (ushort)row.Field ( "DATA_TYPE" );
but that gives me the same invalid cast exception. I can't even be sure that the cast that is causing the exception is ushort and UInt16 because I'm not understanding where to look.
You don't need the extra cast - the
Field<T>method already returns the correct type. There's no boxing/unboxing involved.long ORDINAL_POSITION = row.Field<Int64> ( "ORDINAL_POSITION" );
ushort DATA_TYPE = row.Field<UInt16> ( "DATA_TYPE" );The C# types are just aliases for the .NET equivalents; so
long===System.Int64, etc. I suspect the documentation is probably wrong, and theDATA_TYPEcolumn is most likely anInt32, which can then be cast to anOleDbTypevalue. Check the value returned bytable.Columns["DATA_TYPE"].DataTypeto see what it's defined as.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Presumably you verified that the exception is coming from that exact line. Extract the column as a generic object and then print that out. That will tell you the exact data type. If that doesn't help then post the output from the above along with the class cast exception text.
Thank you. I'm not far enough along to know how to do that just yet, but this is something I've been wanting to learn. It turns out that I was fist having cast issues because of db data type vs my C# data types did not always match appropriately, but there was also an issue with DBNull being returned in numeric types in SQLServer, which is not acceptable for C# value types.
The lack of surety in programming is part of the reason software is fragile.
-
You don't need the extra cast - the
Field<T>method already returns the correct type. There's no boxing/unboxing involved.long ORDINAL_POSITION = row.Field<Int64> ( "ORDINAL_POSITION" );
ushort DATA_TYPE = row.Field<UInt16> ( "DATA_TYPE" );The C# types are just aliases for the .NET equivalents; so
long===System.Int64, etc. I suspect the documentation is probably wrong, and theDATA_TYPEcolumn is most likely anInt32, which can then be cast to anOleDbTypevalue. Check the value returned bytable.Columns["DATA_TYPE"].DataTypeto see what it's defined as.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I'm not sure I understand that because I I really struggled with trying to get the Int64 to cast to long. It *may* have been that I was getting DBNull back for value types out of SQL Server, which causes an exception. I left the explicit cases in my code just because I'm afraid of breaking it and I have moved on to something else, finally. I appreciate your help and will exclude the explicit casts the next time I need to get the values form a database in some code that I feel more comfortable with. This code includes a ton of other C# constructs that are new to me, which lead to a pretty terrible debugging experience.
The lack of surety in programming is part of the reason software is fragile.
