DapperRepo

Simple dapper repo with async implementation.

designed to generate simple crud operations while still being lite weight

Currently supported and tested for these providers

Provider	Db Connection	Nuget Package
Sql	SqlConnection	System.Data.SqlClient
MySql	MySqlConnection	MySql.Data
PostgreSQL	NpgsqlConnection	Npgsql
Sqlite	SqliteConnection	Microsoft.Data.Sqlite

It may work for others.

(sql lite I recently added, I made no changes to the repo just added test setup to confirm it worked).

Please note due to wanting to support multiple Db providers certain choices have been made that are for compatibility over optimisation. The repo its self has no scope over any provider (nor should it).

Setup

All examples are done with the standard repo but will work with the async version to (will need to be awaited). Full examples can be found on the github repo with in the test project.

Initializing a new repo

  new SqlRepo(()=> new DbConnection());
  
  e.g
  new SqlRepo(()=> new SqlConnection("connection string"));     // MsSql
  new SqlRepo(()=> new MySqlConnection("connection string"));   // MySql
  new SqlRepo(()=> new NpgsqlConnection("connection string"));  // PostgreSQL
  new SqlRepo(()=> new SqliteConnection("connection string"));  // Sqlite

or of course via DI

  
  // or where ever you want to get the connection string from it from
  // you can ofc use Scoped or Single instead if you wish but depends on sql implementation and how it handles connections
  // would check dapper and sql providers docs for best practice per implementation 
  
  services.Configure<ConnectionStrings>(Configuration.GetSection(nameof(ConnectionStrings)));
  services.AddTransient(r => new SqlConnection(r.GetService<IOptions<ConnectionStrings>>().Value.SqlDb));
  services.AddScoped(r => new SqlRepoAsync(()=>r.GetService<SqlConnection>));
  

by taking a DbConnection directly it does support multiple providers. Allowing a abstract way to interact with data. Regardless of the db tech.

Usages

Creating entities

please note the primary key attribute found in DapperRepo.PrimaryKeyAttribute.cs

    [SqlTableName("Test_Table")]         // can be used to get around pluralization and also unconventional table names  
    public class TableModel
    {
        [PrimaryKey] // Required for repo to work effectively
        public Guid Id { get; set; }
// if a field is auto generated by the db you can set it to be nullable i.e int? 
// if value is null unless told repo will not try to insert null fields but update will this can be overriden using ignoreNullProperties on the update method 
   
        public string Name { get; set; }
        public string Email { get; set; }
        [SqlColumnName("some_number")]      // this can be used to map columns in sql for unconventional names
        public int SomeNumber { get; set; }
        
        [SqlIgnoreColumn]           // this will tell the dapper repo to ignore this property and not map it to the entitiy
        public string ToBeIgnored { get; set; }
    }

Count

     var count = Repo.Count<TableModel>();

Get All

Repo.GetAll<TableModel>().ToArray();

GetAllByX

Repo.GetAllByX<TableModel, int>("SomeNumber", 35);

Repo.GetAllByX<TableModel, int>(nameof(TableModel.SomeNumber), 35);

Query single

// feel free to use top 1 or limit 1 depending on implementation of the sql provider
Repo.QuerySingle<TableModel>(
                "select * from TableModel where SomeNumber = 33");
         
// or with param    
Repo.QuerySingle<TableModel>(
                "select * from TableModel where SomeNumber = @num",new {num =33});

Query Multiple

Repo.QueryMany<TableModel>(
                "select * from TableModel where SomeNumber = 33");

// or with param                
Repo.QueryMany<TableModel>(
                "select * from TableModel where SomeNumber = @num",new {num=33});

Get By Id

Repo.GetById(new TableModel {Id = Guid.Parse("....")});
Repo.GetById(new TableModelWithIntId {Id =  325});

GetExactMatches

Know details of a Entity but not its id (like one you have just added)

  // false as 2nd param indicates to not to ignore nulls i.e some number has to be null for its model to be returned
  var results = Repo.GetExactMatches(new TableModel {Name = "Michale", SomeNumber = null}, false); 
  
  // true as 2nd param indicates to ignore nulls i.e some number can be anything in this case
  var results = Repo.GetExactMatches(new TableModel {Name = "Michale", SomeNumber = null}, true); 

Delete

var item =Repo.GetById(new TableModel {Id = Guid.Parse("....")});
Repo.Delete(item);

Add

  var testTableItem = new TableModel() {Name = "Michael", SomeNumber = 44};
  Repo.Add(testTableItem);

Update

update command is using only primary key in the where clause. so strictly speaking if you wish to update all the values on a row and know its primary key a get is not required

 var item = Repo.GetById(new TableModel {Id = Guid.Parse("....")});
 item.Name = "mike"
 item.Email = null
 Repo.Update(testTableItem); // will update properites and also null properties so db will set email to null

 // Example 2
 var item = Repo.GetById(new TableModel {Id = Guid.Parse("....")});
 item.Name = "mike"
 item.Email = null
 
 Repo.Update(testTableItem, true);   // will ignore null properties and only update name in db

Search And Search Count

Search will bring you back the entities and search count will give you the number of records.

Count Is more light weight if you just need a any equivalent.

Repo.Search<TableModel>(nameof(TableModel.Name), "%cha%");    // recommended 

// or for specific types
Repo.Search<TableModel, int>(
                    nameof(TableModel.SomeNumber), 33, SearchType.Equals);

// and search multiple criteria

 Repo.Search(new TableModel
            {
                Name = "%hae%",
                SomeNumber = 9
            }, new[]
            {
                SearchCriteria.Create(nameof(TableModel.NameTest), SearchType.Like),
                SearchCriteria.Create(nameof(TableModel.SomeNumber), SearchType.Equals)
            });
            
  Repo.SearchCount(new TableModel
            {
                Name = "%hae%",
                SomeNumber = 9
            }, new[]
            {
                SearchCriteria.Create(nameof(TableModel.NameTest), SearchType.Like),
                SearchCriteria.Create(nameof(TableModel.SomeNumber), SearchType.Equals)
            });
            
           // this will allow you to search where name like "%hae%" 
           // and SomeNumber equal to 9
            

Getting Tests working

Connection.cs contains connection config these are not unit tests they do require a db and will spin one up on fly but for a repo seems better than mocking

   public static string MasterConnectionString = "Server=localhost;Database=master;Trusted_Connection=True";

also please note there are separate getall and insert methods that are raw in DataBaseScriptRunnerAndBuilder.cs as the tests don't use the implemenation they are are testing for setups or verifies.

Located in CreateDbWithTestTable.Sql you will find the create database scripts.

Special Thanks to armanx

Notes:

V2.3.0

Brings with it a new exception PrimaryKeyNotFoundException. You should get this when trying to do a updates or a delete on entities that are missing a primary key attributes.

This should not be breaking as currently it will throw a null ref exception which is not very descriptive

SqlColumnName:

Uses simple reflection on the class looking for both class property names and sql col names (set via attribute) and sets up mapping to both. Check query tests for more details.

Please note as most sql servers implementations are not case sensitive nor is the mapper. Meaning having 2 fields named the same thing with different casing in the same class this may result in undesirable behaviour.