' DB DATA
Public DBDA As SqlDataAdapter
Public DBDT As DataTable
Public DBDS As DataSet


' QUERY PARAMETERS
Public Params As New List(Of SqlParameter)

' QUERY STATISTICS
Public RecordCount As Integer
Public Exception As String

Public Sub New()
End Sub


' ALLOW CONNECTION STRING OVERRIDE
Public Sub New(ByVal ConnectionString As String)
    DBCon = New SqlConnection(ConnectionString)
End Sub

' LOGIN CONNECTION TO DB
Public Function HasConnection() As Boolean
    Try
        DBCon.Open()

        DBCon.Close()
        Return True
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    Return False
End Function

'RUN LOGIN CONNECTION TO DB
Public Sub RunQuery(ByVal Query As String)
    Try
        DBCon.Open()

        'CREATE A COMMAND
        DBCmd = New SqlCommand(Query, DBCon)

        'FILL DATASET
        DBDA = New SqlDataAdapter(DBCmd)
        DBDS = New DataSet
        DBDA.Fill(DBDS)


        DBCon.Close()

    Catch ex As Exception
        MsgBox(ex.Message)
        ' MAKES SURE THAT THE CONNECTION IS CLOSED
        If DBCon.State = ConnectionState.Open Then
            DBCon.Close()
        End If
    End Try
End Sub
' EXECUTE QUERY SUB
Public Sub ExecQuery(ByVal Query As String)
    ' RESET QUERY STATS
    RecordCount = 0
    Exception = ""

    Try
        DBCon.Open()
        ' CREATE DB COMMAND
        DBCmd = New SqlCommand(Query, DBCon)

        'LOAD PARAMS INTO DB COMMAND
        Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

        'CLEAR PARAM LIST
        Params.Clear()

        'EXECUTE COMMAND & FILL DATASET
        DBDT = New DataTable
        DBDA = New SqlDataAdapter(DBCmd)
        RecordCount = DBDA.Fill(DBDT)

    Catch ex As Exception
        'CAPTURE ERROR
        Exception = "ExecQuery Error: " & vbNewLine & ex.Message

    Finally
        ' CLOSE CONNECTION
        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Try
End Sub

'ADD PARAMS
Public Sub AddParam(ByVal Name As String, ByVal Value As Object)
    Dim NewParam As New SqlParameter(Name, Value)
    Params.Add(NewParam)
End Sub

' ERROR CHECKING
Public Function HasException(Optional ByVal Report As Boolean = False) As Boolean
    If String.IsNullOrEmpty(Exception) Then Return False
    If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
    Return True
End Function

Private Sub BtnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
    ' CONNECT TO MAIN FORM USING USERNAME AND PASSWORD FROM DB
    If SQL.HasConnection = True Then
        If IsAuthenticated() = True Then
            AuthUser = txtUsername.Text
            MsgBox("Login Successfully!")
            OfficeEquipments.Show()
            Me.Hide()
        End If
    End If
End Sub

Private Function IsAuthenticated() As Boolean
    ' CLEAR EXISTING RECORDS
    If SQL.DBDS IsNot Nothing Then
        SQL.DBDS.Clear()

    End If
    'SQL PASSWORD CASE SENSITIVE COMMAND
    SQL.RunQuery("SELECT Count(Username) As UserCount " & _
                 "FROM Accounts " & _
                 "WHERE Username='" & txtUsername.Text & "' " & _
                 "AND Password='" & txtPassword.Text & "' COLLATE SQL_Latin1_General_CP1_CS_AS")

    If SQL.DBDS.Tables(0).Rows(0).Item("UserCount") = 1 Then
        Return True

    End If
    MsgBox("Invalid User Credentials.", MsgBoxStyle.Critical, "LOGIN FAILED")
    Return False
End Function