Airquality

AirQuality

We have several air quality monitors. A python script pulls the json data from the devices and storing them in a PostgreSQL database.

We sometimes move them or have connectivity issues with them.

I was curious how difficult it would be to do the same thing using .NET 10 and manage the datasources.

GitLab

Part 1

I created a AqDevice (Air Quality) class that contains the device name and url.

namespace AirQuality.DataSrc;

public class AqDevice
{

    public required string Name {get; set;}
    public required string Uri {get; set;}

}

In the main program, there is an infinite loop that creates an array of the AqDevice instances and requests the data.

namespace AirQuality;

using System.Net.Http;
using AirQuality.DataSrc;

class Program
{

    static public AqDevice[] aqDevices =
    [
        new AqDevice{Name="arduino01", Uri="http://192.168.12.210/json"},
        new AqDevice{Name="21FFA039", Uri="http://192.168.12.199/j"},
        new AqDevice{Name="21313DA1", Uri="http://192.168.12.133/j"},
    ];

    static readonly HttpClient client = new HttpClient();
    static async Task Main(string[] args)
    {

        Console.WriteLine("Air Quality\n");

        while (true)
        {
            foreach (AqDevice ad in aqDevices)
            {
                Task<string> result = GetData(client, ad);
                Console.WriteLine(ad.Name);
                Console.WriteLine(result.Result);
                Console.WriteLine();
            }
        }
    }

    static async Task<string> GetData( HttpClient client, AqDevice ad )
    {
        string responseBody = "";
        try
        {
            using HttpResponseMessage response = await client.GetAsync( ad.Uri );
            response.EnsureSuccessStatusCode();
            responseBody = await response.Content.ReadAsStringAsync();
        }
        catch (HttpRequestException e )
        {
            Console.WriteLine("\nException Caught!");
            Console.WriteLine("Message :{0} ", e.Message);
            responseBody = "ERROR";
        }

        return responseBody;
    }
}

Add Entity Framework

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

We’ll want to leave Program.cs in the root directory, but move the DataSrc.cs file to a Models folder and adjust it.

mkdir Models
mv DataSrc.cs Models/

Now we must create a model for the datapoints.

Here is a sample of the data received from one of the air quality devices.

{
    "data": {
        "id": "21313DA1",
        "type": "21",
        "temperature": 17.71,
        "humidity": 41.00,
        "co2": 410,
        "o3": 44,
        "no2": 11,
        "ch2o": 13,
        "co": 0.5,
        "voc": 0,
        "pm1": 0,
        "pm25": 0,
        "pm10": 0,
        "valid": 0,
        "uptime": 3016610,
        "signal": -66
    }
}

Another type of this provides this data.

{
    "data": {
        "id": "ardi01", 
        "no2": 76 
    }
}

The data sources have an ID field for identifying the device, and they are referenced by the data key. We’ll need to make some adjustments for that in Program.cs.

Also Entity Framework uses camel case which is not preferrable when working in postgres so I rename them in the colum attributes below. Same for the table name.

I use the ? identifier to allow many of the fields to be null.

using System.ComponentModel.DataAnnotations.Schema;

namespace AirQuality.DataPts;

[Table("data_points")]
public class Pt
{
    [Column("id")]
    public long? Id { get; set; }
    [Column("source")]
    public string Source { get; set; } = "";
    [Column("timestamp")]
    public DateTime Timestamp { get; set; }  = DateTime.UtcNow;
    [Column("temperature")]
    public decimal? Temperature {get; set; }
    [Column("humidity")]
    public decimal? Humidity {get; set; }
    [Column("co2")]
    public int? CO2 {get; set; }
    [Column("o3")]
    public int? O3 {get; set; }
    [Column("no2")]
    public int? NO2 {get; set; }
    [Column("ch2o")]
    public int? CH2O {get; set; }
    [Column("co")]
    public decimal? CO {get; set; }
    [Column("voc")]
    public int? VOC {get; set; }
    [Column("pm1")]
    public int? PM1 {get; set; }
    [Column("pm10")]
    public int? PM10 {get; set; }
    [Column("pm25")]
    public int? PM25 {get; set; }
}

I added Id field to AqDevice as a primary key and Index attributes to make sure there are no duplicate entries.

amespace AirQuality.DataSrc;

using AirQuality.DataPts;
using Microsoft.EntityFrameworkCore; 

[Index(nameof(Name), IsUnique=true)]
[Index(nameof(Uri), IsUnique=true)]
public class AqDevice
{
    public int Id {get; set; }
    public required string Name {get; set;}
    public required string Uri {get; set;}
    List<Pt> Data { get; } = new();
}```

Also add a new file for the database context.

```c# {title="DataContext.cs"}
using Microsoft.EntityFrameworkCore;
using AirQuality.DataSrc;
using AirQuality.DataPts;

namespace AirQuality.DataContext;

public class AqContext : DbContext
{
    public DbSet<AqDevice> DataSrc { get; set;}
    public DbSet<Pt> DataPts {get; set;}

