Show status of loading DataSet by Progress Bar

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
LVL 3
pnedicAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
RonaldBiemansConnect With a Mentor Commented:
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
 
RonaldBiemansCommented:
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
 
RonaldBiemansCommented:
Hold the phone, just thought of something, Give me a second and I'll test it first.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pnedicAuthor Commented:
OK, I'm reading info about DataReader
0
 
RonaldBiemansCommented:
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
 
RonaldBiemansCommented:
The same actually goes for the datareader, there you also need to know the maximum value of the progress bar
0
 
pnedicAuthor Commented:
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
 
RonaldBiemansCommented:
HMMM, you actually don't need this line

(SqlDataAdapter1.AcceptChangesDuringFill = True)


sorry my mistake :-)
0
 
pnedicAuthor Commented:
Hm, tomorrow I'll let you know about the result. I need to check this

Thanks
0
 
pnedicAuthor Commented:
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
 
pnedicAuthor Commented:
Yap, one more thing, I have also tried to force refreshing of subform with using Timer object, but also got the same result.
0
 
RonaldBiemansCommented:
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
 
pnedicAuthor Commented:
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
 
RonaldBiemansCommented:
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
 
pnedicAuthor Commented:
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
 
pnedicAuthor Commented:
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
 
RonaldBiemansCommented:
.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
All Courses

From novice to tech pro — start learning today.