locked
Convert a Sub that runs a Linq Query to an Async Sub RRS feed

  • Question

  • I have a Sub that builds multiple datagridviews from Linq queries.
    The datagridviews are contained in Panels. There are 42 panels representing the possible days in a Calendar grid.
    The routine builds a Datatable for the particular Month being displayed, and then adds those records to the datagridview of the corresponding panel. 
    I’d like the entire routine to run asynchronously since the values loaded in the datagridviews are one of many informational areas on the form.

    Here is the code for the Sub:

    Private Sub subLoadDGVs()
    
      Dim strSQL As String
      Dim daBMList As New OleDbDataAdapter
      Dim dtBMList As New DataTable
      Dim intPanelIX As Integer
      Dim pnlCurrent As Panel
    Dim dgvCurrent As DataGridView ' Add BMList Dates to Each Calendar strSQL = "SELECT tblBMList.RecordKey, tblBMList.FirstName, tblBMList.LastName, tblBMList.BMDate, tblBMList.BMLocation " strSQL = strSQL & "FROM tblBMList " strSQL = strSQL & "WHERE tblBMList.InActiveFL=False AND tblBMList.BMDate BETWEEN #" & Format(dteStartOfPanel, "MM/dd/yyyy") & "# AND #" & Format(dteEnd, "MM/dd/yyyy") & "#" daBMList = New OleDbDataAdapter(strSQL, myConnection) daBMList.Fill(dtBMList) Dim qryBMList = From BMListRecord In dtBMList.AsEnumerable Order By BMListRecord.Field(Of Date)("BMDate"), BMListRecord.Field(Of String)("LastName"), BMListRecord.Field(Of String)("FirstName") Select New With { .RecordKey = BMListRecord.Field(Of Integer)("RecordKey"), .FirstName = BMListRecord.Field(Of String)("FirstName"), .LastName = BMListRecord.Field(Of String)("LastName"), .BMDate = BMListRecord.Field(Of Date)("BMDate") } For Each BMListRecord In qryBMList intPanelIX = DateDiff(DateInterval.Day, dteStartOfPanel, BMListRecord.BMDate) + 1 pnlCurrent = Me.flpCalendar.Controls("panl" & Format(intPanelIX, "00")) dgvCurrent = pnlCurrent.Controls("dgvOtherInfo" & Format(intPanelIX, "00")) dgvCurrent.Rows.Add(Format(BMListRecord.BMDate, "Short Time") & " " & BMListRecord.FirstName & " " & BMListRecord.LastName) dgvCurrent.Rows(dgvCurrent.Rows.Count - 1).DefaultCellStyle.ForeColor = Color.Maroon dgvCurrent.ClearSelection() Next daBMList = Nothing dtBMList.Clear() Call subAddOutlookAppts() End Sub

    I tried converting the Sub to an Async by changing the declartion to:

    Private Async Sub subLoadDGVs()

    and substituting the

    For Each BMListRecord In qryBMList
    …<DGV build logic>…
    Next

    loop with:

    Parallel.ForEach(qryBMList, Sub(BMListRecord)
    …<same logic in original loop>…
    End Sub)

    but I’m still getting a warning on the declaration line of the Sub:
    BC42356: This Async method lacks 'Await' operators and so will run synchronously. Consider using the 'Await' operator to await non-blocking API calls, 'Await.Task.Run(…) to do CPU-bound work on a background thread.

    Any ideas would be greatly appreciated.

    Sincerly,
    Paul Goldstein


    Paul D. Goldstein Forceware Systems, Inc.

    Wednesday, October 14, 2020 5:41 PM

All replies

  • Hello Paul,

    I may not get all questions answered with the following.

    The following shows basic logic for populating a DataTable via a DataAdapter then the dummy function and sub show what the compiler needs.

    Note it's unwise to use a sub other than a sub for a click event to use asynchronous logic. 

    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    
    Namespace Classes
        Public Class DataOperations
            Private SqlServerConnectionString As String = "TODO"
            Private AcccessConnectionString As String = "TODO"
            Public Async Function FillForSqlServer() As Task(Of DataTable)
    
                Dim resultsDataTable = New DataTable()
    
                Try
                    Using conn = New SqlConnection(SqlServerConnectionString)
                        Dim adapter = New SqlDataAdapter("TODO", conn)
                        Await Task.Run(Function() adapter.Fill(resultsDataTable))
                    End Using
                Catch ex As Exception
                    ' handle exception
                End Try
    
                Return resultsDataTable
    
            End Function
            Public Async Function FillForAccess() As Task(Of DataTable)
    
                Dim resultsDataTable = New DataTable()
    
                Try
                    Using conn = New OleDbConnection(SqlServerConnectionString)
                        Dim adapter = New OleDbDataAdapter("TODO", conn)
                        Await Task.Run(Function() adapter.Fill(resultsDataTable))
                    End Using
                Catch ex As Exception
                    ' handle exception
                End Try
    
                Return resultsDataTable
    
            End Function
    
            Public Async Sub SubDemo()
                ' appease the compiler, better to use a function
                Await Task.Delay(1)
            End Sub
            Public Async Function FunDemo() As Task
                ' appease the compiler, better to use a function
                Await Task.Delay(1)
            End Function
        End Class
    End Namespace

    Rather than Parallel.ForEach, stick with a conventional for and use await Task.Delay e.g. if you have 10,000 records do a delay (using code logic e.g. similar to is even) every say 1,000 records, something you need to play with.

    Keep delays small as I've done above.

    Other choice is Iterator/Yield

    https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/modifiers/iterator


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, October 14, 2020 6:24 PM