Medhat Elmasry: SQLite

Showing posts with label SQLite. Show all posts

Friday, June 20, 2025

MCP server that connects VS Code to SQLite Database

In this tutorial we will configure VS Code to use an MCP Server that connects to a SQLite database. In a similar manner, you can also connect to PostgreSQL, MySQL, and SQL Server. This is a very compelling proposition because it allows developers to use AI to assist in generating code that dynamically interacts with data in a relational database.

Prerequisites

You will need to install the following software to proceed:

Visual Studio Code with the GitHub Copilot Chat extension
Docker Desktop
Latest versions of node.js, npm, and npx

The Database MCP Server

We will be using the MCP Server from the mcp-database-server GitHub Repo. Visit https://github.com/executeautomation/mcp-database-server for more details

Install and configure the SQLite MCP server

In a suitable working directory, clone the repo, then build, and publish the code by executing these commands in a terminal window:

git clone https://github.com/executeautomation/mcp-database-server.git
cd mcp-database-server
npm install
npm run build

We will next install the MCP server globally with:

npm install -g @executeautomation/database-server

We will run an SQLite using a sample Northwind database.

Download the database file from http://medhat.ca/blog/northwind-sqlite.db.zip.
Extract northwind-sqlite.db from the zip file and place it in the current working directory.

To use the MCP server with our SQLite database, run the following terminal window command:

node dist/src/index.js ./northwind-sqlite.db

Keep the above terminal window open and running.

Configuring VS Code

Open VS Code. Click on the settings gear in the bottom-left corner, followed by Settings.

In the search field, enter MCP, then click on "Edit in settings.json".

Under the mcp >> servers section, add the following MCP server settings:

"sqlite": {
  "command": "npx",
  "args": [
    "-y",
    "@executeautomation/database-server",
    "/path/to/your/northwind-sqlite.db"
  ]
},

Click on Start:

Open the GitHub Copilot Chat panel:

In the GitHub Copilot Chat panel, choose any Claude model followed by Agent Mode.

Click on the tools icon in the prompt window.

You will see a list of commands that the MCP server can carry out with SQLite.

We can now start querying the database using natural language. Start with this prompt:


You have access to the Northwind database through an MCP server. What are the tables in the database?

It detects that it can use the list_tables command.

Click on Continue. I got the following output:

Similarly, you can ask another question like:

Display the contents of the suppliers table.

Yet, another question:

What are the products supplied by "Exotic Liquids"?

Conclusion

It is very easy to connect VS Code with a relational database MCP server. In addition, you can similarly connect MCP Servers any client C# application. MCP Servers open a ton of possibilities for AI aided software development.

Tuesday, January 28, 2025

Built-in token authentication with ASP.NET Core 9.0 Minimal WebAPI

In this tutorial I will showcase the built-in WebApi token authentication. To keep it simple, we shall persist our data in the lightweight SQLite database.

Prerequisites

It is assumed that you have installed the following on your computer:

.NET 9.0
Visual Studio Code editor

Getting Started

In a suitable working directory, create a new WebApi application using the following terminal window command:

dotnet new webapi --name WebApiTokenAuth

Change directory with:

cd WebApiTokenAuth

We will need to add some packages. Also in the same terminal window, run the following commands:

dotnet add package Microsoft.AspNetCore.Identity.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Swashbuckle.AspNetCore

The above packages allow us to use SQLite and Entity Framework. The last package (Swashbuckle.AspNetCore) will be used to provide a default Swagger UI.

Make sure you have the dotnet-ef tool. If you do not, you can globally install it with:

dotnet tool install --global dotnet-ef

If you already have the dotnet-ef, update your version to the latest with:

dotnet tool update --global dotnet-ef

Let us first add Swagger UI to our WebAPI application so that we can easily test our API endpoints. Open your source code with Visual Studio Code by executing the following statement from a terminal window inside the root folder of the application:

code .

You can now add the following statement right under app.MapOpenApi():

app.UseSwaggerUI(options => {
options.SwaggerEndpoint("/openapi/v1.json", "My WebAPI");
});

Edit file Properties/launchSettings.json. In both http and https blocks, change the value of launchBrowser to true.

Also, in both http & https blocks, add the following setting:

"launchUrl": "swagger"

Let us run our web app and see what it does. Run the web app with:

dotnet watch

This gets loaded into your default browser:

Click on GET >> Try it out >> Execute. This will produce the following output:

Database context class

Since we will be using Entity Framework to talk to the SQLite database, we will need a database context class. Add a folder named Data, then add to it a class named ApplicationDbContext that derives from IdentityDbContext<IdentityUser> with the following code:

public class ApplicationDbContext: IdentityDbContext<IdentityUser> {
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
{}
}

Add the following connection string to appsettings.json:

"ConnectionStrings": {
"DefaultConnection": "Data Source=webapi-auth.db;"
}

Next, we must register ApplicationDbContext with our app by adding the following code into Program.cs right before 'var app = builder.Build();':

// Configure identity database access via EF Core
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlite(connectionString));

Other required services

Add the following code right after the above code:

// Authorization
builder.Services.AddAuthorization();

