ASP.NET MVC List of attachments (from binary data)

Question

Using ASP.NET MVC and SQL Server 2016. I have attachments which stored as binary data in SQL Server.

Table with data looks like:

 attachment_ID  | attachment_GUID  | attachment_data | attachment_name|
  --------------|------------------|-----------------|----------------|
       211      |   A893C2Z1-4C0   |  0x255044462... |    file1.doc   |
       212      |   A893C2R5-4F0   |  0x255044455... |    file5.pdf   | 

I need to display list of this attachments (names), as hyperlinks in html page. So if I click on attachment name it should start the downloading process.

Need help with an ASP.NET MVC controller.

I have controller which downloads a file directly:

using System;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.Mvc;
using System.Collections.Generic;

namespace Project.SERVER.Controllers
{
    public class AttachmenttoHTMLController : Controller
    {
        #region View attachments
        [HttpGet]
        public ActionResult Get()
        {
            SqlConnection connection = Project.SERVER.Classes.INT_DataBase.getConnection();
            SqlCommand command = new SqlCommand("SELECT * FROM AllAttachments WHERE document_GUID='F3A2AC32-D98D'", connection);
            command.Dispose();

            SqlDataAdapter sqlDtAdptr = new SqlDataAdapter(command);
            System.Data.DataTable result = new System.Data.DataTable();
            result.Dispose();
            sqlDtAdptr.Fill(result);
            sqlDtAdptr.Dispose();
            connection.Dispose();
            connection.Close();

            if (result.Rows.Count > 0 && !System.Convert.IsDBNull(result.Rows[0]["attachment_data"]))
            {
                byte[] file = (byte[])result.Rows[0]["attachment_data"];
                Response.ContentType = result.Rows[0]["attachment_contentType"].ToString();

                return File(file, Response.ContentType, result.Rows[0]["attachment_fileName"].ToString());
            }
            else return new EmptyResult();
        }
        #endregion
    }
}

What should I add to the code to achieve controller for attachment list with option to download when clicked?

Answer 1

You have to save first the binary data into Image Format into your directory project.

Use FileResult

HTML:

<div>
    @Html.ActionLink("Download", "Download", "Home");
</div>

HomeController:

 public FileResult Download()
    {
        String path = HostingEnvironment.ApplicationPhysicalPath + "Image\\Capture.PNG";
        string fname= Path.GetFileName(path);
        byte[] fileBytes = System.IO.File.ReadAllBytes(path);
        string fileName = fname;
        return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);

    }

Assuming that you have a list in your model

 List<Attachment> lst = new List<Attachment>();

This list is the content of your data from your Database and saved the byte to imageformat into you project folder.

Your code from above should fall in here saved the byte with a filename even id.PNG or any. Adding lst.id and lst.filename for html purpose.

 public class Attachment
        {
            public int id { get; set; }
            public string filename { get; set; }
        }

Your HTML looks like:

<table> 
@{
            if (Models.GetAttachment.lst.Count > 0)
            {
                  for (int m = 0; m < Models.GetAttachment.lst.Count; m++)
                {
                <tr>

                    <td>
                        @Html.ActionLink("Download", "Download", new { id = Models.GetAttachment.lst.Coun[m].id })
                    </td>
                </tr>
                }
            }


  }
</table>

HomeController With id:

 public FileResult Download(int?id)
        {
            String path = HostingEnvironment.ApplicationPhysicalPath + "Image\\Capture.PNG";
            string fname= Path.GetFileName(path);
            byte[] fileBytes = System.IO.File.ReadAllBytes(path);
            string fileName = fname;
            return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);

        }

the purpose of id to locate the file in your project directory for every user click.