Normal

Introduction

Normal is a small and extensible ORM for .NET available as a NuGet package.

It has no third-party dependencies, and can be dropped into an existing project.

• Normal
  • Introduction
  • Installation
  • Usage
    • Database Class
    • Statement Builder
    • CRUD statements
    • Custom Commands
    • Custom Middleware
    • Transactions
    • AspNetCore
  • Building
  • Testing

Installation

Platform	Minimum Version
.NET Standard	2.0
.NET Framework	4.6.1

dotnet add package Normal

--OR--

PM> Install-Package Normal

Usage

Database Class

The entrypoint into the Normal API is the Database class. Typically, only one of these should be created per database in your application lifetime (or HTTP Request lifetime.) A Database is intended to be injected into and shared amongst other classes.

// Use with SQL Servervardatabase=Database.WithConnection<SqlConnection>("Server=...");// Use with PostgreSQLvardatabase=Database.WithConnection<NpgsqlConnection>("Host=...");

Statement Builder

For very simple queries, you can use the inline statement builder for SELECT, INSERT, UPDATE, and DELETE. This can map the results to any POCO class.

classCustomer{publicstringFirstName{get;set;}publicstringLastName{get;set;}publicintAge{get;set;}}// Do a SELECT then map the results to a list.IList<Customer>customers=awaitdatabase.Select("first_name","last_name","age").From("customer").Where("last_name").EqualTo("Cuervo").ToListAsync<Customer>();// Do a SELECT then grab the first result.Customercustomer=awaitdatabase.Select("first_name","last_name","age").From("customer").Where("customer_id").EqualTo(777).FirstOrDefaultAsync<Customer>();// Execute an INSERTintrowsAffected=awaitdatabase.InsertInto("customer").Columns("first_name","last_name","age").Values("Peter","Rabbit",100).Values("Santa","Clause",1000).Execute();// Execute an UPDATEintrowsAffected=awaitdatabase.Update("customer").Set("first_name").EqualTo("Jerry").Set("last_name").EqualTo("Seinfeld").Where("last_name").EqualTo("Cuervo").Execute();// Execute a DELETEintrowsAffected=awaitdatabase.DeleteFrom("customer").Where("last_name").EqualTo("Cuervo").Execute();

CRUD statements

Simple crud operations can be executed using some convenience methods on Database. To use these, it's recommended that you annotate your models with the Table, PrimaryKey and Column annotations. If the annotations are omitted, Normal will use the class name as the table name, and the field names as the column names.

[Table("warehouse.stock_items")]publicclassStockItem{[PrimaryKey][Column("stock_item_id")]publicintStockItemID{get;set;}[Column("stock_item_name")]publicstringStockItemName{get;set;}[Column("supplier_id")]publicintSupplierId{get;set;}[NotMapped]publicstringComputedProperty{get;set;}}// SELECT all rows from stock_items and map them to a list of StockItemvarstockItems=awaitdatabase.SelectAll<StockItem>().ToListAsync();// SELECT the row where stock_item_id = 1 and map it to a StockItem (or null.)varstockItem=awaitdatabase.SelectAsync<StockItem>(1);// SELECT the rows where stock_item_name = "USB missile launcher (Green)" and map it to a list of StockItemvarresults=awaitdatabase.SelectAll<StockItem>().Where("stock_item_name").EqualTo("USB missile launcher (Green)");// INSERT a row into stock_items, using the fields on the stockItem model.varinsertedStockItem=awaitdatabase.InsertAsync<StockItem>(stockItem);// INSERT a row in stock_items, using the fields on the stockItem model.varupdatedStockItem=awaitdatabase.UpdateAsync<StockItem>(stockItem);// DELETE a row from stock_itemsvarrowsAffected=awaitdatabase.DeleteAsync<StockItem>(stockItem);

Custom Commands

For more complicated queries, commands can be created from a string, an embedded resource, or a file.

