Piko ORM

floki

Feb 8, 2024

Piko ORM is so, so tiny. It's Piko. The world's tiniest, smallest ORM for .NET.

Works best with PostgreSQL.

using System.Data;
using NpgsqlTypes;

namespace DataAccess;

public static class PikoOrm
{
    public static IEnumerable<T> Read<T>(this NpgsqlConnection connection,
        IEnumerable<(object value, DbType? dbType, NpgsqlDbType? npgsqlDbType)> parameters,
        string command, 
        Func<NpgsqlDataReader, T> action)
    {
        if (connection.State != ConnectionState.Open) connection.Open();
        using var cmd = connection.CreateCommand();
        cmd.CommandText = command;
        foreach(var (value, dbType, npgsqlDbType) in parameters)
        {
            var p = cmd.CreateParameter();
            p.Value = value ?? DBNull.Value;
            if (dbType.HasValue) p.DbType = dbType.Value;
            if (npgsqlDbType.HasValue) p.NpgsqlDbType = npgsqlDbType.Value;
            cmd.Parameters.Add(p);
        }
        using var reader = cmd.ExecuteReader();
        while (reader.Read()) 
        {
            yield return action(reader);
        }
        reader.Close();
    }

    public static void Execute(this NpgsqlConnection connection, string command)
    {
        if (connection.State != ConnectionState.Open) connection.Open();
        using var cmd = connection.CreateCommand();
        cmd.CommandText = command;
        cmd.ExecuteNonQuery(); 
    }

    public static T Val<T>(this NpgsqlDataReader reader, string name)
    { 
        var value = reader[name];
        return value == DBNull.Value ? default : (T)value;
    }

    public static T Val<T>(this NpgsqlDataReader reader, int ordinal)
    {
        var value = reader[ordinal];
        return value == DBNull.Value ? default : (T)value;
    }
}

using System.Data;
using NpgsqlTypes;

namespace DataAccess;

public static class PikoOrm
{
    public static IEnumerable<T> Read<T>(this NpgsqlConnection connection,
        IEnumerable<(object value, DbType? dbType, NpgsqlDbType? npgsqlDbType)> parameters,
        string command, 
        Func<NpgsqlDataReader, T> action)
    {
        if (connection.State != ConnectionState.Open) connection.Open();
        using var cmd = connection.CreateCommand();
        cmd.CommandText = command;
        foreach(var (value, dbType, npgsqlDbType) in parameters)
        {
            var p = cmd.CreateParameter();
            p.Value = value ?? DBNull.Value;
            if (dbType.HasValue) p.DbType = dbType.Value;
            if (npgsqlDbType.HasValue) p.NpgsqlDbType = npgsqlDbType.Value;
            cmd.Parameters.Add(p);
        }
        using var reader = cmd.ExecuteReader();
        while (reader.Read()) 
        {
            yield return action(reader);
        }
        reader.Close();
    }

    public static void Execute(this NpgsqlConnection connection, string command)
    {
        if (connection.State != ConnectionState.Open) connection.Open();
        using var cmd = connection.CreateCommand();
        cmd.CommandText = command;
        cmd.ExecuteNonQuery(); 
    }

    public static T Val<T>(this NpgsqlDataReader reader, string name)
    { 
        var value = reader[name];
        return value == DBNull.Value ? default : (T)value;
    }

    public static T Val<T>(this NpgsqlDataReader reader, int ordinal)
    {
        var value = reader[ordinal];
        return value == DBNull.Value ? default : (T)value;
    }
}

Here's a sample usage:

public class Person(int id, string name)
{
    public int Id { get; } = id;
    public string Name { get; } = name;
}

public static IEnumerable<Person> GetAllPeople(this NpgsqlConnection connection)
{
    return connection.Read(
        [], 
        "select id, name from people",
        r => new Person(r.Val<int>(0), r.Val<string>(1)));
}

public static Person GePersonById(this NpgsqlConnection connection, int id)
{
    return connection.Read(
        [(id, DbType.Int32, null)],
        "select id, name from people where id = $1",
        r => new Person(r.Val<int>(0), r.Val<string>(1)))
        .FirstOrDefault();
}

public class Person(int id, string name)
{
    public int Id { get; } = id;
    public string Name { get; } = name;
}

public static IEnumerable<Person> GetAllPeople(this NpgsqlConnection connection)
{
    return connection.Read(
        [], 
        "select id, name from people",
        r => new Person(r.Val<int>(0), r.Val<string>(1)));
}

public static Person GePersonById(this NpgsqlConnection connection, int id)
{
    return connection.Read(
        [(id, DbType.Int32, null)],
        "select id, name from people where id = $1",
        r => new Person(r.Val<int>(0), r.Val<string>(1)))
        .FirstOrDefault();
}

Any questions?

Note: PikoORM is AOT ready!

To receive notifications about new posts and updates, consider subscribing to my LinkdIn page:
vb-software linkedin

You will receive notifications about new posts on your LinkedIn feed.

Comments