A daft question about datasets 2

[Appollo14]

Hi,

I'm new to programming and after messing with access an awful lot i've decided to have a go at vb.net so please excuse me if this is a bit basic.

I've written a small app that stores contact details and images (stored in the db) associated with those contacts. I've got one form in particular that is causing me some concern as i've seen how system performance on sql db's can be affected by un thought out select queries. The for in itself is quite simple, it contains one image but gives the user the option to scroll through all of the associated contacts images. At the minute i only have 6 or seven images for each contact but what will happen if i ever get to 100's or even 1000's? will reading all of these items into the recordset cause the system to perform slowly - and possibly the network ? If so is there a way to break the recordset up into smaller chunks so that it is more manageable?

Thanks,
Noel.

 

[jebenson]

One solution is to use paging to retrieve a subset of the contact records at any one time rather than loading all of them at once. Here'a a link to get you started:

Implementing Paging in ADO.NET

This article if for VB .Net 2003 (1.1 Framework), but the code should work in VB .Net 2005 (2.0 Framework).

You don't have to do paging with just the record numbers, as shown in the link. Since this is a contacts database application, you could - probably should, actually - base the paging off the first letter of the contact's last name.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[chrissie1]

A very interesting article jebenson. Now I have to test it to see if it is any faster.

Christiaan Baes
Belgium

"My new site" - Me

 

[Appollo14]

Thanks for that jebenson, it looks like that may be the way to go. I'll have a read and give it a go.

Thanks,
Noel.

 

[chrissie1]

BTW if ever you get to 100's (not 1000's) of images in the db you will reach the access datafile limit of 2Gb. And it will become slow much before that.

What I did (long ago) was save the path (as text) in the database and then open it via image.fromfile (easy as pie). But because pictures can become very big very soon I created a thumbnail for every picture and I load that instead and when they doubleclick on that it will open the real picture. You can create thumbnails at runtime but that is not a good idea since it will still need to get the bigger picture (no pun intended) So you only do that once and save the thumbnail in the same directory with thumb attached to the end or beginning. if you want I can give you the method I use. It is multithreaded.

Christiaan Baes
Belgium

"My new site" - Me

 

[techsmith]

Chrissie, I'd be interested to see your method. We need to provide thumbnail images of reports, so at some stage I will need to generate these.

 

[Appollo14]

Hi Christiaan, thanks for the advice. At the minute the db is only for learning, i'm eventually going to shift it to sql when i settle on an idea of what the final app will be.

While i'm here i've read thro the article posted and have tried to apply the theory to my code and i'm getting a little stuck. I cant seem to move the recordset onto the next record although everything appears fine and i'm getting no errors.

I've stepped thro the code, ran thro the query in an sql view in access and can not spot anything obvious.

After loading the initial image ive put this code in for moving forward thro the images.

if Inc <> MaxRows - 1 Then
Try
Dim ConId As Integer
ConId = Me.txtConId.Text
Sql = "SELECT TOP 1 * from tblConImage where (ContactId = " & ConId & " and ID > " & CurrentRec & ") order by ID;"
Da = New OleDb.OleDbDataAdapter(Sql, Con)
Da.Fill(Ds, "ImageFilter")
Ds.GetChanges()
Inc = Inc + 1
NavigateImages()
Catch ex As Exception
MessageBox.Show("Sorry there are no images for this contact", "No Images", MessageBoxButtons.OK)
Me.Close()
End Try
Else : End If
End Sub

then it goes thro this bit;

Private Sub NavigateImages()
Try
Dim buffer() As Byte = CheckDBNull(CType(Ds.Tables("ImageFilter").Rows(0).Item(1), Byte()))
Dim str As New MemoryStream(buffer)
bxImageView.Image = CheckDBNull(Image.FromStream(str))
Me.lblCount.Text = "Image " & Inc + 1 & " of " & Ds.Tables("ImageFilter").Rows.Count
CurrentRec = Ds.Tables("ImageFilter").Rows(Inc).Item(0)
Catch ex As Exception
MessageBox.Show("Sorry there are no images for this contact", "No Images", MessageBoxButtons.OK)
Me.Close()
End Try

