Insert an Enum

Insert an Enum

Postby mutzel » Fri 04 Apr 2008 08:41

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"?
mutzel
 
Posts: 3
Joined: Fri 04 Apr 2008 08:26

Postby mutzel » Fri 04 Apr 2008 09:39

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.
mutzel
 
Posts: 3
Joined: Fri 04 Apr 2008 08:26

Postby Alexey.mdr » Mon 07 Apr 2008 09:05

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?
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby mutzel » Tue 08 Apr 2008 05:59

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)
mutzel
 
Posts: 3
Joined: Fri 04 Apr 2008 08:26

Postby Alexey.mdr » Tue 08 Apr 2008 09:45

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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for MySQL