Imports System.Data.SqlClient

Module SQLClient
    '********** SQL Declarations
    'Friend stringCon As String = "Integrated Security=SSPI; Data Source=???; Initial Catalog=???"
    'Friend stringCon As String = "Integrated Security=False; Data Source=???; Initial Catalog=???; User ID=???; Password=???"
    'Friend stringCon As String = ""

#Region "SQL Query Reader"
    Friend Function SQLReadQuery(ByVal queryX As String, ByVal timeXout As Integer, connection As String) As DataTable
        Using conX As New SqlConnection(connection), comX As New SqlCommand, adapterX As New SqlDataAdapter, dataTableX As New DataTable
            dataTableX.Dispose()
            With comX
                .Connection = conX
                .CommandTimeout = timeXout
                .CommandText = "set nocount on; " & queryX.Trim
            End With
            adapterX.SelectCommand = comX
            adapterX.Fill(dataTableX)
            Return dataTableX
        End Using
    End Function

    Friend Function SQLReadQuery2(ByVal queryX As String, ByVal timeXout As Integer, connection As String) As Task(Of DataTable)
        Return Task.Factory.StartNew(Of DataTable)(
            Function()
                Using conX As New SqlConnection(connection), comX As New SqlCommand, adapterX As New SqlDataAdapter, dataTableX As New DataTable
                    dataTableX.Dispose()
                    With comX
                        .Connection = conX
                        .CommandTimeout = timeXout
                        .CommandText = "set nocount on; " & queryX.Trim
                    End With
                    adapterX.SelectCommand = comX
                    adapterX.Fill(dataTableX)
                    Return dataTableX
                End Using
            End Function)
    End Function
#End Region

#Region "SQL Query Writer"
    Friend Sub SQLWriteQuery(ByVal queryX As String, ByVal timeXout As Integer, connection As String)
        Using conX As New SqlConnection(connection), comX As New SqlCommand
            If conX.State = ConnectionState.Closed Then conX.Open()
            With comX
                .Connection = conX
                .CommandTimeout = timeXout
                .CommandText = "set nocount on; " & Trim(queryX)
                .ExecuteNonQuery()
            End With
        End Using
    End Sub
#End Region

#Region "SQL Bulk Copy"
    Sub SQLBulk(ByVal hazsh As HashSet(Of String), ByVal destiTable As String, ByRef dtX As Data.DataTable, ByVal connection As String)
        Try
            Using conX As New Data.SqlClient.SqlConnection(connection), bulkX As New Data.SqlClient.SqlBulkCopy(connection, Data.SqlClient.SqlBulkCopyOptions.TableLock Or Data.SqlClient.SqlBulkCopyOptions.UseInternalTransaction Or Data.SqlClient.SqlBulkCopyOptions.FireTriggers)
                bulkX.DestinationTableName = destiTable.Trim
                For Each Strr As String In hazsh
                    Dim rowStr As String() = Strr.Split(vbTab.ToCharArray)
                    dtx.Rows.Add(rowStr)
                Next

                If conX.State = ConnectionState.Closed Then conX.Open()
                With bulkX
                    .BulkCopyTimeout = 0
                    .BatchSize = 0
                    .WriteToServer(dtX)
                End With
                If conX.State = ConnectionState.Open Then conX.Close()
                dtX.Clear()
                hazsh.Clear()
                hazsh.TrimExcess()
            End Using
        Catch ex As Exception
            Console.WriteLine(Err.Source & vbCrLf & Err.Description)
        End Try
    End Sub
#End Region

#Region "SQL Query DataSet Filler"
    Friend Function SQLFillQuery(ByVal queryX As String, ByVal timeXout As Integer, TableName As String, connection As String) As DataSet
        Using conX As New System.Data.SqlClient.SqlConnection(connection), comX As New System.Data.SqlClient.SqlCommand, adapterX As New System.Data.SqlClient.SqlDataAdapter, dataSetX As New DataSet
            With comX
                .Connection = conX
                .CommandTimeout = timeXout
                .CommandType = CommandType.Text
                .CommandText = "set nocount on; " & Trim(queryX)
            End With
            adapterX.SelectCommand = comX
            adapterX.Fill(dataSetX, TableName)
            Return dataSetX
        End Using
    End Function
#End Region

#Region "SQL Datagridview Filler"
    Friend Sub SQLFillDG(ByVal queryX As String, ByVal timeXout As Integer, DG As DataGridView, TableName As String, connection As String)
        Dim dataTableX As New DataTable
        Using conX As New System.Data.SqlClient.SqlConnection(connection), comX As New System.Data.SqlClient.SqlCommand, adapterX As New System.Data.SqlClient.SqlDataAdapter
            With comX
                .Connection = conX
                .CommandTimeout = timeXout
                .CommandType = CommandType.Text
                .CommandText = "set nocount on " & vbCrLf & queryX.Trim
            End With
            adapterX.SelectCommand = comX
            adapterX.Fill(dataTableX)
        End Using
        With DG
            .SuspendLayout()
            .DataSource = dataTableX
            If dataSetX.Tables(TableName).Rows.Count > 0 Then
                .DataSource = dataSetX.Tables(TableName)
                For Each clm As DataGridViewColumn In .Columns
                    Dim notAvailable As Boolean = True
                    For Each row As DataGridViewRow In .Rows
                        System.Windows.Forms.Application.DoEvents()
                        If Not String.IsNullOrEmpty(row.Cells(clm.Index).Value.ToString()) Then
                            notAvailable = False
                            Exit For
                        Else
                            notAvailable = True
                        End If
                    Next
                    If notAvailable Then
                        .Columns(clm.Index).Visible = False
                    Else
                        .Columns(clm.Index).Visible = True
                    End If
                Next
                '.Visible = True
                .ClearSelection()
            End If
            .ResumeLayout()
            .Focus()
        End With
    End Sub
#End Region

End Module