Retrieve and display PDF Files from database in browser in ASPNet

In this article I will explain with an example, how to retrieve and display PDF Files from SQL Server database in Browser in ASP.Net using C# and VB.Net.

This article will also illustrate how to insert PDF file in SQL Server Database Table in ASP.Net using C# and VB.Net.

The PDF File will be displayed in Browser using the PDF.js JavaScript plugin.

PDF.js plugin

The PDF file will be displayed (rendered) in Browser using PDF.js JavaScript plugin.

The following files of PDF.js JavaScript plugin will be used.

1. pdf_viewer.min.css

2. pdf.min.js

3. pdf.worker.min.js

Database

This article makes use of a table named tblFiles whose schema is defined as follows.

Note: You can download the database table SQL by clicking the download link below.
Download SQL file

HTML Markup

The following HTML Markup consists of an ASP.Net FileUpload control, a Button and an ASP.Net GridView control.

The GridView control consists of a BoundField column and a TemplateField column consisting of an HTML Anchor Link.

An HTML DIV is placed below the GridView, which will be used for displaying the PDF file retrieved from Database.

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" />

<hr />

<asp:GridView ID="gvFiles" runat="server" AutoGenerateColumns="false">

<asp:BoundField DataField="Name" HeaderText="File Name" />

<asp:TemplateField ItemStyle-HorizontalAlign="Center">

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<hr />

</div>

Namespaces

You will need to import the following namespaces.

using System.IO;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

VB.Net

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Uploading the files and then saving in SQL Server Database table

When the Upload Button is clicked, first the FileName and ContentType (MIME type) is read and then the File data is converted into Byte Array using BinaryReader class.

Then, the FileName, ContentType and the Byte Array are finally inserted into the SQL Server Database Table.

Note: The Content type (MIME type) is very important while downloading the files as it notifies the browser about type of the File.

Finally, the Page is redirected so that the GridView can be refreshed.

protected void Upload(object sender, EventArgs e)

{

string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);

string contentType = FileUpload1.PostedFile.ContentType;

using (Stream fs = FileUpload1.PostedFile.InputStream)

