How can you display an image from Sql Server 2008 in asp.net using c#?

Question

Possible Duplicate:
What's the best way to display an image from a sql server database in asp.net?
bytearray to image asp.net

How can you display an image from Sql Server 2008 in asp.net using c#?

This is what I got so far in my codebehind. Then I want to get the image and display it onto my table in my .net page. (The images are of .png type and stored as VarBinary)

HANDLER

using System;
using System.Web;
using System.Drawing;
using System.Drawing.Imaging;
using System.Data;
using System.Data.SqlClient;
using System.Web.Caching;
using System.Configuration;
using System.Web.Configuration;
using System.IO;


namespace RocoSportsWA
{
    public class DisplayImage : IHttpHandler, System.Web.SessionState.IRequiresSessionState
    {
        private SqlConnection _connection;
        private SqlCommand _command;
        private HttpContext _context;

        public void ProcessRequest(HttpContext context)
        {

        }

        public bool IsReusable
        {
            get
            {
                return true;
            }
        }

        public IAsyncResult BeginProcessRequest(HttpContext context,  AsyncCallback cb, object state)
        {
            // Get the employee ID from the query string
            string _logo = context.Request["Logo"];
            if (String.IsNullOrEmpty(_logo))
                return null;

            int logo = 0;
            bool ok = int.TryParse(_logo, out logo);
            if (!ok) return null;

            _context = context;
            string conn = WebConfigurationManager.ConnectionStrings["Data Source=ROBEL-HP;Initial Catalog=RocoSportsDB;Integrated Security=True"].ConnectionString;
            // Select the image from the database
            _connection = new SqlConnection(conn);
            _connection.Open();
            _command = new SqlCommand("SELECT Logo from TEAM where Team = @HomeTeam, _connection");
            _command.Parameters.AddWithValue("@HomeTeam", logo);
            return _command.BeginExecuteReader(cb, state);
        }

        public void EndProcessRequest(IAsyncResult ar)
        {
            try
            {
                SqlDataReader reader = _command.EndExecuteReader(ar);
                if (reader != null && reader.HasRows)
                {
                    // Get the image returned in the query
                    reader.Read();
                    try
                    {
                        byte[] image = (byte[])reader[0];
                        // WRite the image into the HTTP response output stream
                        _context.Response.ContentType = "image/png";
                        // strip off the 78 byte Ole header (a relic from old MS Access databases)
                        _context.Response.OutputStream.Write(image, 78, image.Length - 78);
                    }
                    catch
                    {

                    }
                }
            }
            finally
            {
                if (_connection != null)
                    _connection.Close();
            }
        }
    }
}

WEBPAGE.NET

<asp:Image ID="HomeTeamImage" runat="server" ImageUrl='<%# "DisplayImage.cs?Logo=" + Eval("HomeTeam") %>'

The HomeTeam in Eval is a label, since I am getting the text in that from session["HomeTeam"]

CODEBEHIND

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

namespace RocoSportsWA.Reporter
{
    public partial class LiveGameReporting : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Match.Text = Session["HomeTeam"] + " vs. " + Session["AwayTeam"];
            DateTime.Text = "Date: " + Session["Date"];
            Stadium.Text = "Stadium: " + Session["Stadium"];
            HomeTeam.Text = "" + Session["HomeTeam"];
            AwayTeam.Text = "" + Session["AwayTeam"];

            SqlConnection conn = new SqlConnection("Data Source=ROBEL-HP;Initial Catalog=RocoSportsDB;Integrated Security=True");
            conn.Open();


                SqlCommand HomeTeamcmd = new SqlCommand("SELECT Logo from TEAM where Team = @HomeTeam", conn);
                SqlCommand AwayTeamcmd = new SqlCommand("SELECT Logo from TEAM where Team = @AwayTeam", conn);

                HomeTeamcmd.Parameters.AddWithValue("@HomeTeam", Session["HomeTeam"]);
                AwayTeamcmd.Parameters.AddWithValue("@AwayTeam", Session["AwayTeam"]);

                conn.Open();
                byte[] HomeTeamImageByte = (byte[])HomeTeamcmd.ExecuteScalar();
                byte[] AwayTeamImageByte = (byte[])AwayTeamcmd.ExecuteScalar();

                var bitmapImage = new BitmapImage();
                bitmapImage.SetSource(new MemoryStream(HomeTeamImageByte));
                Image1.Source = bitmapImage;
        }

        }

}

Answer 1

This code will convert a binary blob to an image:

var bitmapImage = new BitmapImage();
bitmapImage.SetSource(new MemoryStream(imageData));
newImage.Source = bitmapImage;

where imageData is of type byte[].

As long as you set up your database mapping correctly the data will be in the right format.

Answer 2

The easiest way to accomplish this is to point each image tag's src property (or if you are using ASP.NET Image control, the ImageUrl property at an ASHX handler that will accept the image ID on the querystring, execute the respective SQL Query, and write the image out to the Response OutputStream.

Here's a recent article that shows how to do this asynchronously:

http://goo.gl/zCYKE