.Net Musings: April 2012

A BLOB (alternately known as a binary large object, basic large object, blob, or BLOb) is a collection of binary data stored as a single entity in a database management system. Blobs are typically images, audio or other multimedia objects, though sometimes binary executable code is stored as a blob. Database support for blobs is not universal.(Source Wikipedia)

There are many ways in which BLOB could be stored in Microsoft SQL Server(2005 or 2008). For further details on this subject, you can refer to the articles here and here.

I will not go into those myriad details in this post. This post here is about showing a simple way to store, update BLOBs in a SQL Server database and and retrieve those objects in an ASP .Net web application.

My plan is to:

Create a simple database with two tables, one for Documents and the other one for Pictures.
Create an abstract repository to work with the tables in this database and then define concrete instants of the abstract repository which will be the layer containing the Create, Read, Update and Delete methods for the individual tables.
Create generic HTTP handlers to write data from the binary objects, through which either data could be downloaded or displayed on the browser.
Use an ASPX page and put all of the above together in the page to make the solution work as intended.

Here is step 1: The Database tables

For the database I will be using SQL Server 2008 Express edition, the one which is so generously shipped for free by Microsoft along with Visual Studio 2010.

The below screen shot contain the table structures that I have used in this example.

You can use the scripts given below to create the two tables.

For the Documents table:

USE [<Your database name here>]

