Imports System.Data.SqlClient
Imports System.IO
Imports MyNameSpace.CSVDataAdapter
Imports System.Reflection
Imports System.Text




Module Module1

    Dim tablename As String = ""
    Dim inputfile As String = ""
    Dim Lines As Integer = 0
    Dim totallc As Integer = 0

    Dim dt As New DataSet
    Dim CONNSTRING As String = "data source=shu00;initial catalog=Coldabgleich;persist security info=False;workstation id=;packet size=4096;user id=sa;password=*shu29"
    Dim arg() = Environment.GetCommandLineArgs()

    Sub Main()
        FileOpen(5, ApplicationPath() + "loadjournal.txt", OpenMode.Append, OpenAccess.Write)
        tablename = arg(1)
        inputfile = arg(2)
        Lines = arg(3)
        Console.WriteLine("Tabelle: " + tablename)
        Console.WriteLine("Inputfi: " + inputfile)
        FileOpen(1, ApplicationPath() + "\connstring.txt", OpenMode.Input, OpenAccess.Read)
        Input(1, CONNSTRING)
        FileClose(1)
        Load_data()
        FileClose(5)

    End Sub

    Public Function ApplicationPath() As String
        Return Path.GetDirectoryName([Assembly].GetEntryAssembly().Location) + "\"
    End Function


    Private Sub Load_data()
        Dim lc As Integer = 0
        Dim line As String
        Dim reader As StreamReader = New StreamReader(inputfile, Encoding.Default)
        Dim writer As StreamWriter = New StreamWriter(inputfile + ".tmp", False, System.Text.Encoding.Unicode)
        Do Until reader.EndOfStream
            line = reader.ReadLine
            writer.WriteLine(line)
            lc = lc + 1
            totallc = totallc + 1
            If lc > Lines - 1 Then
                Console.WriteLine(Now.ToString + ": Rows G: " + lc.ToString)
                lc = 0
                writer.Flush()
                writer.Close()
                Bulkloader(inputfile + ".tmp")
                writer.Dispose()
                writer = New StreamWriter(inputfile + ".tmp")
            End If
        Loop
        writer.Flush()
        writer.Close()
        Bulkloader(inputfile + ".tmp")
        Console.WriteLine("Ende")
        WriteLine(5, Now.ToString + " " + inputfile + " Total Rows:" + totallc.ToString)
        Console.WriteLine()
    End Sub

    
    Sub Bulkloader(ByVal filename As String)
        Dim x As New MyNameSpace.CSVDataAdapter(filename, False, vbTab)
        Using bulkCopy = New SqlBulkCopy(CONNSTRING)
            dt.Tables.Clear()
            x.Fill(dt, "Daten")
            Console.WriteLine(Now.ToString + ": Rows I: " + dt.Tables(0).Rows.Count.ToString)
            bulkCopy.DestinationTableName = tablename
            SET_COLUMNAMES(tablename)
            'MsgBox(dt.Tables(0).Rows.Count)
            bulkCopy.WriteToServer(dt.Tables(0))
        End Using ' dispose of SqlBulkCopy object
    End Sub

    Private Sub SET_COLUMNAMES(ByVal TABLENAME)
        Dim connection As New SqlConnection()
        Dim da As New SqlDataAdapter("", connection)
        Dim sqlcmd As New SqlCommand
        Dim ds As New DataSet
        sqlcmd.CommandText = "Select top 1 * from " + TABLENAME
        sqlcmd.CommandType = CommandType.Text
        sqlcmd.Connection = connection
        Try
            connection.ConnectionString = CONNSTRING
            connection.Open()
            da.SelectCommand = sqlcmd
            da.Fill(ds, "Datatable1")
            Dim i As Integer
            For i = 0 To ds.Tables(0).Columns.Count - 1
                dt.Tables(0).Columns(i).ColumnName = ds.Tables(0).Columns(i).ColumnName
            Next
            'Insert_Journal("-> Total Datensätze: " & Trim(Str(ds.Tables(0).Rows.Count)))
        Catch ex As Exception
        Finally
            connection.Close()
            da.Dispose()
            sqlcmd.Dispose()
        End Try

    End Sub

End Module