DockerInteraction.ipynb

{
  "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.Threading;\n",
        "using System.Threading.Tasks;\n",
        "using System.Data.Common;"
      ],
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "Installing package Docker.DotNet..............done!"
            ]
          },
          "execution_count": 1,
          "metadata": {}
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "Successfully added reference to package Docker.DotNet, version 3.125.2"
            ]
          },
          "execution_count": 1,
          "metadata": {}
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "Installing package Npgsql.....done!"
            ]
          },
          "execution_count": 1,
          "metadata": {}
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "Successfully added reference to package Npgsql, version 4.1.1"
            ]
          },
          "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",
        "\t\tFilters = 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",
        "//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": "stream",
          "name": "stdout",
          "text": "True"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Creating a new database with a new user, connecting with user"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "using (var cmd = Db.CreateCommand()) {\n",
        "    cmd.CommandText = \"CREATE DATABASE testdb\";\n",
        "    cmd.ExecuteNonQuery();\n",
        "    \n",
        "    cmd.CommandText = \"CREATE USER pgtester CREATEROLE LOGIN NOREPLICATION PASSWORD 'abcdef@123'\";\n",
        "    cmd.ExecuteNonQuery();\n",
        "    \n",
        "    cmd.CommandText = \"GRANT ALL PRIVILEGES ON DATABASE testdb TO pgtester;\";\n",
        "    cmd.ExecuteNonQuery();\n",
        "}\n",
        "\n",
        "Db.Close(); \n",
        "connectionStringBuilder = new NpgsqlConnectionStringBuilder() { ConnectionString = $\"User ID=pgtester;Password=abcdef@123;Server=127.0.0.1;Port={hostPort};Database=testdb;Integrated Security=true;Pooling=false;CommandTimeout=300\" };\n",
        "Db = new NpgsqlConnection(connectionStringBuilder.ConnectionString);\n",
        "Db.Open();"
      ],
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Creating a table and filling it up "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "using (var cmd = Db.CreateCommand()) {\n",
        "    cmd.CommandText = \"DROP TABLE IF EXISTS counterparties\";\n",
        "    cmd.ExecuteNonQuery();\n",
        "\n",
        "    cmd.CommandText = \"CREATE TABLE counterparties(id serial PRIMARY KEY, cp_name VARCHAR(100) NOT NULL, created_on TIMESTAMP NOT NULL)\";\n",
        "    cmd.ExecuteNonQuery();\n",
        "\n",
        "    cmd.CommandText = \"INSERT INTO counterparties(cp_name, created_on) VALUES ('Investment bank A', '2019-11-11')\";\n",
        "    cmd.ExecuteNonQuery();\n",
        "    \n",
        "    cmd.CommandText = \"INSERT INTO counterparties(cp_name, created_on) VALUES ('Investment bank B','\" + DateTime.Now.ToString(\"yyyy-MM-dd\") + \"')\";\n",
        "    cmd.ExecuteNonQuery();\n",
        "}"
      ],
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Using bound variables for the insertion"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "using (var npgCmd = new NpgsqlCommand(\"INSERT INTO counterparties(cp_name, created_on) VALUES (@n, @d)\", (NpgsqlConnection)Db))\n",
        "{\n",
        "    npgCmd.Parameters.AddWithValue(\"n\", \"Energy Company A\");\n",
        "    npgCmd.Parameters.AddWithValue(\"d\", DateTime.Now);\n",
        "    npgCmd.ExecuteNonQuery();\n",
        "}"
      ],
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Checking how many rows were inserted"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        "using (var cmd = Db.CreateCommand()) {\n",
        "cmd.CommandText = \"SELECT COUNT(*) FROM counterparties\";\n",
        "    var nRows = cmd.ExecuteScalar();\n",
        "    Console.WriteLine($\"Total rows in table - Expected 3 - Found {nRows}\");\n",
        "}"
      ],
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": "Total rows in table - Expected 3 - Found 3"
        }
      ]
    },
    {
      "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": "stream",
          "name": "stdout",
          "text": "Container f061182b6b33b1d5f3acef9284a504c77ed34af1c679e47d1c69d92e1b43ec75 Stopped.\nContainer f061182b6b33b1d5f3acef9284a504c77ed34af1c679e47d1c69d92e1b43ec75 Deleted."
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "dotnet_interactive": {
          "language": "csharp"
        }
      },
      "source": [
        ""
      ],
      "outputs": []
    }
  ],
  "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
}