{ "cells": [ { "cell_type": "markdown", "id": "82cd130a", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "source": [ "操作数据库\n", "=========" ] }, { "cell_type": "markdown", "id": "7c1ce91f", "metadata": {}, "source": [ "原理：使用#!connect命令，连接子内核进行操作。可以连接MSSQL、SQLite、PostgreSQL等；" ] }, { "cell_type": "markdown", "id": "ed3707e0", "metadata": {}, "source": [ "## 初始化\n", "运行各单元格之前，必须先执行一次。" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/html": [ "
Installed Packages
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Installed Packages
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "//全局初始化\n", "#!import \"./Base.ipynb\"\n", "\n", "#r \"nuget:Microsoft.Identity.Client,4.66.2\"\n", "\n", "//共享\n", "using Microsoft.DotNet.Interactive;\n", "using Microsoft.DotNet.Interactive.Commands;" ] }, { "cell_type": "markdown", "id": "8373e80a", "metadata": {}, "source": [ "## 连接数据库(SQL Server 2019为例)" ] }, { "cell_type": "markdown", "id": "24e923e2", "metadata": {}, "source": [ "### 第一步：C#内核单元格中，引入相关的 nuget 包\n", "每种数据库都有自己的包，形如 Microsoft.DotNet.Interactive.DbName, SQL Server的包为 Microsoft.DotNet.Interactive.SqlServer" ] }, { "cell_type": "markdown", "id": "eb2d3716", "metadata": {}, "source": [ "+ 在 `VS Code 终端的 NuGet 包管理` 里输入 `Microsoft.DotNet.Interactive.` 查询(把预览给勾上)\n", "![image](./assets/images/NuGet1.jpg)\n", "\n", "+ 在[NuGet官网](https://www.nuget.org/)搜索 `Microsoft.DotNet.Interactive.`\n", "![image](./assets/images/NuGet2.jpg)" ] }, { "cell_type": "code", "execution_count": 2, "id": "75b36179", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/html": [ "
Installed Packages
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Loading extension script from `C:\\Users\\ruyu\\.nuget\\packages\\microsoft.dotnet.interactive.sqlserver\\1.0.0-beta.25177.1\\interactive-extensions\\dotnet\\extension.dib`" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Query Microsoft SQL Server databases.\r\n", "

This extension adds support for connecting to Microsoft SQL Server databases using the #!connect mssql magic command. For more information, run a cell using the #!sql magic command.

\r\n", "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "//引入 SqlServer 的 NuGet 包\n", "#r \"nuget:Microsoft.DotNet.Interactive.SqlServer,*-*\"" ] }, { "cell_type": "markdown", "id": "e4fc5e3f", "metadata": {}, "source": [ "### 第二步：使用 #！connect 命令的语法，连接数据库子内核" ] }, { "cell_type": "markdown", "id": "dc09ad65", "metadata": {}, "source": [ "使用魔术命令 `#!connect mssql --kernel-name SqlServerKernelDemo \"Server=.\\SQL2019;Database=study;User Id=sa;Password=gly-bicijinlian;TrustServerCertificate=true;\"`\n", "\n", "实测有缺点：\n", "+ 不能重复执行：重复执行会报错\n", "+ 连接字符串必须是真实字符串：不能是变量，不灵活；明文\"不安全\",比如演示环境\n", "\n", "变通用法：在C#程序中，拼接好魔术命令`#!connect`，再发送给内核执行" ] }, { "cell_type": "code", "execution_count": 3, "id": "201f6662", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/plain": [ "Kernel added: #!sql-SqlServerKernelDemo" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "// 连接魔术命令：\n", "// #!connect mssql --kernel-name SqlServerKernelDemo \"Server=.\\SQL2019;Database=study;User Id=sa;Password=密码;TrustServerCertificate=true;\"\n", "\n", "//优化用法\n", "{\n", " //内核名：魔法命令中的内核名，执行后会自动加 sql- 前缀，做为内核名被使用\n", " string magicCommandKernelName = \"SqlServerKernelDemo\";\n", " string completeKernelName = \"sql-\" + magicCommandKernelName;\n", "\n", " //引入内核：可重复执行\n", " if(Microsoft.DotNet.Interactive.Kernel.Root.FindKernelByName(completeKernelName) == null)\n", " {\n", " var connectKernelCode = $\"#!connect mssql --kernel-name {magicCommandKernelName} \\\"{SharedDbConnect.MsSqlConnectionString}\\\"\";\n", " await Kernel.Root.SendAsync(new SubmitCode( connectKernelCode, \"csharp\"));\n", " }\n", " else\n", " {\n", " Console.WriteLine($\"名为 {completeKernelName} 的内核已存在。需要新内核时，请为--kernel-name参数使用不同的值, 本次执行不做任何更改！\");\n", " }\n", "}" ] }, { "cell_type": "markdown", "id": "9dbc4af6", "metadata": {}, "source": [ "## 查询数据库(SQL Server 2019为例)" ] }, { "cell_type": "markdown", "id": "3a4296f8", "metadata": {}, "source": [ "使用SQL语法，进行数据库操作" ] }, { "cell_type": "code", "execution_count": 4, "id": "c6474c18", "metadata": { "polyglot_notebook": { "kernelName": "sql-SqlServerKernelDemo" } }, "outputs": [ { "data": { "text/plain": [ "命令已成功完成。" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "(10 行受到影响)" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,吕宇宁,74\r\n", "2,韦子异,3\r\n", "3,姚宇宁,27\r\n", "4,傅子异,18\r\n", "5,朱子韬,5\r\n", "6,林杰宏,73\r\n", "7,胡璐,60\r\n", "8,周璐,23\r\n", "9,田秀英,6\r\n", "10,姜詩涵,82\r\n" ], "text/html": [ "
IdNameAge
1
吕宇宁
74
2
韦子异
3
3
姚宇宁
27
4
傅子异
18
5
朱子韬
5
6
林杰宏
73
7
胡璐
60
8
周璐
23
9
田秀英
6
10
姜詩涵
82
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "--语句选择 #!connect 命令设置的SQL内核名\n", "#!sql-SqlServerKernelDemo\n", "\n", "--原始SQL查询语句\n", "select top 10 * from student" ] }, { "cell_type": "markdown", "id": "1cc77bdf", "metadata": {}, "source": [ "使用SQL语法，进行数据库操作" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "polyglot_notebook": { "kernelName": "sql-SqlServerKernelDemo" } }, "outputs": [ { "data": { "text/plain": [ "(10 行受到影响)" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,吕宇宁,74\r\n", "2,韦子异,3\r\n", "3,姚宇宁,27\r\n", "4,傅子异,18\r\n", "5,朱子韬,5\r\n", "6,林杰宏,73\r\n", "7,胡璐,60\r\n", "8,周璐,23\r\n", "9,田秀英,6\r\n", "10,姜詩涵,82\r\n" ], "text/html": [ "
IdNameAge
1
吕宇宁
74
2
韦子异
3
3
姚宇宁
27
4
傅子异
18
5
朱子韬
5
6
林杰宏
73
7
胡璐
60
8
周璐
23
9
田秀英
6
10
姜詩涵
82
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "--右下方：选择的SQL内核\n", "\n", "--原始SQL查询语句\n", "select top 10 * from student" ] }, { "cell_type": "markdown", "id": "ec584f13", "metadata": {}, "source": [ "## 连接数据库：使用 `--create-dbcontext` 参数，自动创建 EFCore 上下文 `DbContext`" ] }, { "cell_type": "markdown", "id": "7b4f61fc", "metadata": {}, "source": [ "说明：目前默认情况下，Microsoft.DotNet.Interactive.SqlServer里引用的Microsoft.Identity.Client包，与环境中不一样，故需要单独引用特定版本的Microsoft.Identity.Client包，不知道后续官方是否会改正。目前单独包引用放在初始化单元格。" ] }, { "cell_type": "markdown", "id": "01d19fab", "metadata": {}, "source": [ "### 带 `--create-dbcontext` 参数的连接" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/markdown": [ "Scaffolding a `DbContext` and initializing an instance of it called `SqlServerKernelWithEF` in the C# kernel." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Installed Packages
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Kernel added: #!sql-SqlServerKernelWithEF" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "/* 连接魔术命令\n", " 给 #!connect mssql 加 --create-dbcontext 参数：连接操作，会执行下面的任务：\n", " 1、搭建EFCore基架,并初始化 DBContext 的实例: xxxx\n", " 2、安装包相关Nuget包,详情见输出\n", " 3、添加新的子内核 #!sql-xxx\n", "**/\n", "\n", "//注意：由于引用的Microsoft.Identity.Client包，版本冲突，目前把 Microsoft.Identity.Client的引用，放在 Microsoft.DotNet.Interactive.SqlServer之前\n", "// #r \"nuget:Microsoft.Identity.Client,4.66.2\" 放入初始化\n", "\n", "//优化用法\n", "{\n", " //内核名：魔法命令中的内核名，执行后会自动加 sql- 前缀，做为内核名被使用\n", " string magicCommandKernelName = \"SqlServerKernelWithEF\";\n", " string completeKernelName = \"sql-\" + magicCommandKernelName;\n", "\n", " //引入内核：可重复执行\n", " if(Microsoft.DotNet.Interactive.Kernel.Root.FindKernelByName(completeKernelName) == null)\n", " {\n", " var connectKernelCode = $\"#!connect mssql --kernel-name {magicCommandKernelName} \\\"{SharedDbConnect.MsSqlConnectionString}\\\" --create-dbcontext\";\n", " await Kernel.Root.SendAsync(new SubmitCode( connectKernelCode, \"csharp\"));\n", " }\n", " else\n", " {\n", " Console.WriteLine($\"名为 {completeKernelName} 的内核已存在。需要新内核时，请为--kernel-name参数使用不同的值, 本次执行不做任何更改！\");\n", " }\n", "}" ] }, { "cell_type": "markdown", "id": "eae8ee05", "metadata": {}, "source": [ "### SQL内核中使用(与一般连接一样使用)" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "polyglot_notebook": { "kernelName": "sql-SqlServerKernelWithEF" } }, "outputs": [ { "data": { "text/plain": [ "(5 行受到影响)" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,吕宇宁,74\r\n", "2,韦子异,3\r\n", "3,姚宇宁,27\r\n", "4,傅子异,18\r\n", "5,朱子韬,5\r\n" ], "text/html": [ "
IdNameAge
1
吕宇宁
74
2
韦子异
3
3
姚宇宁
27
4
傅子异
18
5
朱子韬
5
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "--原始SQL查询语句\n", "select top 5 * from student" ] }, { "cell_type": "markdown", "id": "27ee738f", "metadata": {}, "source": [ "### C#语句中，直接使用 DbContext 上下文" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [], "source": [ "//连接建立后，执行环境中就有了相关的类：DBContext等\n", "/* 因为类型版本问题，不能实际执行；后续看\n", "{\n", " //直接查询\n", " var t = SqlServerKernelWithEF.Students.Take(5).ToList();\n", " display(t);\n", "\n", " //EF内执行SQL语句\n", " FormattableString fs = $\"select top 3 * from Student;\";\n", " var c = SqlServerKernelWithEF.Database.ExecuteSql(fs);\n", " display(c);\n", "}\n", "*/" ] }, { "cell_type": "markdown", "id": "867b43cc", "metadata": {}, "source": [ "## 共享数据库操作结果数据(仅支持SQL Server数据库，以SQL Server 2019为例)" ] }, { "cell_type": "markdown", "id": "b1ca8234", "metadata": {}, "source": [ "### 1、引入NuGet包(引入一次就好，为明确步骤再次引入)" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/html": [ "
Installed Packages
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#r \"nuget:Microsoft.DotNet.Interactive.SqlServer,*-*\"" ] }, { "cell_type": "markdown", "id": "a05705ab", "metadata": {}, "source": [ "### 2、连接内核命令：生成新SQL内核" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/plain": [ "Kernel added: #!sql-SqlServerKernelShared" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "//优化变通方式\n", "{\n", " //内核名：魔法命令中的内核名，执行后会自动加 sql- 前缀，做为内核名被使用\n", " string magicCommandKernelName = \"SqlServerKernelShared\";\n", " string completeKernelName = \"sql-\" + magicCommandKernelName;\n", "\n", " //引入内核：可重复执行\n", " if(Microsoft.DotNet.Interactive.Kernel.Root.FindKernelByName(completeKernelName) == null)\n", " {\n", " var connectKernelCode = $\"#!connect mssql --kernel-name {magicCommandKernelName} \\\"{SharedDbConnect.MsSqlConnectionString}\\\"\";\n", " await Kernel.Root.SendAsync(new SubmitCode( connectKernelCode, \"csharp\"));\n", " }\n", " else\n", " {\n", " Console.WriteLine($\"名为 {completeKernelName} 的内核已存在。需要新内核时，请为--kernel-name参数使用不同的值, 本次执行不做任何更改！\");\n", " }\n", "}" ] }, { "cell_type": "markdown", "id": "cc6a6d60", "metadata": {}, "source": [ "### 3、共享方式使用第2步生成的新SQL内核，操作数据库(使用了 --name 命令参数)" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "polyglot_notebook": { "kernelName": "sql-SqlServerKernelShared" } }, "outputs": [ { "data": { "text/plain": [ "(5 行受到影响)" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "(1 行受到影响)" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,吕宇宁,74\r\n", "2,韦子异,3\r\n", "3,姚宇宁,27\r\n", "4,傅子异,18\r\n", "5,朱子韬,5\r\n" ], "text/html": [ "
IdNameAge
1
吕宇宁
74
2
韦子异
3
3
姚宇宁
27
4
傅子异
18
5
朱子韬
5
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Count\r\n", "5\r\n" ], "text/html": [ "
Count
5
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#!sql-SqlServerKernelShared --name sqlServerKernelQueryShared\n", "\n", "SELECT top 5 Id,Name,Age from Student;\n", "SELECT 5 as Count;" ] }, { "cell_type": "markdown", "id": "f4f5988e", "metadata": {}, "source": [ "### 4、多种方式使用查询共享数据" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Id Name Age\n", "1 吕宇宁 74\n", "2 韦子异 3\n", "3 姚宇宁 27\n", "4 傅子异 18\n", "5 朱子韬 5\n" ] }, { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,吕宇宁,74\r\n", "2,韦子异,3\r\n", "3,姚宇宁,27\r\n", "4,傅子异,18\r\n", "5,朱子韬,5\r\n" ], "text/html": [ "
IdNameAge
1
吕宇宁
74
2
韦子异
3
3
姚宇宁
27
4
傅子异
18
5
朱子韬
5
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Count\r\n", "5\r\n" ], "text/html": [ "
Count
5
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
[{"Id":1,"Name":"吕宇宁","Age":74},{"Id":2,"Name":"韦子异","Age":3},{"Id":3,"Name":"姚宇宁","Age":27},{"Id":4,"Name":"傅子异","Age":18},{"Id":5,"Name":"朱子韬","Age":5}]
{"Id":1,"Name":"吕宇宁","Age":74}
Id
1
Name"吕宇宁"
Age
74
{"Id":2,"Name":"韦子异","Age":3}
Id
2
Name"韦子异"
Age
3
{"Id":3,"Name":"姚宇宁","Age":27}
Id
3
Name"姚宇宁"
Age
27
{"Id":4,"Name":"傅子异","Age":18}
Id
4
Name"傅子异"
Age
18
{"Id":5,"Name":"朱子韬","Age":5}
Id
5
Name"朱子韬"
Age
5
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "//C#语言中使用：查询共享数据\n", "\n", "#!set --name fromMsSqlQueryShared --value @sql-SqlServerKernelShared:sqlServerKernelQueryShared\n", "using Microsoft.DotNet.Interactive.Formatting.TabularData;\n", "using Microsoft.DotNet.Interactive.Formatting;\n", "/**\n", " 说明：这里获取到的共享SQL结果，实质是SQL操作结果的Json序列化字符串；\n", " 在C#单元格里，默认被反序列化为了 System.Text.Json.JsonElement,其根元素RootElement，是结果集合(几条SQL语句，结果集就有几项)\n", " .dot interactive程序集中，序列化时，实际是使用 System.Text.Json 序列化的 TabularDataResource 类(在Microsoft.DotNet.Interactive.Formatting.TabularData命名空间)的集合\n", "\n", " 所以C#中使用共享数据，有两种方式：直接操作System.Text.Json.JsonElement对象和反序列化为TabularDataResource数组\n", "*/\n", "//使用共享数据：直接操作JsonElement对象\n", "{\n", " //有几个操作结果，fromMsSqlQuery.RootElement中就有几个结果集\n", " var dataSet = fromMsSqlQueryShared.RootElement;\n", "\n", " //第一个查询结果集\n", " var dataTable1 = dataSet[0];\n", "\n", " //列结构\n", " var fieldNames = dataTable1.GetProperty(\"schema\").GetProperty(\"fields\").EnumerateArray().Select(s=>s.GetProperty(\"name\"));\n", "\n", " Console.WriteLine(string.Join(\" \",fieldNames));\n", "\n", " //数据\n", " var data = dataTable1.GetProperty(\"data\").EnumerateArray().AsEnumerable();\n", " foreach(var row in data)\n", " {\n", " Console.WriteLine($\"{row.GetProperty(\"Id\")} {row.GetProperty(\"Name\")} {row.GetProperty(\"Age\")}\");\n", " }\n", "\n", " //第2个结果集\n", " var count = fromMsSqlQueryShared.RootElement[1].GetProperty(\"data\")[0].GetProperty(\"Count\");\n", "\n", " //优化：使用Html展示\n", "}\n", "\n", "//使用共享数据：反序列化为TabularDataResource数组\n", "{\n", " var dataSetJsonText = fromMsSqlQueryShared.RootElement.GetRawText();\n", " var dataTables = System.Text.Json.JsonSerializer.Deserialize>(dataSetJsonText);\n", "\n", " //结果1\n", " dataTables[0].Display();\n", " \n", " //结果2\n", " dataTables[1].Display();\n", "}\n", "\n", "//使用共享数据：直接转换为TabularDataResource数组\n", "{\n", " var resource = fromMsSqlQueryShared.RootElement.ToTabularDataResource().Data.ToList();\n", " resource[0].First(s => s.Key==\"data\").Value.Display();\n", "}" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "polyglot_notebook": { "kernelName": "javascript" } }, "outputs": [ { "data": { "application/json": [ { "Age": 74, "Id": 1, "Name": "吕宇宁" }, { "Age": 3, "Id": 2, "Name": "韦子异" }, { "Age": 27, "Id": 3, "Name": "姚宇宁" }, { "Age": 18, "Id": 4, "Name": "傅子异" }, { "Age": 5, "Id": 5, "Name": "朱子韬" } ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "//js中使用：查询共享数据\n", "#!set --name fromMsSqlQueryShared --value @sql-SqlServerKernelShared:sqlServerKernelQueryShared\n", "console.log(fromMsSqlQueryShared[0].data)" ] }, { "cell_type": "markdown", "id": "2c79015c", "metadata": {}, "source": [ "## 各数据库示例" ] }, { "cell_type": "markdown", "id": "aacbaafa", "metadata": {}, "source": [ "### SQL Server 数据库" ] }, { "cell_type": "code", "execution_count": 14, "id": "180499e7", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/html": [ "
Installed Packages
  • Microsoft.DotNet.Interactive.SqlServer, 1.0.0-beta.25177.1
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#r \"nuget:Microsoft.DotNet.Interactive.SqlServer,*-*\"" ] }, { "cell_type": "code", "execution_count": 15, "id": "09172abb", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/plain": [ "Kernel added: #!sql-SqlServerKernelStudy" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "using Microsoft.DotNet.Interactive;\n", "using Microsoft.DotNet.Interactive.Commands;\n", "\n", "//优化方式\n", "{\n", " //内核名：魔法命令中的内核名，执行后会自动加 sql- 前缀，做为内核名被使用\n", " string magicCommandKernelName = \"SqlServerKernelStudy\";\n", " string completeKernelName = \"sql-\" + magicCommandKernelName;\n", "\n", " //引入内核：可重复执行\n", " if(Microsoft.DotNet.Interactive.Kernel.Root.FindKernelByName(completeKernelName) == null)\n", " {\n", " var connectKernelCode = $\"#!connect mssql --kernel-name {magicCommandKernelName} \\\"{SharedDbConnect.MsSqlConnectionString}\\\"\";\n", " await Kernel.Root.SendAsync(new SubmitCode( connectKernelCode, \"csharp\"));\n", " }\n", " else\n", " {\n", " Console.WriteLine($\"名为 {completeKernelName} 的内核已存在。需要新内核时，请为--kernel-name参数使用不同的值, 本次执行不做任何更改！\");\n", " }\n", "}" ] }, { "cell_type": "code", "execution_count": 16, "id": "3dac305e", "metadata": { "polyglot_notebook": { "kernelName": "sql-SqlServerKernelStudy" } }, "outputs": [ { "data": { "text/plain": [ "命令已成功完成。" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "(5 行受到影响)" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,吕宇宁,74\r\n", "2,韦子异,3\r\n", "3,姚宇宁,27\r\n", "4,傅子异,18\r\n", "5,朱子韬,5\r\n" ], "text/html": [ "
IdNameAge
1
吕宇宁
74
2
韦子异
3
3
姚宇宁
27
4
傅子异
18
5
朱子韬
5
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "--直接执行SQL语句，不共享结果\n", "#!sql-SqlServerKernelStudy\n", "SELECT top 5 * from Student;" ] }, { "cell_type": "code", "execution_count": 17, "id": "cb3528e0", "metadata": { "polyglot_notebook": { "kernelName": "sql-SqlServerKernelStudy" } }, "outputs": [ { "data": { "text/plain": [ "命令已成功完成。" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "(5 行受到影响)" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "(1 行受到影响)" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,吕宇宁,74\r\n", "2,韦子异,3\r\n", "3,姚宇宁,27\r\n", "4,傅子异,18\r\n", "5,朱子韬,5\r\n" ], "text/html": [ "
IdNameAge
1
吕宇宁
74
2
韦子异
3
3
姚宇宁
27
4
傅子异
18
5
朱子韬
5
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Count\r\n", "5\r\n" ], "text/html": [ "
Count
5
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "--共享操作结果(可多个操作语句)\n", "#!sql-SqlServerKernelStudy --name sqlServerQuerySharedDemo\n", "SELECT top 5 Id,Name,Age from Student;\n", "SELECT 5 as Count;" ] }, { "cell_type": "code", "execution_count": 18, "id": "e41307ed", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Id Name Age\n", "1 吕宇宁 74\n", "2 韦子异 3\n", "3 姚宇宁 27\n", "4 傅子异 18\n", "5 朱子韬 5\n" ] }, { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,吕宇宁,74\r\n", "2,韦子异,3\r\n", "3,姚宇宁,27\r\n", "4,傅子异,18\r\n", "5,朱子韬,5\r\n" ], "text/html": [ "
IdNameAge
1
吕宇宁
74
2
韦子异
3
3
姚宇宁
27
4
傅子异
18
5
朱子韬
5
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Count\r\n", "5\r\n" ], "text/html": [ "
Count
5
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
[{"Id":1,"Name":"吕宇宁","Age":74},{"Id":2,"Name":"韦子异","Age":3},{"Id":3,"Name":"姚宇宁","Age":27},{"Id":4,"Name":"傅子异","Age":18},{"Id":5,"Name":"朱子韬","Age":5}]
{"Id":1,"Name":"吕宇宁","Age":74}
Id
1
Name"吕宇宁"
Age
74
{"Id":2,"Name":"韦子异","Age":3}
Id
2
Name"韦子异"
Age
3
{"Id":3,"Name":"姚宇宁","Age":27}
Id
3
Name"姚宇宁"
Age
27
{"Id":4,"Name":"傅子异","Age":18}
Id
4
Name"傅子异"
Age
18
{"Id":5,"Name":"朱子韬","Age":5}
Id
5
Name"朱子韬"
Age
5
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#!set --name fromMsSqlQuery --value @sql-SqlServerKernelStudy:sqlServerQuerySharedDemo\n", "using Microsoft.DotNet.Interactive.Formatting.TabularData;\n", "using Microsoft.DotNet.Interactive.Formatting;\n", "/**\n", " 说明：这里获取到的共享SQL结果，实质是SQL操作结果的Json序列化字符串；\n", " 在C#单元格里，默认被反序列化为了 System.Text.Json.JsonElement,其根元素RootElement，是结果集合(几条SQL语句，结果集就有几项)\n", " .dot interactive程序集中，序列化时，实际是使用 System.Text.Json 序列化的 TabularDataResource 类(在Microsoft.DotNet.Interactive.Formatting.TabularData命名空间)的集合\n", "\n", " 所以C#中使用共享数据，有两种方式：直接操作System.Text.Json.JsonElement对象和反序列化为TabularDataResource数组\n", "*/\n", "//使用共享数据：直接操作JsonElement对象\n", "{\n", " //有几个操作结果，fromMsSqlQuery.RootElement中就有几个结果集\n", " var dataSet = fromMsSqlQuery.RootElement;\n", "\n", " //第一个查询结果集\n", " var dataTable1 = dataSet[0];\n", "\n", " //列结构\n", " var fieldNames = dataTable1.GetProperty(\"schema\").GetProperty(\"fields\").EnumerateArray().Select(s=>s.GetProperty(\"name\"));\n", "\n", " Console.WriteLine(string.Join(\" \",fieldNames));\n", "\n", " //数据\n", " var data = dataTable1.GetProperty(\"data\").EnumerateArray().AsEnumerable();\n", " foreach(var row in data)\n", " {\n", " Console.WriteLine($\"{row.GetProperty(\"Id\")} {row.GetProperty(\"Name\")} {row.GetProperty(\"Age\")}\");\n", " }\n", "\n", " //第2个结果集\n", " var count = fromMsSqlQuery.RootElement[1].GetProperty(\"data\")[0].GetProperty(\"Count\");\n", "\n", " //优化：使用Html展示\n", "}\n", "\n", "//使用共享数据：反序列化为TabularDataResource数组\n", "{\n", " var dataSetJsonText = fromMsSqlQuery.RootElement.GetRawText();\n", " var dataTables = System.Text.Json.JsonSerializer.Deserialize>(dataSetJsonText);\n", "\n", " //结果1\n", " dataTables[0].Display();\n", " \n", " //结果2\n", " dataTables[1].Display();\n", "}\n", "\n", "//使用共享数据：直接转换为TabularDataResource数组\n", "{\n", " var resource = fromMsSqlQuery.RootElement.ToTabularDataResource().Data.ToList();\n", " resource[0].First(s => s.Key==\"data\").Value.Display();\n", "}" ] }, { "cell_type": "code", "execution_count": 19, "id": "eabc34b9", "metadata": { "polyglot_notebook": { "kernelName": "javascript" } }, "outputs": [ { "data": { "application/json": [ { "Age": 74, "Id": 1, "Name": "吕宇宁" }, { "Age": 3, "Id": 2, "Name": "韦子异" }, { "Age": 27, "Id": 3, "Name": "姚宇宁" }, { "Age": 18, "Id": 4, "Name": "傅子异" }, { "Age": 5, "Id": 5, "Name": "朱子韬" } ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#!set --name fromMsSqlQuery --value @sql-SqlServerKernelStudy:sqlServerQuerySharedDemo\n", "console.log(fromMsSqlQuery[0].data)" ] }, { "cell_type": "markdown", "id": "91512503", "metadata": {}, "source": [ "### PostgreSQL 数据库" ] }, { "cell_type": "code", "execution_count": 20, "id": "45adfb6a", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/html": [ "
Installed Packages
  • Microsoft.DotNet.Interactive.PostgreSQL, 1.0.0-beta.25177.1
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Loading extension script from `C:\\Users\\ruyu\\.nuget\\packages\\microsoft.dotnet.interactive.postgresql\\1.0.0-beta.25177.1\\interactive-extensions\\dotnet\\extension.dib`" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Query PostgreSQL databases.\r\n", "

This extension adds support for connecting to PostgreSql databases using the #!connect postgres magic command.

\r\n", "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#r \"nuget:Microsoft.DotNet.Interactive.PostgreSQL,*-*\"" ] }, { "cell_type": "code", "execution_count": 21, "id": "3d0496c9", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/plain": [ "Kernel added: #!sql-PostgreSQLStudy" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "//魔术命令\n", "//#!connect postgres --kernel-name PostgreSQLStudy \"Host=localhost;Port=5432;Username=postgres;Password=替换成真实密码;Database=Study;\"\n", "\n", "using Microsoft.DotNet.Interactive;\n", "using Microsoft.DotNet.Interactive.Commands;\n", "\n", "//优化方法\n", "{\n", " //内核名：魔法命令中的内核名，执行后会自动加 sql- 前缀，做为内核名被使用\n", " string magicCommandKernelName = \"PostgreSQLStudy\";\n", " string completeKernelName = \"sql-\" + magicCommandKernelName;\n", "\n", " //引入内核：可重复执行\n", " if(Microsoft.DotNet.Interactive.Kernel.Root.FindKernelByName(completeKernelName) == null)\n", " {\n", " var connectKernelCode = $\"#!connect postgres --kernel-name {magicCommandKernelName} \\\"{SharedDbConnect.PSQLConnectionString}\\\"\";\n", " await Kernel.Root.SendAsync(new SubmitCode( connectKernelCode, \"csharp\"));\n", " }\n", " else\n", " {\n", " Console.WriteLine($\"名为 {completeKernelName} 的内核已存在。需要新内核时，请为--kernel-name参数使用不同的值, 本次执行不做任何更改！\");\n", " }\n", "}" ] }, { "cell_type": "code", "execution_count": 22, "id": "df085375", "metadata": { "polyglot_notebook": { "kernelName": "sql-PostgreSQLStudy" } }, "outputs": [ { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,邵致远,62\r\n", "2,孙致远,4\r\n" ], "text/html": [ "
IdNameAge
1
邵致远
62
2
孙致远
4
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "count\r\n", "1000\r\n" ], "text/html": [ "
count
1000
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#!sql-PostgreSQLStudy\n", "SELECT * FROM \"Student\" LIMIT 2;\n", "SELECT COUNT(*) as Count FROM \"Student\";" ] }, { "cell_type": "markdown", "id": "ff349c06", "metadata": {}, "source": [ "### SQLite 数据库" ] }, { "cell_type": "code", "execution_count": 23, "id": "b37177b2", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/html": [ "
Installed Packages
  • Microsoft.DotNet.Interactive.SQLite, 1.0.0-beta.25177.1
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Loading extension script from `C:\\Users\\ruyu\\.nuget\\packages\\microsoft.dotnet.interactive.sqlite\\1.0.0-beta.25177.1\\interactive-extensions\\dotnet\\extension.dib`" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Query SQLite databases.\r\n", "

This extension adds support for connecting to SQLite databases using the #!connect sqlite magic command. For more information, run a cell using the #!sql magic command.

\r\n", "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#r \"nuget:Microsoft.DotNet.Interactive.SQLite,*-*\"" ] }, { "cell_type": "code", "execution_count": 24, "id": "57b043ec", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/plain": [ "Kernel added: #!sql-SQLiteSharedKernel" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "\n", "/* 各种连接参数\n", "\n", " 相对位置:当前目录\n", " #!connect sqlite --kernel-name SQLiteSharedKernel --connection-string \"Data Source=.\\assets\\database\\study.db;\"\n", "\n", " 绝对目录位置\n", " #!connect sqlite --kernel-name MySQLiteDemo \"Data Source=C:\\Database\\SQLiteDemo.db;\"\n", " \n", " 缓存共享\n", " #!connect sqlite --kernel-name MySQLiteDemo \"Data Source=.\\assets\\database\\study.db;Cache=Shared;\"\n", "\n", " 使用带密码\n", " #!connect sqlite --kernel-name MySQLiteDemo \"Data Source=.\\assets\\database\\study.db;Cache=Shared;Password=MyEncryptionKey;\"\n", " \n", " 只读模式\n", " #!connect sqlite --kernel-name MySQLiteDemo \"Data Source=.\\assets\\database\\study.db;Mode=ReadOnly\"\n", "\n", " 读写创建模式\n", " #!connect sqlite --kernel-name MySQLiteDemo \"Data Source=.\\assets\\database\\study.db;Mode=ReadWriteCreate\"\n", "\n", " 读写模式\n", " #!connect sqlite --kernel-name MySQLiteDemo \"Data Source=.\\assets\\database\\study.db;Mode=ReadWrite\"\n", "\n", " 私有内存模式\n", " #!connect sqlite --kernel-name MySQLiteDemo \"Data Source=:memory:\"\n", "\n", " 共享内存模式\n", " #!connect sqlite --kernel-name MySQLiteDemo \"Data Source=Sharable;Mode=Memory;Cache=Shared\"\n", "*/\n", "\n", "using Microsoft.DotNet.Interactive;\n", "using Microsoft.DotNet.Interactive.Commands;\n", "//优化方法\n", "{\n", " //内核名：魔法命令中的内核名，执行后会自动加 sql- 前缀，做为内核名被使用\n", " string magicCommandKernelName = \"SQLiteSharedKernel\";\n", " string completeKernelName = \"sql-\" + magicCommandKernelName;\n", "\n", " //引入内核：可重复执行\n", " if(Microsoft.DotNet.Interactive.Kernel.Root.FindKernelByName(completeKernelName) == null)\n", " {\n", " var connectKernelCode = $\"#!connect sqlite --kernel-name {magicCommandKernelName} --connection-string \\\"{SharedDbConnect.SQLiteConnectionString}\\\"\";\n", " await Kernel.Root.SendAsync(new SubmitCode( connectKernelCode, \"csharp\"));\n", " }\n", " else\n", " {\n", " Console.WriteLine($\"名为 {completeKernelName} 的内核已存在。需要新内核时，请为--kernel-name参数使用不同的值, 本次执行不做任何更改！\");\n", " }\n", "}" ] }, { "cell_type": "code", "execution_count": 25, "id": "76d8d37f", "metadata": { "polyglot_notebook": { "kernelName": "sql-SQLiteSharedKernel" } }, "outputs": [ { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,龚致远,70\r\n", "2,潘宇宁,48\r\n" ], "text/html": [ "
IdNameAge
1
龚致远
70
2
潘宇宁
48
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "Count\r\n", "1000\r\n" ], "text/html": [ "
Count
1000
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#!sql-SQLiteSharedKernel\n", "SELECT * FROM Student LIMIT 2;\n", "SELECT COUNT(*) AS Count FROM Student;" ] }, { "cell_type": "markdown", "id": "eccfd5c5", "metadata": {}, "source": [ "### DuckDB 数据库" ] }, { "cell_type": "code", "execution_count": 26, "id": "5ee11d91", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/html": [ "
Installed Packages
  • Microsoft.DotNet.Interactive.DuckDB, 1.0.0-beta.25177.1
" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Loading extension script from `C:\\Users\\ruyu\\.nuget\\packages\\microsoft.dotnet.interactive.duckdb\\1.0.0-beta.25177.1\\interactive-extensions\\dotnet\\extension.dib`" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Query DuckDB databases.\r\n", "

This extension adds support for connecting to DuckDB databases using the #!connect duckdb magic command.

\r\n", "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#r \"nuget:Microsoft.DotNet.Interactive.DuckDB,*-*\"" ] }, { "cell_type": "code", "execution_count": 27, "id": "52a0668c", "metadata": { "polyglot_notebook": { "kernelName": "csharp" } }, "outputs": [ { "data": { "text/plain": [ "Kernel added: #!DuckDBSharedKernel" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/csv": [ "\r\n" ], "text/html": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "//下面这种被注释的方式：不可重复执行\n", "// #!connect duckdb --kernel-name DuckDBSharedKernel --connection-string \"Data Source=:memory:?cache=shared\"\n", "// #!connect duckdb --kernel-name DuckDBSharedKernel --connection-string SharedDbConnect.DuckDBConnectionString\n", "\n", "using Microsoft.DotNet.Interactive;\n", "using Microsoft.DotNet.Interactive.Commands;\n", "//优化方法\n", "{\n", " //内核名：魔法命令中的内核名，执行后会不加 sql- 前缀\n", " string magicCommandKernelName = \"DuckDBSharedKernel\";\n", "\n", " //引入内核：可重复执行\n", " if(Kernel.Root.FindKernelByName(magicCommandKernelName) == null)\n", " {\n", " var connectKernelCode = $\"#!connect duckdb --kernel-name {magicCommandKernelName} --connection-string \\\"{SharedDbConnect.DuckDBConnectionString}\\\"\";\n", " await Kernel.Root.SendAsync(new SubmitCode( connectKernelCode, \"csharp\"));\n", "\n", " //初始化数据表及填充数据\n", " var connectKernelCode2 = \n", " \"\"\"\n", " #!DuckDBSharedKernel\n", " CREATE TABLE Student (\n", " Id INTEGER,\n", " Name TEXT,\n", " Age INTEGER\n", " );\n", " INSERT INTO Student VALUES (1, '张三', 10), (2, '李四', 33), (3, '王五', 66);\n", " \"\"\";\n", " await Kernel.Root.SendAsync(new SubmitCode( connectKernelCode2, \"csharp\"));\n", " }\n", " else\n", " {\n", " Console.WriteLine($\"名为 {magicCommandKernelName} 的内核已存在。需要新内核时，请为--kernel-name参数使用不同的值, 本次执行不做任何更改！\");\n", " }\n", "}" ] }, { "cell_type": "code", "execution_count": 28, "id": "7afddc24", "metadata": { "polyglot_notebook": { "kernelName": "DuckDBSharedKernel" } }, "outputs": [ { "data": { "text/csv": [ "Id,Name,Age\r\n", "1,张三,10\r\n", "2,李四,33\r\n", "3,王五,66\r\n" ], "text/html": [ "
IdNameAge
1
张三
10
2
李四
33
3
王五
66
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#!DuckDBSharedKernel\n", "SELECT * FROM Student ORDER BY Id" ] } ], "metadata": { "kernelspec": { "display_name": ".NET (C#)", "language": "C#", "name": ".net-csharp" }, "language_info": { "name": "polyglot-notebook" }, "polyglot_notebook": { "kernelInfo": { "defaultKernelName": "csharp", "items": [ { "aliases": [], "languageName": "csharp", "name": "csharp" }, { "aliases": [], "languageName": "KQL", "name": "DuckDBSharedKernel" }, { "aliases": [], "languageName": "T-SQL", "name": "sql-mssql-study" }, { "aliases": [], "languageName": "T-SQL", "name": "sql-mssqlDemo" }, { "aliases": [], "languageName": "PostgreSQL", "name": "sql-PostgreSQLStudy" }, { "aliases": [], "languageName": "SQLite", "name": "sql-SQLiteSharedKernel" }, { "aliases": [], "languageName": "T-SQL", "name": "sql-SqlServerKernelDemo" }, { "aliases": [], "languageName": "T-SQL", "name": "sql-SqlServerKernelShared" }, { "aliases": [], "languageName": "T-SQL", "name": "sql-SqlServerKernelStudy" }, { "aliases": [], "languageName": "T-SQL", "name": "sql-SqlServerKernelStuidy" }, { "aliases": [], "languageName": "T-SQL", "name": "sql-SqlServerKernelWithEF" } ] } } }, "nbformat": 4, "nbformat_minor": 5 }