Windows Core IoT – ASP DotNet Core Razor Pages & SQLite & Web Service on a Raspberry PI3

Building a Web Server App using ASP .Net Core using a SQLite Database and a Web Service starts here using this example.

https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/intro?view=aspnetcore-2.1

I gave it a little twist in order to get it to use an SQLite Database and add a Web Service to it.

This means you need to follow page 1 of 8 from this Tutorial to get the full deal. I only did page 1 & 2.

But make the necessary change mentioned below.

Keep in mind that the we will only use the command line options, and no bulk load of software needed to install

Requirements are that you have Dotnet Core installed.

And have the DB using EF Tools installed from NuGet

Creating the  .Net Razor App

1. Create a new blank RAZOR project

Create a new folder in your PC and run the command to create a new RAZOR project

Command : dotnet New Razor

This will create a new blank Razor project

2. Install the DB using EF Tools

Go to Nuget and look for this package

I used the version 2.1.0-rc1-final

Run this command :

dotnet add package Microsoft.EntityFrameworkCore.Tools –version 2.1.0-rc1-final

After installation you can test is using this command :

dotnet ef

3. Add more packages needed

dotnet add package Microsoft.EntityFrameworkCore.Tools.DotNet –version 2.1.0-preview1-final

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design –Version 2.0.0

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Utils –Version 2.0.0

4. Add missing other packages

Correct the project .Json package references, by adding the missing packages from the Nuget Site :

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.6" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.0.2" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Utils" Version="2.1.0-preview2-final" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.1.0-preview1-final" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
    <PackageReference Include="Microsoft.CodeAnalysis.CSharp.Workspaces" Version="2.8.0" />
    <PackageReference Include="SQLitePCLRaw.bundle_green" Version="1.1.10" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="2.0.2" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.4" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
  </ItemGroup>
</Project>

Next run this command :

dotnet add xxxxx form the .NET CLI command.

It downloads and installed the package selected on the NuGet site

Check you .Json Project file again afterward and continue with the rest of the missing packages

When finished run command :

dotnet Restore

5. Add the missing code to generate the DB

(Or generate the code based on an existing DB using Scaffolding)

Example : Create a Models folder. In the Models folder, create a class file named Student.cs

Content see example Microsoft site :

https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/intro?view=aspnetcore-2.1

6. Create the xxxxContext DB context

xxx = School in my case

In the Data folder create SchoolContext.cs

This is where I modified the code to use SQLite instead of a Local DB

optionsBuilder.UseSqlite(@"Datasource=C:\CoreIoT\ASPnetRazorSQLiteWsAPI\Data\School.db");

In Startup.cs add the missing references (Using xxx) add the DB Context reference :

Add the DB Context reference :

services.AddEntityFrameworkSqlite().AddDbContext<SchoolContext>(); // Added

In the Data folder, create a new class file named DbInitializer.cs see MS Example Site

Which gets called by program.cs

This will create the Database Tables, Fields and Records using the Entity Framework Tools.

Opposite, you can create View Pages from an existing Database using Scaffolding with the Entity Framework Tools too…

Use a command like this to generate an EF Model from and existing Database

dotnet ef dbcontext scaffold “Datasource=C:\CoreIoT\ASPSQLite\Nwind.db” Microsoft.EntityFrameworkCore.Sqlite -o Models -c “DatabaseContext” –f

7. Generate the View pages based on the model you created before :

Courses : dotnet aspnet-codegenerator razorpage -m Course -dc SchoolContext -udl -outDir Pages\Courses –referenceScriptLibraries

Instructor : dotnet aspnet-codegenerator razorpage -m Instructor -dc SchoolContext -udl -outDir Pages\Instructors –referenceScriptLibraries

8. Correct the ERRORS, if needed…

When running the command in step 6 you can get ERRORS.

Globally change _context.Student to _context.Students (add an “s” to Student)

Quote from MS Site :

There are 7 occurrences found and updated. This is a bug and the hope to fix this bug in the next release.

=> TEST THE APP to see all errors are gone :

cd to your project folder, next use this command :

Dotnet Run

9. BEFORE publishing add the following string in the program.cs and republish again.

