Uploading Downloading PDF Files In  ASP.NET MVC Using SQL Server

Follow Github Accunt for Complete Code

https://gist.github.com/Dhan777/cf360ff93cbe423581b0f0aa9091fdeb

Many times, we need to work with the file and storing the physical files on the Server, which is very difficult because it will consume lots of memory of the Server. Thus, in this article, we will learn, how to upload the files in the binary format into the database and download from the database with the help of ASP.NET MVC, using FileResult. Thus, let's learn step by step so the beginners can also understand..

Step 1 - Create MVC Application.

1. create a New ASPMVC4 Application Named "MvcApplication5"
2. Go to File->New->Project->ASP.NET MVC4 Web Application .Then Provide the Project Name as you wish.After clicking, the following window will appear :

Step 2 - Create Model Class

Now, let us create the model class file, named EmployeeModel.cs, by right clicking on Models folder.

Go to Solution Explorer -> Right Click on Models Folder ->Add -> Class ->Write the Name of class EmployeeModel.cs class :

The code snippet of EmployeeModel.cs will look like-

1. public class EmployeeModel  

1.    {     
2.          public int ID                { get; set; }

1.          public string FileName { get; set; }

1.         public byte [] FileContent { get; set; }

1.         public HttpPostedFileBase Files { get; set; }

1.    }

Step 3 - Create Table and Stored Procedure

Now, create the stored procedure and the table to store the uploaded files in binary format and display back to the user's view. Use the script, given below, to create the table named FileDetails as-

