C# ORM example application

The following tutorial implements a REST API server using the Dapper ORM. The scenario is that of an e-commerce application where database access is managed using the ORM.

The source for the application can be found in the Using ORMs with YugabyteDB repository.

Prerequisites

This tutorial assumes that you have:

YugabyteDB up and running. Download and install YugabyteDB by following the steps in Quick start.
.NET SDK 6.0 or later.

Clone the "orm-examples" repository

$ git clone https://github.com/YugabyteDB-Samples/orm-examples.git && cd orm-examples/csharp/dapper/DapperORM

Set up the database connection

To modify the database connection settings, change the DefaultConnection field in appsettings.json file which is in the following format:

Host=$hostName; Port=$dbPort; Username=$dbUser; Password=$dbPassword; Database=$database

Properties	Description	Default
Host	Database server IP address or DNS name.	127.0.0.1
Port	Database port where it accepts client connections.	5433
Username	The username to connect to the database.	yugabyte
Password	The password to connect to the database.
Database	Database instance in database server.	yugabyte

Start the REST API server

To change default port for the REST API Server, go to Properties/launchSettings.json and change the applicationUrl field under the DapperORM field.

Build the REST API server.
```
$ dotnet build
```
Run the REST API server
```
$ dotnet run
```

The REST server runs at http://localhost:8080 by default.

Send requests to the application

Create 2 users.

$ curl --data '{ "firstName" : "John", "lastName" : "Smith", "email" : "jsmith@example.com" }' \
   -v -X POST -H 'Content-Type:application/json' http://localhost:8080/users

$ curl --data '{ "firstName" : "Tom", "lastName" : "Stewart", "email" : "tstewart@example.com" }' \
   -v -X POST -H 'Content-Type:application/json' http://localhost:8080/users

Create 2 products.

$ curl \
  --data '{ "productName": "Notebook", "description": "200 page notebook", "price": 7.50 }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/products

$ curl \
  --data '{ "productName": "Pencil", "description": "Mechanical pencil", "price": 2.50 }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/products

Verify the userId and productId from the database using the following YSQL commands from your ysqlsh shell.

yugabyte=# SELECT * FROM users;

 user_id | first_name | last_name |      user_email
---------+------------+-----------+----------------------
       1 | John       | Smith     | jsmith@example.com
     101 | Tom        | Stewart   | tstewart@example.com
(2 rows)

yugabyte=# SELECT * FROM products;

 product_id |    description    | price | product_name
------------+-------------------+-------+--------------
          1 | 200 page notebook |  7.50 | Notebook
        101 | Mechanical pencil |  2.50 | Pencil
(2 rows)

Create 2 orders with products using the userId for John.

$ curl \
  --data '{ "userId": "1", "products": [ { "productId": 1, "units": 2 } ] }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/orders

$ curl \
  --data '{ "userId": "1", "products": [ { "productId": 1, "units": 2 }, { "productId": 101, "units": 4 } ] }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/orders

Query results

Use the YSQL shell

yugabyte=# SELECT count(*) FROM users;

 count
-------
     2
(1 row)

yugabyte=# SELECT count(*) FROM products;

 count
-------
     2
(1 row)

yugabyte=# SELECT count(*) FROM orders;

 count
-------
     2
(1 row)

Use the REST API

To use the REST API server to verify that the users, products, and orders were created in the yugabyte database, enter the following commands. The results are output in JSON format.

$ curl http://localhost:8080/users

{
  "content": [
    {
      "userId": 101,
      "firstName": "Tom",
      "lastName": "Stewart",
      "email": "tstewart@example.com"
    },
    {
      "userId": 1,
      "firstName": "John",
      "lastName": "Smith",
      "email": "jsmith@example.com"
    }
  ],
  ...
}

$ curl http://localhost:8080/products

{
  "content": [
    {
      "productId": 101,
      "productName": "Pencil",
      "description": "Mechanical pencil",
      "price": 2.5
    },
    {
      "productId": 1,
      "productName": "Notebook",
      "description": "200 page notebook",
      "price": 7.5
    }
  ],
  ...
}

$ curl http://localhost:8080/orders

{
  "content": [
    {
      "orderId":"2692e1e9-0bbd-40e8-bf51-4fbcc4e9fea2",
      "orderTime":"2022-02-24T02:32:52.60555",
      "orderTotal":15.00,
      "userId":1,
      "users":null,
      "products":null
    },
    {
      "orderId":"f7343f22-7dfc-4a18-b4d3-9fcd17161518",
      "orderTime":"2022-02-24T02:33:06.832663",
      "orderTotal":25.00,
      "userId":1,
      "users":null,
      "products":null
    }
  ]
}