asp.netsql-serverasp.net-mvclinqvarbinarymax

How to retrieve file from Database of any type(pdf,xlx,jp or png) and make it download-able on webpage?


I am using SQL Server Management Studio. In my table there is a column with datatype varbinary(max). I have successfully uploaded the file of specific format(pdf,xlx,jp or png) to my table. But now I am facing difficulty to retrieve it. I am using ASP.NET. I need to make a controller which can get the file from database through LINQ and make it download to the user's computer.


Solution

  • There are bunch of tutorials on this problem containing time-taking answers which may or may not deal with the current scenario. So here is what you need to do in steps:

    1. In JavaScript send the data collected data to controller by following steps:
    var file = document.getElementById("---id of the insert tag where document is loaded").files[0];
    

    Then use FormData to attach the file (variable) to the form.

    formData = new FormData();
    formData.append("file",file);     
    

    The formData work as a key:value pair when we send data to controller. Now send the data to controller through ajax:

    $.ajax({
        url: "--url--",
        data: formData,
        type: "POST"
    ...
    

    Now in controller mentioned in URL, make sure it contains the parameter with the same name file as mentioned in formData.append("file",file); . The datatype of the file will be HttpPostedFileBase. The controller will look like this:

     [HttpPost]
     public ActionResult SaveFile( HttpPostedFileBase file)
      {
    //The below mentioned code will convert file in byte[] which can be transferred to database
           using (Stream inputStream = file.InputStream)
            {
              MemoryStream memoryStream = inputStream as MemoryStream;
              if (memoryStream == null)
               {
                  memoryStream = new MemoryStream();
                  inputStream.CopyTo(memoryStream);
               }
                            byteValue = memoryStream.ToArray();// datatype = byte[]
            }
      }         
    

    Now to retrieve file from database by creating file on server and then sending that file further to the user by providing the path of file on server.

    filePath = Path.Combine(HttpContext.Current.Server.MapPath("-----Location where you want to save file----"),"---Name of file with extention---");//filePath will create the path. Note: it's just a path.
    MultiPurpose.DeleteExistingFile(filePath);// It will delete pre-existing file if already present on the path with same name
    System.IO.File.WriteAllBytes(filePath, db.file);// It will create the file present on database with datatype 'byte[]'
    return File(filePath ,Content-Type, "--you can mention new file name here---"); // This will download the file on user's pc with the file name mentioned in third parameter.