Imports System.IO
Imports Npgsql

Module Program
    Sub Main()

        Dim connectionBuilder As New NpgsqlConnectionStringBuilder With {
            .Host = "localhost",
            .Database = "NpgsqDB",
            .Username = "postgres",
            .Password = "123456",
            .Pooling = False,
            .SearchPath = "public",
            .ApplicationName = Process.GetCurrentProcess.Id.ToString(),
            .Enlist = False,
            .MaxAutoPrepare = 100,
            .Port = 20457
        }

        Using connection As New NpgsqlConnection(connectionBuilder.ToString())
            connection.Open()
            For fileIndex As Integer = 0 To 579
                Dim table As New DataTable
                table.ReadXmlSchema($".\Data\{fileIndex}_table_schema.xsd")
                table.ReadXml($".\Data\{fileIndex}_table.xml")
                If table.Rows.Count > 0 Then
                    Dim sql As String = File.ReadAllText($".\Data\{fileIndex}_select_command.sql")
                    Console.WriteLine($"File number: {fileIndex}")
                    SaveTableToPostgres(connection, table, sql)
                Else
                    Console.WriteLine($"File number: {fileIndex} has not rows.")
                End If
            Next
        End Using
    End Sub



    Sub SaveTableToPostgres(connection As NpgsqlConnection, table As DataTable, [select] As String)

        Dim selectCommand As New NpgsqlCommand([select], connection)
            Dim adapter As New NpgsqlDataAdapter(selectCommand)
            Dim commandBuilder As New NpgsqlCommandBuilder(adapter)

            table.AcceptChanges()
            For Each col As DataColumn In table.Columns
                If Not table.PrimaryKey.Contains(col) Then
                    Dim value As Object = table.Rows(0)(col)
                    table.Rows(0)(col) = DBNull.Value
                    table.Rows(0)(col) = value
                End If
            Next

            Dim changes As DataTable = table.GetChanges()
            If changes IsNot Nothing Then
                Dim stopwatch As New Stopwatch()
                stopwatch.Start()
                adapter.UpdateCommand = commandBuilder.GetUpdateCommand()
                stopwatch.Stop()
                Console.WriteLine($"Time taken for GetUpdateCommand: {stopwatch.ElapsedMilliseconds} ms")
                stopwatch.Restart()
                adapter.InsertCommand = commandBuilder.GetInsertCommand()
                stopwatch.Stop()
                Console.WriteLine($"Time taken for GetInsertCommand: {stopwatch.ElapsedMilliseconds} ms")
                stopwatch.Restart()
                adapter.DeleteCommand = commandBuilder.GetDeleteCommand()
                stopwatch.Stop()
                Console.WriteLine($"Time taken for GetDeleteCommand: {stopwatch.ElapsedMilliseconds} ms")
                stopwatch.Restart()
                adapter.Update(changes)
                Console.WriteLine($"Time taken for Adapter Update: {stopwatch.ElapsedMilliseconds} ms")
            End If


    End Sub




End Module
