Retrieve Image from Binary Data in SQL to DataList

Question

I am doing a 3 tier application to retrieve image from sql server which i stored image to binary data in sql, and the problem is i can't retrieve my image from the sql server.

here is my code in DataAccessLayer

  public List<Volunteer> VolunteerTRetrieve()
    {
        List<Volunteer> vList = new List<Volunteer>();
        byte[] volunteerProfilePicture;
        string volunteerName, volunteerNRIC, volunteerAddress, volunteerEmail, volunteerContact;
        string queryStr = "SELECT * FROM TVolunteer Order By VolunteerName";
        SqlConnection conn = new SqlConnection(DBconnStr);
        SqlCommand cmd = new SqlCommand(queryStr, conn);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while ((dr.Read()))
        {
            volunteerName = dr["VolunteerName"].ToString();
            volunteerNRIC = dr["VolunteerNRIC"].ToString();
            volunteerAddress = dr["VolunteerAddress"].ToString();
            volunteerEmail = dr["VolunteerEmail"].ToString();
            volunteerContact = dr["VolunteerContact"].ToString();
            volunteerProfilePicture = (byte[])dr["VolunteerProfilePicture"];

            vList.Add(new Volunteer(volunteerName, volunteerNRIC, volunteerAddress, volunteerEmail, volunteerContact, volunteerProfilePicture));
        }
        conn.Close();
        dr.Dispose();
        return vList;
    }

in BusinessLogicLayer

   public List<Volunteer> RetrieveAllBVolunteer()
    {
        Volunteer v = new Volunteer();
        List<Volunteer> vList = new List<Volunteer>();
        vList = v.VolunteerBRetrieve();
        return vList;
    }

and in PresentationLayer

   List<Volunteer> allVolunteer = new List<Volunteer>();
   allVolunteer = vBLL.RetrieveAllTVolunteer();
   dl_tvolunteer.DataSource = allVolunteer;
   dl_tvolunteer.DataBind();

I have also an image handler class

public class ShowImage : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["DBconnStr"].ConnectionString;
        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();
        string queryStr = "SELECT VolunteerProfilePicture FROM TVolunteer WHERE VolunteerNRIC = @NRIC";
        SqlCommand cmd = new SqlCommand(queryStr, conn);
        cmd.Parameters.Add("@NRIC", SqlDbType.VarChar).Value =
            context.Request.QueryString["VolunteerNRIC"];
        cmd.Prepare();
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        context.Response.BinaryWrite((byte[])dr["VolunteerProfilePicture"]);
    }

Please help, Thankyou!

on your asp.net web page, do you have a picturbox control..? you need how many pictures can be returned from the sql query...? — MethodMan
– MethodMan, Commented Dec 23, 2012 at 15:08
i need to return at least 1 picture for every different volunteer in a datalist — Raynoro
– Raynoro, Commented Dec 24, 2012 at 4:46
you need to do your dr.Read() in a While Loop then look at my example and see if that works. — MethodMan
– MethodMan, Commented Dec 24, 2012 at 14:42
i did that on my data access layer (shown on top) but it doesn't work — Raynoro
– Raynoro, Commented Dec 25, 2012 at 8:29

MethodMan · Accepted Answer · 2012-12-23 15:14:18Z

0

If you are returning one image you could the following

1. place a PictureBox control on the asp.net webpage
2. define the sql connections //not sure why you are using cmd.prepare

byte[] sqlImage = (byte[])cmd.ExecuteScalar();
MemoryStream memStream = new MemoryStream();
memStream.Write(sqlImage, 0, sqlImage.Length);
Bitmap bit = new Bitmap(memStream);

or if you want to do it a different way

  try

  {

    con = new SqlConnection(constr);
    cmd = new SqlCommand("select photopath,Photo from employees where employeeid=14", con);
    con.Open();
    dr = cmd.ExecuteReader();
            while(dr.Read())
            {
                if (!dr.IsDBNull(1))
                {
                    byte[] photo = (byte[])dr[1];
                    MemoryStream ms = new MemoryStream(photo);
                    pictureBox1.Image = Image.FromStream(ms);
                }


            }
        }
        catch (Exception ex)
        {
            dr.Close();
            cmd.Dispose();
            con.Close();

            Response.Write(ex.Message);
        }

answered Dec 23, 2012 at 15:14

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MethodMan Over a year ago

Check this stackoverflow link for other examples stackoverflow.com/questions/11284217/…

Collectives™ on Stack Overflow

Retrieve Image from Binary Data in SQL to DataList

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related