Save image SQL script

[ahhuang]

Hi,
I have a directory storing images.
How will I be able to write a SQL script to save the images into a table,assuming the DataType of image is 'image'
Thanks

 

[amrita418]

Check this article to see if it helps

http://www.sqlteam.com/item.asp?ItemID=986

Regards,
AA

 

[NoCoolHandle]

What client tools (programing languages) are you using?

Does this need to be a complete sqlsoltuion with no outside tools?

 

[ahhuang]

I am using vb.net. however i want it to be independent from any languages because i want run it with stored procedure only.
Thanks

 

[NoCoolHandle]

Alright..

If you are using .net. It is a relativly simple matter.
(and the only diff between .net and other languages is in how you package the image.. In VB6 you can use a stream from a command object or build the image bit by bit using getchunk.. - The stored Proc will work with any language)

In your stored proc..

Create Proc InsertStuff
@description varchar(300),
@image image
as
set nocount on
Insert into SomeTable (Description,Image)
values (@description,@image)

In DotNEt..
(the first trick in dot net is taht you need to convert you image into a byte array)

Dim imgToSave as System.Drawing.Image = system.Drawing.Image.FromFile("SomeImage.jpg")
Dim sc As New ImageConverter
Dim buffer() As Byte
buffer = sc.ConvertTo(imgToSave, GetType(Byte()))
imgToSave.Dispose()
imgToSave = Nothing
buffer

Now we will build the Function for doing the insert..

Private Function UseInsertStuff (byVal image as byte(), byVal description as string) as Boolean
Dim con as New SqlClient.SqlConnection("server=localhost;database=Northwind;trusted_connection=yes")
Dim cmd as New SqlClient.SqlCommand("InsertStuff",con)
Dim da As New SqlClient.SqlDataAdapter(cmd)
With cmd
.CommandType = CommandType.StoredProcedure
With .Parameters
.Add("@image", SqlDbType.image, 16).Value = image
.Add("@description", SqlDbType.varchar, 300).Value = description
End With
con.Open()
try
cmd.ExecuteNonQuery() ' For a stored proc with no records...

catch ex as exception
'msgbox ex.message
'response.write ex.message
Return False
exit function
Finally
con.Close()
cmd.Dispose()
End Try
End With

Return true
End Function

Finally from the first bit of dotnet code call the second and pass the data..

UseInsertStuff (buffer, "FileName - Or some other data")

 

[ahhuang]

that would be great! thanks.