// Activate identity APIs. By default, both cookies and proprietary tokens
// are activated. Cookies will be issued based on the 'useCookies' querystring
// parameter in the login endpoint
builder.Services.AddIdentityApiEndpoints<IdentityUser>()
.AddEntityFrameworkStores<ApplicationDbContext>();

We need to add authorization and authentication middleware with the following code right after 'app.UseHttpsRedirection();':

app.UseAuthentication();
app.UseAuthorization();

Let us secure the /weatherforecast endpoint by forcing authentication. Chain the following to the endpoint by adding this code right under '.WithOpenApi()':

.RequireAuthorization();

The full app.MapGet() code will look like this:

app.MapGet("/weatherforecast", () =>
{
var forecast = Enumerable.Range(1, 5).Select(index =>
new WeatherForecast
(
DateOnly.FromDateTime(DateTime.Now.AddDays(index)),
Random.Shared.Next(-20, 55),
summaries[Random.Shared.Next(summaries.Length)]
))
.ToArray();
return forecast;
})
.WithName("GetWeatherForecast")
.WithOpenApi()
.RequireAuthorization();

Adding Identity API endpoints

Add the identity endpoints to your app by calling MapIdentityApi<IdentityUser>(). Add the following code to Program.cs right before 'app.Run();':

app.MapIdentityApi<IdentityUser>();

Migrations

Since our app uses EF Core, we will create a migration in the Data folder and update the database. Run the following terminal commands:

dotnet ef migrations add M1 -o Data/Migrations
dotnet ef database update

You will notice that a SQLite database file is created named webapi-auth.db.

Try it out

Let us test our application to see whether or not we have indeed succeeded in securing our API endpoint. Start your app with:

dotnet watch

You will see a variety of identity related endpoints when the following page gets loaded into your default browser:

Try to hit the /weatherforecast endpoint and see the data. You will encounter a 401 (unauthorized) error:

Let us register a user. Click on the /register endpoint then click on the "Try it out" button. Update the JSON object so it looks like this:

{
"email": "a@a.a",
"password": "P@$$w0rd"
}

Click on the Execute button. You will get a Code 200 response representing success:

Next, let us login with the credentials we created. Click on the /login endpoint, then click on the "Try it out" button.

Choose true for useCookies and update the JSON object so it only has the credentials we had previously created. Thereafter, click on the Execute button. You should get a code 200 response:

Now let's try getting the data using the GET /weatherforecast endpoint. It should be a relief that we can now see the weather forecast information.

Conclusion

With ASP.NET Core 9.0 we need to follow these steps:

restore Swagger UI
configure our WebAPI application with the built in ,NET token authentication capability

Thursday, February 29, 2024

OpenAI Function Calling with Semantic Kernel, C#, & Entity Framework

In this article, we will create a Semantic Kernel plugin that contains four functions that interact with live SQLite data. Entity Framework will be used to access the SQLite database. The end result is to use the powers of the OpenAI natural language models to ask questions and get answers about our custom data.

Source code: https://github.com/medhatelmasry/EfFuncCallSK

Companion Video: https://youtu.be/4sKRwflEyHk

Getting Started

Let’s start by creating an ASP.NET Razor pages web application. Select a suitable working folder on your computer, then enter the following terminal window commands:

dotnet new razor --auth individual -o EfFuncCallSK
cd EfFuncCallSK

Te above creates a Razor Pages app with support for Entity Framework and SQLite.

Add these packages:

dotnet add package CsvHelper
dotnet add package Microsoft.SemanticKernel
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SQLite.Design

The CsvHelper package will help us load a list of products from a CSV file named students.csv and hydrate a list of Student objects. The second package is needed to work with Semantic Kernel. The rest of the packages support Entity Framework and SQLite.

Let’s Code

appsettings.json

Add these to appsettings.json:

"AIService": "OpenAI", /* Azure or OpenAI */
"AzureOpenAiSettings": {
"Endpoint": "https://YOUR_RESOURCE_NAME.openai.azure.com/",
"Model": "gpt-35-turbo",
"ApiKey": "fake-key-fake-key-fake-key-fake-key"
},
"OpenAiSettings": {
"ModelType": "gpt-3.5-turbo",
"ApiKey": "fake-key-fake-key-fake-key-fake-key"
}

The first setting allows you to choose between using OpenAI or Azure OpenAI.

Of course, you need to adjust the endpoint setting with the appropriate value that pertains to the OpenAI and Azure OpenAI services. Also, enter the correct value for the ApiKey.

NOTE: You can use OpenAI or Azure OpenAI, or both.

Data

Create a folder named Models. Inside the Models folder, add the following Student class:

