Better Data Reader

'------------------------------------------------------------------------------------------
'           Notice of My Copyright and Intellectual Property Rights
'
' Any intellectual property contained within the program by Joseph L. Bolen remains the
' intellectual property of the Joseph L. Bolen. This means that no person may distribute,
' publish or provide such intellectual property to any other person or entity for any
' reason, commercial or otherwise, without the express written permission of Joseph L. Bolen.
'
'                 Copyright © 2016. All rights reserved.
'        All trademarks remain the property of their respective owners.
'-------------------------------------------------------------------------------------------
' Program Name:   Better Data Reader
'
' Author:         Joseph L. Bolen
' Date Created:   23 MAY 2016
'
' Description:    This database inquiry uses the DataReader to quickly
'                 retrieve records from a table and display them in a datagridview.
'                 For this demonstration, the Customers table from the MS Access
'                 database Northwind is being used. The database's file location
'                 is retrieved from the App.config file. The separation of UI and Data
'                 Access Layers are shown in the app.
'
'                 To choose the correct ConnectionString,
'                   see http://www.connectionstrings.com/ .
'
'                 Documentation is at:
'                   App's screen image is at: http://imgur.com/1CijOyT
'                   App's Visual Basic .NET code is at http://pastebin.com/S7mJtk0Q (Original Bare Bones Data Reader)
'                   App's Visual Basic .NET code is at http://pastebin.com/Qm3zCRPY (General Data Reader)
'                   App's Visual Basic .NET code is at http://pastebin.com/cGBPn4xJ (Better Data Reader)
'                   Video tutorial at YouTube: http://www.youtube.com/user/bolenpresents
'-------------------------------------------------------------------------------------------
'Note: Add Reference to DAL (Data Access Layer) to this Project.

Imports DAL                     ' Data Access Layer
Imports System.Data.OleDb       ' Using a MS Access database

Public Class InquiryForm

    Private bs As New BindingSource

    Private Sub InquiryDGV_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) _
        Handles InquiryDGV.DataError

        MessageBox.Show(e.Exception.Message,
                "Data Error",
                MessageBoxButtons.OK,
                MessageBoxIcon.Error)
    End Sub

    ' Form Load - Initialization and Housekeeping.
    Private Sub InquiryForm_Load(sender As Object, e As EventArgs) _
        Handles MyBase.Load

        ' DataGridView property changes that could be done in the design mode.
        With InquiryDGV
            '.Dock = DockStyle.Fill
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            .AllowUserToOrderColumns = True
            .AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke
            .Anchor = (AnchorStyles.Left Or AnchorStyles.Top Or
                AnchorStyles.Right Or AnchorStyles.Bottom)
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            .BorderStyle = BorderStyle.Fixed3D
            .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
            .ReadOnly = True

            ' DataGridView Column Headers Bold – must be set in run mode.
            .ColumnHeadersDefaultCellStyle.Font =
                New Font(.ColumnHeadersDefaultCellStyle.Font, FontStyle.Bold)
        End With

    End Sub

    ' Filter and sort data from the database and display in a datagridview.
    Private Sub SearchToolStripButton_Click(sender As Object, e As EventArgs) _
        Handles SearchToolStripButton.Click

        Try
            Dim tb As DataTable = CustomersDB.GetCustomersByLastName(LastNameToolStripTextBox.Text)

            If tb.Rows.Count > 0 Then
                bs.DataSource = tb
                InquiryDGV.DataSource = bs
            Else
                MessageBox.Show("Search criteria yielded no records.",
                                            Me.Text,
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Information)
            End If
        Catch ex As DataException       ' General ADO.NET Component error.
            MessageBox.Show(ex.Message,
                ex.GetType.ToString,
                MessageBoxButtons.OK,
                MessageBoxIcon.Error)
        Catch ex As OleDbException      ' OleDBException error.
            MessageBox.Show("Database error # 0x" & ex.ErrorCode.ToString("X") & " - " & ex.Message,
                ex.GetType.ToString,
                MessageBoxButtons.OK,
                MessageBoxIcon.Error)
        Catch ex As Exception           ' General catch all error.
            MessageBox.Show(ex.Message,
                 ex.GetType.ToString,
                MessageBoxButtons.OK,
                MessageBoxIcon.Error)
        End Try

        ' Place cursor back into Textbox for next search.
        LastNameToolStripTextBox.SelectAll()
        LastNameToolStripTextBox.Focus()

    End Sub

End Class

'-------------------------------------------------------------------------------------------
' Class Name:     CustomersDB
'
' Author:         Joseph L. Bolen
' Date Created:   23 MAY 2016
'
' Description:    For this demonstration, the Customers table from the MS Access
'                 database Northwind is being used. The database's file location
'                 is retrieved from the App.config file using the NorthwindDB class.
'
Imports System.Data.OleDb

Public Class CustomersDB

    Public Shared Function GetCustomersByLastName(ByVal lName As String) As DataTable

        ' Best Practise is to list fields to be selected. NOT THE WILDCARD!
        ' Dim query As String = "SELECT * " &
        Dim query As String = "SELECT Company, [Last Name], [First Name], " &
                                "[Job Title], [Business Phone], [Fax Number] " &
                            "FROM Customers " &
                            "WHERE [Last Name] Like @p1 " &
                            "ORDER BY [Last Name], [First Name];"

        Dim tb As New DataTable

        Try
            Using con As OleDbConnection = NorthwindDB.GetConnection()
                Using cmd As New OleDbCommand(query, con)
                    ' ANSI-89 wildcard character is the asterisk (*).
                    ' ANSI-92 wildcard characters is the percent sign (%).
                    cmd.Parameters.AddWithValue("@p1", lName & "%")

                    con.Open()
                    Using rdr As OleDbDataReader = cmd.ExecuteReader
                        tb.Load(rdr)
                    End Using
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try

        Return tb

    End Function

End Class

'-------------------------------------------------------------------------------------------
' Class Name:     NorthwindDB
'
' Author:         Joseph L. Bolen
' Date Created:   23 MAY 2016
'
' Description:    For this demonstration, the Customers table from the MS Access
'                 database Northwind is being used. The database's file location
'                 is retrieved from the App.config file.
'-------------------------------------------------------------------------------------------

'Note: Add Reference to System.Configuration to the Project.

Imports System.Configuration
Imports System.Data.OleDb       ' Using a MS Access database

Public Class NorthwindDB

    Public Shared Function GetConnection() As OleDbConnection
        Dim conn As String = ConfigurationManager.ConnectionStrings("NorthwindDB").ConnectionString
        Return New OleDbConnection(conn)
    End Function

End Class

<?xml version="1.0" encoding="utf-8" ?>
<!--
 File Name:      app.config

 Author:         Joseph L. Bolen
 Date Created:   23 MAY 2016

 Description:    The ConnetionStrings block of coded was added to the file.
                 The named ConnectionString is NorthwindDB.
-->
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
  <connectionStrings>
    <add name="NorthwindDB" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
         Data Source=U:\Databases\AccessDatabases\Northwind.accdb;
         Persist Security Info=False;"
         providerName="System.Data.Oledb"/>
  </connectionStrings>
</configuration>