Application Hang during OleDbDataAdapter.Fill

  • Thread starter Thread starter Marcolino
  • Start date Start date
M

Marcolino

Hi all,
I'm using this code to export entire table from access to xml file.

DScmdXML = New OleDb.OleDbCommand(sqlXML, conn)
DScmdXML = New OleDb.OleDbDataAdapter(cmdXML)
DScmdXML.Fill(DSXML, sTableName)
DSXML.WriteXml(sFileName, XmlWriteMode.WriteSchema)
Dim xmlSW2 As System.IO.StreamWriter = New
System.IO.StreamWriter(sFullPath)
DSXML.WriteXml(xmlSW2, XmlWriteMode.WriteSchema)
xmlSW2.Flush()
xmlSW2.Close()

During DScmdXML.Fill(DSXML, sTableName) command the application hangs.
In some case I need to export big tables, I would like to add a
progress bar during the process, but the bar does not work and
application seems frezed. I tried to add a
System.Windows.Forms.Application.DoEvents(), but nothing change.

Do you have any suggestion?

Many Thanks
 
Marcolino said:
Hi all,
I'm using this code to export entire table from access to xml file.

DScmdXML = New OleDb.OleDbCommand(sqlXML, conn)
DScmdXML = New OleDb.OleDbDataAdapter(cmdXML)
DScmdXML.Fill(DSXML, sTableName)
DSXML.WriteXml(sFileName, XmlWriteMode.WriteSchema)
Dim xmlSW2 As System.IO.StreamWriter = New
System.IO.StreamWriter(sFullPath)
DSXML.WriteXml(xmlSW2, XmlWriteMode.WriteSchema)
xmlSW2.Flush()
xmlSW2.Close()

During DScmdXML.Fill(DSXML, sTableName) command the application hangs.
In some case I need to export big tables, I would like to add a
progress bar during the process, but the bar does not work and
application seems frezed. I tried to add a
System.Windows.Forms.Application.DoEvents(), but nothing change.

Do you have any suggestion?

Many Thanks
Click to expand...


Hello ,

If you want a responsive UI, while your program is verry bussy , you might
investigate asynchrounous processing ( multi threading )

i have once written some examples for this newsgroup how to acomplish this
in an easy way with Async delegates

here is one :

take a form throw 2 buttons on it and a progressbar


call the progressbar pbmain


now copy paste this :


Imports System.Threading
Public Class Form1
Private Mvar_Cancell As Boolean
Friend Property Cancell() As Boolean
Get
Return Mvar_Cancell
End Get
Set(ByVal value As Boolean)
Mvar_Cancell = value
Me.Button1.Enabled = value
End Set
End Property
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.Cancell = False
With pbMain
.Value = 0
.Maximum = 100
.Minimum = 0
End With


Dim AsyncProcess As New AsynchMethodInvoker(AddressOf Me.pbLoop)
AsyncProcess.BeginInvoke(Nothing, Nothing)


AsyncProcess = New AsynchMethodInvoker(AddressOf Me.DoSomething)
AsyncProcess.BeginInvoke(Nothing, Nothing)


End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Cancell = True
End Sub


Private Sub DoSomething()
Do While Not Me.Cancell
Debug.WriteLine("hello world")
Loop
End Sub
Delegate Sub CrossThreadInvParaMB(ByVal val As Integer)
Private Sub pbLoop()
Dim i As Integer
Do While Not Me.Cancell
Thread.Sleep(50)
i += 1
If i = 101 Then i = 0
SetPbValue(i)
Loop
i = 100
SetPbValue(i)
End Sub
Delegate Sub AsynchMethodInvoker()
Public Sub SetPbValue(ByVal Count As Integer)
If Me.pbMain.InvokeRequired Then
Dim d As New CrossThreadInvParaMB(AddressOf SetPbValue)
Try
Me.Invoke(d, New Object() {Count})
Catch ex As Exception


End Try
Else
Me.pbMain.Value = Count
End If
End Sub
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As
System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Me.Cancell = True
End Sub
End Class


i hope this gives you all an idea in what i mean ,,, the method that is now
updating the progressbar runs in a seperate thread to make it even nicer i
run another worker thread wich prints hello world statements to the debug
window


these threads can comunicate to the progressbar through the SetPbValue
method

see for more :

http://groups.google.nl/groups/search?hl=nl&q=asynchronous+posseth&qt_s=Discussiegroepen+doorzoeken



Michel
 
"Marcolino" <[email protected]> schreef in bericht









Hello ,

If you want a responsive UI, while your program is verry bussy ,  you might
investigate asynchrounous processing ( multi threading )

i have once written some examples for this newsgroup how to acomplish this
in an easy way with Async delegates

here is one :

take a form throw 2 buttons on it and a progressbar

call the progressbar pbmain

now copy paste this :