public class Student {
public int StudentId { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public string? School { get; set; }

public override string ToString() {
return $"Student ID: {StudentId}, First Name: {FirstName}, Last Name: {LastName}, School: {School}";
}
}

Developers like having sample data when building data driven applications. Therefore, we will create sample data to ensure that our application behaves as expected. Copy the following data and save it in a text file wwwroot/students.csv:

StudentId,FirstName,LastName,School
1,Tom,Max,Nursing
2,Ann,Fay,Mining
3,Joe,Sun,Nursing
4,Sue,Fox,Computing
5,Ben,Ray,Mining
6,Zoe,Cox,Business
7,Sam,Ray,Mining
8,Dan,Ash,Medicine
9,Pat,Lee,Computing
10,Kim,Day,Nursing
11,Tim,Rex,Computing
12,Rob,Ram,Nursing
13,Jan,Fry,Mining
14,Jim,Tex,Nursing
15,Ben,Kid,Business
16,Mia,Chu,Medicine
17,Ted,Tao,Computing
18,Amy,Day,Nursing
19,Ian,Roy,Nursing
20,Liz,Kit,Nursing
21,Mat,Tan,Medicine
22,Deb,Roy,Medicine
23,Ana,Ray,Mining
24,Lyn,Poe,Computing
25,Amy,Raj,Nursing
26,Kim,Ash,Mining
27,Bec,Kid,Nursing
28,Eva,Fry,Computing
29,Eli,Lap,Business
30,Sam,Yim,Nursing
31,Joe,Hui,Mining
32,Liz,Jin,Nursing
33,Ric,Kuo,Business
34,Pam,Mak,Computing
35,Cat,Yao,Medicine
36,Lou,Zhu,Mining
37,Tom,Dag,Business
38,Stu,Day,Business
39,Tom,Gad,Mining
40,Bob,Bee,Business
41,Jim,Ots,Business
42,Tom,Mag,Business
43,Hal,Doe,Mining
44,Roy,Kim,Mining
45,Vis,Cox,Nursing
46,Kay,Aga,Nursing
47,Reo,Hui,Nursing
48,Bob,Roe,Mining
49,Jay,Eff,Computing
50,Eva,Chu,Business
51,Lex,Rae,Nursing
52,Lin,Dex,Mining
53,Tom,Dag,Business
54,Ben,Shy,Computing
55,Rob,Bos,Nursing
56,Ali,Mac,Business
57,Edi,Gee,Computing
58,Eva,Cao,Mining
59,Jun,Lam,Computing
60,Eli,Tao,Computing
61,Ana,Bay,Computing
62,Gil,Tal,Mining
63,Wes,Dey,Nursing
64,Nea,Tan,Computing
65,Ava,Day,Nursing
66,Rie,Ray,Business
67,Ken,Sim,Nursing

Add the following code inside the ApplicationDbContext class located inside the Data folder:

public DbSet<Student> Students => Set<Student>();

protected override void OnModelCreating(ModelBuilder modelBuilder) {
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Student>().HasData(LoadStudents());
}

// Load students from a csv file named students.csv in the wwwroot folder
public static List<Student> LoadStudents() {
var students = new List<Student>();
using (var reader = new StreamReader(Path.Combine("wwwroot", "students.csv"))) {
using var csv = new CsvReader(reader, CultureInfo.InvariantCulture);
students = csv.GetRecords<Student>().ToList();
}
return students;
}

Let us add a migration and subsequently update the database. Execute the following CLI commands in a terminal window.

dotnet ef migrations add Students -o Data/Migrations
dotnet ef database update

At this point the database and tables are created in a SQLite database named app.db.

Helper Methods

We need a couple of static helper methods to assist us along the way. In the Models folder, add a class named Utils and add to it the following class definition:

public class Utils {
public static string GetConfigValue(string config) {
IConfigurationBuilder builder = new ConfigurationBuilder();
if (System.IO.File.Exists("appsettings.json"))
builder.AddJsonFile("appsettings.json", false, true);
if (System.IO.File.Exists("appsettings.Development.json"))
builder.AddJsonFile("appsettings.Development.json", false, true);
IConfigurationRoot root = builder.Build();
return root[config]!;
}

public static ApplicationDbContext GetDbContext() {
var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
var connStr = Utils.GetConfigValue("ConnectionStrings:DefaultConnection");
optionsBuilder.UseSqlite(connStr);
ApplicationDbContext db = new ApplicationDbContext(optionsBuilder.Options);
return db;
}
}

Method GetConfigValue() will read values in appsettings.json from any static method. The second GetDbContext() method gets an instance of the ApplicationDbContext class, also from any static method.

Plugins

Create a folder named Plugins and add to it the following class file named StudentPlugin.cs with this code:

public class StudentPlugin {
[KernelFunction, Description("Get student details by first name and last name")]
public static string? GetStudentDetails(
[Description("student first name, e.g. Kim")]
string firstName,
[Description("student last name, e.g. Ash")]
string lastName
) {
var db = Utils.GetDbContext();
var studentDetails = db.Students
.Where(s => s.FirstName == firstName && s.LastName == lastName).FirstOrDefault();
if (studentDetails == null)
return null;
return studentDetails.ToString();
}

[KernelFunction, Description("Get students in a school given the school name")]
public static string? GetStudentsBySchool(
[Description("The school name, e.g. Nursing")]
string school
) {
var studentsBySchool = Utils.GetDbContext().Students
.Where(s => s.School == school).ToList();
if (studentsBySchool.Count == 0)
return null;
return JsonSerializer.Serialize(studentsBySchool);
}

[KernelFunction, Description("Get the school with most or least students. Takes boolean argument with true for most and false for least.")]
static public string? GetSchoolWithMostOrLeastStudents(
[Description("isMost is a boolean argument with true for most and false for least. Default is true.")]
bool isMost = true
) {
var students = Utils.GetDbContext().Students.ToList();
IGrouping<string, Student>? schoolGroup = null;
if (isMost)
schoolGroup = students.GroupBy(s => s.School)
.OrderByDescending(g => g.Count()).FirstOrDefault()!;
else
schoolGroup = students.GroupBy(s => s.School)
.OrderBy(g => g.Count()).FirstOrDefault()!;
if (schoolGroup != null)
return $"{schoolGroup.Key} has {schoolGroup.Count()} students";
else
return null;
}

[KernelFunction, Description("Get students grouped by school.")]
static public string? GetStudentsInSchool() {
var students = Utils.GetDbContext().Students.ToList().GroupBy(s => s.School)
.OrderByDescending(g => g.Count());
if (students == null)
return null;
else
return JsonSerializer.Serialize(students);
}
}

In the above code, there are four methods with these purposes:

GetStudentDetails()	Gets student details given first and last names
GetStudentsBySchool()	Gets students in a school given the name of the school
GetSchoolWithMostOrLeastStudents()	Takes a Boolean value isMost – true returns school with most students and false returns school with least students.
GetStudentsInSchool()	Takes no arguments and returns a count of students by school.

The User Interface

We will re-purpose the Index.cshtml and Index.cshtml.cs files so the user can enter a prompt in natural language and receive a response that comes from the OpenAI model working with our semantic kernel plugin.

Index.chtml

Replace the content of Pages/Index.cshtml with:

@page
@model IndexModel
@{
ViewData["Title"] = Model.Service + " Function Calling with Semantic Kernel";
}
<div class="text-center">
<h3 class="display-6">@ViewData["Title"]</h3>
<form method="post">
<input type="text" name="prompt" size="80" required />
<input type="submit" value="Submit" />
</form>
<div style="text-align: left">
<h5>Example prompts:</h5>
<p>Which school does Mat Tan go to?</p>
<p>Which school has the most students?</p>
<p>Which school has the least students?</p>
<p>Get the count of students in each school.</p>
<p>How many students are there in the school of Mining?</p>
<p>What is the ID of Jan Fry and which school does she go to?</p>
<p>Which students belong to the school of Business? Respond only in JSON format.</p>
<p>Which students in the school of Nursing have their first or last name start with the letter 'J'?</p>
</div>
@if (Model.Reply != null)
{
<p class="alert alert-success">@Model.Reply</p>
}
</div>

The above markup displays an HTML form that accepts a prompt from a user. The prompt is then submitted to the server and the response is displayed in a paragraph (<p> tag) with a green background (Bootstrap class alert-success).

Meantime, at the bottom of the page there are some suggested prompts – namely:

Which school does Mat Tan go to?
Which school has the most students?
Which school has the least students?
Get the count of students in each school.
How many students are there in the school of Mining?
What is the ID of Jan Fry and which school does she go to?
Which students belong to the school of Business? Respond only in JSON format.
Which students in the school of Nursing have their first or last name start with the letter 'J'?

Index.chtml.cs

Replace the IndexModel class definition in Pages/Index.cshtml.cs with:

public class IndexModel : PageModel {
private readonly ILogger<IndexModel> _logger;
private readonly IConfiguration _config;

[BindProperty]
public string? Reply { get; set; }

[BindProperty]
public string? Service { get; set; }

public IndexModel(ILogger<IndexModel> logger, IConfiguration config) {
_logger = logger;
_config = config;
Service = _config["AIService"]!;
}
public void OnGet() { }
// action method that receives prompt from the form
public async Task<IActionResult> OnPostAsync(string prompt) {
// call the Azure Function
var response = await CallFunction(prompt);
Reply = response;
return Page();
}

private async Task<string> CallFunction(string question) {
string azEndpoint = _config["AzureOpenAiSettings:Endpoint"]!;
string azApiKey = _config["AzureOpenAiSettings:ApiKey"]!;
string azModel = _config["AzureOpenAiSettings:Model"]!;
string oaiModelType = _config["OpenAiSettings:ModelType"]!;
string oaiApiKey = _config["OpenAiSettings:ApiKey"]!;
string oaiModel = _config["OpenAiSettings:Model"]!;
string oaiOrganization = _config["OpenAiSettings:Organization"]!;
var builder = Kernel.CreateBuilder();
if (Service!.ToLower() == "openai")
builder.Services.AddOpenAIChatCompletion(oaiModelType, oaiApiKey);
else
builder.Services.AddAzureOpenAIChatCompletion(azModel, azEndpoint, azApiKey);
builder.Services.AddLogging(c => c.AddDebug().SetMinimumLevel(LogLevel.Trace));
builder.Plugins.AddFromType<StudentPlugin>();
var kernel = builder.Build();
// Create chat history
ChatHistory history = [];
// Get chat completion service
var chatCompletionService = kernel.GetRequiredService<IChatCompletionService>();
// Get user input
history.AddUserMessage(question);
// Enable auto function calling
OpenAIPromptExecutionSettings openAIPromptExecutionSettings = new() {
ToolCallBehavior = ToolCallBehavior.AutoInvokeKernelFunctions
};
// Get the response from the AI
var result = chatCompletionService.GetStreamingChatMessageContentsAsync(
history,
executionSettings: openAIPromptExecutionSettings,
kernel: kernel);
string fullMessage = "";
await foreach (var content in result) {
fullMessage += content.Content;
}
// Add the message to the chat history
history.AddAssistantMessage(fullMessage);
return fullMessage;
}
}

In the above code, the prompt entered by the user is posted to the OnPostAsync() method. The prompt is then passed to the CallFunction() method, which returns the final response from Azure OpenAI.

The CallFunction() method reads the OpenAI or Azure OpenAI settings from appsettings.json, depending on the AIService key.

A builder object is created from Semantic Kernel. If we are using OpenAI, then the AddOpenAIChatCompletion service is added. Otherwise, the AddAzureOpenAIChatCompletion service is added.

The StudentPlugin is then added to the builder object Plugins collection.

The builder Build() method is then called returning a kernel object. From the kernel object we then get a chatCompletionService object by calling the GetRequiredService() method.

Thereafter:

Add the prompt to the history
Make a call to the chat message service and receive a response
Concatenate response into a single string
Return the concatenated message

Trying the application

In a terminal window, at the root of the Razor Pages web application, enter the following command:

dotnet watch

The following page will display in your default browser:

You can enter any of the suggested prompts to ensure we are getting the proper results. I entered the last prompt and got these results:

Conclusion

We have seen how Semantic Kernel and Function Calling can be used with data coming from a database. In this example we are using SQLite. However, an other database source can be used using the same technique.

Monday, December 11, 2023

Built-in authentication with ASP.NET Core .NET 8.0 Minimal WebAPI

In this tutorial I will showcase the built-in WebApi authentication that was introduced with ASP.NET Core 8.0. To keep it simple, we shall persist our data in the lightweight SQLite database.

Source Code: https://github.com/medhatelmasry/WebApiAuth

Companion Video: https://youtu.be/SSLSNWRQJiA

Prerequisites

It is assumed that you have installed the following on your computer:

.NET 8.0
Visual Studio Code editor

Getting Started

In a suitable working directory, create a new WebApi application using the following terminal window command:

dotnet new webapi -f net8.0 --name WebApiAuth

Change directory with:

cd WebApiAuth

We will need to add some packages. Also in the same terminal window, run the following commands:

dotnet add package Microsoft.AspNetCore.Identity.EntityFrameworkCore -v 8.0.0
dotnet add package Microsoft.EntityFrameworkCore.Sqlite -v 8.0.0
dotnet add package Microsoft.EntityFrameworkCore.Tools -v 8.0.0
dotnet add package Microsoft.EntityFrameworkCore.Design -v 8.0.0

The above packages allow us to use SQLite and Entity Framework.

Make sure you have the dotnet-ef tool. If you do not, you can globally install it with:

dotnet tool install --global dotnet-ef

If you already have the dotnet-ef, update your version to the latest with:

dotnet tool update --global dotnet-ef

Let us run our web app and have a peek at what it does. Run the web app with:

dotnet watch

This gets loaded into your default browser:

Click on GET >> Try it out >> Execute. This will produce the following output:

It is time for us to peek into the code. Stop the server with CTRL C, then start Visual Studio Code with the following terminal command:

code .

Database context class

using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

namespace WebApiAuth.Data;

public class ApplicationDbContext: IdentityDbContext<IdentityUser> {
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
{}
}

Add the following connection string to appsettings.json:

"ConnectionStrings": {
"DefaultConnection": "Data Source=webapi-auth.db;"
}

Next, we must register ApplicationDbContext with our app by adding the following code into Program.cs right before 'var app = builder.Build();':

// Configure identity database access via EF Core
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlite(connectionString));

