asp.net mvc c# - problem with reading from sql server database

Question

Hello all,

I cant read any data from database, could you please look at my code and find a problem?

 cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = @"SELECT ImageData, " 
                                 + " ContentType, " 
                                 + " ImageName " 
                                 + " FROM UsersImage "
                                 + " WHERE UserName = @UserName ";

                cmd.Parameters.Add(new SqlParameter("@UserName", ThreadUserName));

                reader = cmd.ExecuteReader();
            if (reader["ContentType"] != DBNull.Value)
            {
                ContentType = Convert.ToString(reader["@ContentType"]);
            }

            if (reader["ImageName"] != DBNull.Value)
            {
                ImageName = Convert.ToString(reader["@ImageName"]);
            }

            if (reader["ImageData"] != DBNull.Value)
            {
                ImageData = Convert.ToByte(reader["@ImageData"]);
            }

            int affectedRows = cmd.ExecuteNonQuery();
            if (affectedRows != 1)
            {

            }

            reader.Close();

My table name is: UsersImage.

My columns, ImageData(image), ContentType(nvarchar50), ImageName(nvarchar50), UserName (varchar20)

Answer 1

Add in reader.Read().

 cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = @"SELECT ImageData, " 
                                 + " ContentType, " 
                                 + " ImageName " 
                                 + " FROM UsersImage "
                                 + " WHERE UserName = @UserName ";

                cmd.Parameters.Add(new SqlParameter("@UserName", ThreadUserName));

using (IDataReader reader = cmd.ExecuteReader())
{

    if (reader.Read())
    {
        if (reader["ContentType"] != DBNull.Value)
        {
            ContentType = Convert.ToString(reader["ContentType"]);
        }

        if (reader["ImageName"] != DBNull.Value)
        {
            ImageName = Convert.ToString(reader["ImageName"]);
        }

        if (reader["ImageData"] != DBNull.Value)
        {
            ImageData = Convert.ToByte(reader["ImageData"]);
        }

        int affectedRows = cmd.ExecuteNonQuery();
        if (affectedRows != 1)
        {

        }
    }
}

EDIT: I added in a using as well to replace the reader.Close().

Answer 2

    string sql = @"SELECT ImageData, ContentType, ImageName FROM UsersImage WHERE UserName = @UserName";

    using (var cn = new SqlConnection("[YOUR CONNECTION STRING]"))
    using (var cmd = new SqlCommand(sql, cn))
    {
        // Set some properties on the cmd object
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@UserName", ThreadUserName);

        // Open the connection
        cn.Open();

        // Execute your command and get back a data reader
        using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            if (reader.HasRows)
            {
                reader.Read();

                if (reader["ContentType"] != DBNull.Value)
                {
                    ContentType = reader["ContentType"].ToString();
                }
                if (reader["ImageName"] != DBNull.Value)
                {
                    ImageName = reader["ImageName"].ToString();
                }
                if (reader["ImageData"] != DBNull.Value)
                {
                    ImageData = (byte[])reader["ImageData"];
                }
            }
        }
    }