Why the Get methods do not convert data

Alot of people ask
me why Connector/Net does not allow converting of field values to various
primitive types simply by calling the appropriate GetXXX methods on the
datareader.  To better understand what they are asking, consider the case
where you have a table with the first column of type int32.  For whatever
reason, you've decided that you want that value to be in a long primitive. 
So you do what seems logical and call GetInt64() on the reader object.  You
are then greeted with an odd InvalidCast exception.  This seems strange
considering that an int32 primitive can easily be cast to an int64 primitive
simply by using the appropriate cast syntax.  Indeed this does seem odd
until you understand what is going on under the hood.

 

When Connector/Net
reads the resultset values off the wire, it parses that data into the
appropriate primitives based on the field type reported by MySQL.  So INT
columns are parsed to an Int32 primitive, TINY columns are parsed to a byte
primitive, and so on.  When one of the Get methods (such as GetInt32,
GetInt64, GetBool) is called on the MySqlDataReader object, that method takes
the index given, accesses the appropriate field object, and reads the
value.  Here is where it gets interesting.

 

The first approach
to implementing these Get methods might be to do the
following:

 

object
val = fields[index].Value

 

However, there is a
problem with this code.  It will create a box to hold our value type and
that takes cycles.  In fact, in tests I've run it can be quite significant
and when you think of sites that are doing millions of hits per day, every cycle
counts.  Casting to object is definitely out of the question.  To
avoid the boxing, I've setup a set of classes that map directly to MySQL
primitives with each class having a value property of that primitive
type.

 

To return to our
original question, to allow someone to convert an int32 column to int64 simply
by calling GetInt64 would require the GetInt64 method to perform a conversion in
every case.  It might only convert to itself (Int64) or it might convert to
the type being requested (Int32).  While this is possible, I believe this
overhead for every call to the Get methods is unwarranted.  First, you can
always cast the returned value to the type you need as
follow:

 

Int64
longval = (Int64)reader.GetInt32(index);

 

The second reason
why I don't believe it's a good idea is that it can hide the real structure of
your database.  If you have an Int32 column and you are needing to retrieve
that value into an Int64 primitive, then perhaps you should reconsider the types
used in your database.