Retrieve and display Word Files from database in browser in ASPNet

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

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

The Word (Docx) File will be displayed in Browser using the docx-preview.js JavaScript plugin.

docx-preview.js plugin

The Word (Docx) file will be displayed (rendered) in Browser using docx-preview.js JavaScript plugin.

Note: This library only works for Word 2007 and higher formats (docx) and not the Word 97 – 2003 formats (doc).

The following files JavaScript plugin will be used.

1. docx-preview.js

2. jszip.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 Word (Docx) 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 />

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 Word 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 GetWordDocument(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 GetWordDocument(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 files are inherited for the docx-preview.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 Word file is fetched (which is a BLOB object) is converted into HTML5 File object.

Finally, the docx-preview.js library options are initialized and the Word file is rendered in the Container DIV using the renderAsync function.

$(function () {

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

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

$.ajax({

type: "POST",

url: "Default.aspx/GetWordDocument",

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

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

dataType: "json",

success: function (r) {

//Convert BLOB to File object.

var doc = new File([new Uint8Array(r.d.Data)], r.d.ContentType);

//If Document not NULL, render it.

if (doc != null) {

//Set the Document options.

var docxOptions = Object.assign(docx.defaultOptions, {

useMathMLPolyfill: true

});

//Reference the Container DIV.

var container = document.querySelector("#word-container");

//Render the Word Document.

docx.renderAsync(doc, container, null, docxOptions);

}

});

</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; }

</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 Word (Docx) files, higher value of maxJsonLength property must be set through Web.Config configuration using the system.web.extensions section as shown below.

For more details, please refer my article ASP.Net MVC Error: The length of the string exceeds the value set on the maxJsonLength property.

<system.web.extensions>