Show images from database in vb.net 2

[dparrott]

Hey everyone,

I am fairly new to VB.NET and have a general background in programming with several different languages, but nothing very deep in any of them. I currently have a database in access 2003 that is storing bitmaps. I am writing a vb.net application that interfaces with the database and performs the functions that I had set up Access to do.

I have got the database working with the app I have written with one glaring issue. I cannot/do no know how to display the bitmaps. I have looked at the bitmap class, and think that that is probably the correct class to use, but cannot figure it out.

The datatype in the database is OLE object.

If you need anymore information, I will try my best to fill you in.

Pulling the files out of the database and into separate files would be my last choice (not only because I would still have to store the filenames in the database, but I have about 500 records in the database and would like to spend my time improving the program than pulling the data out).

Thanks in advance to everyone who posts with ideas.

Danny

 

[SqueakinSweep]

Danny

I use the following code to get Images back from my SQL Database where they are stored as Image Datatype, which is simply binary data, as you have in Access. All you should need to change to the function below is your data connection, and data command, ie use OLEDBCommand instead of SQLCommand.

The code fetches an Object..then opens up a MemoryStream, inorder to get the file to a Bitmap, without saving it is a temporary file.

My SQL Table (Images) has a structure as such
Field DataType
---------------
ID Int
Name Char 40
Picture Image

Public Function zReadImage(ByVal ImageId As Integer) As Bitmap

'I just use this to get my personal connection string!
Dim o1 As New fwDataAccess.clsSQLDataConnection

Dim oCommand As New SqlCommand("select Picture from Images where image_id=@Id")
oCommand.Connection = New SqlConnection(o1.sConnectionString)

oCommand.Parameters.Add("@Id", SqlDbType.Int, 4)
oCommand.Parameters("@Id").Value = ImageId

Dim oPicture As Object
Dim bm As Bitmap

Try
   oCommand.Connection.Open()
   oPicture = oCommand.ExecuteScalar()
   Dim b As Byte() = CType(oPicture, Byte())
   'Open a stream and read in the Image
   Dim ms As New System.IO.MemoryStream(b)
   ms.Read(b, 0, b.Length)
  'Create Bitmap from Stream
   bm = New Bitmap(ms)
Catch ex As SqlException
   Exit Function
End Try

'Clean up
oCommand.Connection.Close()
oCommand.Dispose()
o1 = Nothing

Return bm

End Function

Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[dparrott]

Sweep,

Thanks for the help. When I get a chance, I will try this and see if I need any more help.

Danny

 

[dparrott]

Sweep,

I tried to get the code working. I am getting the following error on the line "bm = New Bitmap(ms)"

System.ArgumentException:Invalid parameter used.
at System.Drawing.Bitmap..ctor(Stream stream)
at Magic_Database.DataAccessInputFromDb.ReadImage(string CardName) in c:\Magic Database\DataAccessInputFromDb.vb:Line 168.

This is the code as it is being run.

Public Shared Function ReadImage(ByVal CardName As String) As Bitmap

        Dim sqlQuery As String = "SELECT Picture FROM Cards WHERE CardName = '" & CardName & "'"
        'I just use this to get my personal connection string!
        ' Dim o1 As New fwDataAccess.clsSQLDataConnection

        Dim oCommand As New OleDbCommand(sqlQuery)
        oCommand.Connection = conCard

        oCommand.Parameters.Add("@Id", SqlDbType.Int, 4)
        oCommand.Parameters("@Id").Value = CardName

        Dim oPicture As Object
        Dim bm As Bitmap

        Try
            oCommand.Connection.Open()
            oPicture = oCommand.ExecuteScalar()
            Dim b As Byte() = CType(oPicture, Byte())
            'Open a stream and read in the Image
            Dim ms As New System.IO.MemoryStream(b)
            ms.Read(b, 0, b.Length)
            'Create Bitmap from Stream
            bm = New Bitmap(ms)
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try

        'Clean up
        oCommand.Connection.Close()
        oCommand.Dispose()
        ' o1 = Nothing

        Return bm

    End Function

Because of the way that I have the function calling this set up, I had to share this function because I don't instantiate the DataAccessInputFromDb class.

If you can help me figure out what is causing this, I would greatly appreciate it.

Danny

 

[SqueakinSweep]

First off I can see....

You're passing in Parameters INT Type, and setting it to a string variasble. You actually dont need the ocommand.parameters bit at all the way you have coded it. Comment out those 2 lines and try again



Sweep
...if it works dont f*** with it
...if its f****ed blame someone else
...if its your fault that its f***ed, say and admit nothing.

 

[dparrott]

Sweep,

I should have seen that myself. However, after commenting out those two lines, I get the same exact error. I have also tried (I think, my instructor was not very good about explaining datasets and such in the class I have taken) using a data adapter to fill a dataset as I was taught and have working to pull the rest of the data out of the database. With the data adapter method I still get the same exact error on the same line of code ("bm = new Bitmap(ms)").


My table structure is as follows:
Field Datatype
-------------------------
PK Number
CardName Text
Color Number
Type Number
Cost Text
Power Text
Toughness Text
Rariy Number
Set Number
Legendary Yes/No
Picture OLE Object
Notes Memo


The datatypes are the Access datatypes.

This is the code with the adapter in it.

Public Shared Function ReadImage(ByVal CardName As String) As Bitmap

        Dim sqlQuery As String = "SELECT Picture FROM Cards WHERE CardName = '" & CardName & "'"
        
        Dim oPicture As Object
        Dim bm As Bitmap
        Dim dsPicture As New DataSet
        Try
           If conCard.State = ConnectionState.Closed Then
                conCard.Open()
            End If           
            Dim adpPicture As New OleDbDataAdapter(sqlQuery, conCard)
            adpPicture.Fill(dsPicture, "Cards")
            If dsPicture.Tables("Cards").Rows.Count > 0 Then
                Dim dsRow As DataRow
                For Each dsRow In dsPicture.Tables("Cards").Rows
                    oPicture = dsRow("Picture")
                    Dim b As Byte() = CType(oPicture, Byte())
                    'Open a stream and read in the Image
                    Dim ms As New System.IO.MemoryStream(b)
                    ms.Read(b, 0, b.Length)
                    'Create Bitmap from Stream
                    bm = New Bitmap(ms)
                Next
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
       
       'Clean up 
        dsPicture = Nothing
        If conCard.State = ConnectionState.Open Then
            conCard.Close()
        End If
        
        
        Return bm

    End Function

I am currently calling this function from within another function that uses the same connection "conCard" because I am pulling all the data out of the table and creating the new card object before adding it to an arraylist.

 

[bjd4jc]

SqueakinSweep-

Just what I needed and it was right on top of the thread list!!! Thanks for posting this!! Have a star on me.