CODEWITHSUNDEEP
c#mysqldatabaseexceptioncasting
Marco Frost
Marco Frost

Reputation: 800

Casting mysql reader result to string throws an exception in C#

My function

public Permission[] GetPermissionsForGroupID(string groupID)
    {
        MySqlCommand command = new MySqlCommand();
        command.Connection = connection;
        command.CommandText = "SELECT `permissions`.`permissionName`, `groups_permissions`.`permissionValue` 
        FROM `db`.`groups_permissions`, `db`.`permissions` 
        WHERE `permissions`.`permissionID` = `groups_permissions`.`permissionID` 
        AND `groups_permissions`.`groupID` = '" + groupID + "'";
        MySqlDataReader reader = command.ExecuteReader();
        List<Permission> permissions = new List<Permission>();
        while (reader.Read())
        {
            permissions.Add(new Permission((string)reader[0], (int)reader[1]));
        }
        reader.Close();
        return permissions.ToArray();
    }

throws an System.InvalidCastException at

permissions.Add(new Permission((string)reader[0], (int)reader[1]));

I narrowed it down to (string)reader[0] where a varchar should be casted into a string.

reader[0] = permissions.permissionName = varchar

What could be the problem?

EDIT:

The value of reader[0] is a string "kick_user_power" and the value of reader[1] is an integer "75".

EDIT 2:

The constructor of Permission

public Permission(string permissionName, int permissionPower)
    {
        PermissionName = permissionName;
        PermissionPower = permissionPower;
    }

And, for better visualization, the values of reader[0] and reader[1]: Reader-Values

Upvotes: 2

Views: 458

Answers (2)

O. Jones
O. Jones

Reputation: 108676

The dotnet SqlDataReader class includes functions for retrieving column data.

You probably want to use 

   reader.GetString(0)

and

   reader.GetSqlInt32(1)

to retrieve your values.

In fact for best results you should use

  reader.IsDbNull(0)? "missing value" : reader.GetString(0)

Upvotes: 3

Remy
Remy

Reputation: 12703

Could it be that it's not the cast to string that is the issue, but the cast to int?
Try

permissions.Add(new Permission(reader[0].ToString(), Convert.ToInt64(reader[1])));

Both ToString() and Convert.ToInt64 are a bit less "sensitive".

Upvotes: 0

Related Questions