MySQL C# - How to retrieve enum type from MySQL

Question

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.

Answer 1

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")
    };