SQL Data Reader into Label – Value doesn’t display

DB-Acess.cs

This is where the Public SqlDataReader getEmail is initialised.

public SqlDataReader getEmail(string UserName)
        {
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            //string noemail ="noemailsaved";
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "Select Email from dbo.EMPLOYEE where Username ='" + UserName + "'";
            SqlDataReader reader = newCmd.ExecuteReader();
            while (reader.Read())
            {
                string email = reader["EMPLOYEE.Email"].ToString();

            }
            conn.Close();
            reader.Close();
            return reader;
          }

I’m using OOP and calling the function in asp.net page and want to display the value in a label. Below is the code I’m using to call the function.

SqlDataReader reader = dba.getEmail(pname);
lblEmail.Text = reader.ToString();
lblEmail.DataBind();

Instead of seeing the Email address of the Employee i’m seeing System.Data.SqlClient.SqlDataReader

Please help in correcting this error.

Thank you in advance.

Answer

ERRORS

1. Return type of the function getEmail is SqlDataReader and you are expecting String i.e. an Email.
2. Declaration of email in string email = reader["EMPLOYEE.Email"].ToString(); is inside while loop. Therefore, email becomes local to the while loop. It will not recognize outside the loop.
3. And you are returning reader' an instance ofSqlDataReader,but you were expecting aString`.
4. In you second code block, what your doing is not wrong(it won’t give error) but that is not what you are expecting to get. You should be declaring a String variable eg. email and assign the function to it(or you can directly assign it to lblEmail Text property.

SUGGESTION

The way you are checking ConnectionState in if(conn.State.ToString() == "Closed") may give you the desired result but is not recommended. Instead you should check like this if (conn.State == ConnectionState.Closed).

Now the most awaiting part: The improvised code: lol!

UPDATE

public string getEmail(string UserName){
    if (conn.State == ConnectionState.Closed){
        conn.Open();
    }
    //string noemail ="noemailsaved";
    string email="";
    using(SqlCommand newCmd = new SqlCommand()){
        newCmd.Connection = conn;
        newCmd.CommandType = CommandType.Text;
        newCmd.CommandText = "Select Email From dbo.EMPLOYEE Where Username = @uname";
        newCmd.Parameters.AddWithValue("@uname",UserName);
        using(SqlDataReader reader = newCmd.ExecuteReader()){
              while (reader.Read()){
                   email = reader["Email"].ToString();
              }
        }
    }
    conn.Close();
    //reader.Close();
    return email ;
}

For setting the Label Text

lblEmail.Text = dba.getEmail(pname);