datareader doesn't read data

Question

I have a bit of an issue but I can't figure out the problem. So I have a DataReader where I read my data from my database. But the problem is despite having a matching row in my database, the DataReader keeps coming back with no rows.

my database connection:

static private String _connectionString = @"Data Source=(localdb)\v11.0;Initial Catalog=dboVids;User id=g;password=g;Connect Timeout=15;Encrypt=False";  
        static private SqlConnection _connection;

        static Connection()
        {
            try
            {
                _connection = new SqlConnection(_connectionString);
                Open();
            }
            catch (Exception ex)
            {
                switch (ex.HResult)
                {
                    default:
                        throw;
                }
            }
        }

my method:

static public SqlDataReader WeergevenRolPerUser(string userName)
            {
                try
                {
                    Open();
                    SqlCommand command = new SqlCommand("select * from [dbo].[fnShowDatabaseRole]('@UserName')", _connection);
                    command.Parameters.AddWithValue("@UserName", userName);
                    SqlDataReader myReader = command.ExecuteReader();
                    myReader.Read();
                    return myReader;
                }
                catch (Exception ex)
                {
                    switch (ex.HResult)
                    {
                        default:
                            throw;
                    }
                }
            }

the Open() method:

private static void Open()
        {
            try
            {
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
            }
            catch (Exception ex)
            {
                switch (ex.HResult)
                {
                    default:
                        throw;
                }
            }
        }

here is where I call the DataReader:

 private void lstUsers_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlDataReader reader = null;
            try
            {
                if (_username != "" && lstUsers.SelectedValue != null)
                {
                    string user = lstUsers.SelectedValue.ToString();
                    reader = Database.Users.WeergevenRolPerUser(user);

                    if (reader.Read())
                    {
                        MessageBox.Show("unreachable");
                        var rol = reader.GetString(0);
                        if (rol == "gebruiker")
                        {
                            rdbUser.Checked = true;

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                switch (ex.HResult)
                {
                    default:
                        throw;
                }
            }
            finally
            {
                if (reader != null) reader.Close();
            }


        }

Answer 1

My guess is that you are calling .Read() twice on the reader object, which, as per MSDN:

Advances the SqlDataReader to the next record.

You do this once in WeergevenRolPerUser which will read the data you are expecting to be returned, and once again in lstUsers_SelectedIndexChanged which as you are finding, won't read anything, because there is no next record to advance to.

If I were you, I would change WeergevenRolPerUser to return a User object.

static public User WeergevenRolPerUser(string userName)
{
    try
    {
        using(var connection = new SqlConnection(/* connection string */))
        {
            connection.Open();

            using(var command = new SqlCommand("select * from [dbo].[fnShowDatabaseRole]('@UserName')", connection))
            {
                 command.Parameters.AddWithValue("@UserName", userName);

                 using(var myReader = command.ExecuteReader())
                 {
                     while(myReader.Read())
                     {
                         return new User
                                {
                                     Username = myReader.GetString(/* column index */),
                                     Role = myReader.GetString(/* column index */)
                                }
                     }
                 }

                 myReader.Close();
             }
         }
    catch (SqlException ex)
    {
        throw;
    }
}