{
"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
}