StudyGroup
/
PolyglotNotebooksStudy
2
0
Fork 0
Code Issues Pull Requests Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
PolyglotNotebooksStudy/Docs/多语言笔记.2.1.操作数据库-SQL内核.ipynb

2387 lines
81 KiB
Plaintext
Raw Permalink Blame History Unescape Escape

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

{
"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": [
"<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Microsoft.Identity.Client, 4.66.2</span></li></ul></div></div>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Microsoft.Extensions.Configuration, 9.0.4</span></li><li><span>Microsoft.Extensions.Configuration.Binder, 9.0.4</span></li><li><span>Microsoft.Extensions.Configuration.CommandLine, 9.0.4</span></li><li><span>Microsoft.Extensions.Configuration.EnvironmentVariables, 9.0.4</span></li><li><span>Microsoft.Extensions.Configuration.ini, 9.0.4</span></li><li><span>Microsoft.Extensions.Configuration.json, 9.0.4</span></li><li><span>Microsoft.Extensions.Configuration.KeyPerFile, 9.0.4</span></li><li><span>Microsoft.Extensions.Configuration.UserSecrets, 9.0.4</span></li><li><span>Microsoft.Extensions.Configuration.xml, 9.0.4</span></li><li><span>Microsoft.Extensions.DependencyInjection, 9.0.4</span></li><li><span>Microsoft.Extensions.Logging, 9.0.4</span></li><li><span>Microsoft.Extensions.Logging.Configuration, 9.0.4</span></li><li><span>Microsoft.Extensions.Logging.Console, 9.0.4</span></li><li><span>Microsoft.Extensions.Logging.Debug, 9.0.4</span></li><li><span>Microsoft.Extensions.Logging.EventSource, 9.0.4</span></li><li><span>Microsoft.Extensions.Options, 9.0.4</span></li><li><span>Microsoft.Extensions.Options.ConfigurationExtensions, 9.0.4</span></li><li><span>Microsoft.Extensions.Options.DataAnnotations, 9.0.4</span></li></ul></div></div>"
]
},
"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": [
"<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Microsoft.DotNet.Interactive.SqlServer, 1.0.0-beta.25177.1</span></li></ul></div></div>"
]
},
"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": [
"<details><summary>Query Microsoft SQL Server databases.</summary>\r\n",
"<p>This extension adds support for connecting to Microsoft SQL Server databases using the <code>#!connect mssql</code> magic command. For more information, run a cell using the <code>#!sql</code> magic command.</p>\r\n",
"</details>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>吕宇宁</td><td><div class=\"dni-plaintext\"><pre>74</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>韦子异</td><td><div class=\"dni-plaintext\"><pre>3</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>3</pre></div></td><td>姚宇宁</td><td><div class=\"dni-plaintext\"><pre>27</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>4</pre></div></td><td>傅子异</td><td><div class=\"dni-plaintext\"><pre>18</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td><td>朱子韬</td><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>6</pre></div></td><td>林杰宏</td><td><div class=\"dni-plaintext\"><pre>73</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>7</pre></div></td><td>胡璐</td><td><div class=\"dni-plaintext\"><pre>60</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>8</pre></div></td><td>周璐</td><td><div class=\"dni-plaintext\"><pre>23</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>9</pre></div></td><td>田秀英</td><td><div class=\"dni-plaintext\"><pre>6</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>10</pre></div></td><td>姜詩涵</td><td><div class=\"dni-plaintext\"><pre>82</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>吕宇宁</td><td><div class=\"dni-plaintext\"><pre>74</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>韦子异</td><td><div class=\"dni-plaintext\"><pre>3</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>3</pre></div></td><td>姚宇宁</td><td><div class=\"dni-plaintext\"><pre>27</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>4</pre></div></td><td>傅子异</td><td><div class=\"dni-plaintext\"><pre>18</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td><td>朱子韬</td><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>6</pre></div></td><td>林杰宏</td><td><div class=\"dni-plaintext\"><pre>73</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>7</pre></div></td><td>胡璐</td><td><div class=\"dni-plaintext\"><pre>60</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>8</pre></div></td><td>周璐</td><td><div class=\"dni-plaintext\"><pre>23</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>9</pre></div></td><td>田秀英</td><td><div class=\"dni-plaintext\"><pre>6</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>10</pre></div></td><td>姜詩涵</td><td><div class=\"dni-plaintext\"><pre>82</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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": [
"<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Humanizer, 2.14.1</span></li><li><span>humanizer.core, 2.14.1</span></li><li><span>microsoft.data.sqlclient, 5.2.2</span></li><li><span>Microsoft.EntityFrameworkCore.Design, 9.0.0</span></li><li><span>microsoft.entityframeworkcore.sqlserver, 9.0.0</span></li><li><span>Microsoft.Identity.Client, 4.66.2</span></li></ul></div></div>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>吕宇宁</td><td><div class=\"dni-plaintext\"><pre>74</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>韦子异</td><td><div class=\"dni-plaintext\"><pre>3</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>3</pre></div></td><td>姚宇宁</td><td><div class=\"dni-plaintext\"><pre>27</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>4</pre></div></td><td>傅子异</td><td><div class=\"dni-plaintext\"><pre>18</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td><td>朱子韬</td><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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": [
"<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Microsoft.DotNet.Interactive.SqlServer, 1.0.0-beta.25177.1</span></li></ul></div></div>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>吕宇宁</td><td><div class=\"dni-plaintext\"><pre>74</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>韦子异</td><td><div class=\"dni-plaintext\"><pre>3</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>3</pre></div></td><td>姚宇宁</td><td><div class=\"dni-plaintext\"><pre>27</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>4</pre></div></td><td>傅子异</td><td><div class=\"dni-plaintext\"><pre>18</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td><td>朱子韬</td><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/csv": [
"Count\r\n",
"5\r\n"
],
"text/html": [
"<table><thead><tr><td><span>Count</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>吕宇宁</td><td><div class=\"dni-plaintext\"><pre>74</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>韦子异</td><td><div class=\"dni-plaintext\"><pre>3</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>3</pre></div></td><td>姚宇宁</td><td><div class=\"dni-plaintext\"><pre>27</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>4</pre></div></td><td>傅子异</td><td><div class=\"dni-plaintext\"><pre>18</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td><td>朱子韬</td><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/csv": [
"Count\r\n",
"5\r\n"
],
"text/html": [
"<table><thead><tr><td><span>Count</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>[{&quot;Id&quot;:1,&quot;Name&quot;:&quot;吕宇宁&quot;,&quot;Age&quot;:74},{&quot;Id&quot;:2,&quot;Name&quot;:&quot;韦子异&quot;,&quot;Age&quot;:3},{&quot;Id&quot;:3,&quot;Name&quot;:&quot;姚宇宁&quot;,&quot;Age&quot;:27},{&quot;Id&quot;:4,&quot;Name&quot;:&quot;傅子异&quot;,&quot;Age&quot;:18},{&quot;Id&quot;:5,&quot;Name&quot;:&quot;朱子韬&quot;,&quot;Age&quot;:5}]</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:1,&quot;Name&quot;:&quot;吕宇宁&quot;,&quot;Age&quot;:74}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>1</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;吕宇宁&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>74</pre></div></span></td></tr></tbody></table></div></details></td></tr><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:2,&quot;Name&quot;:&quot;韦子异&quot;,&quot;Age&quot;:3}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>2</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;韦子异&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>3</pre></div></span></td></tr></tbody></table></div></details></td></tr><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:3,&quot;Name&quot;:&quot;姚宇宁&quot;,&quot;Age&quot;:27}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>3</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;姚宇宁&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>27</pre></div></span></td></tr></tbody></table></div></details></td></tr><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:4,&quot;Name&quot;:&quot;傅子异&quot;,&quot;Age&quot;:18}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>4</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;傅子异&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>18</pre></div></span></td></tr></tbody></table></div></details></td></tr><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:5,&quot;Name&quot;:&quot;朱子韬&quot;,&quot;Age&quot;:5}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>5</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;朱子韬&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>5</pre></div></span></td></tr></tbody></table></div></details></td></tr></tbody></table></div></details><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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<List<TabularDataResource>>(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": [
"<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Microsoft.DotNet.Interactive.SqlServer, 1.0.0-beta.25177.1</span></li></ul></div></div>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>吕宇宁</td><td><div class=\"dni-plaintext\"><pre>74</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>韦子异</td><td><div class=\"dni-plaintext\"><pre>3</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>3</pre></div></td><td>姚宇宁</td><td><div class=\"dni-plaintext\"><pre>27</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>4</pre></div></td><td>傅子异</td><td><div class=\"dni-plaintext\"><pre>18</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td><td>朱子韬</td><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>吕宇宁</td><td><div class=\"dni-plaintext\"><pre>74</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>韦子异</td><td><div class=\"dni-plaintext\"><pre>3</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>3</pre></div></td><td>姚宇宁</td><td><div class=\"dni-plaintext\"><pre>27</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>4</pre></div></td><td>傅子异</td><td><div class=\"dni-plaintext\"><pre>18</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td><td>朱子韬</td><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/csv": [
"Count\r\n",
"5\r\n"
],
"text/html": [
"<table><thead><tr><td><span>Count</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>吕宇宁</td><td><div class=\"dni-plaintext\"><pre>74</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>韦子异</td><td><div class=\"dni-plaintext\"><pre>3</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>3</pre></div></td><td>姚宇宁</td><td><div class=\"dni-plaintext\"><pre>27</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>4</pre></div></td><td>傅子异</td><td><div class=\"dni-plaintext\"><pre>18</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td><td>朱子韬</td><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/csv": [
"Count\r\n",
"5\r\n"
],
"text/html": [
"<table><thead><tr><td><span>Count</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>5</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>[{&quot;Id&quot;:1,&quot;Name&quot;:&quot;吕宇宁&quot;,&quot;Age&quot;:74},{&quot;Id&quot;:2,&quot;Name&quot;:&quot;韦子异&quot;,&quot;Age&quot;:3},{&quot;Id&quot;:3,&quot;Name&quot;:&quot;姚宇宁&quot;,&quot;Age&quot;:27},{&quot;Id&quot;:4,&quot;Name&quot;:&quot;傅子异&quot;,&quot;Age&quot;:18},{&quot;Id&quot;:5,&quot;Name&quot;:&quot;朱子韬&quot;,&quot;Age&quot;:5}]</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:1,&quot;Name&quot;:&quot;吕宇宁&quot;,&quot;Age&quot;:74}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>1</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;吕宇宁&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>74</pre></div></span></td></tr></tbody></table></div></details></td></tr><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:2,&quot;Name&quot;:&quot;韦子异&quot;,&quot;Age&quot;:3}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>2</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;韦子异&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>3</pre></div></span></td></tr></tbody></table></div></details></td></tr><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:3,&quot;Name&quot;:&quot;姚宇宁&quot;,&quot;Age&quot;:27}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>3</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;姚宇宁&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>27</pre></div></span></td></tr></tbody></table></div></details></td></tr><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:4,&quot;Name&quot;:&quot;傅子异&quot;,&quot;Age&quot;:18}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>4</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;傅子异&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>18</pre></div></span></td></tr></tbody></table></div></details></td></tr><tr><td><details class=\"dni-treeview\"><summary><span class=\"dni-code-hint\"><code>{&quot;Id&quot;:5,&quot;Name&quot;:&quot;朱子韬&quot;,&quot;Age&quot;:5}</code></span></summary><div><table><thead><tr></tr></thead><tbody><tr><td>Id</td><td><span><div class=\"dni-plaintext\"><pre>5</pre></div></span></td></tr><tr><td>Name</td><td><span>&quot;朱子韬&quot;</span></td></tr><tr><td>Age</td><td><span><div class=\"dni-plaintext\"><pre>5</pre></div></span></td></tr></tbody></table></div></details></td></tr></tbody></table></div></details><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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<List<TabularDataResource>>(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": [
"<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Microsoft.DotNet.Interactive.PostgreSQL, 1.0.0-beta.25177.1</span></li></ul></div></div>"
]
},
"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": [
"<details><summary>Query PostgreSQL databases.</summary>\r\n",
" <p>This extension adds support for connecting to PostgreSql databases using the <code>#!connect postgres</code> magic command.</p>\r\n",
" </details>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>邵致远</td><td><div class=\"dni-plaintext\"><pre>62</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>孙致远</td><td><div class=\"dni-plaintext\"><pre>4</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/csv": [
"count\r\n",
"1000\r\n"
],
"text/html": [
"<table><thead><tr><td><span>count</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1000</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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": [
"<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Microsoft.DotNet.Interactive.SQLite, 1.0.0-beta.25177.1</span></li></ul></div></div>"
]
},
"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": [
"<details><summary>Query SQLite databases.</summary>\r\n",
" <p>This extension adds support for connecting to SQLite databases using the <code>#!connect sqlite</code> magic command. For more information, run a cell using the <code>#!sql</code> magic command.</p>\r\n",
" </details>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>龚致远</td><td><div class=\"dni-plaintext\"><pre>70</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>潘宇宁</td><td><div class=\"dni-plaintext\"><pre>48</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/csv": [
"Count\r\n",
"1000\r\n"
],
"text/html": [
"<table><thead><tr><td><span>Count</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1000</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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": [
"<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Microsoft.DotNet.Interactive.DuckDB, 1.0.0-beta.25177.1</span></li></ul></div></div>"
]
},
"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": [
"<details><summary>Query DuckDB databases.</summary>\r\n",
" <p>This extension adds support for connecting to <a href=\"https://duckdb.org/\">DuckDB</a> databases using the <code>#!connect duckdb</code> magic command.</p>\r\n",
" </details>"
]
},
"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": [
"<table><thead><tr></tr></thead><tbody></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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": [
"<table><thead><tr><td><span>Id</span></td><td><span>Name</span></td><td><span>Age</span></td></tr></thead><tbody><tr><td><div class=\"dni-plaintext\"><pre>1</pre></div></td><td>张三</td><td><div class=\"dni-plaintext\"><pre>10</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>2</pre></div></td><td>李四</td><td><div class=\"dni-plaintext\"><pre>33</pre></div></td></tr><tr><td><div class=\"dni-plaintext\"><pre>3</pre></div></td><td>王五</td><td><div class=\"dni-plaintext\"><pre>66</pre></div></td></tr></tbody></table><style>\r\n",
".dni-code-hint {\r\n",
" font-style: italic;\r\n",
" overflow: hidden;\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview {\r\n",
" white-space: nowrap;\r\n",
"}\r\n",
".dni-treeview td {\r\n",
" vertical-align: top;\r\n",
" text-align: start;\r\n",
"}\r\n",
"details.dni-treeview {\r\n",
" padding-left: 1em;\r\n",
"}\r\n",
"table td {\r\n",
" text-align: start;\r\n",
"}\r\n",
"table tr { \r\n",
" vertical-align: top; \r\n",
" margin: 0em 0px;\r\n",
"}\r\n",
"table tr td pre \r\n",
"{ \r\n",
" vertical-align: top !important; \r\n",
" margin: 0em 0px !important;\r\n",
"} \r\n",
"table th {\r\n",
" text-align: start;\r\n",
"}\r\n",
"</style>"
]
},
"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
}
Reference in New Issue View Git Blame Copy Permalink