DataReader On Join Demo

'------------------------------------------------------------------------------------------
'           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:   DataReader On Join Demo
'
' Author:         Joseph L. Bolen
' Date Created:   10 NOV 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 Orders table from the MS SQL Express
'                 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 Visual Basic .NET code is at http://pastebin.com/WZ4uWk81
'-------------------------------------------------------------------------------------------
'Note: Add Reference to DAL (Data Access Layer) to this Project.

Imports DAL
Imports System.Data.SqlClient

Public Class InquiryForm

    Private bs As New BindingSource

    Private Sub InquiryForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        OrderDateDTP.CustomFormat = "MM/yyyy"
        OrderDateDTP.Format = DateTimePickerFormat.Custom

        ' 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 SearchButton_Click(sender As Object, e As EventArgs) _
        Handles SearchButton.Click

        Try
            Dim tb As DataTable = OrdersDB.GetOrdersByDate(OrderDateDTP.Value)

            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 SqlException      ' SQLException 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 backfor next search.

        OrderDateDTP.Focus()
    End Sub
End Class

'======================================================================================================

The App.Config file ...

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.2" />
    </startup>
  <connectionStrings>
    <add name="NorthwindDB" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;" providerName="System.Data.SQLClient"/>
  </connectionStrings>
</configuration>

'======================================================================================================
' In the DAL project...

Imports System.Data.SqlClient
Imports System.Configuration

Public Class NorthwindDB

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

'======================================================================================================


Imports System.Data.SqlClient

Public Class OrdersDB

    Public Shared Function GetOrdersByDate(ByVal selectionDate As Date) As DataTable

        ' Best Practise is to list fields to be selected. NOT THE WILDCARD!

        Dim query As String = "SELECT C.CustomerID, C.CompanyName, O.OrderID,  O.OrderDate " &
                              "FROM Orders As O " &
                              "INNER JOIN Customers As C " &
                              "ON O.CustomerID=C.CustomerID " &
                              "WHERE DATEPART(yyyy,O.OrderDate) = @yyyyOrderDate AND DATEPART(mm,O.OrderDate) = @mmOrderDate " &
                              "ORDER BY C.CompanyName;"
        Dim tb As New DataTable

        Try
            Using con As SqlConnection = NorthwindDB.GetConnection()
                Using cmd As New SqlCommand(query, con)
                    ' ANSI-89 wildcard character is the asterisk (*).
                    ' ANSI-92 wildcard characters is the percent sign (%).
                    cmd.Parameters.AddWithValue("@yyyyOrderDate", selectionDate.ToString("yyyy"))
                    cmd.Parameters.AddWithValue("@mmOrderDate", selectionDate.ToString("MM"))

                    con.Open()
                    Using rdr As SqlDataReader = 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