troubleshooting Question

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

Avatar of Karen Wilson
Karen WilsonFlag for United States of America asked on
Microsoft SQL Server 2008Visual Basic.NET
16 Comments1 Solution112 ViewsLast Modified:
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........  

Ess Kay

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros