View All Posts
read
Want to keep up to date with the latest posts and videos? Subscribe to the newsletter
HELP SUPPORT MY WORK: If you're feeling flush then please stop by Patreon Or you can make a one off donation via ko-fi
#CROSS-PLATFORM #DATABASE INTEGRATION #DOTNET COMMAND LINE #MAC #MICROSOFT #NET CORE #POSTGRESQL #WEB API

Microsoft have slowly been making progress with their cross platform efforts and .NET Core is starting to look like it might be interesting. In this post we look at integrating a .NET Core Web API with PostgreSQL running a mac (this code should also work on Linux).

You can download the source code for this post from here (GitHub)

First check that you have the dotnet command line installed.

dotnet --version

If you don’t have it yet then head off to here to download and install it.

Once you are all set up we can create a new project. You can see the types of projects you can create by running:

dotnet new --help

Templates                 Short Name      Language      Tags
----------------------------------------------------------------------
Console Application       console         [C#], F#      Common/Console
Class library             classlib        [C#], F#      Common/Library
Unit Test Project         mstest          [C#], F#      Test/MSTest
xUnit Test Project        xunit           [C#], F#      Test/xUnit
ASP.NET Core Empty        web             [C#]          Web/Empty
ASP.NET Core Web App      mvc             [C#], F#      Web/MVC
ASP.NET Core Web API      webapi          [C#]          Web/WebAPI
Solution File             sln                           Solution

Let’s create a new Web API project:

dotnet new webapi -n MyFirstWebAPI
cd MyFirstWebAPI/
dotnet restore

This will create a new application MyFirstWebAPI, we then cd into the new project and restore all the packages the application requires.

We can the current set of packages by looking in MyFirstWebAPI.csproj

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp1.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <Folder Include="wwwroot\" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore" Version="1.1.2" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.3" />
    <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.2" />
  </ItemGroup>

</Project>

If we look in the Controllers folder we can see that they’ve given us a default api controller called ValuesController.

Lets run up the application and try it out.

dotnet run

Hosting environment: Production
Content root path: /Users/chrisgreening/Work/dotnetcore/MyFirstWebAPI
Now listening on: http://localhost:5000
Application started. Press Ctrl+C to shut down.

Let’s see if we can retrieve the list of values from ValuesController (I’m using jq to pretty print my JSON - you can install this using brew install jq)

curl localhost:5000/api/values -sS | jq

[
  "value1",
  "value2"
]

We’ve got a basic API server working, let’s try hooking it up to a PostgreSQL database. First lets get a database set up for our experimentation (I’m assuming you have either PostgreSQL installed locally or available in a docker environment - I’d recommend getting it set up in docker):

psql -U postgres
CREATE ROLE dotnet_user with encrypted password 'secret' LOGIN;
CREATE DATABASE dotnet_test with owner dotnet_user;

Now we need to add some new packages to our project. Let’s add them via the command line:

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

There is one small change we need to make to our csproj file - we need to change the reference to the Microsoft.EntityFrameworkCore.Tools.DotNet package from a PackageReference to a DotNetCliToolReference. This will expose the dotnet ef command to us. Edit MyFirstWebAPI.csproj and move the package into it’s own group and change it to DotNetCliToolReference.

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>netcoreapp1.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <Folder Include="wwwroot\" />
  </ItemGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore" Version="1.1.2" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="1.1.2" />
    <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.2" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="1.1.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.Design" Version="1.1.0" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.1" />
  </ItemGroup>
</Project>

Now run the following command to make sure we have all the packages installed:

dotnet restore

Let’s now setup our database connection in our app settings. Open up the file appsettings.json in the root of the project and modify it so it contains a connection string to the database we created earlier:

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  },
  "ConnectionStrings": {
    "DATABASE_URL": "User ID=dotnet_user;Password=secret;Host=localhost;Port=5432;Database=dotnet_test;Pooling=true;"
  }
}

Let’s create some entities - we’ll create a simple Test entity - create a folder called Entities and a file called Test.cs:

using System;

namespace MyFirstWebAPI.Entities
{
  public class Test
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
  }
}

We can now create our DbContext - this is the class that gets the Entity Framework up and running. Create a new folder Database and a file ApplicationDbContext.cs

using System;
using Microsoft.EntityFrameworkCore;

namespace MyFirstWebAPI.Database
{
  public class ApplicationDbContext : DbContext
  {
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }
    public DbSet<Entities.Test> TestRecords { get; set; }
  }
}

We now need to add our ApplicationDbContext to the dependency injection framework and configure the connection string. Open up Startup.cs. Add a reference to EntityFrameworkCore

using Microsoft.EntityFrameworkCore;

And in the ConfigureServices method add the following code to setup our ApplicationDbContext:

var sqlConnectionString = Configuration.GetConnectionString("DATABASE_URL");
services.AddDbContext<Database.ApplicationDbContext>(options =>
    options.UseNpgsql(
        sqlConnectionString,
        b => b.MigrationsAssembly("MyFirstWebAPI")
    )
);

We should now be in a position to generate a database migration to add our new entity to the database.

dotnet ef migrations add "Create Test Entity"

This should have created us a new file Migrations/XXXX_Create Test Entity.cs which will contain code for and Up method for creating our new table and a Down method for destroying our new table.

Let’s now run the migration to update our database.

dotnet ef database update

If we now look in our database we should see the new table:

\connect dotnet_test
\d "TestRecords"
                             Table "public.TestRecords"
   Column    |  Type   |                         Modifiers
-------------+---------+------------------------------------------------------------
 Id          | integer | not null default nextval('"TestRecords_id_seq"'::regclass)
 Description | text    |
 Name        | text    |
Indexes:
    "PK_TestRecords" PRIMARY KEY, btree (Id)

Let’s now hook up our ValuesController to our DbContext. We will use the dependency injection system to automatically get a reference to the ApplicationDbContext. Modify the ValuesController class so that it has a member variable and a constructor:

public class ValuesController : Controller
{
  Database.ApplicationDbContext dbContext;

  public ValuesController(Database.ApplicationDbContext dbContext)
  {
    this.dbContext = dbContext;
  }

We’ll wire up the Get method to get all the Test entities:

[HttpGet]
public IEnumerable<Entities.Test> Get()
{
    return dbContext.TestRecords;
}

The get by id method:

[HttpGet("{id}")]
public Entities.Test Get(int id)
{
    return dbContext.TestRecords.Where(t => t.Id == id).FirstOrDefault();
}

The post:

[HttpPost]
public Entities.Test Post([FromBody]Entities.Test value)
{
    dbContext.TestRecords.Add(value);
    dbContext.SaveChanges();
    return value;
}

The put method:

[HttpPut("{id}")]
public Entities.Test Put(int id, [FromBody]Entities.Test value)
{
    var entity = dbContext.TestRecords.Where(t => t.Id == id).FirstOrDefault();
    entity.Name = value.Name;
    entity.Description = value.Description;
    dbContext.SaveChanges();
    return entity;
}

And finally the delete method

[HttpDelete("{id}")]
public Entities.Test Delete(int id)
{
    var entity = dbContext.TestRecords.Where(t => t.Id == id).FirstOrDefault();
    dbContext.TestRecords.Remove(entity);
    dbContext.SaveChanges();
    return entity;
}

Now lets try out our new API:

dotnet run
Hosting environment: Production
Content root path: /Users/chrisgreening/Work/dotnetcore/MyFirstWebAPI
Now listening on: http://localhost:5000
Application started. Press Ctrl+C to shut down.

If we list our objects we should get an empty array as the database is empty:

curl localhost:5000/api/values -sS | jq
[]

Let’s create some objects:

curl localhost:5000/api/values -H "Content-Type: application/json" -X POST -d '{"Name": "Bob", "Description": "A cool guy"}' -sS | jq
{
  "id": 1,
  "name": "Bob",
  "description": "A cool guy"
}
curl localhost:5000/api/values -H "Content-Type: application/json" -X POST -d '{"Name": "Jim", "Description": "Good chap"}' -sS | jq
{
  "id": 2,
  "name": "Jim",
  "description": "Good chap"
}
curl localhost:5000/api/values -H "Content-Type: application/json" -X POST -d '{"Name": "Chris", "Description": "A bit bananas"}' -sS | jq
{
  "id": 3,
  "name": "Chris",
  "description": "A bit bananas"
}
curl localhost:5000/api/values -sS | jq
[
  {
    "id": 1,
    "name": "Bob",
    "description": "A cool guy"
  },
  {
    "id": 2,
    "name": "Jim",
    "description": "Good chap"
  },
  {
    "id": 3,
    "name": "Chris",
    "description": "A bit bananas"
  }
]

Let’s check our put command:

curl localhost:5000/api/values/3 -H "Content-Type: application/json" -X PUT -d '{"Name": "Chris", "Description": "What a guy!"}' -sS | jq
{
  "id": 3,
  "name": "Chris",
  "description": "What a guy!"
}
curl localhost:5000/api/values/3 -sS | jq
{
  "id": 3,
  "name": "Chris",
  "description": "What a guy!"
}

And let check we can delete

curl localhost:5000/api/values/3 -X DELETE
curl localhost:5000/api/values/2 -X DELETE
curl localhost:5000/api/values -sS | jq
[
  {
    "id": 1,
    "name": "Bob",
    "description": "A cool guy"
  }
]

We’ve now got a nice little server running .Net core talking to a PostgreSQL database.

#CROSS-PLATFORM #DATABASE INTEGRATION #DOTNET COMMAND LINE #MAC #MICROSOFT #NET CORE #POSTGRESQL #WEB API

Related Posts

Step 7: Action Cable on Elastic Beanstalk - This blog provides detailed steps and configurations on setting up an application using Action Cable feature in Rails 5. It talks about setting up proper environment for the application to run and includes using both PostgreSQL and Redis as backend services for the ActionCable. The blog also shares troubleshooting commands in case of any issues encountered during the setup. Built on top of WebSockets, Action Cable was first announced at RailsConf 2015 and now a substantial part of Rails 5 providing real time communication with backend server.
Connecting PSQL and Rails Console to Elastic Beanstalk - In this post, we explore a solution to the challenge of running the Rails console against a PostgreSQL database housed in a private subnet for security reasons. While it's a good security practice, it can limit access for bootstrapping users or debugging issues. By tunnelling through a Bastion server, we can establish a connection to the database as if it was running locally. We walk through code examples of SSH tunneling, database connection, and how to run Rails console. A key point is to ensure the Bastion server is shut down after use.

Related Videos

Revolutionize Your Raspberry Pi Development with VSCode Remote! - Learn how to develop code on Raspberry Pi using VSCode without needing VNC or a desktop environment by setting up a remote development environment. Develop your projects more conveniently and efficiently with this powerful tool!
Vision framework and CoreML - Discover the seamless integration of Apple's Vision framework with Core ML for object identification in this astounding demonstration, and lock onto the project at GitHub.
TensorFlow Lite With Platform.io and the ESP32 - Learn how to train a simple TensorFlow Lite model and run it on the ESP32 using PlatformIO! With clear instructions and a helpful video, this tutorial will have your project up and running in no time.
AR Sudoku Solver in Your Browser: TensorFlow & Image Processing Magic - Discover how to recreate a Sudoku app using browser APIs and ecosystem advancements, and explore the image processing pipeline technique for extracting Sudoku puzzles and solving them.
ESP32 HTTP Web Server With Content Served from SPIFFS Filesystem - Learn how to create a simple ESP32 web server and user interface using basic HTML and JavaScript, and control an LED via an HTTP endpoint. This tutorial demonstrates the ease of setting up a web server on ESP32 devices and serving compressed content from SPIFFS.
HELP SUPPORT MY WORK: If you're feeling flush then please stop by Patreon Or you can make a one off donation via ko-fi
Want to keep up to date with the latest posts and videos? Subscribe to the newsletter
Blog Logo

Chris Greening


Published

> Image

atomic14

A collection of slightly mad projects, instructive/educational videos, and generally interesting stuff. Building projects around the Arduino and ESP32 platforms - we'll be exploring AI, Computer Vision, Audio, 3D Printing - it may get a bit eclectic...

View All Posts