Modern and extendible C# 8 data access built for .NET Core 3 era.

Warning THIS IS NOT ORM Norm is NoORM, or not an ORM (although, there is O/R mapping extension).

Norm will postpone any reads from database until they are needed - allowing you to build expression trees and transformations - before it started fetching any data.

This allows avoiding unneccessary iterations and much greater flexibility.

By default it will return iterator over tuples and not serialized instances.

Because that's what databases do returns - tuples.

This allows for more extendibility - iterator results can be then further extended or mapped and transformed to something else (such as dictioanires or O/R mappings, see O/R mapping.

Usage examples can be found in unit tests

Around 99% of API is covered with tests only for PostgreSQL, Microsoft SQL Server and SQLite.

Other types of databases should work theoretically but they are not currently tested.

• Get the first value from single row:

var value = connection.Single<string>("select v from t limit 1");

• Get the first and second value from single row:

var (value1, value2) = connection.Single<string, string>("select v1, v2 from t limit 1");

• Get the first value from single row and pass parameter by position:

var value = connection.Single<string>("select v from t limit 1 where id = @id", 1);

• Get the first value from single row and pass parameter by name:

var value = connection.Single<string>("select v from t limit 1 where id = @id", ("id", 1));

• Iterate trough set of three values:

foreach(var (value, value2, value3) in connection.Read<string, int bool>("select v1, v2, v3 from t"))
{
    // do something with value, value2, value3
}

• Map to class instance:

var instance = connection.Single("select * from t limit 1").Select<MyClass>();

• Map to enumerable of instances and delay query execution until enumeration:

var instances = connection.Read("select * from t limit 1").Select<MyClass>();

• Get the async stream of values from database:

await foreach(var (value, value2, value3) in connection.ReadAsync<string, int bool>("select v1, v2, v3 from t"))
{
    // do something with value, value2, value3
}

• Get the async stream of instances from database:

await foreach(var instance in connection.ReadAsync("select v1, v2, v3 from t").Select<MyClass>())
{
    // do something with each instance
}

Entire API that is implemented as System.Data.Common.DbConnection extension can be found on this interface

Recap:

Each database connection method can receive list of parameters that are mapped to appropriate DbParameter to avoid SQL injection.

There are three overloads:

Map parameter by position (name is not important, but it must start with @ by convention)

connection.Execute("select @p1, @p2", value1, value2);
connection.Execute("select @p1, @p2, @third", value1, value2, value3);
// etc...

Values are parameter values which are mapped to appropriate type by underlying database connection provider.

Map parameter by exact name, position is not important:

connection.Execute("select @p1, @p2", ("p1", value1), ("p2", value2));
connection.Execute("select @p1, @p2, @third", ("p1", value1), ("p2", value2), ("third", value3));
// etc...

Values are parameter values which are mapped to appropriate type by underlying database connection provider.

When using named parameters you can pass ald DbType value to specify underlaying database type exactly__

connection.Execute("select @p1, @p2", ("p1", value1, DbType.Int32), ("p2", value2, DbType.String));
connection.Execute("select @p1, @p2, @third", ("p1", value1, DbType.Int32), ("p2", value2, DbType.String), ("third", value3, DbType.Boolean));
// etc...

Important:

All parameters null values will be interpreted as DbValue.Null to match database nulls.

When passing null values, exact database type of paramater cannot be determined automatically, so, you must pass type value manually.

First overload for positional parameters can receive concrete DbParameter instead of object value if we want to narrow parameter type more precisely.

For example, using PostgreSQL parameters would look like this:

var (s, i, b, d) = connection.Single<string, int, bool, DateTime>(
    "select @s, @i, @b, @d",
    new NpgsqlParameter("s", "str"),
    new NpgsqlParameter("i", 999),
    new NpgsqlParameter("b", true),
    new NpgsqlParameter("d", new DateTime(1977, 5, 19)));

or same call for Microsoft SQL Server:

var (s, i, b, d) = connection.Single<string, int, bool, DateTime>(
    "select @s, @i, @b, @d",
    new SqlParameter("s", "str"),
    new SqlParameter("i", 999),
    new SqlParameter("b", true),
    new SqlParameter("d", new DateTime(1977, 5, 19)));

Note that this approach is actually named parameters approach, since parameters are matched by name again. So, position is not important.

Positional value parameters and DbParameter can be mixed. In that case DbParameter are populated first and ignored by value parameters later. For more examples, see this unit tests for PostgreSQL or this unit tests for Microsoft SQL Server.

Using concrete DbParameter parameter types allows us to use specific parameter types such as arrays or inet addresses for PostgreSQL and also to alter parameter direction and use output parameters.

By convention any extension that Start with Select will build up expression tree and not trigger any iteration. Available extensions are:

Results are always tuples by default.

There is no automatic O/R mapping out-of-the-box, - as name suggest - this is not ORM

• Non generic version will return enumerable iterator of tuples with string name and object value.

• Generic version return tuples of the type indicated by generic type parameters. For example:

IEnumerable<(int, string, string, DateTime)> results = connection.Read<int, string, string, DateTime>(sql);
// use result to Select or map to required structures for your program.

Common usage scenario would be to use Select or SelectMany - Linq extensions to map those tuples to, for example:

• Dictionary where dictionary key is returned from database
• Multiple class instances by using SelectMany - Linq extensions
• etc

And later in program actual iteration will be executed when we call ToList or foreach.

However, if we would wanted to map to class instances - there are couple of ways to do this.

For example, let's take test query:

-- PostgreSQL syntax
select
    i as id,
    'foo' || i::text as foo,
    'bar' || i::text as bar,
    ('2000-01-01'::date) + (i::text || ' days')::interval as datetime
from generate_series(1, 1000000) as i -- return a million

and test class which instances we wish serialize from this query:

class TestClass
{
    public int Id { get; set; }
    public string Foo { get; set; }
    public string Bar { get; set; }
    public DateTime Datetime { get; set; }
}

Example:

IEnumerable<TestClass> results = connection.Read(sql).Select<TestClass>();

Note: This is implemented as extensions to value/name tuples result. Anyone can implemented custom extension for O/R mapping or anything else that is required. If you want greater speed for this functionality new extension that uses external library (such as extra fast FastMember) can be easily implemented.

This will not trigger iteration nor serialization until we call ToList or foreach.

Query can return JSON values. Either entire JSON blob (use SingleJson method for this) or single row containing Json objects (use Single method for this)

We need to modify query to return JSON:

-- PostgreSQL syntax
select to_json(t) -- return json rows:
from (
    select 
        i as id, 
        'foo' || i::text as foo,
        'bar' || i::text as bar,
        ('2000-01-01'::date) + (i::text || ' days')::interval as datetime
    from generate_series(1, 1000000) as i
) t -- return a million

IEnumerable<TestClass> results = connection.Json(sql);

Again, note that this, again will not trigger iteration nor serialization until we call ToList or foreach.

This the method I would personally recommend - because flexibility and speed. Although it might require little bit of typing. We must add specialized constructor to our class first.

If we would map from dictionary, then we need this constructor:

public TestClass(IDictionary<string, object> dictionary)
{
    Id = (int) dictionary["Id"];
    Foo = (string) dictionary["Foo"];
    Bar = (string) dictionary["Bar"];
    Datetime = (DateTime) dictionary["Datetime"];
}

Or, if we map from tuples we need this dictionary:

public TestClass((int id, string foo, string bar, DateTime dateTime) tuple)
{
    Id = tuple.id;
    Foo = tuple.foo;
    Bar = tuple.bar;
    Datetime = tuple.dateTime;
}

And now, to serialize class instances - we can use these following expressions:

IEnumerable<TestClass> results1 = connection.Read(sql).SelectDictionaries().Select(dict => new TestClass(dict));

IEnumerable<TestClass> results2 = connection.Read<int, string, string, DateTime>(TestQuery).Select(tuple => new TestClass(tuple));

See detailed performance tests here.

> dotnet add package Norm.net

• .NET Standard 2.1
• .NET Core 3.0

This is open-source software developed and maintained freely without any compensation whatsoever.

If you find it useful please consider rewarding me on my effort by buying me a beer🍻 or buying me a pizza🍕

Or if you prefer bitcoin: bitcoincash:qp93skpzyxtvw3l3lqqy7egwv8zrszn3wcfygeg0mv

Copyright (c) Vedran Bilopavlović - VB Software 2019 This source code is licensed under the MIT license.