Exception: Specified cast is not valid

Question

public class UserLoginInfo
{
    public UserRole Role;
    public string Username;

    public static UserLoginInfo FetchUser(string username, string password)
    {
        using (var connection = Utils.Database.GetConnection())
        using (var command = new SqlCommand("SELECT [Username], [Password], [Role] FROM [Users] WHERE [Username] = @username", connection))
        {
            command.Parameters.AddWithValue("@username", username);
            using (var reader = command.ExecuteReader())
            {
                if (reader == null || !reader.Read() || !Utils.Hash.CheckPassword(username, password, (byte[])reader["Password"]))
                    throw new Exception("Wrong username or password.");

                return new UserLoginInfo { Username = (string)reader["Username"], Role = (UserRole)reader["Role"] };
            }
        }
    }
}

When I put a breakpoint and debug the error comes from this line

return 
    new UserLoginInfo 
    { 
        Username = (string)reader["Username"], 
        Role = (UserRole)reader["Role"] 
    };

I don't understand why I get this error. Please help me!

EDIT: How can I convert (string)reader["Role"] to UserRole??

public enum UserRole
{
 Admin,
 Maintance,
 User
}

Answer 1

The code is probably failing at:

Role = (UserRole)reader["Role"];

because you're trying to cast an object to a UserRole and the cast doesn't exist.

It looks like you're trying to cast reader["Role"] to a UserRole Object and I'm guessing that's what is failing.

You either need to specify (or implement) a valid cast, or implement something like UserRole.Parse(string value) to parse the string to a valid UserRole object.

Answer 2

Role = (UserRole)reader["Role"]

Presumably UserRole is a type you have defined, hence the SqlDataReader does not know how to convert the data it gets from the database to this type. What is the type of this column in your database?

EDIT: As for your updated question you can do:

var role = (string)reader["Role"];
UserRole role = (UserRole)Enum.Parse( typeof(UserRole), role );

You might want to add in some extra error checking, eg checking that role is not null. Also, before parsing the enum you could check if the parse is valid using Enum.IsDefined.

Answer 3

(UserRole)reader["Role"] should be (string)reader["Role"]. There's no UserRole type in SQL server.

Answer 4

That means you either cannot cast (string)reader["Username"] (not likely), or (UserRole)reader["Role"] (more likely). What is UserRole - can you create it by casting from a db result? Don't you need something like new UserRole(reader["Role"])?

Answer 5

If what you're storing in the database is a string, but you want to convert it to an enum type, then you should use the Enum.Parse() method.

For example:

UserRole userRole = (UserRole) Enum.Parse(typeof(UserRole), (string) reader["Role"]);