Formula.SimpleRepo 1.0.7

Formula.SimpleRepo

Easy repositories for .Net built on Dapper.

Getting Started

Install the nuget package

dotnet add package Formula.SimpleRepo --version 1.0.*

Add a connection to your database (appsettings.json)

{
    "ConnectionStrings": {
        "DefaultConnection": "Server=database.server.com;Database=MyAppDB;User=my_user;Password=my_pw!;MultipleActiveResultSets=true"
    }
}

Special Instructions For Console Applications

For console applications, enable configuration and dependency injection

dotnet add package Microsoft.Extensions.Configuration
dotnet add package Microsoft.Extensions.Configuration.FileExtensions
dotnet add package Microsoft.Extensions.Configuration.Json

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.FileExtensions;
using Microsoft.Extensions.Configuration.Json;
using Microsoft.Extensions.DependencyInjection;

...
var config = new ConfigurationBuilder()
        .SetBasePath(Directory.GetCurrentDirectory())
        .AddJsonFile("appsettings.json", true, true)
        .Build();

var services = new ServiceCollection();
services.AddSingleton<IConfiguration>(config)
        .BuildServiceProvider();

Creating a repository

Step 1 - Create a model

The model represents a single record mapping between a table or view on your datasource and a POCO (plain old c# object) for use within your application.

Annotate your model with the the connection to use from your appSettings, the Table or view to use.

Various other attributes can be used to provide additional mapping assistance (such as Key, Column, GUID). See Dapper.SimpleCRUD for details

using System;
using Dapper;
using Formula.SimpleRepo;

namespace MyApi.Data.Models
{
    [ConnectionDetails("DefaultConnection")]
    [Table ("Todos")]
    public class Todo
    {
        [Key]
        public int Id { get; set; }

        public String Details { get; set; }

        public Boolean Completed { get; set; }
        
        public int? CategoryId { get; set; }
    }
}

Step 2 - Create a Repository

The repository provides simple CRUD operations ( provided by Dapper.SimpleCRUD ), simple constrainable operations (query by JSON), as well as a single place to wrap business concepts into data fetch / store operations by custom function you provide.

The simplest implementation of a repository to take advantage of simple CRUD and constrainables can be implemented as follows.

using System;
using Microsoft.Extensions.Configuration;
using Formula.SimpleRepo;
using MyApi.Data.Models;

namespace MyApi.Data.Repositories
{
    [Repo]
    public class TodoRepository : RepositoryBase<Todo, Todo>
    {
        public TodoRepository(IConfiguration config) : base (config)
        {
        }
    }
}

Registering Repositories

Repositories can be registered into the depencey injection system by implementing a couple steps. In the ConfigureServices section of Startup.cs make sure to make a call to AddRepositories. Failing to do so will result in controllers depending on these respositories bing unable to resolve service for these repository types.

using Formula.SimpleRepo;
...
services.AddRepositories();

All repositories decorated with the [Repo] attribute will be injected.

Step 3 - Work with data

Now you can perform all queries and CRUD operations against the models. For dynamic / business defined constrainables see further steps below. But you now have enough to perform basic operations against your data.

Example...

var repo = new TodoRepository(config);
foreach(var item in repo.Get())
{
    Console.WriteLine(item.Details);
}

What can you do?

Read Operations

There are async versions of all methods.

Get / GetAsync- Fetch data

// Get a single item by it's ID
var record = repo.Get(21); // Can be number or GUID

// Get all records
var records = repo.Get();

// Get by specific fields using JSON to define your constraints
records = repo.Get("{Completed:true}"); 

// Get by hash table
records = repo.Get(new Hashtable() { { "Completed", true } });

// Additional methods like fetching via JObject, Bindable (advanced concept), etc..

// Get a list of all the identity columns
var idFields = repo.GetIdFields();


// You also have access to other operations provided by SimpleCRUD via the Basic property
// These do not apply non database / dynamic constrainable concepts described below
records = repo.Basic.GetList("where column_name like '%asdf%'");

var pagedData = repo.Basic.GetListPaged(1, 10, "where column_a = 'asdf'");

var recordCount = repo.Basic.RecordCount("where column_name like '%asdf%'");

// etc...

Dapper.SimpleCRUD You still have access to all the dapper SimpleCRUD you are used too.

CRUD Operations

Like above, there are async versions of all methods.

// Delete
repo.Delete(21); // Can be number or GUID

// Insert
var modelToSave = new Todo() {
    Details = "Do a backflip",
    Completed = false,
};

var newId = repo.Insert(modelToSave);

// Update
var modelToSave = new Todo() {
    Id = 21,
    Details = "Do a backflip",
    Completed = true,
};

repo.Update(modelToSave);

// Like with the basic query operations, you also still have access to other basic
// operations that don't apply to the constrainable types.

repo.Basic.DeleteList("where yadda yadda...");

Step 5 - Advanced Topics

See Advanced Topics for more details.

(Optional) Step - Expose via API

The Formula.SimpleAPI project provides utilities to expose your repository as a RESTful API.