Another Way to load multiple queries on multiple textboxes

Question

So far I'am using this code. The problem is it loads too slow when it is loading on my textbox, is there another way around it? I was just looking for suggestions or ideas. the data will be displayed on the textbox procqty.text, but it loads like 10 to 12 secs.

Any idea would be helpful.

Thank you.

 Function qtyCheck()


    query = "SELECT SUM(prdInput) AS prdInput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess1.Text & "';"
    retrieveSingleResult(query)

    proc1QTYIn.Text = dt.Rows(0)("prdInput").ToString()

    query = "SELECT SUM(prdOutput) AS prdOutput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess1.Text & "';"
    retrieveSingleResult(query)

    proc1QTYOut.Text = dt.Rows(0)("prdOutput").ToString()
    '/
    query = "SELECT SUM(prdInput) AS prdInput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess2.Text & "';"
    retrieveSingleResult(query)

    proc2QTYIn.Text = dt.Rows(0)("prdInput").ToString()

    query = "SELECT SUM(prdOutput) AS prdOutput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess2.Text & "';"
    retrieveSingleResult(query)

    proc2QTYOut.Text = dt.Rows(0)("prdOutput").ToString()
    '/
    query = "SELECT SUM(prdInput) AS prdInput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess3.Text & "';"
    retrieveSingleResult(query)

    proc3QTYIn.Text = dt.Rows(0)("prdInput").ToString()

    query = "SELECT SUM(prdOutput) AS prdOutput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess3.Text & "';"
    retrieveSingleResult(query)

    proc3QTYOut.Text = dt.Rows(0)("prdOutput").ToString()
    '/
    query = "SELECT SUM(prdInput) AS prdInput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess4.Text & "';"
    retrieveSingleResult(query)

    proc4QTYIn.Text = dt.Rows(0)("prdInput").ToString()

    query = "SELECT SUM(prdOutput) AS prdOutput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess4.Text & "';"
    retrieveSingleResult(query)

    proc4QTYOut.Text = dt.Rows(0)("prdOutput").ToString()
    '/
    query = "SELECT SUM(prdInput) AS prdInput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess5.Text & "';"
    retrieveSingleResult(query)

    proc5QTYIn.Text = dt.Rows(0)("prdInput").ToString()

    query = "SELECT SUM(prdOutput) AS prdOutput FROM ProdOutput WHERE HELPER ='" & txtLot.Text & "-" & txtPartNumber.Text & "-" & txtProcess5.Text & "';"
    retrieveSingleResult(query)

    proc5QTYOut.Text = dt.Rows(0)("prdOutput").ToString()
 
    End Function

Answer 1

The user interface code and the database code are separated and independent.

From the button click send the parameters to the Function. Functions in vb.net must have a DataType and it is preferable to a scope. (Private in this case)

The results of the Function are assigned to the text boxes.

'The first index is the index of the DataTable in the list.
'The second index is the index of the Row in the DataTable
'The third index is the index of the Column in the DataTable

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim lst As List(Of DataTable)
    Try
        lst = qtyCheck(txtLot.Text, txtPartNumber.Text, txtProcess1.Text, txtProcess2.Text, txtProcess3.Text, txtProcess4.Text, txtProcess5.Text)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Return
    End Try

    proc1QTYIn.Text = lst(0)(0)(0).ToString
    proc1QTYOut.Text = lst(0)(0)(1).ToString
    proc2QTYIn.Text = lst(1)(0)(0).ToString
    proc2QTYOut.Text = lst(1)(0)(1).ToString
    proc3QTYIn.Text = lst(2)(0)(0).ToString
    proc3QTYOut.Text = lst(2)(0)(1).ToString
    proc4QTYIn.Text = lst(3)(0)(0).ToString
    proc4QTYOut.Text = lst(3)(0)(1).ToString
    proc5QTYIn.Text = lst(4)(0)(0).ToString
    proc5QTYOut.Text = lst(4)(0)(1).ToString
End Sub

Database objects need to be closed and disposed. Using...End Using blocks handle this for us. I combined the queries into one. Never concatenate strings to build sql strings. Alway use parameters. We can use the same connection, command and parameter for all the calls to the database. The only thing that changes is the value of the parameter.

Private ConStr As String = "Your connection string"

Private Function qtyCheck(Lot As String, PartNum As String, Process1 As String, Process2 As String, Process3 As String, Process4 As String, Process5 As String) As List(Of DataTable)
    Dim query1 As String = "SELECT SUM(prdInput) AS prdInput,SUM(prdOutput) AS prdOutput  FROM ProdOutput WHERE HELPER = @HELPER;"
    Dim dt1 As New DataTable
    Dim dt2 As New DataTable
    Dim dt3 As New DataTable
    Dim dt4 As New DataTable
    Dim dt5 As New DataTable
    Dim lst As New List(Of DataTable)
    Using cn As New SqlConnection(ConStr),
            cmd As New SqlCommand(query1, cn)
        cmd.Parameters.Add("@HELPER", SqlDbType.VarChar).Value = $"{Lot}-{PartNum}-{Process1}"
        cn.Open()
        Using reader = cmd.ExecuteReader
            dt1.Load(reader)
        End Using
        lst.Add(dt1)
        cmd.Parameters("@HELPER").Value = $"{Lot}-{PartNum}-{Process2}"
        Using reader = cmd.ExecuteReader
            dt2.Load(reader)
        End Using
        lst.Add(dt2)
        cmd.Parameters("@HELPER").Value = $"{Lot}-{PartNum}-{Process3}"
        Using reader = cmd.ExecuteReader
            dt3.Load(reader)
        End Using
        lst.Add(dt3)
        cmd.Parameters("@HELPER").Value = $"{Lot}-{PartNum}-{Process4}"
        Using reader = cmd.ExecuteReader
            dt4.Load(reader)
        End Using
        lst.Add(dt4)
        cmd.Parameters("@HELPER").Value = $"{Lot}-{PartNum}-{Process5}"
        Using reader = cmd.ExecuteReader
            dt5.Load(reader)
        End Using
        lst.Add(dt5)
    End Using
    Return lst
End Function