Imports System.Threading
Public Class Form1
    Private Mvar_Cancell As Boolean
    Friend Property Cancell() As Boolean
        Get
            Return Mvar_Cancell
        End Get
        Set(ByVal value As Boolean)
            Mvar_Cancell = value
            Me.Button1.Enabled = value
        End Set
    End Property
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
        Me.Cancell = False
        With pbMain
            .Value = 0
            .Maximum = 100
            .Minimum = 0
        End With

        Dim AsyncProcess As New AsynchMethodInvoker(AddressOf Me.pbLoop)
        AsyncProcess.BeginInvoke(Nothing, Nothing)

        AsyncProcess = New AsynchMethodInvoker(AddressOf Me.DoSomething)
        AsyncProcess.BeginInvoke(Nothing, Nothing)

    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
        Cancell = True
    End Sub

    Private Sub DoSomething()
        Do While Not Me.Cancell
            Debug.WriteLine("hello world")
        Loop
    End Sub
  Delegate Sub CrossThreadInvParaMB(ByVal val As Integer)
    Private Sub pbLoop()
        Dim i As Integer
        Do While Not Me.Cancell
            Thread.Sleep(50)
            i += 1
            If i = 101 Then i = 0
            SetPbValue(i)
        Loop
        i = 100
        SetPbValue(i)
    End Sub
    Delegate Sub AsynchMethodInvoker()
    Public Sub SetPbValue(ByVal Count As Integer)
        If Me.pbMain.InvokeRequired Then
            Dim d As New CrossThreadInvParaMB(AddressOf SetPbValue)
            Try
                Me.Invoke(d, New Object() {Count})
            Catch ex As Exception

            End Try
        Else
            Me.pbMain.Value = Count
        End If
    End Sub
    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As
System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        Me.Cancell = True
    End Sub
End Class

i hope this gives you all an idea in what i mean ,,, the method that is now
updating the progressbar runs in a seperate thread to make it even nicer i
run another worker thread wich prints hello world statements to the debug
window

these threads can comunicate to the progressbar through the SetPbValue
method

see for more :

http://groups.google.nl/groups/search?hl=nl&q=asynchronous+posseth&qt....

Michel- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -
Click to expand...

Hi Michel,
thanks for your precious help.
I'm working on your example. But I have a problem.
In sub DoSomething() i need to access to contron created on form that
generate the second Tread.
When i do this i got this error:
Cross-thread operation not valid: Control 'cboExpAzione' accessed from
a thread other than the thread it was created on.

How can access to control on a form from second tread?
Thanks
 
"Marcolino" <[email protected]> schreef in bericht
"Marcolino" <[email protected]> schreef in
bericht









Hello ,

If you want a responsive UI, while your program is verry bussy , you might
investigate asynchrounous processing ( multi threading )

i have once written some examples for this newsgroup how to acomplish this
in an easy way with Async delegates

here is one :

take a form throw 2 buttons on it and a progressbar

call the progressbar pbmain

now copy paste this :

Imports System.Threading
Public Class Form1
Private Mvar_Cancell As Boolean
Friend Property Cancell() As Boolean
Get
Return Mvar_Cancell
End Get
Set(ByVal value As Boolean)
Mvar_Cancell = value
Me.Button1.Enabled = value
End Set
End Property
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.Cancell = False
With pbMain
.Value = 0
.Maximum = 100
.Minimum = 0
End With

Dim AsyncProcess As New AsynchMethodInvoker(AddressOf Me.pbLoop)
AsyncProcess.BeginInvoke(Nothing, Nothing)

AsyncProcess = New AsynchMethodInvoker(AddressOf Me.DoSomething)
AsyncProcess.BeginInvoke(Nothing, Nothing)

End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Cancell = True
End Sub

Private Sub DoSomething()
Do While Not Me.Cancell
Debug.WriteLine("hello world")
Loop
End Sub
Click to expand...
Delegate Sub CrossThreadInvParaMB(ByVal val As Integer)
Private Sub pbLoop()
Dim i As Integer
Do While Not Me.Cancell
Thread.Sleep(50)
i += 1
If i = 101 Then i = 0
SetPbValue(i)
Loop
i = 100
SetPbValue(i)
End Sub
Delegate Sub AsynchMethodInvoker()
Public Sub SetPbValue(ByVal Count As Integer)
If Me.pbMain.InvokeRequired Then
Dim d As New CrossThreadInvParaMB(AddressOf SetPbValue)
Try
Me.Invoke(d, New Object() {Count})
Catch ex As Exception

End Try
Else
Me.pbMain.Value = Count
End If
End Sub
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As
System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Me.Cancell = True
End Sub
End Class

i hope this gives you all an idea in what i mean ,,, the method that is
now
updating the progressbar runs in a seperate thread to make it even nicer i
run another worker thread wich prints hello world statements to the debug
window

these threads can comunicate to the progressbar through the SetPbValue
method

see for more :

http://groups.google.nl/groups/search?hl=nl&q=asynchronous+posseth&qt...

Michel- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -
Click to expand...



Hello Marco ,

