Imports System.Threading
Imports System.ComponentModel
Imports System.Data.SqlClient

Public Class SQLConnector

    Dim Query As SqlCommand
    Dim Data As New DataSet
    Dim Adapter As SqlDataAdapter
    Dim QueryDone As Boolean
    Dim Connection As New SqlConnection

    Private WithEvents QueryExecutor As New BackgroundWorker
    Private WithEvents ProcExecutor As New BackgroundWorker
    Public Structure ExecutorParams
        Dim Query As String
    End Structure
    Public Sub New()

        'Setup worker thread
        QueryExecutor.WorkerReportsProgress = False
        QueryExecutor.WorkerSupportsCancellation = True

    End Sub
    Public Sub SetConnectionString(ByVal Server As String, ByVal UserName As String, ByVal Password As String, ByVal Database As String)
        'Setup connection
        Connection.ConnectionString = "Server=" & Server & ";Database=" & Database & ";User ID=pnp;Password=pick!@123;Min Pool size=50;Packet Size=32767;Connect Timeout=4096;"
    End Sub
    Public Function HasConnection() As Boolean

        Try
            Connection.Open()
            Connection.Close()
            Return True
        Catch ex As Exception
            MsgBox(ex.Message)
            Return False
        End Try

    End Function
    Public Sub RunQuery(ByVal Query As String)
        Dim Params As ExecutorParams

        Data.Tables.Clear()
        QueryDone = False

        Params.Query = Query
        QueryExecutor.RunWorkerAsync(Params)

    End Sub
    Private Sub QueryExecutorThread(sender As Object, e As DoWorkEventArgs) Handles QueryExecutor.DoWork
        Dim Params As ExecutorParams = e.Argument

        Try
            Connection.Open()

            Query = New SqlCommand(Params.Query, Connection)
            Adapter = New SqlDataAdapter(Query)

            Adapter.Fill(Data)

            Connection.Close()

        Catch ex As Exception
            QueryDone = True
            MsgBox("An error was Thrown from the SQL Query Executor Thread:" & vbCrLf & vbCrLf & ex.Message, vbOKOnly & vbExclamation, "SQL Query failed!")
            If Connection.State = ConnectionState.Open Then Connection.Close()
        End Try

        QueryDone = True

    End Sub
    Public ReadOnly Property QueryExecuterIsIdle As Boolean
        Get
            Return Not QueryExecutor.IsBusy
        End Get
    End Property
    Public ReadOnly Property QueryHasCompleted As Boolean
        Get
            Return QueryDone
        End Get
    End Property
    Public ReadOnly Property DataWasReturned As Boolean
        Get
            If Not Data Is Nothing AndAlso ((Data.Tables.Count > 0) AndAlso (Data.Tables(0).Rows.Count > 0)) Then
                Return True
            Else
                Return False
            End If
        End Get
    End Property
    Public Property DataBuffer As DataSet
        Get
            Return Data
        End Get
        Set(value As DataSet)
            Data = value
        End Set
    End Property


End Class