In this article I will explain with an example, how to upload (insert) and download (retrieve) Files from SQL Database using Generic Handler and jQuery AJAX in ASP.Net with C# and VB.Net.
The Generic Handler will insert and retrieve files from SQL Server database in ASP.Net.
The Generic Handler will be called using jQuery AJAX and the file will be uploaded to Database with Progress Bar using HTML5 Form Data and XmlHttpRequest (XHR).
 
 
Database
This article makes use of a table named tblFiles whose schema is defined as follows.
Upload (Insert) and Download (Retrieve) Files from Database using Generic Handler and jQuery AJAX in ASP.Net
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 
Adding Generic Handler
You will need to add a new Generic Handler (ASHX) file using Add New Item Dialog of Visual Studio as shown below.
Upload (Insert) and Download (Retrieve) Files from Database using Generic Handler and jQuery AJAX in ASP.Net
 
 
Building the Generic Handler
The Generic Handler performs the following operations.
Inserting File
The uploaded File is read from the Request.Files collection.
The uploaded File is converted into an Array of Bytes using BinaryReader class and finally is inserted into the database table.
Then ID and Name of the uploaded file is returned back to the Client in JSON format.
 
Downloading File from Database using ID
The FileId is received as QueryString parameter. The FileId value is used to fetch the record of the inserted File in the tblFiles Table.
Once the record is fetched, the File data is read and it is downloaded to the Client as Attachment using the HttpResponse. 
 
Getting List of Files
The records of Files are fetched from the database and the List is returned to Client in JSON format.
C#
<%@ WebHandler Language="C#" Class="Handler" %>
 
using System;
using System.IO;
using System.Net;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.Script.Serialization;
 
public class Handler : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        int fileId = 0;
        int.TryParse(context.Request.QueryString["FileId"], out fileId);
 
        //Check if Request is to Upload the File.
        if (context.Request.Files.Count > 0)
        {
            HttpPostedFile postedFile = context.Request.Files[0];
 
            //Convert the File data to Byte Array.
            byte[] bytes;
            using (BinaryReader br = new BinaryReader(postedFile.InputStream))
            {
                bytes = br.ReadBytes(postedFile.ContentLength);
            }
 
            //Insert the File to Database Table.
            string fileName = Path.GetFileName(postedFile.FileName);
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                string sql = "INSERT INTO tblFiles VALUES(@Name, @ContentType, @Data)";
                sql += " SELECT SCOPE_IDENTITY()";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@Name", fileName);
                    cmd.Parameters.AddWithValue("@ContentType", postedFile.ContentType);
                    cmd.Parameters.AddWithValue("@Data", bytes);
                    conn.Open();
                    fileId = Convert.ToInt32(cmd.ExecuteScalar());
                    conn.Close();
                }
            }
 
            //Send File details in a JSON Response.
            string json = new JavaScriptSerializer().Serialize(
                new
                {
                    id = fileId,
                    name = fileName
                });
            context.Response.StatusCode = (int)HttpStatusCode.OK;
            context.Response.ContentType = "text/json";
            context.Response.Write(json);
            context.Response.End();
        }
        else if (fileId > 0) //Check if Request is to Download the File.
        {
            byte[] bytes;
            string fileName, contentType;
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@Id";
                    cmd.Parameters.AddWithValue("@Id", fileId);
                    cmd.Connection = conn;
                    conn.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        sdr.Read();
                        bytes = (byte[])sdr["Data"];
                        contentType = sdr["ContentType"].ToString();
                        fileName = sdr["Name"].ToString();
                    }
                    conn.Close();
                }
            }
 
            context.Response.StatusCode = (int)HttpStatusCode.OK;
            context.Response.Clear();
            context.Response.Buffer = true;
            context.Response.Charset = "";
            context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
 
            //Set the File Content Type.
            context.Response.ContentType = contentType;
 
            //Set the Content Disposition Header Value and FileName.
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
 
            //Set the Response Content.
            context.Response.BinaryWrite(bytes);
            context.Response.Flush();
            context.Response.End();
        }
        else //Request to get all Files.
        {
            //Fetch all Files from Database Table.
            List<object> files = new List<object>();
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT Id, Name FROM tblFiles", conn))
                {
                    conn.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            files.Add(new
                            {
                                id = sdr["Id"],
                                name = sdr["Name"]
                            });
                        }
                    }
                    conn.Close();
                }
            }
 
            //Send File data in JSON format for Download.
            string json = new JavaScriptSerializer().Serialize(files);
            context.Response.StatusCode = (int)HttpStatusCode.OK;
            context.Response.ContentType = "text/json";
            context.Response.Write(json);
            context.Response.End();
        }
    }
 
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}
 