GUI elements are always running on the Static thread ( the thread that
handles the mesage pump of the windows form )
so when you want to interact to such an element you could do the same as i
did in my example when dealing with the progressbar ( as it is also a GUI
element )
in the example there are three threads

2 worker threads ( one that writes the debug messages and the one updating
the progressbar ) and one STA Thread ( the one the form is running on )

so use the techniqeu used in the PbLoop as you see this calls the
SetPbValue(i) method and this method handles the synchronization between the
two threads with again a Delegate ( it asks if invoke is required , wich
wil return true if called from abnother thread , we then setup the synch
delegate and call ourself ( same method ) again on the main thread the
invoke required prop now returns false , and we can get or set the values of
our GUI element ) however this time executed on the main thread , it is
that simple :-)

You should ofcourse define a delegate with the parameters that you need in
your situation .

HTH

if you have anny further questions feel free to ask

Michel
 
also note that i answered your question of 19-03 about the Access database
creation

Michel


"Marcolino" <[email protected]> schreef in bericht
"Marcolino" <[email protected]> schreef in
bericht









Hello ,

If you want a responsive UI, while your program is verry bussy , you might
investigate asynchrounous processing ( multi threading )

i have once written some examples for this newsgroup how to acomplish this
in an easy way with Async delegates

here is one :

take a form throw 2 buttons on it and a progressbar

call the progressbar pbmain

now copy paste this :

Imports System.Threading
Public Class Form1
Private Mvar_Cancell As Boolean
Friend Property Cancell() As Boolean
Get
Return Mvar_Cancell
End Get
Set(ByVal value As Boolean)
Mvar_Cancell = value
Me.Button1.Enabled = value
End Set
End Property
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.Cancell = False
With pbMain
.Value = 0
.Maximum = 100
.Minimum = 0
End With

Dim AsyncProcess As New AsynchMethodInvoker(AddressOf Me.pbLoop)
AsyncProcess.BeginInvoke(Nothing, Nothing)

AsyncProcess = New AsynchMethodInvoker(AddressOf Me.DoSomething)
AsyncProcess.BeginInvoke(Nothing, Nothing)

End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Cancell = True
End Sub

Private Sub DoSomething()
Do While Not Me.Cancell
Debug.WriteLine("hello world")
Loop
End Sub
Delegate Sub CrossThreadInvParaMB(ByVal val As Integer)
Private Sub pbLoop()
Dim i As Integer
Do While Not Me.Cancell
Thread.Sleep(50)
i += 1
If i = 101 Then i = 0
SetPbValue(i)
Loop
i = 100
SetPbValue(i)
End Sub
Delegate Sub AsynchMethodInvoker()
Public Sub SetPbValue(ByVal Count As Integer)
If Me.pbMain.InvokeRequired Then
Dim d As New CrossThreadInvParaMB(AddressOf SetPbValue)
Try
Me.Invoke(d, New Object() {Count})
Catch ex As Exception

End Try
Else
Me.pbMain.Value = Count
End If
End Sub
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As
System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Me.Cancell = True
End Sub
End Class

i hope this gives you all an idea in what i mean ,,, the method that is
now
updating the progressbar runs in a seperate thread to make it even nicer i
run another worker thread wich prints hello world statements to the debug
window

these threads can comunicate to the progressbar through the SetPbValue
method

see for more :

http://groups.google.nl/groups/search?hl=nl&q=asynchronous+posseth&qt...

Michel- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -
Click to expand...

Hi Michel,
thanks for your precious help.
I'm working on your example. But I have a problem.
In sub DoSomething() i need to access to contron created on form that
generate the second Tread.
When i do this i got this error:
Cross-thread operation not valid: Control 'cboExpAzione' accessed from
a thread other than the thread it was created on.

How can access to control on a form from second tread?
Thanks
 
also note that i answered your question of 19-03  about the Access database
creation

Michel

"Marcolino" <[email protected]> schreef in bericht


















Hi Michel,
thanks for your precious help.
I'm working on your example. But I have a problem.
In sub DoSomething() i need to access to contron created on form that
generate the second Tread.
When i do this i got this error:
Cross-thread operation not valid: Control 'cboExpAzione' accessed from
a thread other than the thread it was created on.

How can access to control on a form from second tread?
Thanks- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -
Click to expand...

Hi Michel,
i finished now to customize your code and work GREAT. Many thanks.
I will made more deep tests today.

--Marco
 
Hi Michel,
i finished now to customize your code and work GREAT. Many thanks.
I will made more deep tests today.

--Marco- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -
Click to expand...

Hi Michael,
I have anoter question for you.
I have to start a soubroutine only when Async process is finished.
How can I understand when async task is finished and start another
subroutine?

Thanks
 
Hi!.
I have one question. Where be declarate this procedure?

AsyncProcess = New AsynchMethodInvoker(AddressOf Me.DoSomething)

I can see that AsynchMethodInvoker(...) is call... but where be declarate?

Tank.
 
Back
Top