Retrieve a binary image from a SQL database and save it to a file as a .jpeg

I have jpeg images saved into an "image" field in a sql server 2008 database.  I am currently displaying those binary images to a picture box in a form, but I need to be able to download and save that image as a file on the local system (jpeg).

I am using VB.Net Winforms not ASP.NET.  

It would be fine to be able to save the image directly to the file system without displaying the image first.  The image fields are associated with a specific record by primary key.  
LVL 5
rtayIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QPRCommented:
http://support.microsoft.com/kb/308042

Dim con As New SqlConnection _
       ("Server=YourServer;uid=<username>;pwd=<strong password>;database=northwind")
      Dim da As New SqlDataAdapter _
       ("Select * From MyImages", con)
      Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
      Dim ds As New DataSet()

      con.Open()
      da.Fill(ds, "MyImages")
      Dim myRow As DataRow
      myRow = ds.Tables("MyImages").Rows(0)

      Dim MyData() As Byte
      MyData = myRow("imgField")
      Dim K As Long
      K = UBound(MyData)

      Dim fs As New FileStream _
       ("C:\winnt\Gone Fishing2.BMP", FileMode.OpenOrCreate, _
        FileAccess.Write)
      fs.Write(MyData, 0, K)
      fs.Close()

      fs = Nothing
      MyCB = Nothing
      ds = Nothing
      da = Nothing

      con.Close()
      con = Nothing
      MsgBox ("Image retrieved")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rtayIT DirectorAuthor Commented:
I was able to retrieve a file, but need help getting a specific file based on a database key.  The method you suggested just pulled the first image in the database, not the file selected in the form query.  I need the field "imagecontent" in the record for a specific "semiTrlNo" which is specified in the field on the form semiTrlNoTextBox.  The code below is my attempt, but I am doing something wrong.  Also, I need to allow the user to specify a name for the file and where to place it on thier computer.  Thanks.

 ' save file
    Private Sub btnSaveSemi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveSemi.Click
        Dim con As New SqlConnection _
       ("Server=server;uid=Admin;pwd=pc;database=Develop_TareWeight")
        Dim da As New SqlDataAdapter _
         ("Select ImageContent WHERE SemiTrlNo=@Trailer From tbl_TrailerInfo", con)
        Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
        Dim ds As New DataSet()

        Dim sSQL As String = "Select tbl_TrailerInfo Set ImageContent=@Pic WHERE PullTrlNo=@Trailer"

        Dim cmd As SqlCommand = New SqlCommand(sSQL, con)

        'trailer no
        Dim Trailer As SqlParameter = New SqlParameter("@Trailer", SqlDbType.NVarChar, 50)
        Trailer.Value = SemiTrlNoTextBox.Text.ToString()
        cmd.Parameters.Add(Trailer)

        con.Open()
        da.Fill(ds, "Tbl_TrailerInfo")
        Dim myRow As DataRow
        myRow = ds.Tables("Tbl_TrailerInfo").Rows(0)

        Dim MyData() As Byte
        MyData = myRow("ImageContent")
        Dim K As Long
        K = UBound(MyData)

        Dim fs As New FileStream _
         ("C:\temp\Gone Fishing2.jpg", FileMode.OpenOrCreate, _
          FileAccess.Write)
        fs.Write(MyData, 0, K)
        fs.Close()

        fs = Nothing
        MyCB = Nothing
        ds = Nothing
        da = Nothing

        con.Close()
        con = Nothing
        MsgBox("Image retrieved")


    End Sub
lcohanDatabase AnalystCommented:
I think you can use the code below in a SQL StoredProcedure and call it from the .NET code - 2 comments: you can pass in a parameter as row_id(s), and the location of exported file is relative to the sql server where it runs


declare @id sysname
declare @sqlstr varchar(8000)

DECLARE id_list CURSOR FOR
select id from table_name where datalength(image_data) > 4000 order by id
OPEN id_list
FETCH next FROM id_list INTO @id
      WHILE @@fetch_status=0
      BEGIN
            --you would need to execute a command like below to get that data out in a file
            --exec master..xp_cmdshell N'sqlcmd -E -Q"select image_data from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.txt"', no_output
            set @sqlstr = 'exec master..xp_cmdshell N''sqlcmd -E -Q"select image_data from server_name.dba_name.schema_name.table_name where row_id = '+@id+'" -h-1 -s"," -W -o"c:\'+@id+'image_data_output.jpg"'', no_output'
            --exec @sqlstr      
            print @sqlstr
      FETCH next FROM id_list INTO @id
      END
CLOSE table_loop
DEALLOCATE table_loop
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rtayIT DirectorAuthor Commented:
Found a better way for me to handle this issue.

Select the image from the imagebox and add a save file dialog.

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        'Dim path As String
        Dim pic As Image
        pic = PicBlob.Image
        Dim saveFileDialog1 As New SaveFileDialog()
        saveFileDialog1.Filter = "JPeg Image|*.jpg|Bitmap Image|*.bmp|Gif Image|*.gif"
        saveFileDialog1.Title = "Save an Image File"
        saveFileDialog1.ShowDialog()
        pic.Save(saveFileDialog1.FileName)


    End Sub
rtayIT DirectorAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for rtay's comment http:/Q_27382692.html#36927950

for the following reason:

first solution was not complete, no response back but probably would work with some tweeking. &nbsp;final solution is clean and easy.
QPRCommented:
You may have found a better way but the answers given were exactly what your original question asked for. It is poor form to elaborate on a question after it has been answered and then want to close it having had a change of mind on what the question should actually be.

The question was: Retrieve a binary image from a SQL database and save it to a file as a .jpeg
That question/solution was given in full
rtayIT DirectorAuthor Commented:
No problem giving point to qpr as I don't really care.  just not sure how to do it and not accept the solution so other people with same problem will not try.  original posting explaned that images needed to be associated with primary key.  Solution posted would not do this, just pulled first record in the database regardless of which image was requested.  Probably would have worked with some tweeking that copying and pasting a microsoft document could not provide.  
Anthony PerkinsCommented:
>>Solution posted would not do this, just pulled first record in the database regardless of which image was requested. <<
That may be the case, but it lead you to the solution and that is what counts.  You may not want to acknowledge appreciation for a volunteer to go out of their way to help you, but it is only fair to do so.  In the end it does not really matter whether you "really care" or not, but whether you are complying with the guidelines to this site that you accept every time you sign on.
rtayIT DirectorAuthor Commented:
Solution works as long as you only want the first record in your database.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.