Storing BLOB (Binary Large Objects) in a SQL database can be a very convenient way to tie documents with metadata. For example, if you are building a document management system it is very nice to be able to stuff a word document in a data row along with other information such as who created the document, when it was modified and etcetera.
The first step in accomplishing this task is to define a database table. The only real trick is to use a image column for the datatype of the column storing the BLOB data. For example consider this table creation script:
CREATE TABLE [dbo].[PurchaseOrderAttachment](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrder] [nvarchar](50) NOT NULL,
[Data] [image] NOT NULL,
[Filename] [nvarchar](100) NOT NULL
)
To insert the data using C# you will need the following code (using LINQ)
MyDataContext db = new MyDataContext();
PurchaseOrderAttachment a = new PurchaseOrderAttachment();
a.Filename = System.IO.Path.GetFileName(attachment.Filename);
a.Data = System.IO.File.ReadAllBytes(attachment.Filename);
a.PurchaseOrder = "123";
db.PurchaseOrderAttachments.Add(a);
db.SubmitChanges();
Or if you are old school and want to do it with regular T-SQL:
INSERT INTO PurchaseOrderAttachment
( PurchaseOrder , Data, Filename )
VALUES
('123'
,( SELECT * FROM OPENROWSET(BULK 'c:\windows\Santa Fe Stucco.bmp', SINGLE_BLOB)
as x)
,'Santa Fe Stucco.bmp')
After we insert the image into the database we can take a quick look with SQL Server Management studio to see what it looks like in the table:
You can see that the Data column has binary data in it and it is not readable with the human eye. Therefore in order to view the data you will need to write a little code. Fortunately, for you I have written this code in two different ways. The first implementation was for a WinForms application. The second implementation was from a MVC application. Here is the Winforms code:
1: PurchaseOrderAttachment attachment = FindAttachment(1);
2: string filename = Path.Combine( System.IO.Path.GetTempPath(), attachment.Filename );
3: FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write);
4: fs.Write(attachment.Data.ToArray(), 0, attachment.Data.Length);
5: fs.Close();
6:
7: if (File.Exists(filename) == true)
8: System.Diagnostics.Process.Start(filename);
9: else
10: MessageBox.Show(String.Format("Could not retreive file \"{0}\"", filename),
11: "File not found", MessageBoxButtons.OK, MessageBoxIcon.Information);
Lets dissect this code. Line 1 retrieves the record from the database. Line 2 creates a temporary place to store the file on disk. Line 3,4 and 5 saves the file to disk using the FileStream object. Then on Line 8 I call System.Diagnostics.Process.Start(filename) to open the file. The reason I used this technique was because you can store any kind of file in the database. It could be excel, word, a jpeg, a TSQL script or even a text file. By saving the file to disk and calling the Process.Start method on it I can rely on windows to use the file extension and determine which program to launch. However, I can eliminate the need to write the file to disk completely if I build some more intelligence into my code and decide which application to launch based on the extension or content of the file. This is demonstrated in my next example.
In my MVC Web Application, I limited the kinds of files I allowed for upload. I stream the file to the client by using an HTTP Handler:
1: <%@ WebHandler Language="C#" Class="POAttachment" %>
2:
3: using System;
4: using System.Web;
5: using DBA.Common.DAL;
6: using System.Data.Linq;
7: using System.Linq;
8: using System.IO;
9:
10: public class POAttachment : IHttpHandler {
11:
12: public void ProcessRequest (HttpContext context) {
13: try {
14: int id = Convert.ToInt32(context.Request["id"]);
15: DBAInventoryDataContext db = new DBAInventoryDataContext();
16: PurchaseOrderAttachment attachment = FindAttachment(id);
17:
18: FileInfo file = new FileInfo(attachment.Filename);
19: context.Response.ContentType = GetContentType(file.Extension);
20: context.Response.AddHeader("Content-Disposition", " filename=" + attachment.Filename);
21: context.Response.AddHeader("Content-Length", attachment.Data.Length.ToString());
22: context.Response.BinaryWrite(attachment.Data.ToArray());
23: context.Response.Flush();
24: }
25: catch {
26: context.Response.ContentType = "text/plain";
27: context.Response.Write("Error retreving attachment");
28: }
29: }
30:
31: private string GetContentType(string fileExtension)
32: {
33: switch (fileExtension) {
34: case ".xls":
35: return "application/vnd.ms-excel";
38: case ".pdf":
39: return "application/pdf";
40: default:
41: return "application/octet-stream";
42: }
43: }
44: }
This code is somewhat simple. On line 16 I retrieve the row from the database. Then on lines 19-23 I set the Content Type, add some required headers and then I write the contents of the Data field to the Response object. When I want to use this Handler I create a hyperlink in my application that looks like this: http://<mysite>/POAttachment.ashx?id=1. When the user clicks on the link the handler will fetches the data from the database. The browser then will then launch the appropriate application based on the Content Type set in the Response headers.
Hopefully, this code will save you a little time. Although, it is very simple when you see the final code it takes a little bit of reading and experimenting to get something working for the first time. Happy Coding!