UseURLs(“http://*:5000)

10. Publish for RPI3 – ARM

Run this command : dotnet publish -r win8-arm

11. Copy from Publish to your RPI3

Copy the Files from the PUBLISH folder to your RPI3.

ASPRazor -> C:\CoreIoT\ASPnetRazorSQLiteWsAPI\bin\Debug\netcoreapp2.0\win8-arm\publish\

Next MAKE SURE your FOLDER STRUCTURE on the RPI3, is exactly the same as it was on your PC before Publishing.

Or you run into this error : ‘Unable to open database file’

Next COPY the SQLite Database to your RPI3. Because this is NOT included when publishing using DotNet Core to win8Arm

12. Test the .Net Razor App on your RPI3

Login using PowerShell WSMAN to your RPI and start the app.

Go to the URL (it can take a while before all is loaded ….)

Let access the database ?

Let’s add some data ?

Haleluja, this is fully functional and cross platform !!

Adding a Web Service to the .Net Razor App

Next I will add it to the site to see we can access the data using a Web Service API.

Again instead of using the full blown Visual Studio we will only use the command line options.

Because we don’t want to install an elephant weight of software !

Actually Visual Studio uses all these commands in the background too …

Run this command :

dotnet aspnet-codegenerator controller -api -name StudentAPIController -m Student -dc SchoolContext -udl -outDir Models\API –referenceScriptLibraries -f

see the result

You also have the option to generate XML instead of JSON

Check the result !!

This is how you you select ID 5

ID = 5

It contains GET / POST / PUT / DELETE methods !

Accessing the Web Service using PowerShell

CLS

$URL = "http://localhost:5000/api/StudentAPI"

$APIService = Invoke-RestMethod -Uri $URL

$APIService | select ID, lastName, firstMidName 

After copying to your RPI3 you can change the URL to http://rpi3:5000/api/StudentAPI

And test it …

As you can see all runs fine, no Visual Studio needed ….

So now you have a Web Service running on your RPI3, who could have imagined this … ?

Use Case :

Since you now have an SQLite DB running your can feed it with your RPI3 SENSOR data and read it out using the Web

or even as a Web Service as demonstrated above …

Enjoy !

Windows Core IoT – SQLite on a Raspberry PI3

As shown in the previous post you can run use Dotnet Core to compile apps for Windows IoT on a RPI3

See here how to get started with Dotnet Core

On the net you can find how to use Dotnet Core and SQLite :

https://developersoapbox.com/connecting-to-a-sqlite-database-using-net-core/

But the example uses the Dotnet Core v1.0 Framework and run on Windows.

I will show here how to use the latest Dotnet Core v2.0 Framework and publish it for Windows ARM on RPI3

Run command Dotnet new console –n Sqlite –o C:\DotNetCoreApps\Sqlite

And check the result here :

Copy the program.cs as backup, and copy the program code from the site above into the program.cs file.

Change the namespace to SQLite (see-n SQLite in first command)

Change the sqlite.csproj file content by adding a few lines

Add these line :

<Project Sdk=”Microsoft.NET.Sdk”>

  <PropertyGroup>
     <OutputType>Exe</OutputType>
     <TargetFramework>netcoreapp2.0</TargetFramework>
     <RuntimeIdentifiers>win8-arm;ubuntu.14.04-arm;ubuntu.16.04-arm</RuntimeIdentifiers>
   </PropertyGroup>
   <ItemGroup>
<PackageReference Include=”Microsoft.EntityFrameworkCore.Sqlite” Version=”2.0.2″ />
<PackageReference Include=”SQLitePCLRaw.bundle_green” Version=”1.1.10″ />

  </ItemGroup>
   <ItemGroup>
     <DotNetCliToolReference Include=”Microsoft.EntityFrameworkCore.Tools.DotNet” Version=”2.0.0″ />
   </ItemGroup>
</Project>

Run command : dotnet Restore

As you can see it will use some Nuget packages as included in the sqlite.csproj file

Next run this command : dotnet publish -r win8-arm

And copy the files to your RPI3

Next open PowerShell do a remote session and run the .\SQLite.exe

When the SQLite DB doesn’t exist yet, it will be created automatically.

DotNet Core is really a cross-platform language !

It runs flawlessly on a RPI3 – ARM Architecture, by just re-using the same code base we use on a Windows machine

See here for more info on .NET Core Data Access data sources :

https://blogs.msdn.microsoft.com/dotnet/2016/11/09/net-core-data-access/

Enjoy !