Solved

Show status of loading DataSet by Progress Bar

Posted on 2004-11-02
665 Views
Last Modified: 2008-01-09
Hi,

I have to present the status of loading data into a data grid by progress bar.
I have a SQL query that executes for about one minute. During that time (and loading data into grid), I need to present percentage of loaded data (or executed SQL) by progress bar. I do not know how many records will be returned from the query (nor want to execute somethink like "select count(*) from ....").
I have VB code like this :

Dim ds As DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql_query, DB_Connection_String As String
....
ds = New DataSet
da = New OleDb.OleDbDataAdapter(sql_query, DB_Connection_String)

da.Fill(ds)

Thanks, Peter
0
Question by:pnedic
    17 Comments
     
    LVL 25

    Expert Comment

    by:RonaldBiemans
    Hi peter,

    You cannot do that during a fill,

    There are a couple of things you can do,

    1. Use a datareader
    2. Use a seperate thread to simulate the data being (very inaccurate)
    3. just show a hourglass pointer or show a popup window during the fill
    0
     
    LVL 25

    Expert Comment

    by:RonaldBiemans
    Hold the phone, just thought of something, Give me a second and I'll test it first.
    0
     
    LVL 3

    Author Comment

    by:pnedic
    OK, I'm reading info about DataReader
    0
     
    LVL 25

    Expert Comment

    by:RonaldBiemans
    Hi Pnedic,

    I found possible other way but to get it the work properly you really need to now the maximum number of rows that are being read. Because the progressbar needs a .maximim value
    0
     
    LVL 25

    Expert Comment

    by:RonaldBiemans
    The same actually goes for the datareader, there you also need to know the maximum value of the progress bar
    0
     
    LVL 3

    Author Comment

    by:pnedic
    Hm,

    well, when the destiny is such terrible :), let's suppose I know the number of records to be returned (e.g. 1500). Then how to implement progress bar : using DataReader (OleDbDataReader) or I can use DataSet as well ?

    Thanks
    0
     
    LVL 25

    Accepted Solution

    by:
    Hi Pnedic,

    Well you actually can do it during the fill by using the rowchanged event of the dataset, something like this
    you have to use this line for it to work (SqlDataAdapter1.AcceptChangesDuringFill = True)

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            ds.Tables.Add("test")
            SqlDataAdapter1.AcceptChangesDuringFill = True
            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
    0
     
    LVL 25

    Expert Comment

    by:RonaldBiemans
    HMMM, you actually don't need this line

    (SqlDataAdapter1.AcceptChangesDuringFill = True)


    sorry my mistake :-)
    0
     
    LVL 3

    Author Comment

    by:pnedic
    Hm, tomorrow I'll let you know about the result. I need to check this

    Thanks
    0
     
    LVL 3

    Author Comment

    by:pnedic
    Hm, nothing helped. So I given up of progress bar.

    Other thing what I've tried is to remove progress bar and replace it with some animated gif.
    What happens is that subform (containing animated gif) is loaded but at the moment DataSet starts loading data, animated gif becomes frozen.
    My idea was, at form_load event, to open subform with animated gif and when DataSet (and data grid) is loaded with data, to close subform.

    Any idea how to overcome this ?

    Regards
    0
     
    LVL 3

    Author Comment

    by:pnedic
    Yap, one more thing, I have also tried to force refreshing of subform with using Timer object, but also got the same result.
    0
     
    LVL 25

    Expert Comment

    by:RonaldBiemans
    Hi Pnedic,

    What do you mean with nothing helped, do you get an error. Because I tested my solution and it works perfectly (if you know the total rows that are read).

    About the animated gif, if you want that to work you have to do that in a seperate thread otherwise the fill will use all the cpu and leave nothing for the gif.
    0
     
    LVL 3

    Author Comment

    by:pnedic
    Sorry, yes your solution is OK so far as I know the total number of records to be returned. Since I deal with an extreme "heavy" sql (returns more than 350K rows), executing this sql two times (first to get count and second to get data) is extremly "expensive". (I will need to pay attention to optimizing of SQL and tables - beside this programming problem). This is the reason why I have given up of progress bar.

    Can you give some practical example of generating separate thread that runs gif...

    Thanks
    0
     
    LVL 25

    Expert Comment

    by:RonaldBiemans
    So what did you do ?

    by the way I tested an executescalar on 500.000 records (to get to total number of rows) and it only took 0.7 seconds


    0
     
    LVL 3

    Author Comment

    by:pnedic
    Done using thread(s).
    I have placed executing SQL and loading DataSet and data grid in separate thread. Activating subform has placed after activating the loading data thread

    Thanks
    0
     
    LVL 3

    Author Comment

    by:pnedic
    executescalar ?

    can you be more specific ?

    Anyhow, I have tested executing SQL in TOAD, and also it takes one year (definitely it needs some actions on sql / table(s))
    0
     
    LVL 25

    Expert Comment

    by:RonaldBiemans
    .executescalar only returns one value, and is very fast (little overhead)

            dim x as integer
            Dim mycom As New OleDb.OleDbCommand("select count(*) from tabel4", OleDbConnection1)
            OleDbConnection1.Open()
            x = mycom.ExecuteScalar
            OleDbConnection1.Close()

    as you can see this was done on a access database (which is pretty slow), I tested the same on an sql server with a stored procedure and there it only took 0.3 seconds


    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now