Retrieving and storing an image in SQL Server
How can I retrieve and store an image in SQL Server using Visual Basic 2008?
Software/Hardware used:
Software/Hardware used:
ASKED:
August 1, 2008 4:57 PM
UPDATED:
August 1, 2008 6:58 PM
Answer Wiki:
If you are using SQL 2000 or below you will want to use the data type of Image. If you have using SQL 2005 or above then you will want to use the data type of VARBINARY(MAX).
Your best bet will be to load up the image into a .NET variable and pass it to a stored procedure.
The stored procedure can then handle the writing of the data to the table.
<pre>CREATE PROCEDURE InsertImage
@Image VARBINARY(MAX)
AS
INSERT INTO YourTable
(Image)
VALUES
(@Image)
GO</pre>
As for retrieving the image, a single select will return the data to you. Write the data back out to a file (if your images can be in different formats you'll want to store the file extension in the table, unless you want to try and pull it from the binary data it self).
Now, do keep in mind that it's recommended that you not keep the actual images within the database. It's better to store them on a file server somewhere and only keep the path to the file in the database. This will shrink your database file size, and improve response time as the system grows.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.