• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 957
  • Last Modified:

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.  
0
rtay
Asked:
rtay
1 Solution
 
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")
0
 
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
0
 
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
0
 
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.
0
 
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
0
 
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.  
0
 
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.
0
 
rtayIT DirectorAuthor Commented:
Solution works as long as you only want the first record in your database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now