AddWithValue with custom types converting

[pritchin]

In Connector/NET AddWithValue(string parameterName, object value); cast value parameter into byte[] or string if it's not a one of the known types.
MySqlConnector instead use another logic. If it couldn't find a value type the exception will be thrown Parameter type {0} (DbType: {1}) not currently supported. Value: {2}
The problem occures because we have own low-level type UUId which can be cast to string and byte[]. This type is VARCHAR(30) into mysql stored procedures parameters.
In code it may looks like this:

	public sealed class MySqlParameter : DbParameter
	...
		internal void AppendSqlString(BinaryWriter writer, StatementPreparerOptions options)
		{
		...
			else if (MySqlDbType == MySqlDbType.Int64)
			{
				writer.WriteUtf8("{0}".FormatInvariant((long) Value));
			}
			else if (MySqlDbType == MySqlDbType.UInt64)
			{
				writer.WriteUtf8("{0}".FormatInvariant((ulong) Value));
			}
			else if (Value is Enum)
			{
				writer.WriteUtf8("{0:d}".FormatInvariant(Value));
			}
			else
			{
				try
				{
					// here may be more complicated logic
					writer.Write((byte) '\'');
					writer.WriteUtf8(Value.ToString().Replace("\\", "\\\\").Replace("'", "\\'"));
					writer.Write((byte) '\'');
				}
				catch
				{
					throw new NotSupportedException("Parameter type {0} (DbType: {1}) not currently supported. Value: {2}".FormatInvariant(Value.GetType().Name, DbType, Value));
				}
			}
		...
		}

Also it's not possible to create extension to public MySqlParameter AddWithValue(string parameterName, object value) to fix this problem

[bgrainger]

Is the workaround AddWithValue("@parameterName", myObject.ToString()) sufficient?

I'd like to do some testing to evaluate how other ADO.NET connectors handle this situation.

[bgrainger]

As far as I can tell, Connector/NET treats all unknown types as blob. This will be handled by MySqlBinary, which will fall back to calling ToString() on the object (which may perform a culture-sensitive conversion) then calling Encoding.ToBytes using the Packet's Encoding (which I assume is the encoding of the connection).

If this is being inserted in a VARCHAR column then the DB Server will apply a charset conversion from bytes back to the character set of the column.

There's so much "magic" and opportunity for failure here that I'm very reluctant to clone this behaviour. Moreover, this appears to be very non-standard among ADO.NET connectors; dotConnect, Microsoft.Data.Sqlite, Npgsql, and SqlClient all forbid a custom type being used directly as DbParameter.Value.

[bgrainger]

Not implementing, for the reasons given above, and because an easy workaround (calling .ToString() before assigning the parameter value) is available.

[bgrainger]

Another good reason to not automatically call ToString on everything: it may not do what you expect.

In #943, the user wanted to set MySqlParameter.Value to a MemoryStream object. (This is supported by SqlClient.)

In MySqlConnector, this threw an exception with a clear message: the type is not supported.

In MySQL Connector/NET, the driver calls .ToString() on the Stream and writes the literal string System.IO.MemoryStream to the column in the database (converting it to a BLOB if it's a BINARY column): https://bugs.mysql.com/bug.php?id=102593. Throwing an exception is far better than silent data corruption.