Other required services

Add the following code right after the above code:

// Authorization
builder.Services.AddAuthorization();

// Activate identity APIs. By default, both cookies and proprietary tokens
// are activated. Cookies will be issued based on the 'useCookies' querystring
// parameter in the login endpoint
builder.Services.AddIdentityApiEndpoints<IdentityUser>()
.AddEntityFrameworkStores<ApplicationDbContext>();

We need to add authorization and authentication middleware with the following code right after 'app.UseHttpsRedirection();':

app.UseAuthentication();
app.UseAuthorization();

Let us secure the /weatherforecast endpoint by forcing authentication. Chain the following to the endpoint by add ing this code right under '.WithOpenApi()':

.RequireAuthorization();

The full app.MapGet() code will look like this:

app.MapGet("/weatherforecast", () =>
{
var forecast = Enumerable.Range(1, 5).Select(index =>
new WeatherForecast
(
DateOnly.FromDateTime(DateTime.Now.AddDays(index)),
Random.Shared.Next(-20, 55),
summaries[Random.Shared.Next(summaries.Length)]
))
.ToArray();
return forecast;
})
.WithName("GetWeatherForecast")
.WithOpenApi()
.RequireAuthorization();

Adding Identity API endpoints

Add the identity endpoints to your app by calling MapIdentityApi<IdentityUser>(). Add the following code to Program.cs right before 'app.Run();':

