Page 1 of 1

Insert an Enum

Posted: Fri 04 Apr 2008 08:41
by mutzel
Hi,

I want to insert an Enum value using the parameter collection of the MySqlCommand. Sadly this results in an MySqlException telling me this:
"Unknown column 'Done' in 'field list'"
Creating the query manually is working.
The query is than looking like:

Code: Select all

REPLACE INTO result (ErrorMessage,Status,Identification) VALUES ('test','Done', UUID());
This is the c# code which I was using:

Code: Select all

    
public enum Status { Error, Done};

    class Program
    {
        static void Main(string[] args)
        {
            MySqlCommand cmd = new MySqlCommand()
            {
                CommandText = "REPLACE INTO result (ErrorMessage,Status,Identification) VALUES ('test',:Status, UUID());"
            };
            cmd.Parameters.Add("Status", Status.Done);

            using (MySqlConnection connection =
                new MySqlConnection()
                {
                    Host = "localhost",
                    UserId = "root",
                    Password = "xxx",
                    Database = "test"
                })
            {
                connection.Open();
                cmd.Connection = connection;
                cmd.ExecuteNonQuery();
            }
        }
    }
And this is how my table is looking:

Code: Select all

CREATE TABLE  `test`.`result` (
  `Identification` varchar(36) NOT NULL,
  `Status` enum('Error','Done') NOT NULL,
  `ErrorMessage` text NOT NULL,
  PRIMARY KEY  USING BTREE (`Identification`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Could it be that the MySql enum type is simply not supported by MyDirect .NET version "4.50.26 19-Mar-08"?

Posted: Fri 04 Apr 2008 09:39
by mutzel
In addition...

would it be possible to implement some kind of a type converter for a custom type which will automatically be used for converting types unknown to the MySqlCommand from and to mysql...

The reason is because I discovered an additional problem when I try to receive a System.Guid. It is not type of System.Guid anymore .. but type of System.String.

This could also help for the enum problem if this is not suported natively.

Posted: Mon 07 Apr 2008 09:05
by Alexey.mdr
As to the first post:
you may make a string representation of the enum type, before inserting values to the database.
E.g.:

Code: Select all

cmd.Parameters.Add("Status", Status.Done.ToString()); 
As to the second post, could you please provide a small sample?

Posted: Tue 08 Apr 2008 05:59
by mutzel
To help my self a bit I wrote this little converter class:

Code: Select all

 public class MySqlTypeConvert
    {

        public bool CanConvert(Type type)
        {
            return type.IsEnum || type == typeof(Guid);
        }

        public object ConvertFrom(object value, bool throwOnError)
        {
            if (!throwOnError && !CanConvert(value.GetType())) return value;
            return ConvertFrom(value);
        }

        public object ConvertFrom(object value)
        {
            Type type = value.GetType();
            if (type.IsEnum) return ConvertFromEnum(value);
            if (type == typeof(Guid)) return ConvertFromGuid(value);
            throw new NotSupportedException(string.Format("The type '{0}' is not supported by '{1}'", type, this.GetType()));            
        }

        public object ConvertFromEnum(object value)
        {            
            return value.ToString();
        }
        
        public object ConvertFromGuid(object value)
        {
            return value.ToString();
        }


        public object ConvertTo(Type type, object value, bool throwOnError)
        {
            if (!throwOnError && !CanConvert(type)) return value;
            return ConvertTo(type, value);
        }

        public object ConvertTo(Type type, object value)
        {
            if (type.IsEnum) return ConvertToEnum(type, value);
            if (type == typeof(Guid)) return ConvertToGuid(type, value);
            throw new NotSupportedException(string.Format("The type '{0}' is not supported by '{1}'", type, this.GetType()));            
        }

        public object ConvertToEnum(Type type, object value)
        {
            return Enum.Parse(type, value.ToString());           
        }

        public object ConvertToGuid(Type type, object value)
        {
            return new Guid(value.ToString());
        }
    }
I did this to seperate the converting methodes and the mysql stuff ..
..for each new parameter I will do this:

Code: Select all

new MySqlParameter(name, new MySqlTypeConvert().ConvertFrom(value, false));
.. and the other way around for the dataset reading.

This is not very nice since the behavior of converting datatypes from c# to MySql is already somewhere inside the MySirect.Net framework. So it would be better to add the functionallity by implementing an interface which will be called from the MySirect.Net framework to do the conversion whenever the MySirect.Net framework doesn't know how to convert something .. like it is the case for the Enum and Guid.

Maybe such a way of implementing a converter does already exist inside the MySirect.Net framework?

(BTW I need this because I do some kind of serialisation into and from mysql data where the source objects are unknown to me and writing and reading is done by System.Reflection)

Posted: Tue 08 Apr 2008 09:45
by Alexey.mdr
Of course there are some conversions exist within MyDirect .NET.
Though your suggestion is quite natural, but most likely it cannot be implemented.
As to the GUID data type, such conversion wouldn't be correct, as MySQL database engine doesn't have a correspondent data type at all.
As to Enum data type, it's a little bit more complicated.
We cannot map all values of MySQL enum to C# enum, e.g.:
MySql database has such enum values:
-a
-b
-c
-c c

C# respectively:
public enum A { a,b,c,c c} // c c gives an error Syntax error, ',' expected.