Change VB.Net data retrieval to Async

p-plater
p-plater used Ask the Experts™
on
Hello
I have a VB.net 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

        Try

            ' 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
            Throw
        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
        Me.ClearItems()

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

        'Declare Data Reader
        Dim drd As DbDataReader = Nothing

        Try
            '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

                Me.Add(historyItem)
                recordCount = +1
            End While

            drd.Close()

        Catch ex As ExtendedSQLException

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

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

        Catch ex As Exception
            If ExceptionHandlerStandard.HandleException(ex) = True Then
                'Re Throw to the Global Exception Handler
                Throw
            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
        Get
            If _salesHistoryProductYear Is Nothing Then
                _salesHistoryProductYear = New SalesHistoryProductCollection
                _salesHistoryProductYear.RefreshData(customerAccountNumber:=Me.Customer.AccountNumber, includeInActiveProducts:=False,
                 groupPeriodType:=SalesHistoryProduct.GroupPeriodEnum.Yearly)
                OnPropertyChanged(NameOf(SalesHistoryProductYear))
            End If
            Return _salesHistoryProductYear
            OnPropertyChanged(NameOf(SalesHistoryProductYear))
        End Get
        Set(ByVal value As SalesHistoryProductCollection)
            _salesHistoryProductYear = value
            OnPropertyChanged(NameOf(SalesHistoryProductYear))
        End Set
    End Property

Open in new window

Comment
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

Commented:
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:

https://www.dotnetperls.com/backgroundworker-vbnet
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
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.

TL;DR
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: https://msdn.microsoft.com/en-us/magazine/dn605875.aspx

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

Regards,
Chinmay.

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