How to Insert Images into Database Using MSSQL Server
dateJune 27 2013 | comments 0 comments
A fairly common problem is inserting images direcly into a SQL Server database without a front-end application. I'm going to explain how this can be done for a specific file or for a collection of files.
Most of you are going to say that this thing is not possible... How to get an image directly in the database? Well this thing already exists and is used only by experienced DBA (database administrator).

So...what do we need for that?
For this we need a MSSQL database server. I'm going to use MSSQL 2008 R2, because this is what I have installed at this moment. The next thing we need is a folder with some images and you need also to set the folder permision for Network Service user role. And if we want to insert just one image how are we going to proceed?

1) Create our desired table in selected database.



2) Select the image in binary format.

3) Insert it in our table.

4) Now we have our image in the database. Let's check it if it there...  



Remember that this isn't working just for images. It's going to work for all kind of files(doc, ppt, txt), cause they can be stored in binary format.

OPENROWSET supports bulk operations too through a built-in BULK provider that enables data from a file to be read and returned as a rowset.

You need to use the BULK rowset provider for OPENROWSET to read data from a file. In SQL Server, OPENROWSET can read from a data file directly which it lets you use OPENROWSET with a simple SELECT statement. If you want to read the files in the binary format you going to use SINGLE_BLOB, but don't forget that also exist a SINGLE_CLOB(read the data file as ASCII and return its value in varchar format) and also a SINGLE_NCLOB(read the data file as UNICODE and return it as nvarchar format).

All being said but it's possible to do this in a dynamically way? Well it is, even most of forums and microsoft support says that that's not possible. Let's see how we going to proceed.

1) Let's create a looping method - I would use a SQL cursor.
2) Declare the main folder for our images.




3) We gonna presume that we have our images filename as some website urls saved in another database.
4) And now the final code.




If you need the image size you can just use DataLength(@vbin)/1024.0 in your code wich will get the binary length and will divide it to 1024(to transform that value in Kb).
Also you can use those images in asp.net with a http handler to get your image:
ImageHandler.ashx code:



And this can be used like: <img src="ImageHandler.ashx?id=<%=id%>" />

Useful links:
http://msdn.microsoft.com/en-us/library/ms190312.aspx - OPENROWSET documentation.
http://msdn.microsoft.com/en-us/library/system.web.ihttphandler.processrequest.aspx - HTTP Handlers documentation.

Thank you for your time and happy coding.

dateJune 27 2013 | comments 0 comments

How to Insert Images into Database Using MSSQL Server
Comments
Blog post currently doesn't have any comments.
Leave comment



 Security code