app.MapIdentityApi<IdentityUser>();

Migrations

Since our app uses EF Core, we will create a migration in the Data folder and update the database. Run the following terminal commands:

dotnet ef migrations add M1 -o Data/Migrations
dotnet ef database update

You will notice that a SQLite database file is created named webapi-auth.db.

Try it out

Let us test our application to see whether or not we have indeed succeeded in securing our API endpoint. Start your app with:

dotnet watch

You will see a variety of identity related endpoints when the following gets loaded into your default browser:

Try to hit the /weatherforecast endpoint and see the data. You will encounter a 401 (unauthorized) error:

Let us register a user. Click on the /register endpoint then click on the "Try it out" button. Update the JSON object so it looks like this:

{
"email": "a@a.a",
"password": "P@$$w0rd"
}

Click on the Execute button. You will get a Code 200 response representing success:

Next, let us login with the credentials we created. Click on the /login endpoint, then click on the "Try it out" button.

Choose true for useCookies and update the JSON object so it only has the credentials we had previously created. Thereafter, click on the Execute button. You should get a code 200 response:

Now let's try getting the data using the GET /weatherforecast endpoint. It should be a relief that we can now see the data.

With ASP.NET Core 8.0 came an easier way for securing WebAPI.

Monday, November 6, 2023

