Optimize this Database Code (Vb.Net)

Question

So I have code that runs in a Vb.Net project that reads specific cols from a table in a Access Database (2007). It then populates a Datagridview with the results. The Issue is the code is messy, I'm disposing of all the events...etc. Is there a way to optimize it? From what I read, I can use the command "using" but I'm having problems trying to implement it. Any help on cleaning up this code is associated.

Sub Populate_RecordsList_Via_Database()

    'Create a connection to the database
    Dim strConnection As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=SBS2257_Info.accdb;"
    Dim objConnection As New OleDbConnection(strConnection)

    'Open the connection with error handling
    Try
        objConnection.Open()
    Catch OleDbExceptionErr As OleDbException
        MessageBox.Show(OleDbExceptionErr.Message)
    Catch InvalidOperationErr As InvalidOperationException
        MessageBox.Show(InvalidOperationErr.Message)
    End Try

    'Create a command object with the SQL statement needed to select the first and last names
    Dim strSQL As String = "SELECT [RecordID], [FName], [LName], [SBAlias1] FROM [Records];"
    Dim objCommand As New OleDbCommand(strSQL, objConnection)

    'Create a data adapter and data table then fill the data table
    Dim objDataAdapter As New OleDbDataAdapter(objCommand)
    Dim objDataTable As New DataTable("Info")
    objDataAdapter.Fill(objDataTable)

    'close connection and release resources
    objConnection.Close()
    objConnection.Dispose()
    objConnection = Nothing
    objCommand.Dispose()
    objCommand = Nothing
    objDataAdapter.Dispose()
    objDataAdapter = Nothing

    'Populate datagridview
    For Each row As DataRow In objDataTable.Rows

        Dim n As Integer = DGV_ListView.Rows.Add()
        DGV_ListView.Rows.Item(n).Cells(0).Value = row.Item("RecordID")
        DGV_ListView.Rows.Item(n).Cells(1).Value = row.Item("FName")
        DGV_ListView.Rows.Item(n).Cells(2).Value = row.Item("LName")
        DGV_ListView.Rows.Item(n).Cells(3).Value = row.Item("SBAlias1")

    Next

    'Release resources
    objDataTable.Dispose()
    objDataTable = Nothing


End Sub

See: [Using Statement (Visual Basic)](https://msdn.microsoft.com/en-us/library/htd05whh.aspx). `Close()` and `Dispose()` do the same thing, i.e. one calls the other. There is also no need to set the variables to `Nothing`. You can reaplace all three by the `Using` Statement. — Olivier Jacot-Descombes, Jan 05 '17 at 22:30
What command would I put in the using statement? Like: Using objDataAdapter As New OleDbDataAdapter(objCommand)? — Shane McLafferty, Jan 05 '17 at 22:37
Why don't you set the grid DataSource property to your DataTable instead of starting a loop on your own code to fill the grid? — Steve, Jan 05 '17 at 22:46
Yes, see you declare the variable and initialize the object that you want `Using` to dispose automatically later. `Dispose()` also calls `Close()` internally. Note that this works even if you leave the Using Sstatement with `Return` or if an exception is thrown. — Olivier Jacot-Descombes, Jan 05 '17 at 23:20

score 0 · Accepted Answer · edited May 23 '17 at 11:53

The using statement is very useful around disposable objects. This means that every class implementing the IDisposable interface is supposed to free the unmanaged resources acquired during their lifetime.

However in ADO.NET there is no unmanaged resource and thus is not really necessary to Dispose the OleDbDataAdapter and the DataTable while it is very recommended around the classes derived by the DbConnection base class.

So your revised code could be something like this

Sub Populate_RecordsList_Via_Database()

    Dim strSQL As String = "SELECT [RecordID], [FName], [LName], [SBAlias1] FROM [Records];"
    Dim strConnection As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=SBS2257_Info.accdb;"
    Try
        Using objConnection = New OleDbConnection(strConnection)
        Using objCommand = New OleDbCommand(strSQL, objConnection)
        'Using objDataAdapter = New OleDbDataAdapter(objCommand)
            Dim objDataTable As New DataTable("Info")
            objDataAdapter.Fill(objDataTable)
            ' No need to loop over the datatable, just assign it to the DataSource property
            DGV_ListView.DataSource = objDataTable
        'End Using
        End Using
        End Using
    Catch OleDbExceptionErr As OleDbException
        MessageBox.Show(OleDbExceptionErr.Message)
    Catch InvalidOperationErr As InvalidOperationException
        MessageBox.Show(InvalidOperationErr.Message)
    End Try
End Sub

Notice the try/catch encloses everything and not only the opening of the connection because you can never tell where an exceptional situation could arise in this context.

If i bind using a datasource, how do I reassign the col headers to user-friendly names? — Shane McLafferty, Jan 05 '17 at 23:15
Ah, now I remember why I did not want to bind it. By pre-selecting the Datagridview, I can set formatting and non-editable rows, custom headers. Useing datasource bypassed all the formatting and I would need to generate the code at runtime rather then design time. — Shane McLafferty, Jan 05 '17 at 23:33
Yes, that's true, but in terms of performance probably you should leave this task to the NET code and write the customizations of headers and rows. By the way, you can customize the grid with its designer, then look at the InitializeComponent method, grab the customization code, and paste it in your Constructor/Load/Shown event handlers and then remove everything from the designer. — Steve, Jan 06 '17 at 08:41
When you create a form Visual Studio creates two files belonging to the same class. The second one is called _FormName_.Designer.vb and contains the InitializeComponent method where the Visual Studio IDE writes all the property settings that you manually set through the Form Designer. You can see this method expanding the _FormName.vb_ file and then the _FormName_ class. (Where _FormName_ is the name you gave to your form) — Steve, Jan 07 '17 at 14:41

Optimize this Database Code (Vb.Net)

1 Answers1