Store uploaded file content in image field of SQL Server database and retrieve content back into its original file format

Posted: January 31, 2008 in ASP.NET
Tags: , , ,

 

There is one requirement of storing file content in a database using Image data type. Image field can store up to 231 -1 bytes and that equals around 2 GB. So you can store up to 2 GB data in image field of Microsoft SQL Server. Let us come to the point now; I was required to store uploaded file content in image field of database. File is uploaded through file upload control of HTML of course! While uploading the content you need to convert that content into byte array and then you can store it into the database. Now, how to convert file content into byte that I have displayed below with code.

Code: C#

Int32 intDocLength = File1.PostedFile.ContentLength;

byte[] bytDocTemp = new byte[intDocLength];

Stream objStream;

objStream = File1.PostedFile.InputStream;

objStream.Read(bytDocTemp, 0, intDocLength);

 

Now you can use bytDocTemp variable to store content of the file in the form of byte array into the database. Here, File1 is a file upload control and I used content length of the file to create an array of byte with exact size of the file.

I will show you how to retrieve file content from Image data type and showing it in its original form. You can display uploaded content of the file back to its original form with the following code:

Code: C#

Response.Clear();

Response.AddHeader(“Content-Disposition”, “attachment; filename=<filename>” + <File Extention>);

Response.ContentType = “application/octet-stream”;

Response.BinaryWrite(<byte array of the file>);

Response.End();

 

Here, replace <filename> with any of the file name that you need to display. File name must be without extension, and replace <File Extension> with your original file extension (like .doc, .pdf, .html etc). <byte array of the file> can be replaced with the content that you have fetched from the Image field of the database.

Whether you found this article useful or not? please provide your valuable comments.

Advertisements
Comments
  1. MD.Shafikhan says:

    Thanks for providing such a valuable code, I am trying since one week for Storing uploaded file content in image field of SQL Server database and retrieve content back into its original file format

    I did with the help of ur code

    Thanks a lot

    shafikhan
    programmer
    Hypersoft technologies
    Karkhana
    Secunderabad

  2. sagar says:

    How can I show & update Data of the Word document which i have stored in the Database in byte format into some control (like OLE in VB6) in the Windows Form

    Can U have some idea regarding this
    that any control can let me display the file data & update it

  3. hspinfo says:

    Hello Sagar,

    I am not having much knowledge for windows application and VB6, however i found some code that might be helpful to you.

    First of all, create reference to Microsoft Word Library by clicking Project–>References–>Microsoft Word x.x Object Library.
    Now here is the code for creating a document

    Dim MyWord As Word.Application
    Dim WordDoc As Word.Document

    ‘Create word object
    Set MyWord = CreateObject(“Word.Application” ) ‘Assuming word is not running
    Set WordDoc = MyWord.Documents.Add

    ‘Show Word if you wish
    MyWord.Visible = True
    MyWord.Activate
    MyWord.Selection.TypeText “This is sample text”

    ‘Save the file. If the document exists it is overwriten
    WordDoc.SaveAs newfile$
    WordDoc.Close
    Set WordDoc = Nothing
    Set MyWord = Nothing

  4. Ammar says:

    hi

    what if i wanted to use that code in a windows based program to store excel, word or pdf files ?

  5. hspinfo says:

    Hi Ammar,

    The same code can be used for any file type, as it is uploded from a file uploader. I did not know much about the windows application, but the same code can be used to store file in a database.

    However, when you are retriving a file from database and wanted to show it, at that time the following line of code will be change as below:

    //For Word file
    Response.ContentType = “application/vnd.ms-word”;

    //For Excel file
    Response.ContentType = “application/vnd.ms-excel”;

    //For PDF file
    Response.ContentType = “application/pdf”;

  6. Nevil Gandhi says:

    Hello Hemang,

    i really appreciate your post. Actually i face some problem like
    here i search no. of file and his content and i store this file’s content to database ( SQL 2005 ).
    so please help me to solve this query.

    Thanks With Warm Regards,

    Nevil Gandhi

  7. hspinfo says:

    Hi Nevil,

    Please mention your query, I will try to solve it.

  8. Bilal says:

    Hi Hemang,

    it’s really an interesting and helpful post.

    I have one question here, are you displaying the binary content of the document in a new empty aspx page when you retrieve it from DB?

    thanks in advance

  9. hspinfo says:

    Thanks Bilal,

    The following code will ask a dialog box to user, either to OPEN, SAVE or CANCEL the document.

    Response.Clear();

    Response.AddHeader(“Content-Disposition”, “attachment; filename=” + );

    Response.ContentType = “application/octet-stream”;

    Response.BinaryWrite();

    Response.End();

  10. Craig says:

    Great post! Question:

    I have users uploading an Excel File. It is not stored on the server but I am reading (trying to read) the stream. How can I read the inputStream and put it in a dataset or convert it to XML? I haven’t seen anything that allows you to read an Excel stream… the only thing I have seen is when you have to choose the physical path of your Excel file and use it as the datasource in a connection string.

    thanks,
    seed

  11. hspinfo says:

    Hi Craig,

    I guess this article would be useful to you:

  12. Mohd Javed says:

    Superb Articles for every one.

  13. Victor says:

    Thanks for providing us with short and sweet code. It really helps.

  14. Name says:

    You can use these tags :

  15. Moti says:

    hi
    i m developing an app in which i want to store photo in a database, which has a field of image data type and retive that to show in a picturebox control
    how could i do this
    help me plz…

  16. Sandip says:

    Hei thanks for youre initiatives.
    I just wanted to know whether same code will work for .jpg,.bmp files?

  17. antesmept says:

    mm… thanks 🙂

  18. Prashant Dandekar says:

    i tried this binary write method erlier. but the problem is is we delete that record having image. the size of that record is not reduced from db. the db size remains same. i store some high def images to it and delete some bu my db size is 150mb.

  19. nikhil says:

    hi i am store ms word resume to image(BLOB) data type in sql server 2005. now i want to display this resume in HTML page or in text area. and for that i use

    byte[] binary = (objJobResult.resultDS.Tables[0].Rows[0][“DocumentContent”]) as byte[];

    string

    content = Encoding.ASCII.GetString(binary);

    and i got whole resume with some garbage character. that garbage character is of ms Words header footer content.

  20. Debolina mitra says:

    Thank you so much. I was trying for this code for 4 days and now i’m satisfied with your solution..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s