Introduction
There are scenarios where our applications have to store documents, say Document Management Systems. The two main options that I see for handling images/files in web or Windows applications are storing images as a BLOB in the database, or storing the URL/UNC of the file in the database and actually storing the image file on a file share.
Background
Storing BLOB objects in SQL Server is really cumbersome when it comes to retrieval. Hence, the most commonly accepted way of managing images for a website is not to store the images in the database, but only to store the URL in the database, and to store the images on a file share that has been made a virtual folder of your web server.
Writing BLOB values to database
You can write a binary large object (BLOB) to a database as either binary or character data, depending on the type of the field in your data source. To write a BLOB value to your database, issue the appropriate INSERT
or UPDATE
statement and pass the BLOB value as an input parameter. If your BLOB is stored as text, such as a SQL Server text field, you can pass the BLOB as a string parameter. If the BLOB is stored in binary format, such as a SQL Server image field, you can pass an array of type byte
as a binary parameter.
The first thing to be aware of here is, blob data is not stored inline, that is not on the same page as the rest of the data (unless specifically instructed: see sp_tableoption). Secondly, all "blob" data can be forced into a separate filegroup, which you can position on a different IO subsystem. The maximum size for a blob is 2GB, multiple blobs can share the 8K pages, so the minimum "blob" size will be your smallest file.
While designing tables to store BLOBs, always define a column to store the file type or content type, say .doc, .xls, etc. This is very much required while retrieving the files back from the database to identify the content type.
Table structure
Table to store BLOBs:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[Docs]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Docs]
GO
CREATE TABLE [dbo].[Docs] (
[docid] [int] IDENTITY (1, 1) NOT NULL ,
[docuid] [uniqueidentifier] NULL ,
[docname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[document] [image] NOT NULL ,
[doctype] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Docs] WITH NOCHECK ADD
CONSTRAINT [PK_Docs] PRIMARY KEY CLUSTERED
(
[docid]
) ON [PRIMARY]
GO
Stored Procedures
Procedures to dump/retrieve BLOBs:
CREATE proc sp_adddoc(@docname nvarchar(50), @document image, @doctype nvarchar(50))
as
begin
insert into Docs(docuid, docname, document, doctype)
valueS(newid(), @docname, @document, @doctype)
end
GO
CREATE proc sp_getdoc(@docuid uniqueidentifier)
as
begin
select document, docname, doctype from Docs where docuid = @docuid
end
GO
Using the code for dumping document into database from ASPX
Let us try a scenario, say, we have a document to be uploaded to the database with a Primary Key (PK).
Here we go! Drag and place a "FileUpload
" and a "Button
" control into the web page (.aspx) and paste the code below into the "Button_Click
" event of the "Upload" button. Don't forget to mention the connection string in your "Web.config" file before trying this code snippet.
protected void btnUpload_Click(object sender, EventArgs e)
{
if (!FileUpload1.HasFile) return;
byte[] binary = new byte[FileUpload1.PostedFile.ContentLength];
binary = FileUpload1.FileBytes;
SqlParameter param = null;
SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["menu"].ToString());
SqlCommand cmd = new SqlCommand("sp_adddoc", conn);
cmd.CommandType = CommandType.StoredProcedure;
param = new SqlParameter("@docname", SqlDbType.NVarChar, 50,
ParameterDirection.Input, false, 0, 0, "",
DataRowVersion.Default, FileUpload1.FileName);
cmd.Parameters.Add(param);
param = new SqlParameter("@document", SqlDbType.Image);
param.Direction = ParameterDirection.Input;
param.Value = binary;
cmd.Parameters.Add(param);
param = new SqlParameter("doctype", SqlDbType.NVarChar, 50,
ParameterDirection.Input, false, 0, 0, "",
DataRowVersion.Default, FileUpload1.PostedFile.ContentType);
cmd.Parameters.Add(param);
conn.Open();
cmd.ExecuteNonQuery();
}
Using the code for file retrieval from ASPX
Now, it is time to retrieve the BLOB and render it to the web page. To retrieve the document, we must provide the PK of the item. Use the code snippet below to get the BLOB back from the database. Here I am using "binary streaming" for better IO performance.
private void WriteDocumentWithStreaming()
{
string docuid = "864d9871-b6f2-41ec-8a4d-615bd0f03763";
SqlParameter param = null;
SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["menu"].ToString());
SqlCommand cmd = new SqlCommand("sp_getdoc", conn);
cmd.CommandType = CommandType.StoredProcedure;
param = new SqlParameter("@docuid", SqlDbType.NVarChar, 100);
param.Direction = ParameterDirection.Input;
param.Value = docuid;
cmd.Parameters.Add(param);
conn.Open();
SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.HasRows)
{
reader.Read();
string doctype = reader["doctype"].ToString();
string docname = reader["docname"].ToString();
Response.Buffer = false;
Response.ClearHeaders();
Response.ContentType = doctype;
Response.AddHeader("Content-Disposition",
"attachment; filename=" + docname);
const int ChunkSize = 1024;
byte[] buffer = new byte[ChunkSize];
byte[] binary = (reader["document"]) as byte[];
MemoryStream ms = new MemoryStream(binary);
int SizeToWrite = ChunkSize;
for (int i = 0; i < binary.GetUpperBound(0)-1; i=i+ChunkSize)
{
if (!Response.IsClientConnected) return;
if (i + ChunkSize >= binary.Length)
SizeToWrite = binary.Length - i;
byte[] chunk = new byte[SizeToWrite];
ms.Read(chunk, 0, SizeToWrite);
Response.BinaryWrite(chunk);
Response.Flush();
}
Response.Close();
}
}
How to handle BLOBs in WinForms?
See my writings here.
If I get enough queries on this one, I will start writing more articles. My blog.