Server-side Blazor 7.0 APP with CRUD Operations and SQLite

In this post, we will build a Server-side Blazor app talks directly to the SQLite database. This is a very realistic option since both blazor and the database server run on the server.

Source Code: https://github.com/medhatelmasry/ServerBlazorEF7

Overview

Blazor is a framework for developing interactive client-side web applications using C# instead of JavaScript.

Architecture

The architecture of the application that we will be building will look like this:

ASP.NET Core hosts the server-side app and sets up SignalR endpoint where clients connect. SignalR is responsible for updating the DOM on the client with any changes. The Blazor application on the server connects directly to the database using Entity Framework Core.

What are we doing in this tutorial?

In this tutorial I will show you how to build a server-side Blazor application that connects directly with SQLite database using Entity Framework Core.

Let's start coding

1) In a terminal window, go to your working directory. Enter the following command to create a Server-Side Blazor application inside a directory called ServerBlazorEF:

dotnet new blazorserver -f net7.0 -o ServerBlazorEF

2) Open the ServerBlazorEF folder in Visual Studio Code.

3) For a Blazor server-side project, the IDE requests that you add assets to build and debug the project. Select Yes.

4) Hit Ctrl F5 (or dotnet watch in a terminal window) to run the application. Your default browser will load a page that looks like this:

Our objective is to extend the above application so that it talks to SQLite using Entity Framework Core. To this end, we will be dealing with a very simple student model. Therefore, add a Student.cs class file in a folder named Models with the following content:

public class Student {
public int StudentId { get; set; }
[Required]
public string? FirstName { get; set; }
[Required]
public string? LastName { get; set; }
[Required]
public string? School { get; set; }
}

Since we will be using SQLite, we will need to add the appropriate packages. Therefore, from within a terminal window at the root of your ServerBlazorEF project, run the following commands that will add the appropriate database related packages:

dotnet add package Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore
dotnet add package Microsoft.AspNetCore.Identity.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design

dotnet add package CsvHelper

We need to add a connection string for the database. Add the following to the appsettings.json file:

"ConnectionStrings": {

"DefaultConnection": "DataSource=college.db;Cache=Shared"

}

We will be using the Entity Framework Code First approach.

Developers prefer having sample data when building data driven applications. Therefore, we will create some sample data to ensure that our application behaves as expected. Copy the following data and save it in a text file named students.csv in the wwwroot folder:

StudentId,FirstName,LastName,School
1,Tom,Max,Nursing
2,Ann,Fay,Mining
3,Joe,Sun,Nursing
4,Sue,Fox,Computing
5,Ben,Ray,Mining
6,Zoe,Cox,Business
7,Sam,Ray,Mining
8,Dan,Ash,Medicine
9,Pat,Lee,Computing
10,Kim,Day,Nursing
11,Tim,Rex,Computing
12,Rob,Ram,Business
13,Jan,Fry,Mining
14,Jim,Tex,Nursing
15,Ben,Kid,Business
16,Mia,Chu,Medicine
17,Ted,Tao,Computing
18,Amy,Day,Business
19,Ian,Roy,Nursing
20,Liz,Kit,Nursing
21,Mat,Tan,Medicine
22,Deb,Roy,Medicine
23,Ana,Ray,Mining
24,Lyn,Poe,Computing
25,Amy,Raj,Nursing
26,Kim,Ash,Mining
27,Bec,Kid,Nursing
28,Eva,Fry,Computing
29,Eli,Lap,Business
30,Sam,Yim,Nursing
31,Joe,Hui,Mining
32,Liz,Jin,Nursing
33,Ric,Kuo,Business
34,Pam,Mak,Computing
35,Cat,Yao,Medicine
36,Lou,Zhu,Mining
37,Tom,Dag,Business
38,Stu,Day,Business
39,Tom,Gad,Mining
40,Bob,Bee,Business
41,Jim,Ots,Business
42,Tom,Mag,Business
43,Hal,Doe,Mining
44,Roy,Kim,Mining
45,Vis,Cox,Nursing
46,Kay,Aga,Nursing
47,Reo,Hui,Nursing
48,Bob,Roe,Mining
49,Jay,Eff,Computing
50,Eva,Chu,Business
51,Lex,Rae,Nursing
52,Lin,Dex,Mining
53,Tom,Dag,Business
54,Ben,Shy,Computing
55,Rob,Bos,Nursing
56,Ali,Mac,Business
57,Ken,Sim,Medicine

