asked on

Change VB.Net data retrieval to Async

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chinmay Patel

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.