Change VB.Net data retrieval to Async

p-plater used Ask the Experts™
I have a program that retrieves data from an SQL Database.
I have to retrieve some large data sets and would like to run these in the background.

I am using MVM pattern and using the following code to retreive the data.
My question is how can I change this code to run Aysnc?

I have looked online but can't get anything to work.

Code in Data Access Layer that Retrieves Data from Database.

Thank you

Shared Function GetData(ByVal customerAccountNumber As Integer, ByVal includeInActiveProducts As Boolean, ByVal groupPeriodType As GroupPeriodEnum, ByVal salePeriodCount As Integer, ByVal groupFilterTypeID As GroupFilterTypeEnum,
                            ByVal groupFilterValue As Integer, ByVal staffID As Integer) As System.Data.Common.DbDataReader


            ' Create command to send to the database
            Using cmd As New SqlCommand("ppp_sp_Sales_ProductTotals", Sql.ConnSQL)
                cmd.CommandType = CommandType.StoredProcedure
                With cmd.Parameters
                    .AddWithValue("@StaffID", staffID)
                    .AddWithValue("@CustomerAccountNumber", customerAccountNumber)
                    .AddWithValue("@GroupPeriodType", groupPeriodType)
                    .AddWithValue("@IncludeInactiveProducts", includeInActiveProducts)
                    .AddWithValue("@SalePeriodCount", salePeriodCount)
                    .AddWithValue("@GroupFilterTypeID", groupFilterTypeID)
                    .AddWithValue("@GroupFilterValue", groupFilterValue)
                End With

                Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

            End Using   'cmd

        Catch ex As SqlException
            'Throw a New Extended SQL Exception to record the SQL String
            Throw New ExtendedSQLException(ex, sqlString:="")

        Catch ex As Exception
            'Re-throw To the BLL
        End Try

    End Function

Open in new window

Code in the ViewModel that Refreshes the Data Collection
    Public Sub RefreshData(ByVal customerAccountNumber As Integer, ByVal includeInActiveProducts As Boolean,
                           ByVal groupPeriodType As SalesHistoryProduct.GroupPeriodEnum,
                           Optional ByVal monthStart As DateTime? = Nothing, Optional ByVal salePeriodCount As Integer = 1,
                           Optional ByVal groupFilterTypeID As SalesHistoryProduct.GroupFilterTypeEnum = SalesHistoryProduct.GroupFilterTypeEnum.NoFilter, Optional ByVal groupFilterValue As Integer = 0)

        ' Clear Collection

        ' Check Date
        If monthStart Is Nothing Then
            monthStart = CType(SqlDateTime.MinValue, Date?)
        End If

        'Declare Data Reader
        Dim drd As DbDataReader = Nothing

            'Get Data from Data DLL
            Dim recordCount As Integer = 0

            drd = SalesHistoryProductData.GetData(customerAccountNumber:=customerAccountNumber, includeInActiveProducts:=includeInActiveProducts,
                groupPeriodType:=CType(groupPeriodType, SalesHistoryProductPrimaryGroupData.GroupPeriodEnum),
                                                                   staffID:=StaffCurrent.GetID, groupFilterTypeID:=groupFilterTypeID, groupFilterValue:=groupFilterValue, salePeriodCount:=salePeriodCount)

            If drd Is Nothing Then
                Exit Sub
            End If

            While drd.Read
                Dim historyItem As New SalesHistoryProduct
                With historyItem
                    .ProductID = SqlFunctions.IntegerNullCheck(drd(0), -1)
                    .ProductCode = SqlFunctions.StringNullCheck(drd(1))
                    .ProductName = SqlFunctions.StringNullCheck(drd(2))
                    .MonthStart = SqlFunctions.DateTimeNullCheck(drd(3), Now)
                    .TurnOverValue = SqlFunctions.DoubleNullCheck(drd(4))
                    .ProfitValue = SqlFunctions.DoubleNullCheck(drd(5))
                    .SaleQuantity = SqlFunctions.DoubleNullCheck(drd(6))
                    .GroupPeriodValue = groupPeriodType
                End With

                recordCount = +1
            End While


        Catch ex As ExtendedSQLException

            If ExceptionHandlerStandard.HandleSQLExtendedException(ex) = True Then
                'Re Throw to the Global Exception Handler
            End If

        Catch ex As SqlException
            If ExceptionHandlerStandard.HandleSQLException(ex) = True Then
                'Re Throw to the Global Exception Handler
            End If

        Catch ex As Exception
            If ExceptionHandlerStandard.HandleException(ex) = True Then
                'Re Throw to the Global Exception Handler
            End If
        End Try
    End Sub

Open in new window

Property That Refreshes the collection when it is Accessed
    Private _salesHistoryProductYear As SalesHistoryProductCollection
    Public Property SalesHistoryProductYear() As SalesHistoryProductCollection
            If _salesHistoryProductYear Is Nothing Then
                _salesHistoryProductYear = New SalesHistoryProductCollection
                _salesHistoryProductYear.RefreshData(customerAccountNumber:=Me.Customer.AccountNumber, includeInActiveProducts:=False,
            End If
            Return _salesHistoryProductYear
        End Get
        Set(ByVal value As SalesHistoryProductCollection)
            _salesHistoryProductYear = value
        End Set
    End Property

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

You can use a Background Worker to do the actual retrieval of your data. You can either add a BackgroundWorker component, or create one in code. If you add the component, you can then use the DoWork method of the BGW to run your code.

Here's a tutorial on using a BackgroundWorker:
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Hi p-plater,

I am assuming you are building a WPF app. Please let me know if my assumption is right.
If you are building WPF app, then I strongly recommend going with Tasks/Parallelism/Async...Await.

If you convert both GetData and RefreshData to async, your problem is sorted.

I have many working app - unfortunately they all are in C# (My clients do not prefer VB.Net) so I don't have anything ready in VB.Net but I am more than happy to walk you through the steps I take to make almost all of my code execution in async mode. "Almost" is they keyword :).

If you don't mind spending some time, I recommend you check out this wonderful article on Async MVVM Apps:

Let me know if you get stuck converting your subs/functions to async.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial