Page 2: Connecting to a Microsoft SQL Server database

The sample data in SQL is provided by Microsoft's AdventureWorks training database, which can be provided both on your own SQL server and directly as an Azure SQL Database. Microsoft provides instructions.

Please note that Integrated Security=false was set in the connection string used above, as the example uses SQL Server Authentication. If Windows Authentication is used, this must be adapted.

To use SQL, the Microsoft.DotNet.Interactive.SqlServer library must first be installed. As it is an external package, it must be referenced using the #r syntax (see also Referencing external assemblies):

// magic commands for SQL Server connection
#r "nuget: Microsoft.DotNet.Interactive.SqlServer, *-*"

Only then can the SQL connection string be used to create a Polyglot SQL subkernel. This is done with the magic command #!connect. To be able to use the C# variable connectionString, the name of the subkernel must be specified (here @csharp, see next listing). The topic of variable sharing is covered in more detail in the next sections.

The next step is to create an SQL subkernel with the SQL connection string in a C# cell:

#!connect mssql --kernel-name DemoKernel @csharp:connectionString

Users of the notebook can now either use the default productId or have it queried. This is how the standard productId is defined in an SQL cell:

-- use default productId
#!set --name productId --value "988"
Sie lässt sich wie folgt abfragen:
-- user defined productId
#!set --name productId --value @input:productId

Next, the SQL query must be created. It joins the two tables SalesOrderHeader and SalesOrderDetail to obtain the customer number (CustomderId) and number of products purchased per order (OrderQty). By saving the result in a variable, it can be used outside the query. In this example (Listing 3), it is the variable QueryResult.

#!sql-DemoKernel --name QueryResult
SELECT CustomerID, SalesOrderHeader.SalesOrderID, ProductID, OrderQty
  FROM SalesLT.SalesOrderHeader JOIN SalesLT.SalesOrderDetail
          ON SalesLT.SalesOrderDetail.SalesOrderID = SalesLT.SalesOrderHeader.SalesOrderID
WHERE ProductID = @productId
ORDER BY OrderQty desc

Listing 3: Calling the SQL query from Polyglot in an SQL cell

Immediately after executing the SQL cell, the response appears in the Notebook (see Figure 4).

Exchanging variables between languages

After executing the SQL query, the output should be used to generate a graphic. First, however, the variable from the SQL subkernel must be split into the C# subkernel. Polyglot Notebooks uses the concept of variable sharing for this purpose. This allows variables to be exchanged between almost all supported languages and technologies, except Mermaid and HTML (see Table 1).

Table 1: Currently supported languages with a note on whether variable sharing is possible

(Source: Microsoft)

In this example, it was necessary to save the output of the SQL query in a variable QueryResult (see first line in Listing 3). Other languages work natively with variables. These are usually already in the working memory and can be shared without an additional command. Which variables can be found in which subkernel can be looked up in the previously mentioned variable view (see Figure 2).

In both cases, the next step is to create a new cell in a different language. The cell starts with the magic command #!share, which receives the information as --from parameter, from which subkernel which variable is to be shared into the current subkernel. This example is intended to use both the query output (QueryResult) and the product ID (productId) in the C# subkernel. After executing the following cell, both values are available as C# variables. The source subkernel and the variable name are passed as parameters:

#!share --from sql-DemoKernel QueryResult
#!share --from sql-DemoKernel productId