Question 220725 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL C# – How to retrieve enum type from MySQL

I have an enum called ProfileType and in MySQL it is stored as an enum like this:

The problem is that once I try to login it crashes giving me a "specified cast is not valid" which happens when instatiating the Profile.

var cmd = new MySqlCommand($"select * from users where username='{username}' and password='{password}'", connection);
        var rd = cmd.ExecuteReader();
        if (rd.HasRows)
        {
            rd.Read();
            return new Profile
            {
                Username = rd.GetString("username"),
                Email = rd.GetString("email"),
                Password = rd.GetString("password"),
                Picture = rd.GetString("picture"),
                ProfileType = (ProfileType)rd.GetInt16("profileType"),
                AdditionalData = rd.GetString("additionalData")
            };

This is the enum:

public enum ProfileType
{
    User = 0,
    Library = 1,
    Admin = 2
}

I used int to store enum but it was always giving me ‘0’ even if I was signing up as a library.

Tags:

2

Answers


  1. 0

    Since "rd.GetFieldType("profileType").ToString()" is "System.String", you aren’t able to GetInt16 from it.

    ProfileType = (ProfileType)rd.GetInt16("profileType")

    –>

    ProfileType = Enum.Parse<ProfileType>(rd.GetString("profileType"))
    Login or Signup to reply.
  2. 0

    MySQL enum types are actually returned as strings, so you need to parse it

    ProfileType = Enum.Parse<ProfileType>(rd.GetString("profileType")),

    Or on older versions of .NET

    ProfileType = (ProfileType)Enum.Parse(typeof(ProfileType), rd.GetString("profileType")),

    Side notes on your code:

    • You have dangerous SQL injection and you should use parameters instead.
    • You need to dispose the reader with a using.
    • I sincerely hope you are not storing plain-text passwords. You should be salt-and-hashing them.
    • Why bring the password back from the database, it seems like another chance for it to be read maliciously?
    • if (rd.HasRows) is unnecessary, you can get that from rd.Read()
    using var cmd = new MySqlCommand(@"
select
  username,
  email,
  picture,
  profileType,
  additionalData
from users
where username = @username
  and passwordHash = @passwordHash;
", connection);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@passwordHash", SaltAndHash(password, username));

using var rd = cmd.ExecuteReader();
if(rd.Read())
    return new Profile
    {
        Username = rd.GetString("username"),
        Email = rd.GetString("email"),
        Picture = rd.GetString("picture"),
        ProfileType = Enum.Parse<ProfileType>(rd.GetString("profileType")),
        AdditionalData = rd.GetString("additionalData")
    };
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search