VB.Net
<%@ WebHandler Language="VB" Class="Handler" %>
 
Imports System
Imports System.IO
Imports System.Net
Imports System.Web
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Web.Script.Serialization
 
Public Class Handler : Implements IHttpHandler
   
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim fileId As Integer = 0
        Integer.TryParse(context.Request.QueryString("FileId"), fileId)
 
        'Check if Request is to Upload the File.
        If context.Request.Files.Count > 0 Then
            Dim postedFile As HttpPostedFile = context.Request.Files(0)
 
            'Convert the File data to Byte Array.
            Dim bytes As Byte()
            Using br As BinaryReader = New BinaryReader(postedFile.InputStream)
                bytes = br.ReadBytes(postedFile.ContentLength)
            End Using
 
            'Insert the File to Database Table.
            Dim fileName As String = Path.GetFileName(postedFile.FileName)
            Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using conn As SqlConnection = New SqlConnection(constr)
                Dim sql As String = "INSERT INTO tblFiles VALUES(@Name, @ContentType, @Data)"
                sql += " SELECT SCOPE_IDENTITY()"
 
                Using cmd As SqlCommand = New SqlCommand(sql, conn)
                    cmd.Parameters.AddWithValue("@Name", fileName)
                    cmd.Parameters.AddWithValue("@ContentType", postedFile.ContentType)
                    cmd.Parameters.AddWithValue("@Data", bytes)
                    conn.Open()
                    fileId = Convert.ToInt32(cmd.ExecuteScalar())
                    conn.Close()
                End Using
            End Using
 
            'Send File details in a JSON Response.
            Dim json As String = New JavaScriptSerializer().Serialize(New With {
                .id = fileId,
                .name = fileName
            })
            context.Response.StatusCode = CInt(HttpStatusCode.OK)
            context.Response.ContentType = "text/json"
            context.Response.Write(json)
            context.Response.End()
           
        ElseIf fileId > 0 Then 'Check if Request is to Download the File.
            Dim bytes As Byte()
            Dim fileName, contentType As String
            Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using conn As SqlConnection = New SqlConnection(constr)
                Using cmd As SqlCommand = New SqlCommand()
                    cmd.CommandText = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@Id"
                    cmd.Parameters.AddWithValue("@Id", fileId)
                    cmd.Connection = conn
                    conn.Open()
                    Using sdr As SqlDataReader = cmd.ExecuteReader()
                        sdr.Read()
                        bytes = CType(sdr("Data"), Byte())
                        contentType = sdr("ContentType").ToString()
                        fileName = sdr("Name").ToString()
                    End Using
                    conn.Close()
                End Using
            End Using
 
            context.Response.StatusCode = CInt(HttpStatusCode.OK)
            context.Response.Clear()
            context.Response.Buffer = True
            context.Response.Charset = ""
            context.Response.Cache.SetCacheability(HttpCacheability.NoCache)
 
            'Set the File Content Type.
            context.Response.ContentType = contentType
           
            'Set the Content Disposition Header Value and FileName.
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" & fileName)
           
            'Set the Response Content.
            context.Response.BinaryWrite(bytes)
            context.Response.Flush()
            context.Response.End()
           
        Else 'Request to get all Files.
           
            'Fetch all Files from Database Table.
            Dim files As List(Of Object) = New List(Of Object)()
            Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using conn As SqlConnection = New SqlConnection(constr)
                Using cmd As SqlCommand = New SqlCommand("SELECT Id, Name FROM tblFiles", conn)
                    conn.Open()
                    Using sdr As SqlDataReader = cmd.ExecuteReader()
                        While sdr.Read()
                            files.Add(New With {
                                .id = sdr("Id"),
                                .name = sdr("Name")
                            })
                        End While
                    End Using
                    conn.Close()
                End Using
            End Using
 
            'Send File data in JSON format for Download.
            Dim json As String = New JavaScriptSerializer().Serialize(files)
            context.Response.StatusCode = CInt(HttpStatusCode.OK)
            context.Response.ContentType = "text/json"
            context.Response.Write(json)
            context.Response.End()
        End If
    End Sub
 
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property
 