1. CREATE TABLE [dbo].[FileDetails](  
2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
3.     [FileName] [varchar](60) NULL,  
4.     [FileContent] [varbinary](max) NULL  
5. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  

As mentioned in the preceding table script, we have created three columns Id, which is to identify the files unique key. FileName to store uploaded file name and FileContent to store the uploaded file contents in the binary format.

Create the stored procedure to insert the file details, using the script, given below-

1. Create Procedure [dbo].[AddFileDetails]  
2. (  
3. @FileName varchar(60),  
4. @FileContent varBinary(Max)  
5. )  
6. as  
7. begin  
8. Set NoCount on  
9. Insert into FileDetails values(@FileName,@FileContent)  
10.   
11. End  

To get the uploaded file details, use the code, given below-

1. CREATE Procedure [dbo].[GetFileDetails]  
2. (  
3. @Id int=null  
4. )  
5. as  
6. begin   
7. select Id,FileName,FileContent from FileDetails  
8. where Id=isnull(@Id,Id)  
9. End  

Step 4 - Add DataLayer Class

Now, create the DataLayer Class  to Create the Connection With DataBase and Methods to Upload and Download Files From Database. Use the script, given below, to create the DataLayer Class named , Details as-

2. using System;
3. using System.Collections.Generic;
4. using System.Linq;
5. using System.Web;
6. using System.Data.SqlClient;
7. using System.Configuration;
8. using Dapper;
9. using System.Data;
10. using System.Web.Mvc;
11. 
    
12. namespace MvcApplication5.Models
13. {
14.     public class DataLayer
15.     {
16.         SqlConnection Con;    string s_Con;
17. 
    
18.         public DataLayer()
19.         {
20.             Connectin();
21.         }
22. 
    
23.         private void Connectin()
24.         {
25.             s_Con = ConfigurationManager.ConnectionStrings["Db"].ToString();
26. 
    
27.             Con = new SqlConnection(s_Con);
28.             if (Con.State == System.Data.ConnectionState.Open)
29.             {
30.                 Con.Close();
31.             }
32.             Con.Open();
33.         }
34. 
    
35.         public List<EmployeeModel> GetFileList()
36.         {
37.            List<EmployeeModel> DetList = new List<EmployeeModel>();
38.             DetList = SqlMapper.Query<EmployeeModel>(Con, "GetFileDetails", commandType: CommandType.StoredProcedure).ToList();
39.             return DetList;
40.         }
41. 
    
42.         public bool SaveFileDetails(EmployeeModel objDet)
43.         {
44.             try
45.             {
46.                 DynamicParameters Parm = new DynamicParameters();
47.                 Parm.Add("@FileName", objDet.FileName);
48.                 Parm.Add("@FileContent", objDet.FileContent);
49.                 Con.Execute("AddFileDetails", Parm, commandType: System.Data.CommandType.StoredProcedure);
50.                 return true;
51.             }
52.             catch (Exception E) { return false; }
53.         }  
54.     }
55. }

Step 5 - 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 in 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-

2. using System;
3. using System.Collections.Generic;
4. using System.Linq;
5. using System.Web;
6. using System.Web.Mvc;
7. using MvcApplication5.Models;
8. using Dapper;
9. using System.IO;
10. namespace MvcApplication5.Controllers
11. {
12.     public class HomeController : Controller
13.     {
14.         public ActionResult Index()
15.         {
16.             return View(new DataLayer().GetFileList());
17.         }
18.         public ActionResult Upload()
19.         {
20.             return View();
21.         }
22.         [HttpPost]
23.         public string Upload(EmployeeModel E)
24.         {
25.             String FileExt = Path.GetExtension(E.Files.FileName).ToUpper();
26.             if (FileExt == ".PDF")
27.             {
28.                 Byte[] data = new byte[E.Files.ContentLength];
29.                 E.Files.InputStream.Read(data, 0, E.Files.ContentLength);
30.                 E.FileName = E.Files.FileName; ;
31.                 E.FileContent = data;
32.                 if (new DataLayer().SaveFileDetails(E))
33.                 {
34.                     return string.Format("<script>alert('File Uploaded');location.assign('/Home/Index');</script>");
35.                 }
36.                 else
37.                 {
38.                     return string.Format("<script>alert('Error Occured');location.assign('/Home/Index');</script>");
39.                 }
40.             }
41.             else
42.             {
43.                 return string.Format("<script>alert('Invalid File ');location.assign('/Home/Index');</script>");  
44.             }
45.         }
46.         [HttpGet]
47.         public FileResult DownLoadFile(int id)
48.         {
49.             List<EmployeeModel> ObjFiles = new DataLayer().GetFileList();
50.             var FileById = (from FC in ObjFiles
51.                             where FC.ID.Equals(id)
52.                             select new { FC.FileName, FC.FileContent }).ToList().FirstOrDefault();
53.             return File(FileById.FileContent, "application/pdf", FileById.FileName);
54.         }  
55.     }
56. }

The preceding code snippet explained everything to upload and download PDF files from the database. I hope, you have followed the same.

Step 6 - Create strongly typed View

Right click on View folder of the created Application and create two strongly typed views; one is to upload the files by choosing EmpModel.cs class  and Partial View by choosing EmployeeModel class to display the uploaded files. The code snippet of the view looks like-

FileUpload.cshtml 

1. @model MvcApplication5.Models.EmployeeModel  
2.   
3. @{  
4.     ViewBag.Title = "www.compilemode.com";  
5. }
6. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
7. <script src="~/Scripts/jquery.validate.min.js"></script>  
8. <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>  
9.   
10. @using (Html.BeginForm("FileUpload", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))  
11. {  
12.   
13.     @Html.AntiForgeryToken()  
14.   
15.     <div class="form-horizontal">  
16.         <hr />  
17.         @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
18.         <div class="form-group">  
19.             @Html.LabelFor(model => model.files, htmlAttributes: new { @class = "control-label col-md-2" })  
20.             <div class="col-md-10">  
21.                 @Html.TextBoxFor(model => model.files, "", new { @type = "file", @multiple = "multiple" })  
22.                 @Html.ValidationMessageFor(model => model.files, "", new { @class = "text-danger" })  
23.             </div>  
24.         </div>  
25.         <div class="form-group">  
26.             <div class="col-md-offset-2 col-md-10">  
27.                 <input type="submit" value="Upload" class="btn btn-primary" />  
28.             </div>  
29.         </div>  
30.         <div class="form-group">  
31.             <div class="col-md-offset-2 col-md-10 text-success">  
32.                 @ViewBag.FileStatus  
33.             </div>  
34.         </div>  
35.   
36.         <div class="form-group">  
37.             <div class="col-md-8">  
38.                 @Html.Action("Index", "Home")  
39.   
40.             </div>  
41.         </div>  
42.     </div>  
43. }  
44. 
    

Index.cshtml

1. @model IEnumerable<MvcApplication5.Models.EmployeeModel>
2.   
3. <table class="table table-bordered">  
4.     <tr>  
5.         <th class="col-md-4">  
6.             @Html.DisplayNameFor(model => model.FileName)  
7.         </th>  
8.           
9.         <th class="col-md-2"></th>  
10.     </tr>  
11.   
12. @foreach (var item in Model) {  
13.     <tr>  
14.         <td>  
15.             @Html.DisplayFor(modelItem => item.FileName)  
16.         </td>  
17.           
18.         <td>  
19.             @Html.ActionLink("Downlaod", "DownLoadFile", new { id=item.Id })   
20.              
21.         </td>  
22.     </tr>  
23. }  
24.   
25. </table>   

Step 7 : Add Connection String in Web.config File:

 <add name="db" connectionString="Data Source=DHANANJAY/SQLExpress;User Id=sa;Password=1;Initial Catalog=Demo" providerName="System.Data.SqlClient" />

 Now we have done all coding to upload files .

Step 8 : Now run the application.