How to save many pictures from SQL Server database to specific folder using vb.net

I'm new to vb.net and I have a problem to do my task.. Firstly, I have a project in vb.net that has a process button which, when clicked, will show a list of staff names from a SQL Server database (I'm using SQL Server 2008 R2) to a list view. Then I have copy button, one button is for copying the list name to notepad (success) and another one is for copying the list name to Excel (success). The list view includes staff id, first name and last name and each id has its own image (staff image).

I want to save all the images (1137 images) from the SQL Server database to a specific folder on the desktop. I did, but only one image is saved. How can I save all 1137 images to the folder? Can someone help me?

This is my code:

 Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
         Dim cn As SqlConnection
         cn = New SqlConnection
         cn.ConnectionString = "MyConnString;"
         Dim cmd As New System.Data.SqlClient.SqlCommand("SELECT pt_image FROM BadgeImages.dbo.portrait")

         cmd.Connection = cn
         cmd.CommandType = CommandType.Text

         cn.Open()
         Dim ImgStream As New IO.MemoryStream(CType(cmd.ExecuteScalar(), Byte()))
         Dim Image As System.Drawing.Image = System.Drawing.Image.FromStream(ImgStream)
         Image.Save("C:\Documents and Settings\prymax\Desktop\images + ".jpg", Imaging.ImageFormat.Jpeg)
End Sub 

This code only saves 1 image =(

Answers


You need to use a DataReader. The following are your options:

  1. use a DataReader, and loop through your results while saving them using the technique you demonstrated above.

  2. turn the contents of btnsave_Click into a function, and loop through a list while passing the data to the said function (using the contents of the list as the identifier for WHERE in your SQL)

  3. you can set each image using DataReader into a Picturebox, then immediately save the content of your Picturebox using

    Picturebox.Image.Save("path of image", imagefiletype)
    

These should all work.


Public Sub ExportImages()
    ConnDB()
    sql = "select Photo,ID from student"
    cmd = New MySqlCommand(sql, con)
    dr = cmd.ExecuteReader
    While dr.Read
        Dim byteImg As Byte() = CType(dr(0), Byte())
        Dim strPath As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\StudentPics\" & "" & dr(1) & ".jpg"
        Dim fs As FileStream = New FileStream(strPath, FileMode.CreateNew, FileAccess.Write)
        fs.Write(byteImg, 0, byteImg.Length)
        fs.Flush()
        fs.Close()
    End While
    dr.Close()
    con.Close()
End Sub

This For me is Working fine


Need Your Help

Is there something like "if not exist create sequence ..." in Oracle SQL?

sql oracle sequence

For my application that uses an Oracle 8 DB, I am providing an SQL script to setup stuff like triggers, sequences etc., which can be copied and pasted into SQL*Plus. I would like the script to not ...