Mapping Datatypes

Developer
Aug 8, 2008 at 8:42 PM
Edited Aug 9, 2008 at 2:23 AM
I was looking at the BOL entry about Datatypes 

http://msdn.microsoft.com/en-us/library/ms131092.aspx

How should we deal with the datatypes that have no CLR equivalent? My thinking is that the initial verion should only support character based or number based datatypes. Since this thing is supposed to be database agnostic we should probably stick to the types that are common to all databases.
Coordinator
Aug 8, 2008 at 9:26 PM
So the types I see in question are below, the others have logical mappings:

image = byte[]
sql_variant = object ???
unique_identifier = String
timestamp = Who cares, its worthless data.  Do you port it as a string just so its there?

I like this:

"Ahhh. The dreaded TIMESTAMP datatype. Unfortunately you really can't use TIMESTAMP for any type of comparison. Or really for much of anything. To quote from SQL Server Books Online:

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms."

http://www.sqlteam.com/article/timestamps-vs-datetime-data-types
Developer
Aug 9, 2008 at 2:32 AM
I am not really concerned with the timestamp either. I am thinking that anything that we don't have a specific rule for gets converted as byte[]. I have had issued when converting binary data to strings. The codepage sometimes makes an unexpected conversion.
 
Why isn't string the CLR equivalent for varchar?
Coordinator
Aug 9, 2008 at 3:07 AM
Edited Aug 9, 2008 at 3:07 AM
As far as I am concerned, varchar and string are equivalent.  I don't think there is much choice with the other types but to load them as byte[].  I can convert the image type to a System type pretty easily from there.