Click here to Skip to main content
15,867,750 members
Articles / Web Development / ASP.NET
Article

Store and retrieve objects as BLOBs in SQL Server 2000 and 2005 using ASP.NET 2.0

Rate me:
Please Sign up or sign in to vote.
4.34/5 (19 votes)
5 Aug 2008CPOL3 min read 200.6K   80   37
How To: Read and write BLOB data by using ADO.NET through ASP.NET 2.0.

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:

SQL
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:

SQL
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.

C#
protected void btnUpload_Click(object sender, EventArgs e)
{
    //Validation to make sure that the file 
    //upload control has a valid file
    if (!FileUpload1.HasFile) return;

    //Get the binary array directly from the control
    byte[] binary = new byte[FileUpload1.PostedFile.ContentLength];
    binary = FileUpload1.FileBytes;
    //SQL connection and paramters to dump 
    //the document via stored procedure
    SqlParameter param = null; 
    SqlConnection conn = new SqlConnection(
      ConfigurationManager.ConnectionStrings["menu"].ToString());

    SqlCommand cmd = new SqlCommand("sp_adddoc", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    //Parameters and values 
    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);
    //Connection open and execute
    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.

C#
private void WriteDocumentWithStreaming()
{
    // Request.QueryString["docid"].ToString(); 
    string docuid = "864d9871-b6f2-41ec-8a4d-615bd0f03763";
    //Connection and Parameters
    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);
    //Open connection and fetch the data with reader
    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); 
        //
        //Code for streaming the object while writing
        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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
United States United States
Systems Architect & Analyst with concentration in various IT technologies, especially on Microsoft platform. He is an active member of Sharepoint Community and areas of expertise are ASP.Net, SharePoint, WinForms and Workflow technologies. You can visit his blog
http://www.dotnetsoldier.blogspot.com/

Comments and Discussions

 
QuestionDoesn't Save Large PDF Files. Pin
SJBUPI17-May-12 21:07
SJBUPI17-May-12 21:07 
QuestionHow to display the pdf retrieved Pin
New_hunter2-Mar-11 10:33
New_hunter2-Mar-11 10:33 
GeneralMy vote of 5 Pin
EngrBS13-Oct-10 7:38
professionalEngrBS13-Oct-10 7:38 
QuestionRetrieving SQL BLOB data (text) from Cognos Studio Report Pin
jcalleto30-Sep-10 6:21
jcalleto30-Sep-10 6:21 
Generalsearch the similarity image using BLOB Algorithm Pin
shibu 201021-Jan-10 20:45
shibu 201021-Jan-10 20:45 
Sir,
I am doing final year MCA student,now i doing 6mount project.
That project is "search the similarity image using BLOB Algorithm ".Sir I need some doubt in this topic ,how to image will be segmented in some particular part ,how it is identified an how to retrieve that particular image in database.

please rectifier that doubt sir
i am waiting for your response ..
thank you

M.shibu
GeneralRe: search the similarity image using BLOB Algorithm Pin
Ziyad Mohammad26-Jan-10 18:41
Ziyad Mohammad26-Jan-10 18:41 
GeneralProblem in downloading Pin
asif rehman baber15-Jun-09 23:56
asif rehman baber15-Jun-09 23:56 
GeneralRe: Problem in downloading Pin
Ziyad Mohammad11-Sep-09 15:23
Ziyad Mohammad11-Sep-09 15:23 
GeneralI'm not able to fetch the entire content of the text document Pin
Monalisa016-May-09 22:08
Monalisa016-May-09 22:08 
GeneralRe: I'm not able to fetch the entire content of the text document Pin
Ziyad Mohammad11-Sep-09 15:24
Ziyad Mohammad11-Sep-09 15:24 
QuestionHow can I show the file in the same window ? [modified] Pin
drorby12-Mar-09 5:10
drorby12-Mar-09 5:10 
AnswerRe: How can I show the file in the same window ? Pin
Ziyad Mohammad11-Sep-09 15:25
Ziyad Mohammad11-Sep-09 15:25 
GeneralSize file Pin
arturo10-Mar-09 11:21
arturo10-Mar-09 11:21 
GeneralRe: Size file Pin
Ziyad Mohammad11-Sep-09 15:26
Ziyad Mohammad11-Sep-09 15:26 
GeneralGetting garble-goop Pin
fiesty8-Oct-08 6:57
fiesty8-Oct-08 6:57 
GeneralRe: Getting garble-goop Pin
Ziyad Mohammad11-Sep-09 15:27
Ziyad Mohammad11-Sep-09 15:27 
QuestionWhy you declare buffer array if you don't use it further in the code? Pin
Oleksii Prosiankin12-Aug-08 4:13
Oleksii Prosiankin12-Aug-08 4:13 
AnswerRe: Why you declare buffer array if you don't use it further in the code? Pin
Ziyad Mohammad11-Sep-09 15:29
Ziyad Mohammad11-Sep-09 15:29 
Generalcompress/decompress Pin
Samir NIGAM5-Aug-08 20:26
Samir NIGAM5-Aug-08 20:26 
GeneralRe: compress/decompress Pin
Ziyad Mohammad11-Sep-09 15:28
Ziyad Mohammad11-Sep-09 15:28 
Questiondoes it include Videos? Pin
Red Valdez23-Jul-08 2:27
Red Valdez23-Jul-08 2:27 
AnswerRe: does it include Videos? Pin
Ziyad Mohammad5-Aug-08 19:40
Ziyad Mohammad5-Aug-08 19:40 
GeneralDownload bitmap and fill an image on a web page Pin
Arthur Gladney16-Jul-08 17:32
Arthur Gladney16-Jul-08 17:32 
AnswerRe: Download bitmap and fill an image on a web page Pin
Ziyad Mohammad5-Aug-08 19:38
Ziyad Mohammad5-Aug-08 19:38 
GeneralVery interesting article. Pin
Federico Orlandini18-Dec-07 7:58
Federico Orlandini18-Dec-07 7:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.