Kelson.Common

Collection of packages published under the Kelson.Common namespace

View the Project on GitHub KelsonBall/Kelson.Common

Ooorm.Data

Repository

Ooorm.Data is intended as a scriptable database interface and solves similar problems as Entity Framework.

Some advantages over EF would include

Strongly Typed, Version Controllable Schemas

With Ooorm.Data, the database schema is defined with C# classes that implement IDbItem, these classes are typically “Plain Old CLR Objects” or “POCOs.”

An Employee table definition might look something like this,

public class Employee : IDbItem
{
    public int ID { get; set; }
    public string Name { get; set; }
    public decimal Salary { get; set; }
    public string DepartmentName { get; set; }
}

One of the key advantages of using Ooorm.Data is that references to other tables can be strongly typed, allowing easy navigation using editor features like “Go To Definition” and “Find All References,” as well as allowing the C# compiler to find breaking schema changes by emitting compilation errors. There are 2 kinds of foriegn references in Ooorm.Data, DbVal<T> and DbRef<T>. DbVal represents properties that should always have a value referenced, and DbRef properties are ‘nullable’ references.

An update to our schema might look something like this now

public class Employee : IDbItem
{
    public int ID { get; set; }
    public DbVal<Person> PersonId { get; set; }
    public decimal Salary { get; set; }
    public DbRef<Department> DepartmentId { get; set; }
    // . . .
}

public class Person : IDbItem
{
    public int ID { get; set; }
    public string Name { get; set; }
    // . . .
}

public class Department : IDbItem
{
    public int ID { get; set; }
    public string Name { get; set; }
    // . . .
}

Connecting to a Database

Using Ooorm.Data requires an instance of IDatabase. Currently 2 implementations are provided, SqlDatabaseand VolatileDatabase. SqlDatabase is for connecting to Microsoft Sql Server instances. VolatileDatabase is a pure in-memory implementation of the IDatabase interface.

When connecting to a SqlDatabase you must provide a connection source using the SqlConnection static constructors, .CreateShared(string connection) or .CreateTransient(string connection). A shared connection will create a single ado.net IDbConnection instance for all queries and commands. A transient connection will create, connect, then close a new connection for each query or command.

var sqlDb = new SqlDatabase(SqlConnection.CreateTransient(MY_CONNECTION_STRING));
var tempDb = new VolatileDatabase();

The Ooorm.Data namespace also includes 2 “wrapper” databases.

Scriptable Database Management

Another goal of Ooorm.Data is to provide a scriptable interface for database management systems. The ISchema interface defines methods for creating and dropping databases and tables. The IDatabase interface (which will be covered in the next section) defines methods for reading and writing to a database. Database management systems like Sql Server implement both of these interfaces.

public interface ISchema
{
    Task DropDatabase(string name);
    Task CreateDatabase(string name, params Type[] tables);
    Task CreateTable<T>() where T : IDbItem;
    Task CreateTables(params Type[] tables);
    Task DropTable<T>() where T : IDbItem;
    Task DropTables(params Type[] tables);
}

public interface IDatabase { } // covered in next section

public interface IDbms : IDatabase, ISchema { }

A script to drop and recreate a database with out schema above might look like this

IDbms db = new SqlDatabase(SqlConnection.CreateTransient(MASTER_CONNECTION_STRING));
await db.DropDatabase("staff");
await db.CreateDatabase("staff", typeof(Employee), typeof(Person), typeof(Department));
var staffDb = new SqlDatabase(SqlConnection.CreateShared(STAFF_CONNECTION_STRING));
// , , ,

Note that VolatileDatabase does not implement IDbms. Tables are created when they are needed.

Using the Databases

The IDatabase interface provides a number of methods for reading and writing data. These interfaces are split up into IReadable and IWritable.

The interfaces,

public interface IDatabase : IReadable, IWritable { }
public interface IReadable 
{
    // Read all rows from a table
    Task<IEnumerable<T>> Read<T>() where T : IDbItem;
    Task<IEnumerable<object>> Read(Type type);

    // Read a single item by ID
    Task<T> Read<T>(int id) where T : IDbItem;

    // Read all items that match a simple predicate (predicate can not capture state!)
    Task<IEnumerable<T>> Read<T>(Expression<Func<T, bool>> predicate) where T : IDbItem;

    // Read all items that match a simple parameterized predicate (predicate can not capture state!)
    Task<IEnumerable<T>> Read<T, TParam>(Expression<Func<T, TParam, bool>> predicate, TParam param) where T : IDbItem ;

    // Read object referred to by a reference
    Task<T> Dereference(DbVal<T> value) where T : IDbItem;

    // Try to read an object referred to by a nullable reference
    Task<(bool exists, T value)> Dereference(DbRef<T> value) where T : IDbItem;
}
public interface IWritable
{
    // Write values to the database
    Task<int> Write<T>(params T[] values) where T : IDbItem;

    // Update the values of existing items
    Task<int> Update<T>(params T[] values) where T : IDbItem;

    // Delete the items with the specified IDs
    Task<int> Delete<T>(params T[] values) where T : IDbItem;

    // Delete the items that match a simple predicate
    Task<int> Delete<T>(Expression<Func<T, bool>> predicate) where T : IDbItem;

    // Delete the items that match a parameterized predicate
    Task<int> Delete<T, TParam>(Expression<Func<T, TParam, bool>> predicate, TParam param) where T : IDbItem;
}

Examples,

‘Hello World,’ write an item to the database:

async Task HelloWorld(IDatabase db)
{
    await db.Write(new Person
    {
        Name = "Hello, world",
    });
}

Write records including a reference, read the value, read the reference:

async Task WriteWriteReadRead(IDatabase db)
{
    var person = new Person { Name = "Example McSample", };
    
    await db.Write();

    var employee = new Employee
    {
        PersonId = person.In(db),
        Salary = 2,
    };

    await db.Write(employee);

    var employee_from_db = await db.Read<Employee>(employee.ID);

    var person_from_employee = await employee_from_db.PersonId.Get();
}

Create, read, update, delete a record

async Task AwCrud(IDatabase db)
{
    await db.Write(
        new Person { Name = "First", },
        new Person { Name = "Second", },
        new Person { Name = "Third", }
    );

    var second = 
        (await db.Read<Person>(p => p.Name == "Second"))
        .Single();

    second.Name = "Nth";

    await db.Update(second);

    await db.Write(new Employee{ PersonId = second.In(db) });

    // select from 'Employee' where 'Person'
    var second_employees = db.Read<Employee, Person>((employee, person) => employee.PersonId == person.ID, second);

    await db.Delete(second_employees.First());
}

Using the provided extensions on IDbItem

async Task MoreFluent(IDatabase db)
{
    var person = await new Person { "Hi I'm New!" }.WriteTo(db);
    person.Name = "💩";
    await person.WriteTo(db);
    await person.DeleteFrom(db);
}

Home