Vb.net Winform Update GUI between Syncronous Sql calls

Question

topic Hi all, I've searched heavily for this but was unable to find a result that truly fits this situation.

I have a (presently) single threaded application written in VB.Net The major processing steps are 12 checked list items in a treeview control. The program executes the steps (functions, and stored procedures) in turn and checks the result before continuing.

The steps (greatly simplified) are: 1. Import data from a txt file 2. Bulk Insert it into a database 3. Do some processing 4. Use that data to retrieve a lot more related data across a network in a complex join from a linked server. 5. Update the local database with the results 6. Do more processing 7. Insert the final results into a different database on a different server.

I won't go into all the reasons it had to be done this way like (different server owners within the company, lack of trust between servers, read only access to certain databases, etc.) but the problem is Step 4.

Depending on the processing load on the (foreign) server and the amount of data in the import file, this step can take 1-1/2 hours to execute. Since this is a single threaded app the gui freezes waiting for the data to be retrieved from the linked server.

Aside from the grey block on the desktop (due to no gui updates) the program runs perfectly.

Attempted solutions: 1) I tried the suggestion of a timer to refresh the form with no success.

2) I have tried using the background worker process but could not get the application to wait for a result before the program continued.

3) I also tried different examples of threading with no success. All the other steps complete so quickly that the gui never freeses, but I wouldn't be averse to threading all my sql calls in this app.

In all my time doing this, it is the first time I haven't been able to find a solution on the web, and never needed to post before, so help with this matter would be greatly appreciated.

--edit

Here is what i tried: Thank you for the quick response.

I used the process described here: http://midnightprogrammer.net/post/Using-Background-Worker-in-C.aspx (after converting it to vb.net). but the program blew right past the step (Case Node6 at bottom).

Imports System.Runtime.InteropServices
Imports System.Threading
Imports System.ComponentModel

Private Sub bgw_ProgressChanged(ByVal sender As System.Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs)
        'Report progress bar change     
        progress.Value = e.ProgressPercentage

    End Sub
Private Sub bgw_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs)
    If (e.Cancelled) Then
        WriteStatus("Operation Cancelled")
        bRetval = False
    Else
        WriteStatus("Operation Completed")
        bRetval = True
    End If

End Sub


'Background worker DoWork method. Here we will perform our heavy duty tasks. 
Private Sub bgw_DoWork(ByVal sender As Object, ByVal e As DoWorkEventArgs)

    Dim Sql As String = "Get_ALS_Data"
    Dim cmd As New SqlCommand(Sql, ConnLocal)
    cmd.CommandTimeout = 9000 ' 2 and a half hours 
    Try
        Dim i As Integer = 0
        ConnLocal.Open()

        'ImportRowCount = cmd.ExecuteScalar()
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        While dr.Read()
            i = i + 1
            'report to the backgroundworkerprogress.changed event of the background worker class
            bgw.ReportProgress(i)
            Thread.Sleep(1)
            'Call and check if the cancellation of the operation is pending. If returned true        
            'DoWorkEventArgs object cancels the operation.        
            If bgw.CancellationPending Then
                e.Cancel = True
                Return
            End If
            ImportRowCount = CInt(dr(0))
        End While

    Catch ex As Exception
        WriteStatus("Get ALS Data error: " & ex.Message)
        e.Cancel = True
        Return
    Finally
        ConnLocal.Close()
    End Try

    WriteStatus("Get ALS Data completed successfully.")
    e.Cancel = False
    Return


End Sub

Private Sub bgw_ProgressChanged(ByVal sender As System.Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs)
    'Report progress bar change     
    progress.Value = e.ProgressPercentage

End Sub

Private Sub bgw_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs)
    If (e.Cancelled) Then
        WriteStatus("Operation Cancelled")
        bRetval = False
    Else
        WriteStatus("Operation Completed")
        bRetval = True
    End If

