DapperGlib 1.0.0

This is a beta phase of the project, it is still under construction!!

Dapper.DapperGlib

Introduction

This Dapper extension will allow you to map the database in an easy way, inspired by Laravel Eloquent. Has Relationships, Query Builder, Basic Where Clauses, Advanced Where Clauses, Ordering and more. If you have any questions, suggestions or bugs, please don't hesitate to contact me or create an issue.

Download

Install-Package DapperGlib

Configuration

in the appsettings.json file placed in the root folder of your startup project you must define the connection string

{
  "ConnectionStrings": {
    "SqlConnection": "Your connection string"
  }
}

Getting Started

#Model Conventions

As I mentioned before DapperGlib is inspired by Eloquent Laravel, so we will be working with Models. let's examine a basic model class and explain each part.

using DapperGlib;

namespace App.Models
{
    public class Actor : Model<Actor>
    {
        public Actor()
        {
        }
    }
}

#Table Names

After looking at the example above, you may have noticed that we didn't specify which database table corresponds to our Actor Model. This is because the name of the Class will be used as the name of the table unless another name is explicitly specified. So in this case it will be assumed that the Actor model stores records in the Actor table

If your model's corresponding database table does not fit this convention, you may manually specify the model's table name by defining a table property on the model:

public class Actor : Model<Actor>
{
    [TableName]
    public override string? Table { get; } = "Actors";
    ...
}

Note that the TableName attribute must be used on the Property

#Primary Keys

Unlike the table name, the primary key property must always be specified. The primary key will be used for action queries like Update, Delete, Etc. We need to use the PrimaryKey Attribute on the Property.

public class Actor : Model<Actor>
{
    [PrimaryKey]
    public int ActorId { get; set; }
    ...
}

In addition, We assumes that the primary key is an incrementing integer value

#Fillable Properties

Properties that are to be filled and are defined in the Model table must be specified with the Fillable attribute.

public class Actor : Model<Actor>
{
    [Fillable]
    public string Name { get; set; }
    ...
}

#Inserting & Updating Models

Once you have created a model and its associated database table, you are ready to start interacting with data from your database. You can think of each Model as a query builder allowing you to fluently query the database table associated with the model.

#Insert

Now insert records will be very simple.
To insert a new record into the database, you should instantiate a new Model instance and set attributes on the Model. Then, call the Create static method on the Model instance:

Actor ActorToSave = new();

ActorToSave.Name = "Keanu";
ActorToSave.LastName = "Reeves";

Actor NewActor = Actor.Create(ActorToSave);

You can keep it simpler by using:

Actor NewActor = Actor.Create(new()
{
    Name = "Keanu",
    LastName = "Reeves"
});

#Update

You can use the Update function to update an existing Model in the database.
The Update function will create a query based on ALL Fillable Properties and the Primary Key and update the record in the database.
Following the example above:

NewActor.Name = "Tom";
NewActor.LastName = "Hanks";
NewActor.Update(); 

If you want to update only specific properties you can pass an anonymous object to the Update function

NewActor.Update(new
{
    LastName = "Holland",
});

#Retrieving Models

Of course you can get data from your database using very simple functions.
The model's ToList method will retrieve all of the records from the model's associated database table:

List<Actor> Actors = Actor.ToList();

and like this, the Model has a list of functions that will allow you to retrieve data from the database quickly

• First
• FirstOrDefault
• FirstAsync
• FirstOrDefaultAsync
• Find
• FindAsync
• FindOrDefault
• FindOrDefaultAsync
• ToList
• ToListAsync

#Deleting Models

You can delete a record using the Delete function

NewActor.Delete();

Query Builder

Query Builder provides a convenient fluent interface for creating and executing database queries. It can be used to perform most database operations.

#Basic Where Clauses

#Where Clauses

You may use the query builder's where method to add "where" clauses to the query. The most basic call to the where method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.

var actor = Actor.Where("Name", "=", "Tom").First();

If you want to verify that a column is = to a given value, you may pass the value as the second argument to the where method.
The Query Builder will assume you would like to use the = operator:

var actor = Actor.Where("Name", "Tom").First();

As previously mentioned, you may use any operator that is supported by your database system:

var actor = Actor.Where("Name", "like", "%Tom%").First();

var actor = Actor.Where("Votes", ">=", 80).First();

#Or Where Clauses

When chaining together calls to the query builder's where method, the "where" clauses will be joined together using the and operator.
However, you may use the orWhere method to join a clause to the query using the or operator. The orWhere method accepts the same arguments as the where method:

var actor = Actor.Where("Name","Tom").OrWhere("Name", "Keanu").First();

#Additional Where Clauses

WhereBetween / WhereDateBetween

The WhereBetween method verifies that a column's value is between two values.
Receives two parameters, the first is the column's name and the second is an instance of the DapperGlib.Util.Between class which only has From and To properties

List<Actor> Actors = Actor.WhereBetween("Votes", new()
{
    From = 50,
    To = 100
}).ToList();