    public AqContext()
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql(@"Host=localhost;Username=sammy;Password=sammy;Database=postgres");
}

And finally updated Program.cs…

We need to be able to parse the JSON we get from the AQ devices and import them into the database.

dotnet add package Newtonsoft.Json

namespace AirQuality;

using System.Net.Http;
using AirQuality.DataSrc;
using AirQuality.DataContext;
using AirQuality.DataPts;
using System.Linq;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.ComponentModel.DataAnnotations;

class Program
{
    static AqContext db = (new AqContext());

    static async Task Initiate()
    {

        Dictionary<string,string> devices = new Dictionary<string, string>();
        devices.Add("arduino01", "http://192.168.12.210/json");
        devices.Add("21FFA039", "http://192.168.12.199/j");
        devices.Add("21313DA1", "http://192.168.12.133/j");

        var data_sources = db.DataSrc.ToList(); 

        foreach( var item in devices )
        {
            // Use LINQ expression here to test for uniqueness of the data sources.
            var entries = (from d in db.DataSrc
                where d.Name == item.Key || d.Uri == item.Value
                select d).ToList();

            if( entries.Count == 0 )
            {
                db.Add( new AqDevice{Name=item.Key, Uri=item.Value} );
            }

        }

        await db.SaveChangesAsync();
    }

    Program ()
    {
    }

    static readonly HttpClient client = new HttpClient();

    static async Task Main(string[] args)
    {
        Console.WriteLine("Air Quality\n");
        await Initiate();

        var data_sources = db.DataSrc.ToList(); 

        while (true)
        {
            foreach (AqDevice ad in data_sources)
            {
                Task<string> result = GetData(client, ad);
                Console.WriteLine(ad.Name);

                string? json = result.Result;

                if( json != null )
                {
                    // dereference the JSON data object
                    JObject jobject_data = JObject.Parse( json );
                    JToken?  jtoken_data  = jobject_data["data"];

                    if( jtoken_data != null )
                    {
                        // convert the id field to source so id can be a primary key in psql
                        if( jtoken_data["id"] != null )
                        {
                            jtoken_data["source"] = jtoken_data["id"];
                            jtoken_data["id"] = null;
                        }
                        
                        string final = JsonConvert.SerializeObject( jtoken_data );

                        Console.WriteLine("New data: {0}", final);

                        Pt? deserialized_data = JsonConvert.DeserializeObject<Pt>(final);

                        if( deserialized_data != null ) {
                            db.DataPts.Add( deserialized_data );
                            await db.SaveChangesAsync();
                        }
                    }

                }

            }

            // The devices update their data every 1 minute.
            await Task.Delay( TimeSpan.FromMinutes(1));
        }

    }

    static async Task<string> GetData( HttpClient client, AqDevice ad )
    {
        string responseBody = "";
        try
        {
            using HttpResponseMessage response = await client.GetAsync( ad.Uri );
            response.EnsureSuccessStatusCode();
            responseBody = await response.Content.ReadAsStringAsync();
        }
        catch (HttpRequestException e )
        {
            Console.WriteLine("\nException Caught!");
            Console.WriteLine("Message :{0} ", e.Message);
            responseBody = "ERROR";
        }

        return responseBody;
    }
}

Created migrations.

dotnet ef migrations add InitialCreate
# check
ls Migrations/

Next update the database

dotnet ef database update

If you look at your postgresql database you’ll see the new tables.

postgres=# \dt
               List of relations
 Schema |         Name          | Type  | Owner 
--------+-----------------------+-------+-------
 public | DataPts               | table | sammy
 public | DataSrc               | table | sammy
 public | __EFMigrationsHistory | table | sammy
(5 rows)

After that, run the application and see the data.

dotnet run

postgres=# SELECT * FROM "DataSrc";
 Id |   Name    |            Uri             
----+-----------+----------------------------
  1 | arduino01 | http://192.168.12.210/json
  2 | 21FFA039  | http://192.168.12.199/j
  3 | 21313DA1  | http://192.168.12.133/j
(3 rows)

Adding ASP NET pages

The contents of the wwwroot folder created by the MVC template originate from here:

https://github.com/dotnet/aspnetcore/tree/main/src/Identity/UI/src/assets/V5

The wwwroot folder is stored in the .nuget package repository:

$HOME/.nuget/packages/microsoft.visualstudio.web.codegenerators.mvc/10.0.1/Templates/Identity/wwwroot/

Add Properties folder with launchSettings.json file.

{
  "$schema": "https://json.schemastore.org/launchsettings.json",
  "profiles": {
    "http": {
      "commandName": "Project",
      "dotnetRunMessages": true,
      "launchBrowser": true,
      "applicationUrl": "http://localhost:5285",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    },
    "https": {
      "commandName": "Project",
      "dotnetRunMessages": true,
      "launchBrowser": true,
      "applicationUrl": "https://localhost:7216;http://localhost:5285",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    }
  }
}