/****** Object: Table [dbo].[Documents] Script Date: 04/15/2012 20:31:52 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Documents](

[DocID] [uniqueidentifier] NOT NULL,

[Title] [nvarchar](250) NOT NULL,

[Description] [nvarchar](500) NULL,

[FileBytes] [varbinary](max) NOT NULL,

[DateCreated] [datetime] NOT NULL,

CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED

(

[DocID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

For the Pictures table:

USE [[<Your database name here>]

/****** Object: Table [dbo].[Pictures] Script Date: 04/15/2012 20:41:19 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Pictures](

[PictureID] [uniqueidentifier] NOT NULL,

[Title] [nvarchar](250) NOT NULL,

[Description] [nvarchar](100) NULL,

[FileBytes] [varbinary](max) NOT NULL,

[DateCreated] [datetime] NOT NULL,

[PictureMimeType] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED

(

[PictureID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

Please note:

In both the tables, the column which stores the BLOB data is the FileBytes column and the type of the data is varbinary max.
The question that may arise here is why I'm using two different tables to store that same type of data in a single blog post? Well the answer is simple; I just wanted to demonstrate the options to fetch and display the stored BLOB data differently and created different tables to make the example easier to understand (hopefully!)

Then, Step 2: The Repositories

In this example, I have used an abstract repository very similar to the one which has been illustrated in one of my earlier posts.

The repository pattern in this example is using Entity Framework 4.1. There will be an abstract repository and concrete implementations of it for the individual tables.

The diagram below shows the designer view of my ADO .Net Entity Data Model.

I will only show the concrete implementation of the abstract repository for the Picture table.

/// <summary>

/// Concrete implementation of the repository for the Picture table

/// </summary>

[System.ComponentModel.DataObject]

public class PictureRepository

{

/// <summary>

/// The Picture repository is instantiated

/// </summary>

readonly IDataRepository<Picture> objPictureRepository = new DataRepository<Picture>();

/// <summary>

/// Get all Pictures

/// </summary>

/// <returns>Returns a list of all Pictures</returns>

[System.ComponentModel.DataObjectMethodAttribute

(System.ComponentModel.DataObjectMethodType.Select, true)]

public List<Picture> GetAllPictures()

{

return objPictureRepository.GetAll().ToList<Picture>();

}

/// <summary>

/// Updates a Picture

/// </summary>

/// <param name="pic">The Picture entity</param>

[System.ComponentModel.DataObjectMethodAttribute

(System.ComponentModel.DataObjectMethodType.Update, true)]

public void UpdatePicture(Picture pic)

{

objPictureRepository.Update(pic);

}

/// <summary>

/// Delete a Picture

/// </summary>

/// <param name="pic">The Picture entity to be deleted</param>

[System.ComponentModel.DataObjectMethodAttribute

(System.ComponentModel.DataObjectMethodType.Delete, true)]

public void DeletePicture(Picture pic)

{

objPictureRepository.Delete(pic);

}

/// <summary>

/// Gets a Picture by ID, assuming the Picture ID is unique

/// </summary>

/// <param name="PictureID">The value for the primary key PictureID</param>

/// <returns>The Picture associated with the integer ID</returns>

[System.ComponentModel.DataObjectMethodAttribute

(System.ComponentModel.DataObjectMethodType.Select, true)]

public Picture GetPictureByID(Guid PictureID)

{

try

{

return objPictureRepository.GetBySpecificKey("PictureID", PictureID.ToString()).FirstOrDefault<Picture>();

}

catch

{

return null;

}

/// <summary>

/// Inserts a Picture entity into the database

/// </summary>

/// <param name="pic">The entity to be inserted</param>

[System.ComponentModel.DataObjectMethodAttribute

(System.ComponentModel.DataObjectMethodType.Insert, true)]

public void InsertPicture(Picture pic)

{

objPictureRepository.Insert(pic);

}

I have created another very similar concrete repository for the Documents table, where everything is same except that it works with the Document objects instead of Picture objects.

I do not intend to copy paste the entire code as it would unnecessarily take up a lot of space, and by now you already have a fair idea about how the code is going to look like.

Time for Step 3: Generic HTTP Handlers

Once the data in binary format is retrieved from the database, how do you serve it? Surely it is not going to be as simple as displaying string data or writing date time objects? Exactly! And that is why I am going to use HTTP Handlers. Not just one, but two of them to be specific.

One to download files that has been uploaded in the database and the other one to display images stored in the database in a Gridview.

The code for the HTTP handler which will download the documents from the Documents table is given below:

/// <summary>

/// The documents download handler is a HTTP handler

/// and as the name suggests is used to download a file

/// from the database

/// </summary>

public class DocumentsDownloadHandler : IHttpHandler

{

/// <summary>

/// The good old process request method is over-ridden

/// to serve my sinister purpose of downloading files

/// </summary>

/// <param name="context">The HTTP Context</param>

public void ProcessRequest(HttpContext context)

{

//If DocID is not null, find the file and download it

if (!string.IsNullOrEmpty(context.Request.QueryString["DocID"]))

{

string DocID = context.Request.QueryString["DocID"];

DocumentRepository objDocRepository = new DocumentRepository();

Document objDoc = objDocRepository.GetDocumentByID(new Guid(DocID));

byte[] byteArray = objDoc.FileBytes.ToArray();

context.Response.Clear();

//Once all information is fetched, perform a binary write

context.Response.AddHeader("Content-Disposition", "attachment; filename=" + objDoc.Title);

context.Response.AddHeader("Content-Length", byteArray.Length.ToString());

context.Response.ContentType = "application/octet-stream";

context.Response.BinaryWrite(byteArray);

}

public bool IsReusable

{

get

{

return false;

}

The code for the HTTP handler which will display the pictures from the Pictures table is given below. Please note that for all intents and purposes it is very similar to the HTTP Handler for the Documents. The only difference is how it performs the Binary write operation.

/// <summary>

/// The PictureDownloadHandler is a HTTP handler

/// and as the name suggests is used to download a picture

/// from the database

/// </summary>

public class PictureDownloadHandler : IHttpHandler

{

/// <summary>

/// The over-ridden ProcessRequest method

/// which performs the core task of fetching the picture and

/// writing the binary data contained by it

/// </summary>

/// <param name="context">The current HTTP Context</param>

public void ProcessRequest(HttpContext context)

{

//Proceed only if you have a PictureID to look for

if (!string.IsNullOrEmpty(context.Request.QueryString["PictureID"]))

{

string PictureID = context.Request.QueryString["PictureID"];

PictureRepository objPicRepository = new PictureRepository();

Picture objPic = objPicRepository.GetPictureByID(new Guid(PictureID));

//Once you have all the information, perform the binary write

context.Response.ContentType =string.Format("image/{0}",objPic.PictureMimeType.Replace(".", ""));

context.Response.BinaryWrite(objPic.FileBytes);

}

public bool IsReusable

{

get

{

return false;

}

And Finally Step 4: The User interface

One ring to bring them all,

And in the darkness bind them

Aaha, not quite! But close enough. Let us now get to our ASPX page which will bear the fruit of all the hard toil that has happened till now.

In this simple web form page, I will be using an ASP .Net file upload control, a button to upload the file selected in the file upload control and a Gridview to display the files that has been uploaded into the database.

<table>

<tr>

<td><asp:Button ID="btn_Upload" runat="server" Text="Upload"

onclick="btn_Upload_Click" /></td>

</tr>

<tr>

<td colspan="2"><asp:GridView ID="gv_Documents" runat="server"

AutoGenerateColumns="False" CellPadding="4" DataSourceID="ods_Documents" DataKeyNames="DocID"

ForeColor="#333333" GridLines="None">

<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

<asp:BoundField DataField="DocID" HeaderText="DocID" SortExpression="DocID" Visible="false"/>

<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />

<asp:BoundField DataField="Description" HeaderText="Description"

SortExpression="Description" />

<asp:BoundField DataField="DateCreated" HeaderText="DateCreated"

SortExpression="DateCreated" />

<asp:HyperLinkField HeaderText="Download" DataNavigateUrlFormatString="~/Handlers/DocumentsDownloadHandler.ashx?DocID={0}"

DataNavigateUrlFields="DocID" Text="Download file" />

</Columns>

</asp:GridView>

<asp:ObjectDataSource ID="ods_Documents" runat="server"

DataObjectTypeName="Application.Data.Document" DeleteMethod="DeleteDocument"

OldValuesParameterFormatString="original_{0}" SelectMethod="GetAllDocuments"

TypeName="Application.DataModels.DocumentRepository"

UpdateMethod="UpdateDocument"></asp:ObjectDataSource>

</td>

</tr>

</table>

In the code behind file, the call back event for the button click is very simple.

/// <summary>

/// The call back event to upload a file into

/// the documents table

/// </summary>

protected void btn_Upload_Click(object sender, EventArgs e)

{

//If the file upload control contains a document then upload the file

if (flupld_Documents.HasFile && flupld_Documents.FileBytes != null)

{

Document objDocument = new Document();

objDocument.DocID = Guid.NewGuid();

objDocument.DateCreated = DateTime.Now;

objDocument.FileBytes = flupld_Documents.FileBytes;

objDocument.Title = flupld_Documents.FileName;

DocumentRepository objDocRep = new DocumentRepository();

objDocRep.InsertDocument(objDocument);

gv_Documents.DataBind();

}

Once the code is in place and you have built the solution and started the website, you would be able to upload files into the Documents table and subsequently download them by clicking on the Download file link.

Similarly in the HTML code to upload files in the Pictures table, I will be again using an ASP .Net file upload control and then a button to upload the file into the Pictures table. Below this there will be a Gridview control which displays the pictures uploaded in the table.

<table>

<tr>

<td><asp:Button ID="btn_UploadImage" runat="server" Text="Upload"

onclick="btn_UploadImage_Click" /></td>

</tr>

<tr>

<asp:GridView ID="gv_Images" runat="server" AutoGenerateColumns="False" DataKeyNames="PictureID"

CellPadding="4" DataSourceID="ods_Images" ForeColor="#333333" GridLines="None">

<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

<asp:BoundField DataField="PictureID" HeaderText="PictureID"

SortExpression="PictureID" Visible="false" />

<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />

<asp:BoundField DataField="Description" HeaderText="Description"

SortExpression="Description" />

<asp:BoundField DataField="DateCreated" HeaderText="DateCreated"

SortExpression="DateCreated" />

<asp:ImageField DataImageUrlField="PictureID" DataImageUrlFormatString="~/Handlers/PictureDownloadHandler.ashx?PictureID={0}" />

</Columns>

</asp:GridView>

<asp:ObjectDataSource ID="ods_Images" runat="server"

DataObjectTypeName="Application.Data.Picture" DeleteMethod="DeletePicture"

OldValuesParameterFormatString="original_{0}" SelectMethod="GetAllPictures"

TypeName="Application.DataModels.PictureRepository"

UpdateMethod="UpdatePicture" InsertMethod="InsertPicture">

</asp:ObjectDataSource>

</td>

</tr>

</table>

Again, absolutely simple piece of code in the call back function for the Image upload button click.

/// <summary>

/// The call back event to upload the picture

/// into the picture table

/// </summary>

protected void btn_UploadImage_Click(object sender, EventArgs e)

{

//Proceed when the file upload control has a file which is not

//empty

if (flupld_Images.HasFile && flupld_Images.FileBytes != null)

{

Picture pic = new Picture();

pic.PictureID = Guid.NewGuid();

pic.DateCreated = DateTime.Now;

pic.FileBytes = flupld_Images.FileBytes;

pic.Title = flupld_Images.FileName;

pic.PictureMimeType = flupld_Images.PostedFile.ContentType;

PictureRepository objPicRep = new PictureRepository();

objPicRep.InsertPicture(pic);

gv_Images.DataBind();

}

And this is how it looks in the browser.

.Net Musings

Monday, April 16, 2012

Working with BLOBs in ASP .Net