The starting point is to create a database context class. Add a C# class file named SchoolDbContext.cs in the Data folder with the following class code:

public class SchoolDbContext : DbContext {

public DbSet<Student> Students => Set<Student>();

public SchoolDbContext(DbContextOptions<SchoolDbContext> options)

: base(options) { }

protected override void OnModelCreating(ModelBuilder modelBuilder) {

base.OnModelCreating(modelBuilder);

modelBuilder.Entity<Student>().HasData(GetStudents());

}

private static IEnumerable<Student> GetStudents() {

string[] p = { Directory.GetCurrentDirectory(), "wwwroot", "students.csv" };

var csvFilePath = Path.Combine(p);

var config = new CsvConfiguration(CultureInfo.InvariantCulture) {

PrepareHeaderForMatch = args => args.Header.ToLower(),

};

var data = new List<Student>().AsEnumerable();

using (var reader = new StreamReader(csvFilePath)) {

using (var csvReader = new CsvReader(reader, config)) {

data = csvReader.GetRecords<Student>().ToList();

}

return data;

}

Notice the above code is adding the contents of the wwwroot/students.csv file as seed data into the database.

In the Program.cs file, just before ‘var app = builder.Build();’, add the following code so that our application can use SQLite:

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

builder.Services.AddDbContext<SchoolDbContext>(

options => options.UseSqlite(connectionString)

);

We are now ready to apply Entity Framework migrations, create the database and seed some sample data. If you have not done so already, you will need to globally install the Entity Framework CLI tool. This tooling is installed globally on your computer by running the following command in a terminal window:

dotnet tool install --global dotnet-ef

Remember to build your entire solution before proceeding. Then, from within a terminal window inside the ServerBlazorEF root directory, run the following command to create migrations:

dotnet ef migrations add M1 -o Data/Migrations

You should get no errors and this results in the creation of a migration file ending with the name ....M1.cs in the Migrations folder which contains commands for inserting sample data.

The next step is to create the SQLite college.db database file. This is done by running the following command from inside a terminal window at the root folder of the application.

dotnet ef database update

If no errors are encountered, you can assume that the database was created and properly seeded with data.

Add a class file named StudentService.cs in the Data folder with following code:

public class StudentService {

private SchoolDbContext _context;

public StudentService(SchoolDbContext context) {

_context = context;

}

public async Task<List<Student>> GetStudentsAsync() {

return await _context.Students.ToListAsync();

}

public async Task<Student?> GetStudentByIdAsync(int id) {

return await _context.Students.FindAsync(id) ?? null;

}

public async Task<Student?> InsertStudentAsync(Student student) {

_context.Students.Add(student);

await _context!.SaveChangesAsync();

return student;

}

public async Task<Student> UpdateStudentAsync(int id, Student s) {

var student = await _context.Students!.FindAsync(id);

if (student == null)

return null!;

student.FirstName = s.FirstName;

student.LastName = s.LastName;

student.School = s.School;

_context.Students.Update(student);

await _context.SaveChangesAsync();

return student!;

}

public async Task<Student> DeleteStudentAsync(int id) {

var student = await _context.Students!.FindAsync(id);

if (student == null)

return null!;

_context.Students.Remove(student);

await _context.SaveChangesAsync();

return student!;

}

private bool StudentExists(int id) {

return _context.Students!.Any(e => e.StudentId == id);

}

The above StudentService class provides all the necessary methods for CRUD operations involving data retrieval, insertion, update and deletion.

We need to configure the StudentService class as a scoped service so that we can use dependency injection. Scoped lifetime services are created once per client request (connection). Add the following statement to the Program.cs just before ‘var app = builder.Build()’:

builder.Services.AddScoped<StudentService>();

Close all the files in your editor. Rename FetchData.razor file in the Pages folder to Students.razor. Replace its contents with the following code:

@page "/students"

@using ServerBlazorEF.Data

@using ServerBlazorEF.Models

@inject StudentService studentService

<h1>Students</h1>

@if (students == null) {

<p><em>Loading...</em></p>

} else {

<thead>

<tr>

<th>First Name</th>

<th>School</th>

</tr>

</thead>

<tbody>

@foreach (var item in students)

{

<tr>

<td>@item.StudentId</td>

<td>@item.FirstName</td>

<td>@item.LastName</td>

<td>@item.School</td>

</tr>

}

</tbody>

</table>

}

@code {

List<Student>? students;

protected override async Task OnInitializedAsync() {

students = await studentService.GetStudentsAsync();

}

Since we will be using the Student class in multiple razor pages, move the @using ServerBlazorEF.Models statement on line 3 in the above code to _Imports.razor.

Let us focus on the @code block. The OnInitAsyns() method is called when the page gets loaded. It makes a call to the student service which loads a list of students from the database. The remaining HTML/Razor code simply displays the data in a table.

Let's modify the menu item on the left navigation of our application. Open Shared/NavMenu.razor in the editor and change the link for “Fetch data” so it looks like this:

<span class="oi oi-list-rich" aria-hidden="true"></span> Get Students

</NavLink>

</div>

You must be eager to test out the server-side Blazor project. Run your app and select the “Get Students” link on the left navigation, this is what the output will look like:

Adding data

Our Blazor app is not complete without add, edit and delete functionality. We shall start with adding data.

Let us re-purpose Counter.razor so that it becomes our page for adding data. Rename Counter.razor to AddStudent.razor.

Replace AddStudent.razor with the following code:

@page "/addstudent"

@using ServerBlazorEF.Models

@inject ServerBlazorEF.Data.StudentService studentService

@inject NavigationManager NavManager

<PageTitle>Add Student</PageTitle>

<h1>Add Student</h1>

<label for="FirstName">First Name:</label>

</div>

</div>

<label for="School">School:</label>

</div>

<button type="submit" class="btn btn-primary">Submit</button>

</EditForm>

@code {

private Student student = new Student();

private async Task HandleValidSubmit() {

await studentService.InsertStudentAsync(student);

NavManager.NavigateTo("/students");

}

Open Shared/NavMenu.razor in the editor and change the link for “Counter” so it looks like this:

<span class="oi oi-list-rich" aria-hidden="true"></span> Add Student

</NavLink>

</div>

Run the Blazor server-side project and select Add Student on the left navigation menu. This is what it should look like:

I entered Bob, Smith and Travel for data and when I clicked on the Submit button I got the following data inserted into the database:

Update & Delete data using PUT & DELETE methods

We want to be able to select a row of data and update or delete it. Add the following additional cells to the table row in Students.razor:

The above would pass the appropriate studentId and mode parameters to another page with route /updel.

Create a text file named UpdateDelete.razor in the Pages folder with the following content:

@page "/updel/{id}/{mode}"
@using ServerBlazorEF.Models

@inject ServerBlazorEF.Data.StudentService studentService

@inject NavigationManager NavManager

<style>

fieldset {

border: 2px solid #000;

padding-left: 20px;

margin-bottom: 20px;

}

</style>

<PageTitle>Update/Delete Student</PageTitle>

@if (student != null && Mode == "edit") // Update

{

<p>Update Student with ID == @Id</p>

<label for="FirstName">First Name:</label>

</div>

</div>

<label for="School">School:</label>

</div>

<button type="submit" class="btn btn-primary">Update</button>

</EditForm>

@code {

private async Task HandleValidSubmit()

{

await studentService.UpdateStudentAsync(student!.StudentId, student);

NavManager.NavigateTo("/students");

}

else if (student != null && Mode == "del")

{ // Delete

// display student details

<legend>Student Information</legend>

<p>Student ID: @Id</p>

<p>First Name: @student.FirstName</p>

<p>Last Name: @student.LastName</p>

<p>School: @student.School</p>

</fieldset>

<p>Delete Student with ID == @Id</p>

<button type="button" class="btn btn-danger" @onclick="HandleDeleteStudent">Delete</button>

@code {

private async Task HandleDeleteStudent()

{

await studentService.DeleteStudentAsync(student!.StudentId);

NavManager.NavigateTo("/students");

}

else

{

<p>Student with ID == @Id not found</p>

}

@code {

[Parameter]

public string? Id { get; set; }

[Parameter]

public string? Mode { get; set; }

private Student? student = new Student();

protected override async Task OnInitializedAsync()

{

int intId = Convert.ToInt32(Id);

student = await studentService.GetStudentByIdAsync(intId);

}

Note how parameters are passed from one page to another.

1) {id}/{mode} are defined in the route

2) In the @code section, the following parameters are defined:

[Parameter]
public string? Id { get; set; }
[Parameter]
public string? Mode { get; set; }

CRUD Experience

Component CSS

In the UpdateDeleter.razor page, notice that we have some CSS in the <style> . . . </style> block. We can move this into a CSS file that only serves the UpdateDelete.razor component.

In the Pages foldr, create a text file name UpdateDelete.razor.css and add to it the following CSS:

fieldset {
border: 2px solid #000;
padding-left: 20px;
margin-bottom: 20px;
}

Meantime, delete the entire <style> . . . . </style> block in UpdateDeleter.razor. The page should behave just like it did before when you view the delete page.

SignalR

Server-side Blazor uses SignalR to keep a copy of the DOM on the server and to only update changes on the client. Open your Chrome browser development settings and look at the blazor line in the Network tab. This gives you an indication that websockets are used to transmit data between clent and server.

Also, look at the negotiate line in the Network tab.

I hope you learned something new in this tutorial and trust that you will build much more sophisticated Blazor apps.