Connect to IBM DB2 & Read Image Column Using VB.Net

IBM DB2 LUW or DB2 Linux Unix Windows is one of a recommended RDBMS software used by many organizations.
By its name this DB2 LUW is a platform independent because it can be run on Linux, Windows, Solaris, Mac OS.

IBM has free version of DB2 LUW which is DB2 Express-C. Unlike another RDBMS, DB2 Express-C does not have any time restrictions or database size limitations.
So this free version can be used in production environment.

Because of platform independent we can install DB2 server on Linux as database server to avoid virus infection. Since MS Windows is more widely used (at least in my country) then we also can use MS Windows to run client application (VB.Net) that connect to DB2.
I will show you example using VB.Net to work with DB2 and also to get image data.

DB2 .Net Data Provider

IBM provides .Net Data Provider for DB2. It is located on installation of DB2 folder like C:\Program Files\IBM\SQLLib\Bin\netf20\IBM.Data.DB2.dll. I added this library file to project’s reference.
I use DB2Connection class to establish connection to DB2. DB2DataAdapter to retrieve recordset and DB2Command to execute sql query.

Example

I use SAMPLE database and populate DataGridView with Employee data. If employee has a photo then it will be showed on picture box.
Please refer to this pic below:


Imports IBM.Data.DB2
Imports System.Data
Imports System.IO
Public Class Form1
    Private connectionstring As String

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        connectionstring = "server=127.0.0.1;database=SAMPLE;UID=my_user_id;PWD=my_passwd"

        LoadGridView()
    End Sub
    Private Sub LoadGridView()
        Dim ds As New DataSet()
        Dim dbconn As New DB2Connection(connectionstring)
        dbconn.Open()
        Dim da As New DB2DataAdapter("select empno, firstnme, lastname, hiredate, job from EMPLOYEE", dbconn)
        da.Fill(ds)

        DataGridView1.DataSource = ds
        DataGridView1.DataMember = ds.Tables(0).TableName
        da.Dispose()
        dbconn.Close()

    End Sub
    Private Sub DataGridView1_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
        Dim currentRow As Integer = e.RowIndex

        If Not IsNothing(DataGridView1(0, currentRow).Value) Then

            Dim empno As String = DataGridView1(0, currentRow).Value.ToString()

            Dim dbconn As New DB2Connection(connectionstring)
            dbconn.Open()
            Dim cmd = New DB2Command()
            cmd.Connection = dbconn
            cmd.CommandText = "select picture from emp_photo where empno='" & empno & "'"

            Dim b() As Byte
            b = cmd.ExecuteScalar()
            dbconn.Close()

            If IsNothing(b) Then
                PictureBox1.Image = Nothing
                PictureBox1.Show()
            Else
                Dim ms As New MemoryStream(b)
                Dim img As Image = Image.FromStream(ms)
                AutosizeImage(img, PictureBox1)
                PictureBox1.Show()

            End If
        End If
    End Sub

    Private Sub AutosizeImage(ByVal imgOrigin As Image, ByVal picBox As PictureBox, Optional ByVal pSizeMode As PictureBoxSizeMode = PictureBoxSizeMode.CenterImage)
        Try
            picBox.Image = Nothing
            picBox.SizeMode = pSizeMode

            Dim imgOrg As Bitmap
            Dim imgShow As Bitmap
            Dim g As Graphics
            Dim divideBy, divideByH, divideByW As Double
            imgOrg = DirectCast(imgOrigin, Bitmap)

            divideByW = imgOrg.Width / picBox.Width
            divideByH = imgOrg.Height / picBox.Height
            If divideByW > 1 Or divideByH > 1 Then
                If divideByW > divideByH Then
                    divideBy = divideByW
                Else
                    divideBy = divideByH
                End If

                imgShow = New Bitmap(CInt(CDbl(imgOrg.Width) / divideBy), CInt(CDbl(imgOrg.Height) / divideBy))
                imgShow.SetResolution(imgOrg.HorizontalResolution, imgOrg.VerticalResolution)
                g = Graphics.FromImage(imgShow)
                g.InterpolationMode = Drawing2D.InterpolationMode.HighQualityBicubic
                g.DrawImage(imgOrg, New Rectangle(0, 0, CInt(CDbl(imgOrg.Width) / divideBy), CInt(CDbl(imgOrg.Height) / divideBy)), 0, 0, imgOrg.Width, imgOrg.Height, GraphicsUnit.Pixel)
                g.Dispose()
            Else
                imgShow = imgOrg
            End If
            imgOrg.Dispose()

            picBox.Image = imgShow

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

End Class

The connection string is simply “server=ip_address;database=SAMPLE;UID=my_user_id;PWD=my_passwd”. LoadGridView() method is used to fill the grid with employee data.

Event DataGridView1_RowEnter will check whether employee has a photo or not. If yes then show it and fit the photo’s size into picture box.
I get the image byte data using DB2Command and ExecuteScalar method. After that I use MemoryStream class to convert array of bytes to image.
Please follow this url http://dotnet-snippets.com/dns/vbnet-resize-image-to-fit-in-picturebox-SID213.aspx to know how to resize image.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s