Status Bar Updating and SQL Connections

[Guest]

Hi,

I have a simple Windows Forms app that connects to a SQL server, extracts
about 50,000 records and saves them to a file. During the query, I have a
small bit of text on a status bar that increments a period (.) every second
to show the user that the app is still running and not hung. I have used
timers, timer threading and system threading to increment the "dot" in a
separate thread with no problem UNTIL I execute the query using
SQLDataAdapter. While the query proc is being run the dots just stop
incrementing..As soon as the query is done, the dataset is filled, the "dots"
begin to increment again. Not what I intended. It seems that the UI thread is
still running but never gets enough CPU time to update the status bar on the
screen. Is there anyway to give that UI updating thread enough time to update
the screen or do I have to run the SQL Query routine in it's own thread. Any
help would be greatly appreciated ?

Max

 

[Guest]

Hi Max,

Do you launch a worker thread and use a timer on the form to keep the dots
moving? You could try adding a call to Application.DoEvents() after you've
modified your dots, however it shouldn't be necessary.

Can you provide a small sample of what you're doing?

Regards,
Matt Garven

 

[Guest]

Hi Matt,

I have tried 3 different ways ( Timer Control, Timer CallBack ,
System.Thread ). They all exhibit the same behaviour. I figured the
System.Theading way would be the most reliable, here is some code:

Public Class IRISLogin

Dim waitText As String = "Please wait"
Dim sb_thread As New Thread(AddressOf updateDisplay)

'status bar updating thread
Private Sub updateDisplay()
Dim c As Integer
While Thread.CurrentThread.ThreadState = ThreadState.Running
For c = 0 To 10
waitText += "."
sbStatus.Text = waitText
Application.DoEvents()
Application.DoEvents()
Application.DoEvents()
Application.DoEvents()
Application.DoEvents()
Thread.CurrentThread.Sleep(1000)
Next
waitText = "Please wait"
End While
End Sub

Private Sub cmdExport_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdExport.Click

' display a Save As dialog
Dim sa_dlg As New SaveFileDialog
sa_dlg.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
sa_dlg.Title = "Enter a file name for the IRIS Extract file"

If sa_dlg.ShowDialog() = DialogResult.Cancel Then Exit Sub
Dim saPath As String
Dim recCount As Integer

saPath = sa_dlg.FileName
lSaveTo.Text = "Output file: " & saPath
' refresh screen before performing query to allow Windows to erase
the Save As dialog box
Application.DoEvents()
' THREAD IS SUPPOSED TO START HERE !!!!
sb_thread.Start()
Dim recAd As SqlDataAdapter
Dim DBconn As New SqlConnection
Dim DBcommand As New SqlCommand

sbStatus.Text = "Getting records...please wait"
Me.Cursor.Current = Cursors.WaitCursor

DBconn.ConnectionString = SQL_CONN
DBcommand.Connection = DBconn
DBcommand.CommandType = CommandType.StoredProcedure
DBcommand.CommandText = "dsbn_IRISExtract"
'set CommandTimeout to 0 for long queries !!!!
DBcommand.CommandTimeout = 0
DBcommand.Parameters.Add("@schoolYear", SqlDbType.VarChar).Value =
cmdSchoolYear.SelectedItem
Try
recAd = New SqlDataAdapter(DBcommand)
DBconn.Open()
recAd.Fill(IRISRecs, "Recs")
DBconn.Close()
' ONLY STARTS TO DISPLAY HERE !!!! '
recCount = writeRecs(saPath)
Me.Cursor.Current = Cursors.Default
sbStatus.Text = "Done. " & recCount & " records processed"
If MsgBox("Would you like to view the output file ?",
MsgBoxStyle.Question & MsgBoxStyle.YesNo, "Trillium :: IRIS Data Extract
Utility") = MsgBoxResult.Yes Then
' open Notepad to view the extract file
Shell("notepad " & saPath, AppWinStyle.NormalFocus, False)
End If
Catch ex As SqlClient.SqlException
Me.Cursor.Current = Cursors.Default
MsgBox(ex.Message, MsgBoxStyle.Critical, "Critical Error")
sbStatus.Text = String.Empty
Exit Sub
End Try
End Sub

Pretty standard stuff, but for some reason the thread does not work here. As
a test, if I start the thread in the Load event it chugs along until the SQL
query starts. Then it just stops. When the query is finished, it starts up
again. Very frustrating.
Thanks for your help !!!
Max

 

[Guest]

Hi Max,

The problem is that you can't update the GUI from different threads. So it's
your main thread that needs to update the GUI and your new thread that needs
to do the work. Something like this...

Public Class IRISLogin
Private Sub cmdExport_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdExport.Click
Dim sb_thread As New Thread(AddressOf DoWork)
sb_thread.Start()

While sb_thread.ThreadState = ThreadState.Running
UpdateStatusBar()
Thread.Sleep(1000)
End While

End While
End Sub

Private Sub DoWork()
' do work here
End Sub

 

[Guest]

Hi Matt,
Thanks for the suggestion but it didn't work. I had the status bar update
run from the main thread and the SQL query in a new thread according to you
suggestion. Same thing. I also created 2 threads, 1 for the screen update and
1 for the SQL query and that didn't make a difference. I'm convinced the
SQLAdapter class(es) is doing something to my other thread. I don't know.
This has to be possible. I'll have to do more testing, maybe with something
non-SQL but a simple loop or another GUI routine.

Max

 

[Guest]

Hi Max,

I'm testing here with an SqlDataAdapter running on a separate thread, with
the click handler looking something like this (C# sorry):

ThreadStart ts = new ThreadStart(DoWork);
Thread thread = new Thread(ts);
thread.Start();

button1.Enabled = false;

StatusBar1.Text = "";
while (thread.ThreadState == ThreadState.Running)
{
statusBar1.Text += ".";
Application.DoEvents();
Thread.Sleep(100);
}

statusBar1.Text = "Finished.";
button1.Enabled = true;

Works pretty well to my testing. If you want your App to remain fully
responsive during this time, you can run the UI updater in another thread but
you must invoke your calls onto the control rather than modify them directly.

I can send you the sample application if you want to provide me with an
email address.

Hope this helps.

Regards,
Matt Garven