How can I save image in SQL SERVER by VB.NET?

[Kingcoder210]

Hi everyone! I am a junior database programmer. I need to know how can I save image in SQL SERVER 2000 by VB.NET? I made a table in SQL SERVER with two fields. One is Id[nvarchar] & another pic[nvarchar]. I got two forms there in VB.NET project. In the first form I got Picture Box[PictureBox], Text Box[txt1] & two buttons[Button1 & Button2]. I got one module there in my project.

Module code :

Public con As New ADODB.Connection
Public rst As New ADODB.Recordset

Button1 code :

Dim dlg As OpenFileDialog
                             Dim img As Image
                             Try
                             dlg = New OpenFileDialog
                             dlg.Filter = "All Pictures|*.bmp;*.gif;*.jpg;*.png|" & _
                             "Bitmaps|*.bmp|GIFs|*.gif|JPEGs|*.jpg|PNGs|*.png"
                             If dlg.ShowDialog = DialogResult.OK Then
                             Me.Cursor = Cursors.WaitCursor
                             PictureBox.Image = New Bitmap(dlg.FileName)
                             End If
                             Catch ex As Exception
                             Finally
                             img = Nothing
                             dlg = Nothing
                             Me.Cursor = Cursors.Default
                             End Try
        
[B][U]Button2 code :[/U][/B] con.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test")
                             rst = New ADODB.Recordset
                             With rst
                             .Open("Select * from pic_info", con, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
                             .AddNew()
                             .Fields("Id").Value = txt1.Text
                             .Fields("pic").Value = PictureBox.Image
                             .Update()
                             .Close()
                             End With

Result: It saves Id number. I guess it also saves image because when I open pic_info table from SQL SERVER 2000 it shows "System.Drawing.Bitmap" there in the pic field.

Second form:

I got one Combo Box[cmbid], one Picture Box[picbox] & one button[Button1].

Form load code :

cmbid.Items.Clear()
                               con = New ADODB.Connection
        con.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test")
                               rst = New ADODB.Recordset
                               With rst
                               .Open("Select * From pic_info", con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
                               If .BOF = False Then
                               .MoveFirst()
                               While .EOF = False
                               cmbid.Items.Add(.Fields("Id").Value)
                               .MoveNext()
                               End While
                               End If
                               .Close()
                               End With

[B][U]Button1 code :[/U][/B] rst = New ADODB.Recordset
                                   With rst
            .Open("Select pic From pic_info where Id = '" & cmbid.Text & "'", con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
                                   If .BOF = False Then
                                   .MoveFirst()
                                   While .EOF = False
                                   picbox.Image = (.Fields("pic").Value)
                                   .MoveNext()
                                   End While
                                   End If
                                   .Close()
                                   End With

Result : It shows error message "Unable to cast object of type 'System.String' to type 'System.Drawing.Image'" when I run this project.

So I need help. Please help me. I need to know how can I save & retrieve image in SQL SERVER 2000 by VB.NET easily.

[Teme64]

> I guess it also saves image because when I open pic_info
> table from SQL SERVER 2000 it shows "System.Drawing.Bitmap" there in the pic field.

No it didn't. It converted PictureBox.Image object to a string and saved the string "System.Drawing.Bitmap".

Easiest way to handle System.Drawing.Bitmap type is to convert Image object to an array of bytes. Now you'll have two types in the SQL Server that support storing binary data, namely Binary and Image.

First, change the database schema:
Id nvarchar
pic image

and name the table as "ImageTable" (or change the table's name in the following code).

Here's the routines to save/load array of bytes:

''' <summary>
  ''' Save a byte array to database
  ''' </summary>
  ''' <param name="ByteArr">Contains bytes to be saved</param>
  ''' <param name="ImageID">ID for the image</param>
  ''' <remarks></remarks>
  Public Sub SaveByteArray(ByVal ByteArr() As Byte, ByVal ImageID As String)
    '
    Dim strSQL As String
    Dim oConn As SqlConnection
    Dim oCmd As SqlCommand
    Dim oVarCharParam As SqlParameter
    Dim oBLOBParam As SqlParameter

    Try
      MsgBox(ByteArr.Length) ' DEBUG
      ' Create and open connection object. Replace m_CnStr with your connection string
      oConn = New SqlConnection(m_CnStr)
      oConn.Open()
      ' Insert statement
      ' Notice that @BLOBValue is a placeholder for the actual data
      strSQL = "INSERT INTO ImageTable (Id, pic) VALUES (@IDValue, @BLOBValue)"
      ' Create a command object
      oCmd = oConn.CreateCommand()
      ' Set SQL statement
      oCmd.CommandText = strSQL
      ' Create a command parameter
      oVarCharParam = New SqlParameter("@IDValue", SqlDbType.VarChar, _
         50, ParameterDirection.Input.ToString)
      ' Set the actual data
      oVarCharParam.Value = ImageID
      ' Add this parameter to the command
      oCmd.Parameters.Add(oVarCharParam)
      ' Create a command parameter
      oBLOBParam = New SqlParameter("@BLOBValue", SqlDbType.Binary, _
        ByteArr.Length, ParameterDirection.Input.ToString)
      ' Finally, set the actual data
      oBLOBParam.Value = ByteArr
      ' Add this parameter to the command
      oCmd.Parameters.Add(oBLOBParam)
      ' Execute SQL statement
      oCmd.ExecuteNonQuery()
      ' Close the connection
      oConn.Close()
    Catch ex As Exception
      MsgBox(ex.Message) ' DEBUG
    End Try

  End Sub

  ''' <summary>
  ''' Load a byte array from database
  ''' </summary>
  ''' <param name="ByteArr">Contains bytes from the database</param>
  ''' <param name="ImageID">ID for the image</param>
  ''' <remarks></remarks>
  Public Sub LoadByteArray(ByRef ByteArr() As Byte, ByVal ImageID As String)
    '
    Dim strSQL As String
    Dim oConn As SqlConnection
    Dim oCmd As SqlCommand
    Dim ValueFromDB As Object ' ExecuteScalar method returns an object

    Try
      ' Create and open connection object. Replace m_CnStr with your connection string
      oConn = New SqlConnection(m_CnStr)
      oConn.Open()
      ' Select statement
      strSQL = "SELECT pic FROM ImageTable WHERE Id='" & ImageID & "'"
      ' Create a command object
      oCmd = oConn.CreateCommand()
      ' Set SQL statement
      oCmd.CommandText = strSQL
      ' Execute SQL statement
      ValueFromDB = oCmd.ExecuteScalar()
      ' Close the connection
      oConn.Close()
      ' Convert returned object to an array of bytes
      If ValueFromDB IsNot DBNull.Value Then
        ByteArr = CType(ValueFromDB, Byte())
      End If
    Catch ex As Exception

    End Try

  End Sub

Now you need the routines to convert between VB's Image type and Byte Array type:

''' <summary>
  ''' Convert a byte array to an Image
  ''' </summary>
  ''' <param name="NewImage">Image to be returned</param>
  ''' <param name="ByteArr">Contains bytes to be converted</param>
  ''' <remarks></remarks>
  Public Sub Byte2Image(ByRef NewImage As Image, ByVal ByteArr() As Byte)
    '
    Dim ImageStream As MemoryStream

    Try
      If ByteArr.GetUpperBound(0) > 0 Then
        ImageStream = New MemoryStream(ByteArr)
        NewImage = Image.FromStream(ImageStream)
      Else
        NewImage = Nothing
      End If
    Catch ex As Exception
      NewImage = Nothing
    End Try

  End Sub

  ''' <summary>
  ''' Convert an image to array of bytes
  ''' </summary>
  ''' <param name="NewImage">Image to be converted</param>
  ''' <param name="ByteArr">Returns bytes</param>
  ''' <remarks></remarks>
  Public Sub Image2Byte(ByVal NewImage As Image, ByRef ByteArr() As Byte)
    '
    Dim ImageStream As MemoryStream

    Try
      ReDim ByteArr(0)
      If NewImage IsNot Nothing Then
        ImageStream = New MemoryStream
        NewImage.Save(ImageStream, ImageFormat.Jpeg)
        ReDim ByteArr(CInt(ImageStream.Length - 1))
        ImageStream.Position = 0
        ImageStream.Read(ByteArr, 0, CInt(ImageStream.Length))
        MsgBox(ByteArr.Length) ' DEBUG
      End If
    Catch ex As Exception
      MsgBox(ex.Message) ' DEBUG
    End Try

  End Sub

Ok. Now you have all the pieces. Here's an example how to save and load the image:

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    ' Saves the image from the picture box control to SQL Server
    Dim ImageByteArr(0) As Byte ' An array to hold image (bytes)
    Dim ImageID As String ' ID for the image

    ' Get ID from a text box control and remove spaces
    ImageID = TextBox1.Text.Trim
    ' Convert Image to an array of bytes
    Image2Byte(PictureBox1.Image, ImageByteArr)
    ' Save byte array to SQL Server's field of type Image
    SaveByteArray(ImageByteArr, ImageID)

  End Sub

  Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    ' Loads the image to the picture box control from SQL Server
    Dim ImageByteArr(0) As Byte ' An array to hold image (bytes)
    Dim ImageID As String ' ID for the image

    ' Get ID from a text box control and remove spaces
    ImageID = TextBox1.Text.Trim
    ' Load an array of bytes from SQL Server's field of type Image
    LoadByteArray(ImageByteArr, ImageID)
    ' Convert an array of bytes to image
    Byte2Image(PictureBox1.Image, ImageByteArr)

  End Sub

You could have used also DaniWeb's search function to locate this or similar answers that have been posted in a past few years to this same question :)

HTH