1 Replies - 187 Views - Last Post: 19 August 2019 - 12:22 PM Rate Topic: -----

#1 dtgeek   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 01-August 19

OutOfMemory Exception thrown

Posted 19 August 2019 - 12:10 PM

    Private Function DoImpItems() As Boolean
        Dim percent As Integer = 0
        Dim currentRow As Integer = 0
        Dim ItemDataRow As DataSetPricing.ItemsRow
        Dim CommodityDataRow As DataSetPricing.CommoditiesRow
        Dim LevelTypeDataRow As DataSetPricing.LevelTypesRow
        Dim StatusDataRow As DataSetPricing.StatusRow
        Dim ManufacturerDataRow As DataSetPricing.ManufacturersRow
        Dim UomDataRow As DataSetPricing.UOMRow
        Dim WorkgroupDataRow As DataSetPricing.WorkGroupsRow
        Dim Item As Models.Item
        'Dim Items As DataSetPricing.ItemsRow

        Manufacturers = ServiceLocator.Current.GetInstance(Of IManufacturers).Get
        WorkGroups = ServiceLocator.Current.GetInstance(Of IWorkgroups).Get
        LevelTypes = ServiceLocator.Current.GetInstance(Of ILevelTypes).Get
        ItemStatus = ServiceLocator.Current.GetInstance(Of IItemStatusTypes).Get
        UOM = ServiceLocator.Current.GetInstance(Of IUom).Get

        Using scope As New EntitySpaces.Interfaces.esTransactionScope
            'Update Existing Items
            Try
                currentRow = 0
                'Dim list = Item.qu
                For Each row In DataSetPricing.Items
                    Call TestReader()
                    Item = ServiceLocator.Current.GetInstance(Of IItems).GetByRowId(row.RowID)


                    If Not Item Is Nothing Then
                        If Not Item.DoNotUpdate Then
                            Item.EriNumber = row.ERINumber
                            Item.EriNumberSearch = row.ERINumber
                            Item.Comparative = row.Comparative
                            If row.ParentID <> 0 Then
                                ItemDataRow = DataSetPricing.Items.Where(Function(d) d.PK_ItemID = row.ParentID).SingleOrDefault
                                If Not ItemDataRow Is Nothing Then
                                    Dim ItemParent As Models.Item = ServiceLocator.Current.GetInstance(Of IItems).GetByRowId(ItemDataRow.RowID)
                                    If Not ItemParent Is Nothing Then
                                        Item.ParentId = ItemParent.PkItems
                                    Else
                                        Item.ParentId = 0
                                    End If
                                Else
                                    Item.ParentId = 0
                                End If
                            Else
                                Item.ParentId = 0
                            End If

                            Item.CatalogNumber = row.CatalogNumber
                            Item.Description = row.Description
                            Item.MyCost = CDec(Math.Round(row.Cost * IIf(IsDBNull(Item.MyMultiplier), 1, Item.MyMultiplier), 2))
                            Item.Cost = row.Cost
                            Item.Retail = row.Retail
                            If Not Config.NoUpdateLabor Then
                                Item.Labor = row.Labor
                            End If
                            Item.EffectiveDate = row.DateEffective
                            Item.Comparative = row.Comparative
                            Item.UPC = Strings.Right(row.UPC, 5)
                            Item.AlphaIndex = row.AlphaIndex
                            Item.ReportGroup = row.ReportGroup
                            Item.ImKing = row.ImKing
                            Item.ImageUrl = IIf(IsDBNull(row.ImageURL), String.Empty, row.ImageURL)
                            Item.CatalogPageUrl = IIf(IsDBNull(row.CatalogPageURL), String.Empty, row.CatalogPageURL)
                            Item.LastCostUpdate = Date.Now

                            LevelTypeDataRow = DataSetPricing.LevelTypes.Where(Function(d) d.PK_LevelTypeID = row.FK_LevelTypeID).SingleOrDefault
                            If Not LevelTypeDataRow Is Nothing Then
                                Dim q = (From c In LevelTypes Where c.RowId = LevelTypeDataRow.RowID).SingleOrDefault
                                Item.FkLevel = q.LevelTypeId
                            Else
                                Item.FkLevel = 1
                            End If

                            StatusDataRow = DataSetPricing.Status.Where(Function(d) d.PK_StatusID = row.FK_StatusID).SingleOrDefault
                            If Not StatusDataRow Is Nothing Then
                                Dim q = (From c In ItemStatus Where c.Status = StatusDataRow.Description).SingleOrDefault
                                Item.FkStatus = q.PkItemStatusTypes
                            Else
                                Item.FkStatus = 1
                            End If

                            UomDataRow = DataSetPricing.UOM.Where(Function(d) d.PK_UOMID = row.FK_UOMID).SingleOrDefault
                            If Not UomDataRow Is Nothing Then
                                Dim q = (From c In UOM Where c.UOM = UomDataRow.ShortDescription).SingleOrDefault
                                Item.FkUom = q.IdUom
                            Else
                                Item.FkUom = 0
                            End If

                            WorkgroupDataRow = DataSetPricing.WorkGroups.Where(Function(d) d.PK_WorkGroupID = row.FK_WorkgroupID).SingleOrDefault
                            If Not WorkgroupDataRow Is Nothing Then
                                Dim q = (From c In WorkGroups Where c.RowId = WorkgroupDataRow.RowID).SingleOrDefault
                                Item.FkWorkgroup = q.PkWorkgroups
                            Else
                                Item.FkWorkgroup = 0
                            End If

                            CommodityDataRow = DataSetPricing.Commodities.Where(Function(d) d.PK_CommodityID = row.FK_CommodityID).SingleOrDefault
                            If Not CommodityDataRow Is Nothing Then
                                Dim q = ServiceLocator.Current.GetInstance(Of ICommodities).GetByRowId(CommodityDataRow.RowID)
                                Item.FkCommodityId = q.PkCommodityId
                            Else
                                Item.FkCommodityId = 0
                            End If

                            ManufacturerDataRow = DataSetPricing.Manufacturers.Where(Function(d) d.PK_ManufacturerID = row.FK_ManufacturerID).SingleOrDefault
                            If Not ManufacturerDataRow Is Nothing Then
                                Dim q = (From c In Manufacturers Where c.RowId = ManufacturerDataRow.RowID).FirstOrDefault
                                Item.FkManufacturer = q.PkManufacturerId
                            Else
                                Item.FkManufacturer = 0
                            End If
                        End If

                        ServiceLocator.Current.GetInstance(Of IItems).Update(Item)
                        'Item.save()
                    Else
                        Dim entity As New Models.Item

                        entity.EriNumber = row.ERINumber
                        entity.EriNumberSearch = row.ERINumber
                        entity.CatalogNumber = row.CatalogNumber
                        entity.Description = row.Description
                        entity.MyCost = row.Cost
                        entity.Cost = row.Cost
                        entity.Retail = row.Retail
                        entity.Labor = row.Labor
                        entity.EffectiveDate = row.DateEffective
                        entity.Comparative = row.Comparative
                        entity.UPC = Strings.Right(CStr(row.UPC), 5)
                        entity.AlphaIndex = row.AlphaIndex
                        entity.ReportGroup = row.ReportGroup
                        entity.ImKing = row.ImKing
                        entity.MyMultiplier = 1
                        entity.RowId = row.RowID
                        entity.ImageUrl = row.ImageURL
                        entity.CatalogPageUrl = row.CatalogPageURL
                        entity.LastCostUpdate = Date.Now
                        entity.DefaultCostCode = 0

                        If row.ParentID <> 0 Then
                            ItemDataRow = DataSetPricing.Items.Where(Function(d) d.PK_ItemID = row.ParentID).SingleOrDefault
                            If Not ItemDataRow Is Nothing Then
                                Dim ItemParent As Models.Item = ServiceLocator.Current.GetInstance(Of IItems).GetByRowId(ItemDataRow.RowID)
                                If Not ItemParent Is Nothing Then
                                    entity.ParentId = ItemParent.PkItems
                                Else
                                    entity.ParentId = 0
                                End If
                            Else
                                entity.ParentId = 0
                            End If
                        Else
                            entity.ParentId = 0
                        End If

                        LevelTypeDataRow = DataSetPricing.LevelTypes.Where(Function(d) d.PK_LevelTypeID = row.FK_LevelTypeID).SingleOrDefault
                        If Not LevelTypeDataRow Is Nothing Then
                            Dim q = (From c In LevelTypes Where c.RowId = LevelTypeDataRow.RowID).SingleOrDefault
                            entity.FkLevel = q.LevelTypeId
                        Else
                            entity.FkLevel = 1
                        End If

                        StatusDataRow = DataSetPricing.Status.Where(Function(d) d.PK_StatusID = row.FK_StatusID).SingleOrDefault
                        If Not StatusDataRow Is Nothing Then
                            Dim q = (From c In ItemStatus Where c.Status = StatusDataRow.Description).SingleOrDefault
                            entity.FkStatus = q.PkItemStatusTypes
                        Else
                            entity.FkStatus = 1
                        End If

                        UomDataRow = DataSetPricing.UOM.Where(Function(d) d.PK_UOMID = row.FK_UOMID).SingleOrDefault
                        If Not UomDataRow Is Nothing Then
                            Dim q = (From c In UOM Where c.UOM = UomDataRow.ShortDescription).SingleOrDefault
                            entity.FkUom = q.IdUom
                        Else
                            entity.FkUom = 0
                        End If

                        WorkgroupDataRow = DataSetPricing.WorkGroups.Where(Function(d) d.PK_WorkGroupID = row.FK_WorkgroupID).SingleOrDefault
                        If Not WorkgroupDataRow Is Nothing Then
                            Dim q = (From c In WorkGroups Where c.RowId = WorkgroupDataRow.RowID).SingleOrDefault
                            entity.FkWorkgroup = q.PkWorkgroups
                        Else
                            entity.FkWorkgroup = 0
                        End If

                        CommodityDataRow = DataSetPricing.Commodities.Where(Function(d) d.PK_CommodityID = row.FK_CommodityID).SingleOrDefault
                        If Not CommodityDataRow Is Nothing Then
                            Dim q = ServiceLocator.Current.GetInstance(Of ICommodities).GetByRowId(CommodityDataRow.RowID)
                            entity.FkCommodityId = q.PkCommodityId
                        Else
                            entity.FkCommodityId = 0
                        End If

                        ManufacturerDataRow = DataSetPricing.Manufacturers.Where(Function(d) d.PK_ManufacturerID = row.FK_ManufacturerID).SingleOrDefault
                        If Not ManufacturerDataRow Is Nothing Then
                            Dim q = (From c In Manufacturers Where c.RowId = ManufacturerDataRow.RowID).FirstOrDefault
                            entity.FkManufacturer = q.PkManufacturerId
                        Else
                            entity.FkManufacturer = 0
                        End If
                        entity.save()
                        ServiceLocator.Current.GetInstance(Of IItems).Add(entity)
                        'Call Dispose()
                    End If

                    currentRow += 1
                    percent = CInt((currentRow / DataSetPricing.Items.Rows.Count) * 100)
                    BackgroundWorker.ReportProgress(percent)
                    scope.Complete()
                   
                Next

                
            Catch ex As Exception
                secondLevelMsg = ex.Message

                Return False

                Exit Function
            End Try
        End Using

        Return True
    End Function



I have tried XMLtextreader but it reads the items slow. What this part of the program is doing refreshing the database with full price sync of 60,000 or more items with their prices but I can't seem to get the items not to be read in memory one at a time.

Is This A Good Question/Topic? 0
  • +

Replies To: OutOfMemory Exception thrown

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15261
  • View blog
  • Posts: 61,150
  • Joined: 12-June 08

Re: OutOfMemory Exception thrown

Posted 19 August 2019 - 12:22 PM

Any particular reason you wouldn't cut the bulk of this out and just use a SQLCommand object to run the update?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1