Basic Concepts

Edit this page on GitHub

Extensions And Fluid Syntax

  • Norm is implemented as a set of extensions over the System.Data.Common.DbConnection object.

  • Some extensions may return the new Norm object instance.

  • Norm object instances have methods with the same signature as the System.Data.Common.DbConnection extensions.

  • This allows the use of the fluid syntax over the connection object. Example:

//
// Execute a stored procedure with command timoout 60 seconds
//
connection
    .AsProcedure()
    .WithTimeout(60)
    .Execute("delete_inactive_customers");
//
// Execute a stored procedure with command timoout 60 seconds
//
connection
    .AsProcedure()
    .WithTimeout(60)
    .Execute("delete_inactive_customers");
  • Since WithTimeout exists as an extension and as an instance method, the example from above could have been written like this:
//
// Execute a stored procedure with command timoout 60 seconds
//
connection
    .WithTimeout(60)
    .AsProcedure()
    .Execute("delete_inactive_customers");
//
// Execute a stored procedure with command timoout 60 seconds
//
connection
    .WithTimeout(60)
    .AsProcedure()
    .Execute("delete_inactive_customers");

Database Commands

  • There are three groups of methods used to execute database commands
  1. Execution
  1. Read Mappings
  1. Multiple Mappings
public static Norm ExecuteAsync(this DbConnection connection, 
    string command,
    object parameters = null,
    [CallerMemberName] string memberName = "",
    [CallerFilePath] string sourceFilePath = "",
    [CallerLineNumber] int sourceLineNumber = 0);
public static Norm ExecuteAsync(this DbConnection connection, 
    string command,
    object parameters = null,
    [CallerMemberName] string memberName = "",
    [CallerFilePath] string sourceFilePath = "",
    [CallerLineNumber] int sourceLineNumber = 0);
  • The last three parameters with the Caller attribute (memberName, sourceFilePath and sourceLineNumber) - should not be supplied by the user - they are intended to be used in diagnostics and logging and are supplied automatically by the compiler.

Read Iterators

  • The Read extension method and all the overload versions of that method - will always return the iterator of the Enumerable<T> interface type.

  • Values are yielded to the iterator as they are returned from the database.

  • For example, the following statements:

using Norm;

// doesn't do anything with the database
var result1 = connection.Read<int>("select count(*) from actor");

// doesn't do anything with the database
var result2 = connection.Read<string>("select title from film");
using Norm;

// doesn't do anything with the database
var result1 = connection.Read<int>("select count(*) from actor");

// doesn't do anything with the database
var result2 = connection.Read<string>("select title from film");

  • These two lines will NOT send or execute any commands to the database.

  • Instead, they will create an iterator over the database connection that will be executed when the actual iteration starts:

using System.Linq;
using Norm;

// doesn't do anything with the database, return iterator
var result1 = connection.Read<int>("select count(*) from actor");

// doesn't do anything with the database, return iterator
var result2 = connection.Read<string>("select title from film");

// executes select count(*) from actor and retuns int
var count = result1.Single();

// executes select title from film and builds a list of film titles
var list = result2.Tolist();
using System.Linq;
using Norm;

// doesn't do anything with the database, return iterator
var result1 = connection.Read<int>("select count(*) from actor");

// doesn't do anything with the database, return iterator
var result2 = connection.Read<string>("select title from film");

// executes select count(*) from actor and retuns int
var count = result1.Single();

// executes select title from film and builds a list of film titles
var list = result2.Tolist();
  • Or, simply combine Linq statements with the iterator returned from the Read method:
using System.Linq;
using Norm;

// executes select count(*) from actor and retuns int
var count = connection.Read<int>("select count(*) from actor").Single();

// executes select title from film and builds a list of film titles
var list = connection.Read<string>("select title from film").Tolist();
using System.Linq;
using Norm;

// executes select count(*) from actor and retuns int
var count = connection.Read<int>("select count(*) from actor").Single();

// executes select title from film and builds a list of film titles
var list = connection.Read<string>("select title from film").Tolist();
  • Or, for example, a standard foreach iteration without Linq:
using Norm;

// executes select title from film and iterate titles
foreach(var title in connection.Read<string>("select title from film"))
{
    // ...
}
using Norm;

// executes select title from film and iterate titles
foreach(var title in connection.Read<string>("select title from film"))
{
    // ...
}

  • As we can see, the actual execution is delayed until the first iteration starts.

  • In simple pseudo-code, this is what the Read method does:

IEnumerable<TResult> Read()
{
    foreach(var record in reader)
    {
        yield return record;
    }
}
IEnumerable<TResult> Read()
{
    foreach(var record in reader)
    {
        yield return record;
    }
}
  • This approach allows us to build Linq expressions over the iterator - which are then executed only once per iteration, and there is no need for additional buffering.

Async Read Iterators

  • The asynchronous version ReadAsync extension method and all the overload versions of that method - will always return the iterator of the IAsyncEnumerable<T> interface type.

  • The asynchronous version ReadAsync - behaves exactly the same as the synchronous Read method - execution is delayed until the first iteration starts.

  • IAsyncEnumerable<T> enumerator interface that provides asynchronous iteration over values:

using System.Linq;
using Norm;

