How to call the datatable fill code without double in vb.net

Question

How to call the datatable fill code without double in vb.net?. in the code below you might see I wrote back the fill datatable code if there is a solution without me writing it back in "GenerateReport()". Please Recommend.

Thanks

Private WithEvents dt As New DataTable
 Public Sub fillDataGridView1()
            dt = New DataTable
            Dim query As String = "SELECT NOD,ITM,CIA,DPR,QTY FROM RSD WHERE QTY > 0 AND PNM=@PNM"
            Using con As OleDbConnection = New OleDbConnection(GetConnectionString)
                Using cmd As OleDbCommand = New OleDbCommand(query, con)
                    cmd.Parameters.AddWithValue("@PNM", ComboBox1.SelectedValue)
                    Using da As New OleDbDataAdapter(cmd)
                        da.Fill(dt)
                        da.Dispose()
                        Dim totalColumn As New DataColumn()
                        totalColumn.DataType = System.Type.GetType("System.Double")
                        totalColumn.ColumnName = "Total"
                        totalColumn.Expression = "[CIA]*[QTY]*(1-[DPR]/100)"
                        dt.Columns.Add(totalColumn)
                        Me.grid.DataSource = dt
                        Me.grid.Refresh()
                    End Using
                End Using
            End Using
End Sub
Private Sub GenerateReport()
KtReport1.Clear()
'the code below actually already exists but I reuse it
dt = New DataTable
            Dim query As String = "SELECT NOD,ITM,CIA,DPR,QTY FROM RSD WHERE QTY > 0 AND PNM=@PNM"
            Using con As OleDbConnection = New OleDbConnection(GetConnectionString)
                Using cmd As OleDbCommand = New OleDbCommand(query, con)
                    cmd.Parameters.AddWithValue("@PNM", ComboBox1.SelectedValue)
                    Using da As New OleDbDataAdapter(cmd)
                        da.Fill(dt)
Dim dtCloned As DataTable = dt.Clone()
dtCloned.Columns("CIA").DataType = GetType(String)
For Each row As DataRow In dt.Rows
                            dtCloned.ImportRow(row)
                        Next row
KtReport1.AddDataTable(dtCloned)

Answer 1

The issue is that you are essentially duplicating code despite the fact that you have a variable setup to hold the filled DataTable at the Form level.

What I would suggest doing is creating a function that returns the filled DataTable, then at the top of your two existing methods do a null check against the Form level variable.

Take a look at this example:

Private _dt As DataTable

Private Function GetAndFillDataTable() As DataTable
    Dim dt As New DataTable()
    Dim query As String = "SELECT NOD,ITM,CIA,DPR,QTY FROM RSD WHERE QTY > 0 AND PNM=@PNM"
    Using con As OleDbConnection = New OleDbConnection(GetConnectionString)
        Using cmd As OleDbCommand = New OleDbCommand(query, con)
            cmd.Parameters.AddWithValue("@PNM", ComboBox1.SelectedValue)
            Using da As New OleDbDataAdapter(cmd)
                da.Fill(dt)
                da.Dispose()
                Dim totalColumn As New DataColumn()
                totalColumn.DataType = System.Type.GetType("System.Double")
                totalColumn.ColumnName = "Total"
                totalColumn.Expression = "[CIA]*[QTY]*(1-[DPR]/100)"
                dt.Columns.Add(totalColumn)
                Return dt
            End Using
        End Using
    End Using
End Function

Private Sub FillDataGridView1()
    If (_dt Is Nothing) Then
        _dt = GetAndFillDataTable()
    End If
    grid.DataSource = _dt
    grid.Refresh()
End Sub

Private Sub GenerateReport()
    If (_dt Is Nothing) Then
        _dt = GetAndFillDataTable()
    End If
    Dim dtCloned As DataTable = _dt.Clone()
    dtCloned.Columns("CIA").DataType = GetType(String)
    For Each row In _dt.Rows
        dtCloned.ImportRow(row)
    Next row
    KtReport1.AddDataTable(dtCloned)
End Sub