Is this the most efficient way to get data from database to datagridview?-CodePudding

Im a beginner and hoping to know whats the most memory/processor efficient way to query. Am i placing the using and end using properly?. Is the New OleDbCommand still needed? Please comment your suggestions and tips to improve my code :)

    Dim con As New OleDbConnection
 Dim dbProvider As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
      Dim dbSource As String = ("Data Source=" & Application.StartupPath & "\DBbms.accdb")
       con.ConnectionString = dbProvider & dbSource
       

Using connection As New OleDbConnection(con.ConnectionString)
        Try

            Dim query As String = "Select * from Household"
            'cmd = New OleDbCommand(query, con)
            Dim da As New OleDbDataAdapter(query, con)

            connection.Open()
            Dim dt As New DataTable
            da.Fill(dt)


            DataGridView1.DataSource = dt.DefaultView


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Using

CodePudding user response：

Separate your user interface code from your database code. If you want to show a message box in your Catch do it in the UI code.

You may want to make the connection string a class level variable so other database code can use it.

The Command is also included in the Using block. Note the comma at the end of the Connection line.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim dt As DataTable
    Try
        dt = GetHouseholdData()
    Catch ex As Exception
        MsgBox(ex.Message)
        Exit Sub
    End Try
    DataGridView1.DataSource = dt.DefaultView
End Sub

Private HHConStr As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={Application.StartupPath}\DBbms.accdb"

Private Function GetHouseholdData() As DataTable
    Dim dt As New DataTable
    Using con As New OleDbConnection(HHConStr),
            cmd As New OleDbCommand("Select * from Household", con)
        con.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function