End Sub

 Private Sub ProcessCheckedNodes(ByVal parentNode As TreeNode)
    Dim Success As Boolean = False
    For Each childNode As TreeNode In parentNode.Nodes
        ProgBar += BarSeg
        If ProgBar > 100 Then
            ProgBar = 100
        End If
        pb1.Value = ProgBar
        Me.Refresh()
        If childNode.Checked Then
            Select Case childNode.Name
                Case "Node1"
                    '1. Clear local work tables
                    SetChild(childNode, "True")
                    Success = DoCleanup()
                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If

                    SetChild(childNode, "False")
                Case "Node2"
                    '2. Clear Server Intake table
                    SetChild(childNode, "True")
                    Success = TruncateInserts("DoCleanUp")

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                Case "Node3"
                    '3. Load the temp table
                    SetChild(childNode, "True")
                    Success = LoadMyTempTable()

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                Case "Node4"
                    '4. Load the data from the temp table to the local database
                    SetChild(childNode, "True")
                    Success = BulkCopy_Intake()

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                Case "Node5"
                    '5. Get Intake Dup's
                    SetChild(childNode, "True")
                    Success = GetIntakeDups()

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                **Case "Node6"
                    '6. Get the matching data from the ALS database
                    SetChild(childNode, "True")
                    'Success = GetALS_Data()
                    bgw.RunWorkerAsync()
                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")**
                Case "Node7"
                    '7. Get Core Dup's
                    SetChild(childNode, "True")
                    Success = GetCoreDups()

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                Case "Node8"
                    '8. Process
                    SetChild(childNode, "True")
                    Success = Process()

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                Case "Node9"
                    '9. Export NotFound
                    SetChild(childNode, "True")
                    Success = ExportNotFound()

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                Case "Node10"
                    '10. Move Inserts
                    SetChild(childNode, "True")
                    Success = MoveInserts()

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                Case "Node11"
                    '11. Backup
                    SetChild(childNode, "True")
                    Success = Backup()

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                Case "Node12"
                    SetChild(childNode, "True")
                    'Success = LoadDc()

                    If Success <> True Then
                        SetChild(childNode, "Error")
                        Cursor = Cursors.Default
                        Exit Sub
                    End If
                    SetChild(childNode, "False")
                Case Else
                    'Ignore  it
            End Select
        Else
            childNode.ImageIndex = 0
            childNode.SelectedImageIndex = 0
        End If
        ProcessCheckedNodes(childNode)
    Next
    pb1.Value = 100
End Sub**strong text**


Public Function GetALS_Data() As Boolean

    'refresh the form while waiting for the ALS data
    Dim sAcct As String = ""

    Dim Sql As String = "Get_ALS_Data"
    Dim cmd As New SqlCommand(Sql, ConnLocal)
    cmd.CommandTimeout = 9000 ' 2 and a half hours 
    Try
        ConnLocal.Open()
        ImportRowCount = cmd.ExecuteScalar()
    Catch ex As Exception
        WriteStatus("Get ALS Data error: " & ex.Message)
        Return False
    Finally
        ConnLocal.Close()
    End Try

    WriteStatus("Get ALS Data completed successfully.")
    Return True

End Function

Answer 1

Not sure why your background thread did not work for you but if you are having issues for one reason or another you can try this solution. May be a bit overkill for what you need but it would work.

So what you may want to look into is a synchronization pattern using the Syncevents process and a Queue. If you were to pop all your items into a queue FIFO model and use them from a background thread you could do all that you are talking about and maintain a usuable UI, make it cancelable, etc. A nice benefit to this is no timeouts except during your database calls or other items but not the thread.

My code is in C# but you can get the picture. You can also look at this for information on the EventHandle process: MSDN - EventWaitHandle

I have not tested this as I have just typed it in however we use this type of internal queuing model on the applications that I work with and it works very well for processing data in the background and maintaining a responsive UI for long running and cancellable operations. If you want to cancel the operation you just set the ExitThreadEvent. Hopefully this might give you some additional paths to try.

Create a SyncEvent class

public class SyncEvents
{
    private EventWaitHandle _newItemEvent;

private EventWaitHandle _exitThreadEvent;
private WaitHandle[] _eventArray;

public SyncEvents()
{
    _newItemEvent = new AutoResetEvent(false);
    _exitThreadEvent = new ManualResetEvent(false);
    _eventArray = new WaitHandle[2];
    _eventArray[0] = _newItemEvent;
    _eventArray[1] = _exitThreadEvent;
}

public EventWaitHandle ExitThreadEvent
{
    get { return _exitThreadEvent; }
}

public EventWaitHandle NewItemEvent
{
    get { return _newItemEvent; }
}

public WaitHandle[] EventArray
{
    get { return _eventArray; }
}
}

Using a Queue object you can create a instruction class to do your work and create a class like so with the threading involved as well.

public class RunItem
{

public int SequenceToRun { get; set; }

}

public class ItemRunner
{

private SyncEvents _syncEvents;
private Queue<RunItem> _items;
private Thread _processThread;

public ItemRunner(SyncEvents events, Queue<RunItem> items)
{
   this._syncEvents = events;
   this._items = items;
}

public void Start()
{
   this._processThread = new Thread(this.Run);
   this._processThread.IsBackground = true;
   this._processThread.Start();
}

private void Run()
{
   try
   {
            while (WaitHandle.WaitAny(_syncEvents.EventArray) != 1)
            {
                RunItem item = null;

                lock (this._items)
                {
                    if (this._items.Count > 0)
                    {
                        item = this._items.Dequeue();
                        if (item != null)
                        this.ProcessItem(item);
                        this._syncEvents.NewItemEvent.Set();
                    }
                    else
                    {
                      this._syncEvents.ExitThreadEvent.Set();
                    }

                }
            }
   }
   catch (Exception ex)
   {
      // do something to log your exception here
      // you should have the try catch since you are running in a thread and if it 
      // throws an exception it could kill your entire app.

   }
}

private void ProcessItem(RunItem item)
{
      // Do your item processing here.
   // You could have a final item that executes the 
  // this._syncEvents.ExitThreadEvent.Set(); so that it actually will stop waiting 

}
}