MENU

Download Binary Data From SQL Using ASP.NET MVC

Filed Under: , , , , on
This articles explains how to download binary data from SQL database using ASP.NET MVC, please read my previous article which shows how to upload files into the database in binary format.
Let's consider we have following SQL table having the binary format data.


Now let's create the front end application step by step using ASP.NET MVC to download the files 

Step 1: Create an ASP.NET MVC Application

  1. "Start", followed by "All Programs" and select "Microsoft Visual Studio 2015".
  2. Click "File", followed by "New" and click "Project". Select "ASP.NET Web Application Template", provide the Project a name as you wish and click OK. After clicking, the following Window will appear:


Step 2: Create Model Class

Now, let us create the model class file, named FileDetailsModel.cs, by right clicking on Models folder and define the following properties  as:
public class FileDetailsModel  
   {  
       public int Id { get; set; }  
       [Display(Name = "Uploaded File")]  
       public String FileName { get; set; }  
       public byte[] FileContent { get; set; }  
  
  
   }

Step 3 : Create Stored Procedure

Now Create the stored procedure to view the uploaded files using following script as
CREATE Procedure [dbo].[GetFileDetails]  
(  
@Id int=null  
)  
as  
begin  
select Id,FileName,FileContent from FileDetails  
where Id=isnull(@Id,Id)  
End

Step 4 : Add Controller Class

Now, let us add ASP.NET MVC controller, as shown in the screenshot, given below:



After clicking Add button, it will show the Window. Specify the Controller name as Home with suffix Controller. Now, let's modify the default code of Home controller . After modifying the code of Homecontroller class, the code will look like:

HomeController.cs
using System;  
    using System.Collections.Generic;  
    using System.IO;  
    using System.Linq;  
    using System.Web;  
    using System.Web.Mvc;  
    using Dapper;  
    using System.Configuration;  
    using System.Data.SqlClient;
    using FileUploadDownLoadInMVC.Models;  
    using System.Data;  
      
    namespace FileUploadDownLoadInMVC.Controllers  
    {  
        public class HomeController : Controller  
        {  
             
            #region Upload Download file  
            public ActionResult Index()  
            {  
                return View();  
            }  
                   
            [HttpGet]  
            public FileResult DownLoadFile(int id)  
            {  
      
      
                List<FileDetailsModel> ObjFiles = GetFileList();  
      
                var FileById = (from FC in ObjFiles  
                                where FC.Id.Equals(id)  
                                select new { FC.FileName, FC.FileContent }).ToList().FirstOrDefault();  
      
                return File(FileById.FileContent, "application/pdf", FileById.FileName);  
      
            }  
            #endregion  
     
            #region View Uploaded files  
            [HttpGet]  
            public PartialViewResult FileDetails()  
            {  
                List<FileDetailsModel> DetList = GetFileList();  
      
                return PartialView("FileDetails", DetList);  
      
      
            }  
            private List<FileDetailsModel> GetFileList()  
            {  
                List<FileDetailsModel> DetList = new List<FileDetailsModel>();  
      
                DbConnection();  
                con.Open();  
                DetList = SqlMapper.Query<FileDetailsModel>(con, "GetFileDetails", commandType: CommandType.StoredProcedure).ToList();  
                con.Close();  
                return DetList;  
            }  
     
            #endregion       
            #region Database connection  
      
            private SqlConnection con;  
            private string constr;  
            private void DbConnection()  
            {  
                 constr =ConfigurationManager.ConnectionStrings["dbcon"].ToString();  
                 con = new SqlConnection(constr);  
      
            }  
            #endregion  
        }  
    }
The preceding code snippet explained everything to upload  PDF file into database , I hope you have followed the same.

 Step 5:  Create View

Right click on View folder of the created Application and create view named Index and Partial view FileDetails , The code snippet of the view's is look like as following.

Index.cshtml 

@{  
    ViewBag.Title = "www.compilemode.com";  
}  
  
@using (Html.BeginForm())  
{  
    @Html.AntiForgeryToken()    
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10 text-success">  
                @ViewBag.FileStatus  
            </div>  
        </div>  
  
        <div class="form-group">  
            <div class="col-md-8">  
                @Html.Action("FileDetails", "Home")  
  
            </div>  
        </div>  
    </div>  
}  
<script src="~/Scripts/jquery-1.10.2.min.js"></script>  
<script src="~/Scripts/jquery.validate.min.js"></script>  
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

FileDetails.cshtml

 
@model IEnumerable<FileUploadDownLoadInMVC.Models.FileDetailsModel>  
<table class="table table-bordered">  
    <tr>  
        <th class="col-md-4">  
            @Html.DisplayNameFor(model => model.FileName)  
        </th>  
          
        <th class="col-md-2"></th>  
    </tr>  
  
@foreach (var item in Model) {  
    <tr>  
        <td>  
            @Html.DisplayFor(modelItem => item.FileName)  
        </td>  
          
        <td>  
            @Html.ActionLink("Downlaod", "DownLoadFile", new { id=item.Id })   
             
        </td>  
    </tr>  
}  
  
</table>


Step 6 - Run the Application

After running the Application, the UI of the Application will look like as follows




Now click on download button , then it will shows the following popup




Choose to open or save the file , I have chosen to open the files , the contents of the files will be look like as follows




I hope, from the preceding examples, you have learned, how to download binary formatted PDF files from database.

Note
  • This article used dapper ORM to interact with the database. Thus, you need to install dapper ORM into the Application. If you don't know how to install dapper ORM in MVC, watch the video, using the link, given below
  • Makes changes in web.config file connectionstring tag, based on your database location and configuration.
  • Since this is a demo, it might not be using the proper standards. Thus, improve it, depending on your skills.
Summary

I hope, this article is useful for all the readers. If you have any suggestions, please contact me.

    Post a Comment

    www.CodeNirvana.in

    Protected by Copyscape
    Copyright © Compilemode