// doesn't do anything with the database, return iterator
var result1 = connection.ReadAsync<int>("select count(*) from actor");

// doesn't do anything with the database, return iterator
var result2 = connection.ReadAsync<string>("select title from film");

// executes select count(*) from actor and retuns int
var count = await result1.SingleAsync();

// executes select title from film and builds a list of film titles
var list = await result2.ToListAsync();
using System.Linq;
using Norm;

// doesn't do anything with the database, return iterator
var result1 = connection.ReadAsync<int>("select count(*) from actor");

// doesn't do anything with the database, return iterator
var result2 = connection.ReadAsync<string>("select title from film");

// executes select count(*) from actor and retuns int
var count = await result1.SingleAsync();

// executes select title from film and builds a list of film titles
var list = await result2.ToListAsync();

  • The example above uses the System.Linq.Async package.

  • System.Linq.Async is an official .NET Foundation implementation of asynchronous LINQ extension methods. It provides an asynchronous version of every LINQ extension method over the IAsyncEnumerable<T> interface.

Working With Tuples

  • Both Read and ReadAsync methods implement a complex mapping system - but they both return a single generic value (IEnumerable<T> and IAsyncEnumerable<T>, respectively).

  • When those iterators should return multiple values, that generic value is a tuple type. For example:

  • IEnumerable<(T1, T2)> - two value tuple (T1, T2) for two values.

  • IEnumerable<(T1, T2, T3)> - three value tuple (T1, T2, T3) for three values.

  • etc.

  • The same goes for the IAsyncEnumerable<T> type.

  • This allows for a convenient tuple deconstruction, for example:

using Norm;

foreach(var (title, description, year) in 
        connection.Read<string, string, int>("select title, description, release_year from film"))
{
    Console.WriteLine("Title: {0}, Description: {1}, Year: {2}", title, description, year)
}
using Norm;

foreach(var (title, description, year) in 
        connection.Read<string, string, int>("select title, description, release_year from film"))
{
    Console.WriteLine("Title: {0}, Description: {1}, Year: {2}", title, description, year)
}
  • Tuples can also be named tuples:
using Norm;

foreach(var tuple in 
        connection.Read<(string title, string description, int year)>("select title, description, release_year from film"))
{
    Console.WriteLine("Title: {0}, Description: {1}, Year: {2}", tuple.title, tuple.description, tuple.year)
}
using Norm;

foreach(var tuple in 
        connection.Read<(string title, string description, int year)>("select title, description, release_year from film"))
{
    Console.WriteLine("Title: {0}, Description: {1}, Year: {2}", tuple.title, tuple.description, tuple.year)
}
  • We can also return multiple named tuples. In that case, each named tuple is a new tuple:
using Norm;

// deconstruction of named tuples
foreach (var (actor, film) in connection.Read<(int id, string name), (int id, string name)>(@"
    select 
        actor_id, first_name || ' ' || last_name, 
        film_id, title
    from 
        actor
        join film_actor using (actor_id)
        join film using (film_id)
    limit 3"))
{
    WriteLine("Actor: {0}-{1}, Film: {2}-{3}", actor.id, actor.name, film.id, film.name);
}
using Norm;

// deconstruction of named tuples
foreach (var (actor, film) in connection.Read<(int id, string name), (int id, string name)>(@"
    select 
        actor_id, first_name || ' ' || last_name, 
        film_id, title
    from 
        actor
        join film_actor using (actor_id)
        join film using (film_id)
    limit 3"))
{
    WriteLine("Actor: {0}-{1}, Film: {2}-{3}", actor.id, actor.name, film.id, film.name);
}
  • That same approach applies when mapping to multiple class instances. Multiple values always yield a tuple:
using Norm;

// deconstruction of class instances
foreach (var (actor, film) in connection.Read<Actor, Film>(@"
    select 
        actor_id, first_name || ' ' || last_name, 
        film_id, title
    from 
        actor
        join film_actor using (actor_id)
        join film using (film_id)
    limit 3"))
{
    WriteLine("Actor: {0}-{1}, Film: {1}-{2}", actor.Id, actor.Name, film.Id, film.Name);
}
using Norm;

// deconstruction of class instances
foreach (var (actor, film) in connection.Read<Actor, Film>(@"
    select 
        actor_id, first_name || ' ' || last_name, 
        film_id, title
    from 
        actor
        join film_actor using (actor_id)
        join film using (film_id)
    limit 3"))
{
    WriteLine("Actor: {0}-{1}, Film: {1}-{2}", actor.Id, actor.Name, film.Id, film.Name);
}

Global Settings

  • It is possible to modify Norm's default behavior for the entire application by setting the global settings. For example:
using Norm;

NormOptions.Configure(options =>
{
    options.CommandTimeout = 60;
});
using Norm;

NormOptions.Configure(options =>
{
    options.CommandTimeout = 60;
});
  • This example sets a global option for all commands executed by Norm to have a timeout of 60 seconds.

Important note: NormOptions.Configure call is not thread-safe.

  • NormOptions.Configure call is intended to be executed only once from the application's startup code, typically Startup.cs or Program.cs, before any database command.
PREVIOUS
First Use
NEXT
Methods
Comments
Comments are not available.