{

using (BinaryReader br = new BinaryReader(fs))

{

byte[] bytes = br.ReadBytes((Int32)fs.Length);

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

string query = "INSERT INTO tblFiles VALUES (@Name, @ContentType, @Data)";

using (SqlCommand cmd = new SqlCommand(query))

{

cmd.Connection = con;

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

cmd.Parameters.AddWithValue("@ContentType", contentType);

cmd.Parameters.AddWithValue("@Data", bytes);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

Response.Redirect(Request.Url.AbsoluteUri);

}

VB.Net

Protected Sub Upload(sender As Object, e As EventArgs)

Dim filename As String = Path.GetFileName(FileUpload1.PostedFile.FileName)

Dim contentType As String = FileUpload1.PostedFile.ContentType

Using fs As Stream = FileUpload1.PostedFile.InputStream

Using br As New BinaryReader(fs)

Dim bytes As Byte() = br.ReadBytes(DirectCast(fs.Length, Long))

Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

Using con As New SqlConnection(constr)

Dim query As String = "INSERT INTO tblFiles VALUES (@Name, @ContentType, @Data)"

Using cmd As New SqlCommand(query)

cmd.Connection = con

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename

cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contentType

cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes

con.Open()

cmd.ExecuteNonQuery()

con.Close()

End Using

Response.Redirect(Request.Url.AbsoluteUri)

End Sub

Displaying the uploaded files from Database Table in ASP.Net GridView

Inside the Page Load event, the GridView is populated with the records from the Database Table.

protected void Page_Load(object sender, EventArgs e)

{

if (!this.IsPostBack)

{

BindGrid();

}

private void BindGrid()

{

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlCommand cmd = new SqlCommand())

{

cmd.CommandText = "SELECT Id, Name FROM tblFiles";

cmd.Connection = con;

con.Open();

GridView1.DataSource = cmd.ExecuteReader();

GridView1.DataBind();

con.Close();

}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

If Not Me.IsPostBack Then

BindGrid()

End If

End Sub

Private Sub BindGrid()

Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

Using con As New SqlConnection(constr)

Using cmd As New SqlCommand()

cmd.CommandText = "SELECT Id, Name FROM tblFiles"

cmd.Connection = con

con.Open()

GridView1.DataSource = cmd.ExecuteReader()

GridView1.DataBind()

con.Close()

End Using

End Sub

WebMethod for Downloading PDF File from Database

The following WebMethod will be called using jQuery AJAX function when the View Anchor Link is clicked inside the GridView.

Inside the WebMethod, the FileId is accepted as parameter and it fetches the File record based on Id field and returns the FileName, ContentType and the Byte Array in an Anonymous object.

[System.Web.Services.WebMethod]

public static object GetPDF(int fileId)

{

byte[] bytes;

string fileName, contentType;

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = 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 = con;

con.Open();

using (SqlDataReader sdr = cmd.ExecuteReader())

{

sdr.Read();

bytes = (byte[])sdr["Data"];

contentType = sdr["ContentType"].ToString();

fileName = sdr["Name"].ToString();

}

con.Close();

}

return new { FileName = fileName, ContentType = contentType, Data = bytes };

}

VB.Net

<System.Web.Services.WebMethod()> _

Public Shared Function GetPDF(ByVal fileId As Integer) As Object

Dim bytes As Byte()

Dim fileName As String, contentType As String

Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

Using con As New SqlConnection(constr)

Using cmd As New SqlCommand()

cmd.CommandText = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id = @Id"

cmd.Parameters.AddWithValue("@Id", fileId)

cmd.Connection = con

con.Open()

Using sdr As SqlDataReader = cmd.ExecuteReader()

sdr.Read()

bytes = DirectCast(sdr("Data"), Byte())

contentType = sdr("ContentType").ToString()

fileName = sdr("Name").ToString()

End Using

con.Close()

End Using

Return New With {.FileName = fileName, .ContentType = contentType, .Data = bytes}

End Function

JavaScript Implementation

First, the JavaScript and CSS files are inherited for the PDF.js JavaScript plugin.

When the View Anchor Link is clicked, the FileId is fetched from the rel attribute and then a jQuery AJAX call is made to the WebMethod.

Inside the Success event handler of the jQuery AJAX function, the Binary Data of the PDF file is fetched and passed to the LoadPdfFromBlob function.

Inside the LoadPdfFromBlob function, first the count of the pages of the PDF are read and then a loop is executed and the RenderPage function is called for each PDF page.

Inside the RenderPage function, a HTML5 Canvas element is created and the PDF page is rendered on the HTML5 Canvas.

$(function () {

$("[id*=gvFiles] .view").click(function () {

var fileId = $(this).attr("rel");

$.ajax({

type: "POST",

url: "Default.aspx/GetPDF",

data: "{fileId: " + fileId + "}",

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function (r) {

LoadPdfFromBlob(r.d.Data);

}

});

var pdfjsLib = window['pdfjs-dist/build/pdf'];

pdfjsLib.GlobalWorkerOptions.workerSrc = 'https://cdnjs.cloudflare.com/ajax/libs/pdf.js/2.6.347/pdf.worker.min.js';

var pdfDoc = null;

var scale = 1; //Set Scale for zooming PDF.

var resolution = 1; //Set Resolution to Adjust PDF clarity.

function LoadPdfFromBlob(blob) {

//Read PDF from BLOB.

pdfjsLib.getDocument({ data: blob }).promise.then(function (pdfDoc_) {

pdfDoc = pdfDoc_;

//Reference the Container DIV.

var pdf_container = document.getElementById("pdf_container");

pdf_container.innerHTML = "";

pdf_container.style.display = "block";

//Loop and render all pages.

for (var i = 1; i <= pdfDoc.numPages; i++) {

RenderPage(pdf_container, i);

}

});

};

function RenderPage(pdf_container, num) {

pdfDoc.getPage(num).then(function (page) {

//Create Canvas element and append to the Container DIV.

var canvas = document.createElement('canvas');

canvas.id = 'pdf-' + num;

ctx = canvas.getContext('2d');

pdf_container.appendChild(canvas);

//Create and add empty DIV to add SPACE between pages.

var spacer = document.createElement("div");

spacer.style.height = "20px";

pdf_container.appendChild(spacer);

//Set the Canvas dimensions using ViewPort and Scale.

var viewport = page.getViewport({ scale: scale });

canvas.height = resolution * viewport.height;

canvas.width = resolution * viewport.width;

//Render the PDF page.

var renderContext = {

canvasContext: ctx,

viewport: viewport,

transform: [resolution, 0, 0, resolution, 0, 0]

};

page.render(renderContext);

});

};

</script>

CSS

The following CSS needs to be added to the HEAD section of the page.

body { font-family: Arial; font-size: 10pt; }

table { border: 1px solid #ccc; border-collapse: collapse; }

table th { background-color: #F7F7F7; color: #333; font-weight: bold; }

table th, table td { padding: 5px; border: 1px solid #ccc; }

#pdf_container { background: #ccc; text-align: center; display: none; padding: 5px; height: 820px; overflow: auto; }

</style>

Setting the Maximum JSON Length

The AJAX call will fail in cases when the File size exceeds the default predefined limit of 2097152 characters i.e. 4 MB (MSDN).

Thus in order to download large PDFs, higher value of maxJsonLength property must be set through Web.Config configuration using the system.web.extensions section as shown below.

<system.web.extensions>