Showing the progress of an SqL or even any database query

Is there anyway that i can show the progress of an SQL or other Database query when I am to call the fill dataset method on the Database Adapter, in VB.Net.  For instance if i wanted to have a progress indicator bar.  
LVL 2
ethnarchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
As far that I know, this is not possible.
Anyhow,
* as you / the reader cannot know how many rows will arrive in your recordset in advance, it won't help anyhow.
  --> workaround in case you knew the number of records: read 1 record at a time :::: DONT DO THAT ::::
* as MOST queries should return 1-few rows, this should be a fast process (<3 seconds), so showing a progress would just "flicker" in front of the user
  --> if you basic single-record queries take > 1 second, tune them (add indexes on the keys)
* for larger results, you should prepare to get a solution to "page" results, to show as few as possible to the user
  --> posting 1000 records at a time to the user at once DOES NOT MAKE ANY SENSE  (use drill-down and/or reports)

SweetsGreenCommented:
I don't know of any way to do what you are asking, but in situations like yours I usually use somthing like an animated icon or a simple spinning cursor.  Or anything that give the user a sense that somthing is actually working even if it does not progress from 1-100 like a progress bar.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
planoczCommented:
Here is a sample that I use when loads datasets from my database...
Just add a pic for the background.

'FORM 1

Public Class frmSplash
    Inherits System.Windows.Forms.Form
    Public Shared oform As frmMDI         '<---- My MDI main Form
#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents ProgressBar1 As System.Windows.Forms.ProgressBar
    Friend WithEvents Timer1 As System.Windows.Forms.Timer
    Friend WithEvents StartTimer As System.Windows.Forms.Timer
    Friend WithEvents LinkLabel As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(frmSplash))
        Me.ProgressBar1 = New System.Windows.Forms.ProgressBar
        Me.Timer1 = New System.Windows.Forms.Timer(Me.components)
        Me.StartTimer = New System.Windows.Forms.Timer(Me.components)
        Me.LinkLabel = New System.Windows.Forms.Label
        Me.SuspendLayout()
        '
        'ProgressBar1
        '
        Me.ProgressBar1.Location = New System.Drawing.Point(4, 208)
        Me.ProgressBar1.Name = "ProgressBar1"
        Me.ProgressBar1.Size = New System.Drawing.Size(88, 16)
        Me.ProgressBar1.TabIndex = 0
        '
        'Timer1
        '
        Me.Timer1.Interval = 300
        '
        'StartTimer
        '
        '
        'LinkLabel
        '
        Me.LinkLabel.BackColor = System.Drawing.Color.Transparent
        Me.LinkLabel.ForeColor = System.Drawing.Color.Black
        Me.LinkLabel.Location = New System.Drawing.Point(100, 208)
        Me.LinkLabel.Name = "LinkLabel"
        Me.LinkLabel.Size = New System.Drawing.Size(156, 16)
        Me.LinkLabel.TabIndex = 1
        Me.LinkLabel.Text = "Please Wait... Loading Tables"
        '
        'frmSplash
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.BackColor = System.Drawing.Color.White
        Me.BackgroundImage = CType(resources.GetObject("$this.BackgroundImage"), System.Drawing.Image)
        Me.ClientSize = New System.Drawing.Size(252, 228)
        Me.ControlBox = False
        Me.Controls.Add(Me.LinkLabel)
        Me.Controls.Add(Me.ProgressBar1)
        Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "frmSplash"
        Me.Opacity = 0
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.TopMost = True
        Me.ResumeLayout(False)

    End Sub
#End Region
    Private Sub beginLoadingComponents()
        Me.LinkLabel.Text = "Please Wait... Loading Tables"
        oform = New frmMDI
        Me.Timer1.Enabled = True
        Me.ProgressBar1.Visible = True
        Dim i As Short

        Try
            For i = 1 To 9
                Me.ProgressBar1.Value += i
            Next
            ' FillDataSet(fTable.QuoteTable)   'Dataset Table =0  USED to FILL DATASETS
            For i = 1 To 8
                Me.ProgressBar1.Value += i
            Next
            'FillDataSet(fTable.QuoteDates)    'Dataset Table =1
            Me.ProgressBar1.Value += i
            'FillDataSet(fTable.JoinTables)   'Dataset Join both tables Table =2
            Me.LinkLabel.Text = "Please Wait... Loading Tables"
            If Me.ProgressBar1.Value >= 90 Then
                Me.LinkLabel.Text = "Tables loaded"
                Timer1.Enabled = False
                Me.Visible = False
            End If
        Catch Exp As ArgumentException
            If ProgressBar1.Value > 100 Then
                ProgressBar1.Value = 100
            End If
            Cursor.Current = Cursors.Default
            Application.Exit()  '<--- exit
        End Try
        oform.ShowDialog()      '<--- show your main form
        Application.Exit()      '<--- exit
    End Sub
    Dim elapsed As Short = 0
    Private Sub StartTimer_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles StartTimer.Tick
        elapsed += 1
        Me.Opacity = elapsed * 0.1
        If elapsed >= 10 Then
            Me.LinkLabel.Visible = True
            Me.StartTimer.Enabled = False
            Me.beginLoadingComponents()
        End If
    End Sub
    Private Sub frmSplash_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.StartTimer.Enabled = True
    End Sub
End Class
RonaldBiemansCommented:
you could do something like this (if you know the number of rows you are getting)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ds.Tables.Add("test")
        AddHandler ds.Tables(0).RowChanged, AddressOf ron
        SqlDataAdapter1.Fill(ds, "test")
        DataGrid1.DataSource = ds.Tables(0)
    End Sub

    Private Sub ron(ByVal sender As Object, ByVal e As System.Data.DataRowChangeEventArgs)
        pb1.Value += 1
    End Sub

but filling a dataset is not the most time consuming the sql server usually takes the most time getting the data in the first place

and take the things AngelIII said into acoount, he is absolutly right in all of his points :-)
ethnarchAuthor Commented:
AngelIII is correct however i never said anything about showing the user any results, I am not going to page the data to the user because the user does not have to see the data.  Let me explain better the purpose:

I have a pos program that keeps track of inventory.  We have a massive problem with the quantity of items we recieve.  I am just trying to find a way to find items in the inventory which have a quantity of 0 on hand and have do not show up in any of the invoices(which is basically the items on the receipt when you buy something).  The company i work for sells many things it will never get instock for a long time.  However our database grows exponentially we currently have over 200,000 items in it.  when there are only about 5,000 in inventory so if you can imagine this is a huge problem with speed.  So i am trying to find a way to remove the unused items from the database and then put them in a temporary table incase we need them again. I have to admit i am a newbie as far as programming. That is pretty much the gist of the situation.  I was just looking for a better way to "Realistically" what progress was taking place.

As far as flickering that's certainly not a problem here when the query i am running takes about 2-3 minutes to run, but i certainly would not want to run each row at a time, ugh that would be much to slow never mind the extra step to get a row count first. So unless someone else has a way to do this i thankyou all for your input.

I had actually implemented something like what planocz suggested, but thanks.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.