We help IT Professionals succeed at work.
Get Started

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

109 Views
Last Modified: 2016-07-22
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)
                        Else
                            Dim row As DataGridViewRow = TblCrazyLandDataGridView.Rows(rowIndex - 1)
                            lastWeek = row.Cells(2).Value

                        End If
                    Else
                        '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

========THEN IF STATEMENT H-E-DOUBLE TOOTHPICKS STARTS======================

 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
                            Else
                                planRegHrsDIA = thisWeekPlans.Item(0).RegHrs
                            End If

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

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

                            If thisWeekPlans.Item(0).SubOTHrs Is Nothing Then
                                'do nothing
                            Else
                                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
                    Try
                        d.SubmitChanges()

                    Catch ex As Exception

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

                    End Try

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


Help!
Comment
Watch Question
Entrapenuer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 16 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE