I am working on a cross-db ADO.NET wrapper project. I am using the DbProviderFactory for connections, and working with System.Data.Common classes. I have noticed the following, when reading back a TINYINT(1) return value from a test function:
On the Devart dotConnect for MySQL driver:
- If I assign a bool value to my return parameter, the parameter DbType gets automatically set to Int64 and the returned value is a C# short (!)
- If I assign a byte value, the parameter DbType is set to Int16 and the returned value is a C# short
- If I assign an sbyte value, the parameter DbType is set to Int16 and the returned value is a C# short
This compares to the Oracle/MySQL Connector/NET driver on which:
- If I assign a bool value to my return parameter, the parameter DbType gets automatically set to SByte and the returned value is a C# sbyte
- If I assign a byte value, the parameter DbType is set to Byte and the returned value is a C# byte
- If I assign an sbyte value, the parameter DbType is set to SByte and the returned value is a C# sbyte
I find the pattern of results in dotConnect for MySQL quite unexpected (unlike the pattern in Connector/NET) and I suspect other users might find it so too?
Thanks.
Unexpected pattern of auto-assigned parameter types
-
- Posts: 4
- Joined: Tue 28 Feb 2017 10:26
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Unexpected pattern of auto-assigned parameter types
We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Unexpected pattern of auto-assigned parameter types
The MySqlParameter.DbType property can be set manually, otherwise it will defined automatically basing on a parameter value. If MySqlParameter.Value is set to a bool value, then its MySqlType property is automatically assigned to MySqlType.Bit. In its turn, MySqlType.Bit is represented as Int64. For more information, please refer to https://www.devart.com/dotconnect/mysql ... lType.html.
DbType determines how dotConnect for MySQL converts value before sending it to MySQL server. Bool can have two values (1 and 0), and it doesn't matter what DbType is used: Int16, Int32 or Int64.
The described mapping is used in dotConnect for MySQL for a long time, and changing it could break the backward compatibility for many users.
If you do not want to get Int64 for bool value, you can manually assign MySqlParameter.DbType=DbType.Int16 (it is the smallest integer type which can be assigned to the DbType property for MySqlParameter).
DbType determines how dotConnect for MySQL converts value before sending it to MySQL server. Bool can have two values (1 and 0), and it doesn't matter what DbType is used: Int16, Int32 or Int64.
The described mapping is used in dotConnect for MySQL for a long time, and changing it could break the backward compatibility for many users.
If you do not want to get Int64 for bool value, you can manually assign MySqlParameter.DbType=DbType.Int16 (it is the smallest integer type which can be assigned to the DbType property for MySqlParameter).