Avatar of Karen Wilson
Karen Wilson
Flag for United States of America

asked on 

Convert a complicated Windows Form application process to a SQL Process.

I have a process that I run in a Windows Form application,  I use LINQ to connect to a SQL server database.  The process has over 100 variables. It was running fine until a few weeks ago when we added all the financial data for year 4 in our contract.  Year 5 will probably cause a melt down so I need a better plan of attack.  Currently, I bring in the info, process it using queries in the application and views in SQL, then update the bound record and send it back to SQL.  A full process can take up to 20 minutes now, which is ridiculous yet not if the analyst wants water cooler time.

I think the answer lies in using SQL for the whole thing but I'm not sure where to begin.   Below is parts of the code to help you understand what I'm doing.

If TblCrazyLandDataGridView.Rows.Count > 0 Then

                For Each idRow As DataGridViewRow In TblCrazyLandDataGridView.Rows
                    tblCd = idRow.DataBoundItem

                    Me.NASA_ContractComboBox.SelectedItem = tblCd.NASA_Contract
                    Me.ComboBoxRev.SelectedItem = tblCd.Rev
                    Me.WeekEndingDateTimePicker.Text = CStr(tblCd.WeekEnding)

                    '============planned side variables===================
                    Dim planLab As Decimal = 0
                    Dim planMat As Decimal = 0
                    Dim planTrav As Decimal = 0
                    Dim planOther As Decimal = 0
                    Dim planSubC As Decimal = 0
                    Dim planURS As Decimal = 0
                    Dim planURSMat As Decimal = 0
                    Dim planProv As Decimal = 0
                    Dim planTotal As Decimal = 0

 '========Set up dates
                    Dim currentWeek As Date = WeekEndingDateTimePicker.Text
                    Dim lastWeek As Date

                    If TblCrazyLandDataGridView.Rows.Count > 0 Then
                        Dim rowIndex As Integer
                        rowIndex = TblCrazyLandDataGridView.CurrentCell.RowIndex

                        If rowIndex = 0 Then
                            lastWeek = DateAdd(DateInterval.Day, -7, currentWeek)
                            Dim row As DataGridViewRow = TblCrazyLandDataGridView.Rows(rowIndex - 1)
                            lastWeek = row.Cells(2).Value

                        End If
                        'do nothing
                    End If

                    '===============planned side processing ===========================

                    Dim lwPlans = (From id In d.tblCrazyLandDevels _
                                   Where id.NASA_Contract = CStr(Me.NASA_ContractComboBox.SelectedItem) _
                                   And id.Rev = CStr(Me.ComboBoxRev.SelectedItem) _
                                   And id.WeekEnding = CDate(lastWeek) _
                                   Select id).ToList

                    Dim thisWeekPlans = (From id In d.vw_PlansByOrigBases _
                                   Where id.NASAContract = CStr(Me.NASA_ContractComboBox.SelectedItem) _
                                   And id.Rev = CStr(Me.ComboBoxRev.SelectedItem) _
                                    And id.WeekEnding = CDate(currentWeek) _
                                   Select id).ToList


 If lwPlans.Count > 0 Then

                        If thisWeekPlans.Count > 0 Then

                            planLab = thisWeekPlans.Item(0).Labor
                            planMat = thisWeekPlans.Item(0).Materials
                            planTrav = thisWeekPlans.Item(0).Travel
                            planOther = thisWeekPlans.Item(0).Other
                            planSubC = thisWeekPlans.Item(0).SubC
                            planURS = thisWeekPlans.Item(0).URS
                            planURSMat = thisWeekPlans.Item(0).URSMat
                            planProv = provTotal
                            planTotal = thisWeekPlans.Item(0).WkTotal + provTotal

                            If thisWeekPlans.Item(0).RegHrs Is Nothing Then
                                'do nothing
                                planRegHrsDIA = thisWeekPlans.Item(0).RegHrs
                            End If

                            If thisWeekPlans.Item(0).OTHours Is Nothing Then
                                'do nothing
                                planOTHrsDIA = thisWeekPlans.Item(0).OTHours
                            End If

                            If thisWeekPlans.Item(0).SubRegHrs Then
                                'do nothing
                                planSubRegHrsDIA = thisWeekPlans.Item(0).SubRegHrs
                            End If

                            If thisWeekPlans.Item(0).SubOTHrs Is Nothing Then
                                'do nothing
                                planSubRegHrsDIA = thisWeekPlans.Item(0).SubOTHrs
                            End If

  '========================accept record=======================
                    tblCd.NASA_Contract = CStr(Me.NASA_ContractComboBox.SelectedItem)
                    tblCd.WeekEnding = CDate(Me.WeekEndingDateTimePicker.Text)
                    tblCd.Rev = CStr(Me.ComboBoxRev.SelectedItem)

                    tblCd.planLabor = planLab
                    tblCd.planMat = planMat
                    tblCd.planOther = planOther
                    tblCd.planTravel = planTrav
                    tblCd.planSubContrURS = planURS
                    tblCd.planSubContr = planSubC
                    tblCd.planURSMat = planURSMat
                    tblCd.planProv = planProv
                    tblCd.planTotal = planTotal

                    tblCd.plRegHrs = planRegHrsDIA
                    tblCd.plOTHrs = planOTHrsDIA
                    tblCd.plSubRegHrs = planSubRegHrsDIA
                    tblCd.plSubOThrs = planSubOTHrsDIA

 'update the record

                    Catch ex As Exception

                        MessageBox.Show(ex.Message, ex.GetType.ToString)

                    End Try

And then it goes to the next row........  

Microsoft SQL Server 2008Visual Basic.NET

Avatar of undefined
Last Comment
Karen Wilson

8/22/2022 - Mon