How to download and view images from SQL Server table?

Question

I have a problem. I want to download and view in browser images from a SQL Server table. I don't know how to do.

My code:

public partial class IndexForm : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGrid();
        }
    }

    private void BindGrid()
    {
        string CurrentUser = User.Identity.Name;
        string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        using (SqlConnection con = new SqlConnection(constr))
        {
            if (CurrentUser == null || CurrentUser == "" || CurrentUser == " ")
            {
                //Zaprzeczenie w if'ie nie działa...
            }
            else if (CurrentUser != null || CurrentUser != "" || CurrentUser != " ")
            { 
                using (SqlCommand cmd = new SqlCommand())
                {
                    GridView GridView1 = LoginView3.FindControl("GridView1") as GridView;
                    cmd.CommandText = "select Id, Name from tblFiles WHERE email = @CurrentUser";
                    cmd.Parameters.Add("@CurrentUser", SqlDbType.NVarChar);
                    cmd.Parameters["@CurrentUser"].Value = User.Identity.Name;
                    cmd.Connection = con;

                    con.Open();

                    GridView1.DataSource = cmd.ExecuteReader();
                    GridView1.DataBind();

                    con.Close();
                }
            }
        }
    }

    protected void DownloadFile(object sender, EventArgs e)
    {
        int id = int.Parse((sender as LinkButton).CommandArgument);
        byte[] bytes;
        string fileName, contentType;

        string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "select Name, Data, ContentType from tblFiles where Id=@Id";
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Connection = con;

                con.Open();

                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    sdr.Read();
                    bytes = (byte[])sdr["Data"];
                    contentType = sdr["ContentType"].ToString();
                    fileName = sdr["Name"].ToString();
                }

                con.Close();
            }
        }

        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = contentType;
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
        Response.BinaryWrite(bytes);
        Response.Flush();
        Response.End();
    }

    protected void Upload(object sender, EventArgs e)
    {
        FileUpload FileUpload1 = LoginView3.FindControl("FileUpload1") as FileUpload;
        string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string contentType = FileUpload1.PostedFile.ContentType;
        string email = User.Identity.Name;

        using (Stream fs = FileUpload1.PostedFile.InputStream)
        {
            using (BinaryReader br = new BinaryReader(fs))
            {
                byte[] bytes = br.ReadBytes((Int32)fs.Length);
                string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

                using (SqlConnection con = new SqlConnection(constr))
                {
                    string query = "insert into tblFiles values (@Name, @ContentType, @Data, @email)";
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@Name", filename);
                        cmd.Parameters.AddWithValue("@ContentType", contentType);
                        cmd.Parameters.AddWithValue("@Data", bytes);
                        cmd.Parameters.AddWithValue("@email", email);

                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }
        }

        Response.Redirect(Request.Url.AbsoluteUri);
    }
}

This is my table in the SQL Server database:

CREATE TABLE [dbo].[tblFiles] 
(
    [id]          INT             IDENTITY (1, 1) NOT NULL,
    [Name]        VARCHAR (50)    NOT NULL,
    [ContentType] NVARCHAR (200)  NOT NULL,
    [Data]        VARBINARY (MAX) NOT NULL,
    [email]       VARCHAR (50)    NOT NULL
);

How to download and view images from SQL Server table?

This code doesn't work:

foreach (string strfile in Directory.GetFiles(Server.MapPath("~/Files")))

and

public FileContentResult GetFile(int id)
    {
        SqlDataReader rdr;
        byte[] fileContent = null;
        string mimeType = "";
        string fileName = "";

        const string connect = @"Server=your_servername;Database=your_database;User 
        Id=user_id;password=user_password;";

        using (var conn = new SqlConnection(connect))
        {
            var qry = "SELECT FileContent, MimeType, FileName FROM FileStore WHERE ID 
            = @ID";
            var cmd = new SqlCommand(qry, conn);
            cmd.Parameters.AddWithValue("@ID", id);

            conn.Open();
            rdr = cmd.ExecuteReader();

            if (rdr.HasRows)
            {
                rdr.Read();

                fileContent = (byte[])rdr["FileContent"];
                mimeType = rdr["MimeType"].ToString();
                fileName = rdr["FileName"].ToString();
            }
        }

        return File(fileContent, mimeType, fileName);
    }

Answer 1

You will have to use an HttpHandler in order to transform back your byte array.

    public class ImageHandler : IHttpHandler
    {
      public void ProcessRequest(HttpContext context)
      {
         int id = context.Request.QueryString.Get("ID");
         SqlDataReader rdr;
         byte[] fileContent = null;
         const string connect = @"Server=your_servername;Database=your_database;User 
         Id=user_id;password=user_password;";

         using (var conn = new SqlConnection(connect))
         {
            var qry = "SELECT FileContent, MimeType, FileName FROM FileStore WHERE ID = @ID";
            var cmd = new SqlCommand(qry, conn);
            cmd.Parameters.AddWithValue("@ID", id);
            conn.Open();
            rdr = cmd.ExecuteReader();

            if (rdr.HasRows)
            {
              rdr.Read();
              context.Response.Clear();
              context.Response.ContentType = rdr["MimeType"].ToString();
              context.Response.BinaryWrite((byte[])rdr["FileContent"]);
              context.Response.End();
            }
         }
      }

      public bool IsReusable
      {
        get
        {
          return true;
        }
      }
    }

Once your image handler is created, register it in your web.config file.

<configuration>
  . . .
  <system.webServer>
    . . .
    <handlers>
      <add name="ImageHandler" verb="*" path="*.ashx" type="Assembly.ImageHandler,Assembly" />
      . . .
    </handlers>
  </system.webServer>
  . . .
</configuration>

Then add the route to ignore in order to handle the file

routes.IgnoreRoute("{handler}.ashx");

Finally, you will be able to display the image by calling it from your html

<img src="~/ImageHandler.ashx?ID=@YourItemId"/>

Regards.