C# IDataReader System.IndexOutOfRangeException Problem-CodePudding

I encountered IndexOutOfRangeException issue when using IDataReader in C#. Below are my sample code. The code thrown me the Exception when it executed the functions of GetDataValue.

public List<Users> SelectUsersbyUsernamePassword(string username, string password)
    {
        const string SQL_STATEMENT =
            "SELECT U.Username, U.Password, U.Role "  
            "FROM Users U ";

        List<Users> listusers = new List<Users>();
        Database db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(CONNECTION_NAME);

        using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
        {
            db.AddInParameter(cmd, "@username", DbType.String, username);
            db.AddInParameter(cmd, "@password", DbType.String, password);
            try
            {
                using (IDataReader dr = db.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        Users users = new Users();
                        users.UserID = base.GetDataValue<int>(dr, "UserID");
                        users.Username = base.GetDataValue<string>(dr, "Username");
                        users.Password = base.GetDataValue<string>(dr, "Password");
                        users.Role = base.GetDataValue<string>(dr, "Role");

                        listusers.Add(users);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
                //throw new DbException(ex.Message, SQL_STATEMENT, "username = "   username, ex.InnerException);
            }
        }
        return listusers;
    }

Data Access

protected T GetDataValue<T>(IDataReader dr, string columnName)
    {
        int i = dr.GetOrdinal(columnName);

        if (!dr.IsDBNull(i))
            return (T)dr.GetValue(i);
        else
            return default(T);
    }

CodePudding user response：

As per commented from @Dai, the IndexOutOfRangeException Problem had been resolved and it is due to the SELECT query not getting the correct column.

// Read values.
users.UserID = base.GetDataValue<int>(dr, "UserID");
users.Username = base.GetDataValue<string>(dr, "Username");
users.Password = base.GetDataValue<string>(dr, "Password");
users.Role = base.GetDataValue<string>(dr, "Role");

SQL Query (To Be)

const string SQL_STATEMENT =
   "SELECT U.UserID, U.Username, U.Password, U.Role "  
   "FROM Users U "