.NET Interactive with SQL!| .NET Notebooks in Visual Studio Code

In our last post , we announced  that you can create .NET Notebooks in Visual Code. Today we are announcing that .NET Interactive now ships with T-SQL support in addition to C#, F#, PowerShell, JavaScript, and HTML.

The .NET Interactive team has collaborated with the Azure Data Studio team to bring SQL integration to .NET Notebooks. SQL notebooks combine the benefits of querying data with the rich visualization of notebooks.

Getting Started

First, you’ll need a Microsoft SQL Server or Azure SQL database. If you already have a SQL database, you can use that. If not, you can follow along with the examples below by installing the following:

• SQL Server Developer edition 2019
• A database – In this blog post, I will be using the Adventure Works 2019 lightweight edition. You’ll also need:
• Visual Studio Code
• The .NET Interactive extension

Creating a new SQL Connection in VS Code

Once you have the requirements listed above installed, you are ready to start using SQL in .NET Interactive Notebooks in VS Code.

To create a new notebook, open the Command Palette(Ctrl+Shift+P on Windows or Cmd-Shift-P on macOS), and select .NET Interactive: Create new blank notebook. You can also create a new notebook with Ctrl+Shift+Alt+N key combination.

Every notebook has a default language. In our case, it’s C# (.NET Interactive), and we will need to switch the language to SQL (.NET Interactive). To change the language in a cell, you can either use a magic command in Jupyter (e.g. #!sql) or the VS Code notebook language selector. The language selector is in the lower right corner of the cell. Click on it and pick `SQL (.NET Interactive) from the language list.

Test the SQL cell out by writing a simple SQL select statement; SELECT * FROM table_name and run the cell.

Once the cell execution is complete, a list of instructions appears on connecting to a SQL database.

Connecting to a SQL database

The SQL language support in .NET Interactive is added by installing the Microsoft.DotNet.Interactive.SqlServer package. We can do this by adding a new C# (.NET Interactive) cell and running(Click on +Code to add a new cell):

`#r "nuget:Microsoft.DotNet.Interactive.SqlServer,*-*""`

To learn more about connecting to a Microsoft SQL Server Database, execute the help command in a new cell.

`#!connect mssql -h`

Now that we have all the prerequisites listed above, we can connect to database. To establish a connection, you will need a --kernel-name and the connection string to the database.

In a new C# (.NET Interactive) cell, establish a connection and label it adventureworks and add the connection string. Once this cell is executed the #!sql-adventureworks sub- kernel is available for use.

We have our database connected so, let’s create a new SQL(.NET Interactive) cell and write a select query that would grab all the Adventure Works database data. Enter this SELECT * FROM AdventureWorksLT2019 and execute the cell.

After the cell runs, you will notice an info message in the available connections in the output. In this case, we only have the #!sql-adventureworks sub-kernel, but you can connect have multiple databases in a single notebook.

Querying and Visualizing Data

In a new SQL (.NET Interactive) cell, you can now specify your connection using #!sql-adventureworks magic command followed by a query against the AdventureWorks database. You will notice in the image below that since we have specified the SQL sub-kernel, we get code completion on the database tables and columns!

Once you have completed writing your SQL query,run the cell. The results of the query are displayed using the nteract Data Explorer,providing a rich interactive filter and visualization experience where the user can explore their data in a number of ways.

• Filter and sort by column – Click on Show Filters
• Using the toolbar, select a preferred visualization:

Entity Frameworks Core in a SQL Notebook

EF Core is the data access API of choice for .NET developers. Bringing EF Core and SQL together in .NET Interactive Notebooks provides users the quick iteration of notebooks while providing strongly typed APIs, code completion over tables and columns, and Language-Integrated-Query (LINQ). Quickly explore your data’s shape and schema, craft queries with filtering, sorting, aggregations, and projections that seamlessly translate to SQL, and return results for analysis and visualization.

As an example, I am going to demo how you can start leveraging EF Core and quickly. I will demonstrate how you can use --create-dbcontext option to scaffold a DbContext that you can access in the C# cell.

Start by adding a new C#(.NET Interactive) cell and establish a new connection to your database label it adventureworksEF. In addition to giving a new --kernel-name, you will need the include the --create-dbcontext option.

Your new connection should look like the code snippet below; once you are done, run the cell.

#!connect mssql --create-dbcontext --kernel-name adventureworksEF <connectionString>

The cell above does the following:

• Scaffolds a DbContext and initializes an instance of it called adventureworksEF in the C# kernel.
• Installs the Microsoft.EntityFrameworkCore.Design version package.
• Adds a new sub-kernel #!sql-kernel-name.

In a new C# (.NET Interactive) cell, you can now start exploring and querying your data using the adventureworksEF DbContext. You will notice in the image that we continue to get code completion over tables and columns and get visualizations of our data. The visualizations are generated through dataFrame.Explore() from the nteract data explorer.

Now, since we know the shape of our data, I can write a LINQ  to query all the data in my table of a particular order number. For example

var qty = adventureworksEF.SalesOrderDetail.Where(q => q.OrderQty.Equals(4)).ToList(); qty.Explore();

And there you have it! A simple demonstration on how you can leverage .NET Interactive notebooks with SQL and EF Core.

This brings support for T-SQL syntax highlighting, IntelliSense, and results grid support in a multi-language notebook.

Resources

• Documentation
• Samples
• Source code
• .NET Interactive Notebook Extension
• Azure Data Studio
• Introduction to LINQ

Happy interactive programming!

The post .NET Interactive with SQL!| .NET Notebooks in Visual Studio Code appeared first on .NET Blog.

source https://devblogs.microsoft.com/dotnet/net-interactive-with-sql-net-notebooks-in-visual-studio-code/