[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-05
9
Medium Priority
?
946 Views
Last Modified: 2012-05-12
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
Comment
Question by:rtay
9 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 1500 total points
ID: 36921767
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
 
LVL 5

Author Comment

by:rtay
ID: 36925868
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36925961
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 5

Author Comment

by:rtay
ID: 36927950
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
 
LVL 5

Author Comment

by:rtay
ID: 36927993
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
 
LVL 29

Expert Comment

by:QPR
ID: 36927994
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
 
LVL 5

Author Comment

by:rtay
ID: 36928007
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36928073
>>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
 
LVL 5

Author Closing Comment

by:rtay
ID: 36928120
Solution works as long as you only want the first record in your database.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question