End Class
 
 
HTML Markup
The HTML Markup consists of a Form with an HTML FileUpload element and a Button. Below the Form there’s an HTML5 Progress element for displaying the progress of the uploading File.
There’s also an HTML Table which will be used to display the List of uploaded files.
Uploading File
When the Upload button is clicked, an AJAX call is made to the Generic Handler using jQuery.
The data of the selected File is read using HTML5 FormData and the File is uploaded to the Database using XmlHttpRequest (XHR).
A Progress event handler is attached to the XmlHttpRequest (XHR), which displays the progress of the File being uploaded using the HTML5 Progress element.
Finally, the received ID and Name of the File is displayed in the HTML Table.
 
Getting File using ID
When the Download link is clicked, the Generic Handler is called and the ID of the File is passed as parameter.
The File is downloaded as Attachment in browser.
 
Getting List of Files
Inside the jQuery document ready event handler, the Generic Handler is called and a loop is executed over the received File records.
Inside the loop, the ID and Name values of each record is fetched and a row is added to the HTML Table.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server" enctype="multipart/form-data">
    <input type="file" name="postedFile" />
    <input type="button" id="btnUpload" value="Upload" />
    <progress id="fileProgress" style="display: none"></progress>
    <hr />
    <table id="tblFiles" cellpadding="0" cellspacing="0">
        <tr>
            <th style="width: 50px">File ID</th>
            <th style="width: 120px">File Name</th>
            <th style="width: 80px">Download</th>
        </tr>
        <tr>
            <td></td>
            <td></td>
            <td></td>
        </tr>
    </table>
 
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            GetFiles();
        });
        function GetFiles() {
            $.ajax({
                type: "POST",
                url: "Handler.ashx",
                data: '{}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (files) {
                    $(files).each(function(){
                        AppendRow(this.id, this.name);
                    });
                },
                failure: function (r) {
                    alert(r.responseText);
                },
                error: function (r) {
                    alert(r.responseText);
                }
            });
        };
 
        function AppendRow(id, name) {
            var row = $("#tblFiles tbody tr:last-child");
 
            //Remove if the row is dummy row.
            if (row.find("td:empty").length > 0) {
                row.remove();
            }
 
            row = row.clone();
            $("td", row).eq(0).html(id);
            $("td", row).eq(1).html(name);
            $("td", row).eq(2).html("");
            var a = $("<a />");
            a.html("Download");
            a[0].href = 'Handler.ashx?FileId=' + id;
            $("td", row).eq(2).append(a);
            $("#tblFiles tbody").append(row);
        };
 
        $("body").on("click", "#btnUpload", function () {
            $.ajax({
                url: 'Handler.ashx',
                type: 'POST',
                data: new FormData($('form')[0]),
                cache: false,
                contentType: false,
                processData: false,
                success: function (file) {
                    $("#fileProgress").hide();
                    AppendRow(file.id, file.name);
                },
                xhr: function () {
                    var fileXhr = $.ajaxSettings.xhr();
                    if (fileXhr.upload) {
                        $("progress").show();
                        fileXhr.upload.addEventListener("progress", function (e) {
                            if (e.lengthComputable) {
                                $("#fileProgress").attr({
                                    value: e.loaded,
                                    max: e.total
                                });
                            }
                        }, false);
                    }
                    return fileXhr;
                }
            });
        });
    </script>
    </form>
</body>
</html>
 
 
Screenshot
Upload (Insert) and Download (Retrieve) Files from Database using Generic Handler and jQuery AJAX in ASP.Net
 
 
Downloads
Download Code