Is there a way to use a progressbar while filling a dataset with a dataadapter?
In the application there is a dataadapter which fills a dataset. The problem is that there are sometimes >10000 records to be filled in the dataset. this takes some time. After the fillup of the dataset then a datagrid has to be filled with the dataset
Therefore I would like to use a progressbar to show the user that the application is processing the request.
The problem is that I don't know how to set the progressvalue of the progressbar, because the min and max values depend on the number of rows in the dataset and you can't know this before it is filled!!!!!!
I don't believe that the dataadapter provides any method of callback to show progress. You could fill it one row at a time but then you'd slow the whol process down. It may be easier to just show some form of animation like the copy dialog that runs while it is working but doesn't actually show the progress.
@Mike Hildner
I get the values of an excelsheet. The dataadapter selectcommand is based on the values the user inputs (eg hitword,...). So this isn't a case where I can count the rows in the datatable because it doesn't exist untill the user creates the select command. The number of rows depends on the selectcommand.
@edneeis : same idea as zebula8, and I agree that slowing down the application can't be a good choice.
Haven't used Excel as a data source, but would it be possible to do something like a SELECT COUNT (*) FROM MYTABLE...?
Edneeis is right about slowing the app down, but depending on what you're doing, this can be OK sometimes - albeit maybe not in this case.
Perceived speed versus actual speed is something to consider. A progress bar of any sort will be slower than no progress bar, but to the user, the application will appear fast.
As suggested, just showing that something is going on can be good enough.
I appreciate your posting, but I think it's a pretty complicated for me because I consider myself still as a newbie. Delegates and asynchronous programming isn't my best. But I gave it a try and adopted your code to my application:
VB Code:
Public Class FrmSearchCorresp2
Public Delegate Sub FillHandler(ByVal DSExcel As DataSet)
Private distraction As FrmDistraction
Private filled As Boolean
Public Function GetSourcePath(ByVal number As String) As String
Dim FileName As String
'alle info bevindt zich in nummer.xls
FileName = "\\Epsilon2\vol3\dok\" & number & "\corresp\" & number & "Nummer.xls"
Return FileName
End Function
'GetDataFromExcel creates a dataset from an excel file on the server in my company
Public Function GetDataFromExcel(ByVal FileName As String, ByVal RangeName As String) As System.Data.DataSet
Dim SQLString As String
Dim hitword, firma, afzender, bestemmeling As Boolean
If Me.TextBoxHitword.Text <> "" Then hitword = True Else hitword = False
If Me.TextBoxFirma.Text <> "" Then firma = True Else firma = False
If Me.TextBoxAfzender.Text <> "" Then afzender = True Else afzender = False
If Me.TextBoxBestemmeling.Text <> "" Then bestemmeling = True Else bestemmeling = False
MsgBox("Dossier " & Me.TextBoxNummer.Text & " bestaat niet. Gelieve een ander dossiernr. in te geven", MsgBoxStyle.Critical, "")
End If
End Sub
Public Sub CallBack(ByVal ar As IAsyncResult)
Dim DS As DataSet = CType(ar.AsyncState, DataSet)
If filled = True Then
distraction.DialogResult = DialogResult.OK
If DS.Tables(0).Rows.Count > 0 Then
Me.createstyles()
Me.dataGrid1.DataSource = DS
Me.dataGrid1.DataMember = "Excel"
Else
MsgBox("No results", MsgBoxStyle.Critical, "")
End If
End If
End Sub
The actual distraction animation is in the FrmDistraction and the code is the same as in your sample.
The problem is the following: when the user hits the button1, then the distractionanimation appears and starts working. After a few seconds (I suppose after the dataset DSExcel is created) the animation stops working and disappears but my datagrid1 isn't filled up with the results however it should because it's in my CallBack Sub.
I think there is a problem with the different threads (one for the distraction animation and one for filling up the dataset) and the fact that the dataset necessary for filling up the datagrid is created or passed to the wrong thread.
You are right it is because of the way you are handling the dataset. Instead of passing one into the method on the other thread you are letting it grab one from the other thread and then trying to return it as a function result but not grabbing the result. You almost have it. I made a few slight changes give this a try:
VB Code:
Public Class FrmSearchCorresp2
Public Delegate Sub FillHandler(ByVal ds As DataSet)
Private distraction As FrmDistraction
Private filled As Boolean
Public Function GetSourcePath(ByVal number As String) As String
Dim FileName As String
'alle info bevindt zich in nummer.xls
FileName = "\\Epsilon2\vol3\dok\" & number & "\corresp\" & number & "Nummer.xls"
Return FileName
End Function
'GetDataFromExcel creates a dataset from an excel file on the server in my company
Public Sub GetDataFromExcel(ByVal FileName As String, ByVal RangeName As String, ByVal ds As System.Data.DataSet)
Dim SQLString As String
Dim hitword, firma, afzender, bestemmeling As Boolean
If Me.TextBoxHitword.Text <> "" Then hitword = True Else hitword = False
If Me.TextBoxFirma.Text <> "" Then firma = True Else firma = False
If Me.TextBoxAfzender.Text <> "" Then afzender = True Else afzender = False
If Me.TextBoxBestemmeling.Text <> "" Then bestemmeling = True Else bestemmeling = False
The application still isn't working correct. The distraction is showing up and disappearing (after the filling up has completed I suppose) but the datagrid isn't filled up. So there is a problem with the passing of the dataset I think.
Here you can find the code I used, with some comments in it and some questions.
VB Code:
Public Class FrmSearchCorresp2
Inherits BeheerCDs.Form1
Private WithEvents dataGrid1 As DataGrid
Public DSExcel As New System.Data.DataSet
Public DataConnectionCorresp As OleDbConnection
Public Delegate Sub FillHandler(ByVal ds As DataSet)
Private distraction As FrmDistraction
Private filled As Boolean = False
Public Function GetSourcePath(ByVal number As String) As String
Dim FileName As String
'alle info bevindt zich in nummer.xls
FileName = "\\Epsilon2\vol3\dok\" & number & "\corresp\" & number & "Nummer.xls"
Return FileName
End Function
'GetDataFromExcel creates a dataset from an excel file on the server in my company
Public Sub GetDataFromExcel(ByVal FileName As String, ByVal RangeName As String, ByVal ds As System.Data.DataSet)
Dim SQLString As String
Dim hitword, firma, afzender, bestemmeling As Boolean
If Me.TextBoxHitword.Text <> "" Then hitword = True Else hitword = False
If Me.TextBoxFirma.Text <> "" Then firma = True Else firma = False
If Me.TextBoxAfzender.Text <> "" Then afzender = True Else afzender = False
If Me.TextBoxBestemmeling.Text <> "" Then bestemmeling = True Else bestemmeling = False
The first item is just a parameter required by the method, the 2nd is the callback address, the third is the state object which can be retrieved at any point in the async call and is retrieved to return the result in the end call (Callback method):
Dim DS As DataSet = CType(ar.AsyncState, DataSet)
That is probably why it wasn't returning anything. If by chance its not that it must be something very simple that has been overlooked by both of us.
The signature of a BeginInvoke call essentially goes like this:
BeginInvoke([Any Parameters for the Sub that the delegate represents],[Where to call when the Invoked method is done],[AsyncState object which is just a generic object that we can optionally include that can be retrieved at any point in the async call])
it still isn't working but we are close. I inserted :
MsgBox(DS.Tables(0).Rows.Count)
in the callback method before me.createstyles and if the application runs it gives the correct number of rows in the dataset. So this means the dataset is filled but the datagrid still isn't filled.
MsgBox("Dossier " & Me.TextBoxNummer.Text & " bestaat niet. Gelieve een ander dossiernr. in te geven", MsgBoxStyle.Critical, "")
End If
End Sub
' this method is the end of the second thread : the asynchronous operation. Am I correct?
Public Sub EndFill(ByVal ds As DataSet)
'close the distraction dialog
distraction.DialogResult = DialogResult.OK
'handle results
If ds.Tables(0).Rows.Count > 0 Then
Me.createstyles()
Me.dataGrid1.DataSource = ds
Me.dataGrid1.DataMember = "Excel"
Else
MsgBox("No results", MsgBoxStyle.Critical, "")
End If
End Sub
'creation of the callback method
Public Sub CallBack(ByVal ar As IAsyncResult)
Dim DS As DataSet = CType(ar.AsyncState, DataSet)
Dim del As New FillHandler(AddressOf EndFill)
'marshal back to the UI thread with the results
Me.Invoke(del, New Object() {DS})
End Sub
The problem was that the callback method wasn't on the UI thread so it couldn't display the dataset. To solve it you just use Me.Invoke to marshal back to the UI thread.
I think Asyncstate must be set to nothing, and not to DSExcel?? Or am i wrong? Can I use the DSExcel to pass as a variable?
AsyncState is our return value, similiar to passing a variable to a sub ByRef to change the value. You can use DSExcel although there is no need to use a variable with full form level scope, but if you want to then that is fine too.
Why do I need this dialogresult method? Can't I detect when the dataset filling operation has finished with the filled boolean or with the Endinvoke method of the delegate?
Setting the dialogresult to ok is what closes the distraction form. Using Close should work too, but setting the value to a dialogresult will allow you to catch that result if its needed. There is no need for the filled variable either since the callback method will not be called until the operation is complete. If you want to double check the operation you can check the ar.IsComplete property. Also we never call EndInvoke we just let it fall out of scope. EndInvoke blocks a thread until the operation is complete which is not what we want.
Also note: End Fill only needs the code to display the UI for the results. Execution will continue back at the line following distraction.ShowDialog() in the button.click event after endfill is done.
As promised I tested the code you suggested and it works fine. However there is still something strange going on. If the user clicks button1 the first time, then the dataset is filled while the distraction form is appearing. After the fillup of the dataset, the distraction form disappears and the datagrid is filled up with the correct data. So far so good.
The problem arises when the user clicks the button a second time (after the datagrid is filled). The distraction form appears and the dataset is filled up. Also the datagrid is filled up, again with the correct results, but the distraction form doesn't disappear (even after the filling up of the datagrid). Because of this the program crashes (you can't do anything anymore).
Any idea why this is so. I think it has something to do with me.invoke(..) but I can't figure out what.
I'm not sure it works fine for me. I tried several times but could not reproduce the problem. I was able to successfully run the fill bit several times. Here is my test example (see attached). I had it exactly like yours, using a dataadapter and connection created at designtime but switched to an xml file to make it more portable.
Me.Invoke simply invokes the delegate passed in on the same thread as the form (ME).
Hey I was thinking, this example and the code exposed came about to make it appear to show progress on a lengthy process, but I wanted to point out that the same code (minus the distraction form) will allow the user to continue using your app and complete the process in the background. If you want the user to be able to interact and work in your application while the dataset is being filled then just don't show the distraction form. The only thing in the current code stopping the user from interaction is the fact that the distraction form is shown modally (ShowDialog). So I figured I'd let you know that you have that option if you prefer it.
Progress bar - filling dataset
Reply With Quote