The problem is that when i run the sql query it seems to ignore the "ID > CurrentRec" part. After stepping thro the code I can see that the value of current rec is the value of the first record that i'd expect to pick up.

I'd be gratefull for any pointers as to where i'm going wrong.

Thanks,
Noel.

 

[chrissie1]

Well here it is.

Public Class Changeimage
        Private strImagepath As String
        Private objNormalimage As Image
        Private objThumbnailimage As Image
        Private intMaximumImagesize As Integer
        Private bolLoadingLargeImageFinished As Boolean
        Private bolLoadingThumbnailImageFinished As Boolean
        Private thrChangeLargeImgage As New System.Threading.Thread(AddressOf ChangeLargeImage)
        Private thrChangethumbnailimage As New System.Threading.Thread(AddressOf ChangeThumbnailImage)

        Public Event ThumbnailPictureChanged(ByRef ThumbnailImage As Image)
        Public Event Largepicturechanged(ByRef NormalImage As Image, ByVal ImagePath As String)

        Public Property ImagePath() As String
            Get
                Return strImagepath
            End Get
            Set(ByVal Value As String)
                strImagepath = Value
            End Set
        End Property

        Public ReadOnly Property NormalImage() As Image
            Get
                Return objNormalimage
            End Get
        End Property

        Public ReadOnly Property ThumbnailImage() As Image
            Get
                Return objThumbnailimage
            End Get
        End Property

        Public Property MaximumImageSize() As Integer
            Get
                Return intMaximumImagesize
            End Get
            Set(ByVal Value As Integer)
                intMaximumImagesize = Value
            End Set
        End Property

        Public ReadOnly Property LoadingLargImageFinished() As Boolean
            Get
                Return bolLoadingLargeImageFinished
            End Get
        End Property

        Public ReadOnly Property LoadingThumbnailImageFinished() As Boolean
            Get
                Return bolLoadingThumbnailImageFinished
            End Get
        End Property

        Public Sub New()
            Me.bolLoadingLargeImageFinished = True
            Me.bolLoadingThumbnailImageFinished = True
            Me.thrChangeLargeImgage = Nothing
            Me.thrChangethumbnailimage = Nothing
        End Sub

        Public Sub ChangeImages(ByVal Imagepath As String, ByVal MaximumImageSize As Integer)
            strImagepath = Imagepath
            intMaximumImagesize = MaximumImageSize
            Me.bolLoadingThumbnailImageFinished = False
            Me.bolLoadingLargeImageFinished = False
            If Not thrChangeLargeImgage Is Nothing Then
                thrChangeLargeImgage.Abort()
            End If
            If Not Me.thrChangethumbnailimage Is Nothing Then
                Me.thrChangethumbnailimage.Abort()
            End If
            thrChangeLargeImgage = New System.Threading.Thread(AddressOf ChangeLargeImage)
            thrChangeLargeImgage.Start()
            thrChangethumbnailimage = New System.Threading.Thread(AddressOf ChangeThumbnailImage)
            thrChangethumbnailimage.Start()
        End Sub

        Public Sub ChangeImages()
            Me.bolLoadingThumbnailImageFinished = False
            Me.bolLoadingLargeImageFinished = False
            If Not thrChangeLargeImgage Is Nothing Then
                thrChangeLargeImgage.Abort()
            End If
            If Not Me.thrChangethumbnailimage Is Nothing Then
                Me.thrChangethumbnailimage.Abort()
            End If
            thrChangeLargeImgage = New System.Threading.Thread(AddressOf ChangeLargeImage)
            thrChangeLargeImgage.Start()
            thrChangethumbnailimage = New System.Threading.Thread(AddressOf ChangeThumbnailImage)
            thrChangethumbnailimage.Start()
        End Sub

        Public Sub RedoThumbnailImage()
            Me.bolLoadingThumbnailImageFinished = False
            If Not Me.thrChangethumbnailimage Is Nothing Then
                Me.thrChangethumbnailimage.Abort()
            End If
            Me.LoadNonExistingThumbnail()
        End Sub

        Private Sub ChangeThumbnailImage()
            If System.IO.File.Exists(strImagepath) Then
                If System.IO.File.Exists((strImagepath.Substring(0, strImagepath.Length - 4)) & "thumb.jpg") Then
                    Me.LoadExistingThumbnail()
                Else
                    Me.LoadNonExistingThumbnail()
                End If
            Else
                objThumbnailimage = Nothing
                RaiseEvent ThumbnailPictureChanged(Nothing)
            End If
            Me.bolLoadingThumbnailImageFinished = True
            Me.thrChangethumbnailimage = Nothing
        End Sub

        Private Sub LoadExistingThumbnail()
            Dim originalimage As Image
            Dim newgraphics As Graphics

            originalimage = Image.FromFile((strImagepath.Substring(0, strImagepath.Length - 4)) & "thumb.jpg")
            objThumbnailimage = New Bitmap(originalimage.Width, originalimage.Height)
            newgraphics = Graphics.FromImage(objThumbnailimage)
            newgraphics.DrawImage(originalimage, 0, 0, originalimage.Width, originalimage.Height)
            originalimage.Dispose()
            RaiseEvent ThumbnailPictureChanged(objThumbnailimage)
            originalimage = Nothing
            newgraphics = Nothing
        End Sub

        Private Sub LoadNonExistingThumbnail()
            Dim mycallback As System.Drawing.Image.GetThumbnailImageAbort = Nothing
            Dim picOriginalHeight, picOriginalWidth As Double
            Dim picNewHeight, picNewWidth As Double
            Dim originalimage As Image
            Dim objTempNormalImage As Image
            Dim newGraphics As Graphics

            originalimage = Image.FromFile(strImagepath)
            objTempNormalImage = New Bitmap(originalimage.Width, originalimage.Height)
            newGraphics = Graphics.FromImage(objTempNormalImage)
            newGraphics.DrawImage(originalimage, 0, 0, originalimage.Width, originalimage.Height)
            originalimage.Dispose()
            picOriginalHeight = objTempNormalImage.Height
            picOriginalWidth = objTempNormalImage.Width
            If picOriginalHeight > picOriginalWidth Then
                If picOriginalHeight > intMaximumImagesize Then
                    picNewHeight = intMaximumImagesize
                    picNewWidth = picOriginalWidth / picOriginalHeight * intMaximumImagesize
                Else
                    picNewHeight = picOriginalHeight
                    picNewWidth = picOriginalWidth
                End If
            Else
                If picOriginalWidth > intMaximumImagesize Then
                    picNewWidth = intMaximumImagesize
                    picNewHeight = picOriginalHeight / picOriginalWidth * intMaximumImagesize
                Else
                    picNewHeight = picOriginalHeight
                    picNewWidth = picOriginalWidth
                End If
            End If
            objThumbnailimage = objTempNormalImage.GetThumbnailImage(Convert.ToInt32(picNewWidth), Convert.ToInt32(picNewHeight), mycallback, System.IntPtr.Zero)
            RaiseEvent ThumbnailPictureChanged(objThumbnailimage)
            originalimage = Nothing
            objTempNormalImage = Nothing
            newGraphics = Nothing
        End Sub

        Private Sub ChangeLargeImage()
            If System.IO.File.Exists(strImagepath) Then
                Dim originalimage As Image = Image.FromFile(strImagepath)
                objNormalimage = New Bitmap(originalimage.Width, originalimage.Height)
                Dim newgraphics As Graphics = Graphics.FromImage(objNormalimage)
                newgraphics.DrawImage(originalimage, 0, 0, originalimage.Width, originalimage.Height)
                originalimage.Dispose()
                RaiseEvent Largepicturechanged(objNormalimage, strImagepath)
            Else
                RaiseEvent Largepicturechanged(Nothing, "No image available")
            End If
            thrChangeLargeImgage = Nothing
            Me.bolLoadingLargeImageFinished = True
        End Sub
    End Class

Christiaan Baes
Belgium

"My new site" - Me

 

[techsmith]

Thanks Chrissie, that's a big help.