Upload and Download Files From Database in Web API

Introduction

This article explains how to upload files in the database and download files from the database in the ASP.NET Web API. This file can be a text file or an image file.

The procedure for creating the application is as in the following.

Step 1

First we create a database in SQL.

• Open SQL Server 2012.
• Select "New Query" and create the database and table.

The following commands are used for creating the database and table:

create database Demo

use Demo

create table Datafile(ID int IDENTITY NOT NULL,Filerecord image NOT NULL ,Filetype varchar(50) NOT NULL,Name varchar(50) NOT NULL)

 

Step 2

Create the Web API application:

• Start Visual Studio 2012.

• From the start window select "New Project".

• In the Template window select "Installed" -> "Visual C#" -> "Web".

• Select "ASP.NET MVC4 Web Application" and click the "OK" button.

• From the "MVC4 Project" window select "Web API".

• Click the "OK" button.

Step 3

Open the "HomeController" file and write the code for uploading the file in the database. This file exists:

• In the "Solution Explorer".

• Select "Controller" -> "HomeController".

Add the following code:

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace FileUploadDatabase.Controllers

{

    public class HomeController : Controller

    {

         public bool Infile(HttpPostedFileBase imgfile)

 

        {

            return (imgfile != null && imgfile.ContentLength > 0) ? true : false;

        }

        public ActionResult Index()

        {

            foreach (string Save in Request.Files)

            {

                if (!Infile(Request.Files[Save])) continue;

                string fileType = Request.Files[Save].ContentType;

                Stream file_Strm = Request.Files[Save].InputStream;

                string file_Name = Path.GetFileName(Request.Files[Save].FileName);

                int fileSize = Request.Files[Save].ContentLength;

                byte[] fileRcrd = new byte[fileSize];

                file_Strm.Read(fileRcrd, 0, fileSize);

                const string connect = @"Server=.;Database=Demo; User Id=sa; password=wintellect;";

                using (var conn = new SqlConnection(connect))

                {

                    var qry = "INSERT INTO Datafile (Filerecord, Filetype, Name)VALUES (@Filerecord, @Filetype, @Name)";

                    var cmd = new SqlCommand(qry, conn);

                    cmd.Parameters.AddWithValue("@Filerecord", fileRcrd);

                    cmd.Parameters.AddWithValue("@Filetype", fileType);

                    cmd.Parameters.AddWithValue("@Name", file_Name);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                }

            }

            return View();

        }

        public ActionResult DownloadImage()

        {

            const string connect = @"Server=.;Database=Demo;User id=sa;password=wintellect;";

            List<string> fileList = new List<string>();

            using (var con = new SqlConnection(connect))

            {

                var query = "SELECT Filerecord, Filetype,Name FROM Datafile";

                var cmd = new SqlCommand(query, con);

                con.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

                    fileList.Add(rdr["Name"].ToString());

                }

            }

            ViewBag.Images = fileList;

            return View();

        }

        public FileContentResult GetFile(int id)

        {

            SqlDataReader rdr;

            byte[] fileContent = null;

            string fileType = "";

            string file_Name = "";

            const string connect = @"Server=.;Database=Demo;User id=sa;password=wintellect;";

            using (var con = new SqlConnection(connect))

            {

                var query = "SELECT Filerecord, Filetype, Name FROM Datafile WHERE ID = @ID";

                var cmd = new SqlCommand(query, con);

                cmd.Parameters.AddWithValue("@ID", id);

                con.Open();

                rdr = cmd.ExecuteReader();

                if (rdr.HasRows)

                {

                   rdr.Read();

                    fileContent = (byte[])rdr["Filerecord"];

                    fileType = rdr["Filetype"].ToString();

                    file_Name = rdr["Name"].ToString();

                }

            }

            return File(fileContent, fileType, file_Name);

        }

    }

}

   

 Step 4

Now we create a "MVC4 View Page (ASPX)" named "Index.aspx".

• In the "Solution Explorer".

• Right-click on "Home" then select "Add" -> "New Item".

• Select "Installed" -> "Visual C#" -> "Web" -> "MVC4 View Page (ASPX)".

• Click the "Add" button.

Add the following code:

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage" %> 

<!DOCTYPE html>

<html>

<head runat="server">

    <meta name="viewport" content="width=device-width" />

    <title></title>

</head>

<body>

    <div>

        <%

            using (Html.BeginForm("", "home", FormMethod.Post, new { enctype = 

           "multipart/form-data" }))

            {%>

        <input type="file" name="FileUpload1" /><br />

        <input type="file" name="FileUpload2" /><br />

        <input type="file" name="FileUpload3" /><br />

        <input type="submit" name="Submit" id="Submit" value="SendToDatabase" /><br />

        <% }%>

        <h4>

            <a href="/Home/DownloadImage">DownLoad Image from Database</a></h4>

    </div>

</body>

</html>
 

The "Html.Bigenform" method opens the <form> tag. When the form is submited this request is handled by the action method.

 Step 5

Create one more View Page "DownloadImage.aspx". Follow the same procedure as for Step 4:

Add the following code:

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage" %>

<!DOCTYPE html>

<html>

<head runat="server">

    <meta name="viewport" content="width=device-width" />

    <title></title>

</head>

<body>

    <div>

        <%int Rcrd = 1; %>

        <% foreach (string file in ViewBag.Images)

           { %>

        <h4>

            <%: Html.ActionLink(file,"GetFile/"+Rcrd++) %></h4>

        <%} %>

        <%if (Rcrd == 1)

          { %>

        <h2>

            There is No file for Downloading from the Database</h2>

        <%} %>

    </div>

</body>

</html>

 

In the preceding we use the "Html.ActionLink" helper. This method is not linked to the view, it creates the link with the Controller Action.

Step 6

Execute the application, press "F5":

Browse the files and click on the "Send" button. These files are stored in the database.

Click on the "Download Image from the Database" link then display files, click on the file for downloading.

Display a dialog box, click on "Save".

Step 7

When you use the "Select" command for selecting the record from the table, all the files are displayed in the database. The command is as follows:

select * from Datafile