// Create a command from a string, add a parameter, and map results to a list.varcustomers=awaitdatabase.CreateCommand(@"SELECT first_name, last_name FROM customer WHERE last_name = @lastName").WithParameter("lastName","Cuervo").ToListAsync<Customer>();// Normal will load the resource from the calling assembly. database.CreateCommandFromResource("My.Assembly.GetCustomers.sql");// The assembly name may be omitted. Normal will load the first resource that ends with the given string.database.CreateCommandFromResource("GetCustomers.sql");// Optionally, you may pass an assembly to load the embedded resource fromdatabase.CreateCommandFromResource("GetCustomers.sql",myAssembly);// Also, you can load a command from any file.database.CreateCommandFromFile("/path/to/sql/GetCustomers.sql");

Custom Middleware

Normal is extensible, and you can write your own middleware!

publicclassAwesomeHandler:DelegatingHandler{publicoverrideasyncTask<int>ExecuteNonQueryAsync(ICommandBuildercommandBuilder,CancellationTokencancellationToken){// Do stuff before non-queryvarrowsAffected=awaitInnerHandler.ExecuteNonQueryAsync(commandBuilder,cancellationToken);// Do stuff after non-queryreturnrowsAffected;}publicoverrideasyncTask<IEnumerable<T>>ExecuteReaderAsync<T>(ICommandBuildercommandBuilder,CancellationTokencancellationToken){// Do stuff before queryvarresults=awaitInnerHandler.ExecuteReaderAsync<T>(commandBuilder,cancellationToken);// Do stuff after queryreturnresults;}}

You can install this on Database by using new Database.

vardatabase=newDatabase(c =>{c.UseConnection(connection);c.UseDelegatingHandler(newAwesomeHandler());// Add custom middleware.});

Middleware is executed in the order that it was added. For example, if you added three DelegatingHandlers...

vardatabase=newDatabase(c =>{c.UseDelegatingHandler(newA())
c.UseDelegatingHandler(newB())
c.UseDelegatingHandler(newC())});

Then for every database query, the middlewares are executed in order in a nested fashion.

A
B
C
BaseHandler
C
B
A

Transactions

To start a new database transaction, call BeginTransaction on Database. Once a transaction is begun on an instance of Database, all statements executed against that database automatically join the transaction on the same connection. Once the transaction is disposed, the database returns to connection pooling behavior.

This is useful because different repositories sharing the same Database instance can also share transactions. Say you have a service class with several repositories. Because you're using dependency injection, each of those repositories shares the same Database instance...

privatereadonlyIDatabase_database;publicasyncTaskPlaceCustomerOrder(CustomerDetailscustomerDetails,OrderDetailsorderDetails){using(vartransaction=await_database.BeginTransactionAsync()){// Automatically joins the transactionvaruserId=await_userRepository.CreateCustomer(customerDetails);// Automatically joins the transactionvarorderId=await_orderRepository.CreateOrder(orderDetails);// Automatically joins the transactionvarfulfillmentTicket=await_fulfillmentRepository.CreateFulfillmentTicket(userId,orderId);transaction.Commit();}}

AspNetCore

There is an AspNetCore plugin that adds caching, logging, and DI support. See Normal.AspNetCore.

Building

Prerequisites:

• .NET Core SDK 3.1
• Mono or .NET Framework
• Gnu Make

To build the NuGet package.

make pack

This project targets both .NET Standard 2.0 and .NET Framework 4.6.1. Because of this, you must have .NET Framework or Mono installed (in addition to .NET Core).

On macOS and Linux build environments, to build from .NET Core you must set the FrameworkPathOverride environment variable.

export FrameworkPathOverride=$(dirname $(which mono))/../lib/mono/4.5/

See dotnet/sdk#335

Testing

Prerequisites:

• .NET Core SDK 3.1
• Gnu Make
• Docker
• Bash

Running the integration tests requires having a recent version of Docker installed. Two database servers (PostgreSQL and SQL Server) will be brought up with

make databases

After the servers are up and the databases are restored, the tests can be run.

make test

To bring down the servers and clean up the backup files,

make clean-databases

Built with ♥ by Calvin.

© Calvin Furano