{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "#r \"nuget:Docker.DotNet\"\n",
        "#r \"nuget:Npgsql\"\n",
        "\n",
        "using Docker.DotNet;\n",
        "using Docker.DotNet.Models;\n",
        "using Npgsql;\n",
        "using System.Data;\n",
        "using System.Data.Common;\n",
        "using System.Threading;\n",
        "using System.Threading.Tasks;"
      ],
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": "<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Docker.DotNet, 3.125.5</span></li><li><span>Npgsql, 6.0.5</span></li></ul></div></div>"
          },
          "execution_count": 1,
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "DbConnection Db;\n",
        "DockerClient _client;\n",
        "CreateContainerResponse _containerResponse;\n",
        "\n",
        "_client = new DockerClientConfiguration(new Uri(\"npipe://./pipe/docker_engine\")).CreateClient();"
      ],
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "var hostPort = new Random((int)DateTime.UtcNow.Ticks).Next(10000, 12000);\n",
        "\n",
        "var images = await _client.Images.ListImagesAsync(new ImagesListParameters()\n",
        "    { \n",
        "      Filters = new Dictionary<string, IDictionary<string, bool>>\n",
        "                   {\n",
        "                       [\"reference\"] = new Dictionary<string, bool>\n",
        "                       {\n",
        "                           [\"postgres:latest\"] = true\n",
        "                       }\n",
        "                   }\n",
        "    }, CancellationToken.None);\n",
        "\n",
        "\n",
        "\n",
        "\t \n",
        "\n",
        "//check if container exists\n",
        "    var pgImage = images.FirstOrDefault();\n",
        "    if (pgImage == null) throw new Exception($\"Docker image for postgres:latest not found.\");\n",
        "\n",
        "    //create container from image\n",
        "    var container = await _client.Containers.CreateContainerAsync(new CreateContainerParameters()\n",
        "    {\n",
        "        User = \"postgres\",\n",
        "        Env = new List<string>() {\n",
        "            \"POSTGRES_PASSWORD=password\",\"POSTGRES_DB=repotest\",\"POSTGRES_USER=postgres\"\n",
        "        },\n",
        "        ExposedPorts = new Dictionary<string, EmptyStruct>()\n",
        "        {\n",
        "            [\"5432\"] = new EmptyStruct()\n",
        "        },\n",
        "        HostConfig = new HostConfig()\n",
        "        {\n",
        "            PortBindings = new Dictionary<string, IList<PortBinding>>()\n",
        "            {\n",
        "                [\"5432\"] = new List<PortBinding>(){new PortBinding() {HostIP = \"0.0.0.0\", HostPort = $\"{hostPort}\"}}\n",
        "            }\n",
        "        },\n",
        "        Image = \"postgres:latest\",\n",
        "    }, CancellationToken.None);\n",
        "    \n",
        "    if (!await _client.Containers.StartContainerAsync(container.ID, new ContainerStartParameters()\n",
        "    { DetachKeys = \"d=postgres\" }, CancellationToken.None))\n",
        "        { throw new Exception($\"Could not start container: {container.ID}\");\n",
        "    }\n",
        "\n",
        "    var count = 10;\n",
        "    Thread.Sleep(5000);\n",
        "    var containerStat = await _client.Containers.InspectContainerAsync(container.ID, CancellationToken.None);\n",
        "    while (!containerStat.State.Running && count-- > 0)\n",
        "    {\n",
        "        Thread.Sleep(1000);\n",
        "        containerStat = await _client.Containers.InspectContainerAsync(container.ID, CancellationToken.None);\n",
        "    }\n",
        "    Thread.Sleep(10000); //I need some time for the DB to finish starting up so that my tests don't report the DB is starting up"
      ],
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "var connectionStringBuilder = new NpgsqlConnectionStringBuilder() { \n",
        "    ConnectionString = $\"User ID=postgres;Password=password;Server=127.0.0.1;Port={hostPort};\" + \n",
        "                        \"Database=repotest;Integrated Security=true;Pooling=false;CommandTimeout=300\" };\n",
        "Db = new NpgsqlConnection(connectionStringBuilder.ConnectionString);\n",
        "Db.Open();\n",
        "\n",
        "Console.WriteLine(String.Equals(\"repotest\", Db.Database)); "
      ],
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": "True\r\n"
          },
          "execution_count": 1,
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Creating a table for our bulk insert "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "using (var cmd = Db.CreateCommand()) {\n",
        "    cmd.CommandText = \"DROP TABLE IF EXISTS datatimeseries\";\n",
        "    cmd.ExecuteNonQuery();\n",
        "\n",
        "    cmd.CommandText = \"CREATE TABLE datatimeseries(idtimeseries INTEGER NOT NULL, timepoint TIMESTAMP NOT NULL, value1 NUMERIC NOT NULL)\";\n",
        "    cmd.ExecuteNonQuery();\n",
        "}"
      ],
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# bulk insert generic function"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "public void DumpDataTableToDB(string TableName, DataTable dt, int nCount)\n",
        "{\n",
        "    Dictionary<Type, NpgsqlTypes.NpgsqlDbType> TypeDict = new Dictionary<Type, NpgsqlTypes.NpgsqlDbType>();\n",
        "\n",
        "    TypeDict.Add(typeof(int), NpgsqlTypes.NpgsqlDbType.Integer);\n",
        "    TypeDict.Add(typeof(double), NpgsqlTypes.NpgsqlDbType.Double);\n",
        "    TypeDict.Add(typeof(decimal), NpgsqlTypes.NpgsqlDbType.Numeric);\n",
        "    TypeDict.Add(typeof(string), NpgsqlTypes.NpgsqlDbType.Varchar);\n",
        "    TypeDict.Add(typeof(DateTime), NpgsqlTypes.NpgsqlDbType.Timestamp);\n",
        "    TypeDict.Add(typeof(char[]), NpgsqlTypes.NpgsqlDbType.Varchar);\n",
        "    TypeDict.Add(typeof(Guid), NpgsqlTypes.NpgsqlDbType.Uuid);\n",
        "\n",
        "    string sql = \"COPY \" + TableName + \" ( \";\n",
        "    foreach (System.Data.DataColumn col in dt.Columns)\n",
        "    {\n",
        "        sql += (col.ColumnName.ToLower() + \",\");\n",
        "    }\n",
        "    sql = sql.TrimEnd(',') + \") FROM STDIN (FORMAT BINARY)\";\n",
        "    int nRows = dt.Rows.Count;\n",
        "    using (var BulkWrite = ((Npgsql.NpgsqlConnection)Db).BeginBinaryImport(sql))\n",
        "    {\n",
        "        for (int idRow = 0; idRow < nRows; idRow++)\n",
        "        {\n",
        "            BulkWrite.StartRow();\n",
        "            foreach (System.Data.DataColumn col in dt.Columns)\n",
        "            {\n",
        "                if (dt.Rows[idRow].IsNull(col))\n",
        "                {\n",
        "                    BulkWrite.WriteNull();\n",
        "                }\n",
        "                else\n",
        "                {\n",
        "                    if (col.DataType == typeof(string) && string.IsNullOrEmpty(dt.Rows[idRow].Field<string>(col)))\n",
        "                    {\n",
        "                        BulkWrite.WriteNull();\n",
        "                    }\n",
        "                    else\n",
        "                    {\n",
        "                        BulkWrite.Write(dt.Rows[idRow][col.Ordinal], TypeDict[col.DataType]);\n",
        "                    }\n",
        "                }\n",
        "            }\n",
        "\n",
        "        }\n",
        "        BulkWrite.Complete();\n",
        "    }\n",
        "}"
      ],
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Fill Data table"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "DataTable dt = new DataTable();\n",
        "dt.Columns.Add(\"IDTIMESERIES\", typeof(int));\n",
        "dt.Columns.Add(\"TIMEPOINT\", typeof(DateTime));\n",
        "dt.Columns.Add(\"VALUE1\", typeof(decimal));\n",
        "\n",
        "int nRowsMaxDt = 1000;\n",
        "DateTime now = DateTime.Now; \n",
        "\n",
        "for(int i = 0; i < nRowsMaxDt; i++)\n",
        "{\n",
        "    object[] oRowDt = new object[3];\n",
        "    oRowDt[0] = i / 100; oRowDt[1] = now.AddHours(i); oRowDt[2] = (decimal)(20 + 10 * Math.Sin(i / 66.0)); \n",
        "    dt.Rows.Add(oRowDt);\n",
        "}"
      ],
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Run bulk Insert"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "DumpDataTableToDB(\"datatimeseries\", dt, nRowsMaxDt);"
      ],
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Check number of Inserted rows"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "using (var cmd = Db.CreateCommand()) {\n",
        "cmd.CommandText = \"SELECT COUNT(*) FROM datatimeseries\";\n",
        "    var nRows = cmd.ExecuteScalar();\n",
        "    Console.WriteLine($\"Total rows in table - Expected 1000 - Found {nRows}\");\n",
        "}"
      ],
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": "Total rows in table - Expected 1000 - Found 1000\r\n"
          },
          "execution_count": 1,
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "Db.Close(); \n",
        "Db.Dispose();"
      ],
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "if (await _client.Containers.StopContainerAsync(container.ID, new ContainerStopParameters(), CancellationToken.None))\n",
        "{\n",
        "    Console.WriteLine(\"Container \" + container.ID + \" Stopped.\");\n",
        "    //delete container\n",
        "    await _client.Containers.RemoveContainerAsync(container.ID, new ContainerRemoveParameters(), CancellationToken.None);\n",
        "    Console.WriteLine(\"Container \" + container.ID + \" Deleted.\");\n",
        "}\n",
        "else\n",
        "{\n",
        "    Console.WriteLine(\"Container \" + container.ID + \" Unaffacted.\");\n",
        "}\n",
        "\n",
        "_client?.Dispose();"
      ],
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": "Container 3381398387d1a321f0bfd73e579ee3c4bdbd209aaa746fb9fe0a8f20dd46fc3b Deleted.\r\n"
          },
          "execution_count": 1,
          "metadata": {}
        }
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": ".NET (C#)",
      "language": "C#",
      "name": ".net-csharp"
    },
    "language_info": {
      "file_extension": ".cs",
      "mimetype": "text/x-csharp",
      "name": "C#",
      "pygments_lexer": "csharp",
      "version": "8.0"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 4
}