Dapper.SimpleCRUD - simple CRUD helpers for Dapper

Fork

This is a fork of Dapper.SimpleCRUD that has the following enhancements:

• MultiQuery<T1,T2,T3(,...,T8)> - Enhanced version of Dapper.Query<T1,T2,T3(,...T8)> that uses a special syntax and templating engine to simplify a couple of common use cases. See below.
• Mapper that supports all-lowercase column names (PostgreSQL)
• Introduced EnumString<T> wrapper around enums to persist the enum as a string.

MultiQuery<>

The following example shows a couple of features of MultiQuery:

var posts = connection.MultiQuery<Post, User, Post>(@"
SELECT p.* ||| u.*
FROM Posts p, Users u
WHERE p.AuthorId = u.Id
@Id?{AND p.""Id"" = @Id}
@FilterByDayOff?{AND u.""ScheduledDayOff"" = @ScheduledDayOff }
@OrderBy?{ORDER BY p.@@OrderBy}:{ORDER BY p.id DESC}", (p, u) =>
                {
                    p.User = u;
                    return p;
                }, new {
                    ScheduledDayOff = DayOfWeek.Monday.ToString(),
                    FilterByDayOff = false,
                    OrderBy = "date DESC"
                });

• You can use a "|||" delimiter between tables. This is automatically replaced by a dummy column name that is specified as the "splitOn" for Dapper. The main reason to introduce this is so that you do not have to know which column of each table comes first; it might change over time or be different across multiple database (version)s.

• The objects that are returned by Dapper's multi query -- in this example p and u -- are set to null if their primary key is not set, for instance when u.Id equals 0. This way it is easier to spot and handle (non existent) entities that could not be joined.

• You can use syntax @Param?{ ... } to optionally include the block if the parameter named 'Param' exists AND is not null or false. You can also use syntax @Param?{ ... }:{ ... } where the last block is executed as an 'else' block (i.e. when the parameter does not exist or it is null or false).

• You can insert the value of a parameter literally using syntax @@Param. This should be used only with TRUSTED INPUT as it injects values directly into the SQL. Use cases are ORDER BY or LIMIT clauses that are specified server-side.

Features

Dapper.SimpleCRUD is a [single file](https://github.com/ericdc1/Dapper.SimpleCRUD/blob/master/Dapper.SimpleCRUD/SimpleCRUD.cs) you can drop in to your project that will extend your IDbConnection interface. (If you want dynamic support, you need an [additional file](https://github.com/ericdc1/Dapper.SimpleCRUD/blob/master/Dapper.SimpleCRUD/SimpleCRUDAsync.cs).)

Who wants to write basic read/insert/update/delete statements?

The existing Dapper extensions did not fit my ideal pattern. I wanted simple CRUD operations with smart defaults without anything extra. I also wanted to have models with additional properties that did not directly map to the database. For example - a FullName property that combines FirstName and LastName in its getter - and not add FullName to the Insert and Update statements.

I wanted the primary key column to be Id in most cases but allow overriding with an attribute.

Finally, I wanted the table name to match the class name by default but allow overriding with an attribute.

This extension adds the following 8 helpers:

• Get(id) - gets one record based on the primary key
• GetList<Type>() - gets list of records all records from a table
• GetList<Type>(anonymous object for where clause) - gets list of all records matching the where options
• GetList<Type>(string for conditions, anonymous object with parameters) - gets list of all records matching the conditions
• GetListPaged<Type>(int pagenumber, int itemsperpage, string for conditions, string for order, anonymous object with parameters) - gets paged list of all records matching the conditions
• Insert(entity) - Inserts a record and returns the new primary key (assumes int primary key)
• Insert<Guid,T>(entity) - Inserts a record and returns the new guid primary key
• Update(entity) - Updates a record
• Delete<Type>(id) - Deletes a record based on primary key
• Delete(entity) - Deletes a record based on the typed entity
• DeleteList<Type>(anonymous object for where clause) - deletes all records matching the where options
• DeleteList<Type>(string for conditions, anonymous object with parameters) - deletes list of all records matching the conditions
• RecordCount<Type>(string for conditions,anonymous object with parameters) -gets count of all records matching the conditions

For projects targeting .NET 4.5 or later, the following 8 helpers exist for async operations:

• GetAsync(id) - gets one record based on the primary key
• GetListAsync<Type>() - gets list of records all records from a table
• GetListAsync<Type>(anonymous object for where clause) - gets list of all records matching the where options
• GetListAsync<Type>(string for conditions, anonymous object with parameters) - gets list of all records matching the conditions
• GetListPagedAsync<Type>(int pagenumber, int itemsperpage, string for conditions, string for order, anonymous object with parameters) - gets paged list of all records matching the conditions
• InsertAsync(entity) - Inserts a record and returns the new primary key (assumes int primary key)
• InsertAsync<Guid,T>(entity) - Inserts a record and returns the new guid primary key
• UpdateAsync(entity) - Updates a record
• DeleteAsync<Type>(id) - Deletes a record based on primary key
• DeleteAsync(entity) - Deletes a record based on the typed entity
• DeleteListAsync<Type>(anonymous object for where clause) - deletes all records matching the where options
• DeleteListAsync<Type>(string for conditions, anonymous object with parameters) - deletes list of all records matching the conditions
• RecordCountAsync<Type>(string for conditions, anonymous object with parameters) -gets count of all records matching the conditions

If you need something more complex use Dapper's Query or Execute methods!

Note: all extension methods assume the connection is already open, they will fail if the connection is closed.

Install via NuGet - https://nuget.org/packages/Dapper.SimpleCRUD

Check out the model generator T4 template to generate your POCOs. Documentation is at https://github.com/ericdc1/Dapper.SimpleCRUD/wiki/T4-Template

Get a single record mapped to a strongly typed object

 public static T Get<T>(this IDbConnection connection, int id)

Example basic usage:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}
      
var user = connection.Get<User>(1);   

Results in executing this SQL

Select Id, Name, Age from [User] where Id = 1 

More complex example:

    [Table("Users")]
    public class User
    {
        [Key]
        public int UserId { get; set; }
        [Column("strFirstName")]
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }
    }
    
    var user = connection.Get<User>(1);  

Results in executing this SQL

Select UserId, strFirstName as FirstName, LastName, Age from [Users] where UserId = @UserID

Notes:

• The [Key] attribute can be used from the Dapper namespace or from System.ComponentModel.DataAnnotations

• The [Table] attribute can be used from the Dapper namespace, System.ComponentModel.DataAnnotations.Schema, or System.Data.Linq.Mapping - By default the database table name will match the model name but it can be overridden with this.

• The [Column] attribute can be used from the Dapper namespace, System.ComponentModel.DataAnnotations.Schema, or System.Data.Linq.Mapping - By default the column name will match the property name but it can be overridden with this. You can even use the model property names in the where clause anonymous object and SimpleCRUD will generate a proper where clause to match the database based on the column attribute

• GUID (uniqueidentifier) primary keys are supported (autopopulates if no value is passed in)

Execute a query and map the results to a strongly typed List

public static IEnumerable<T> GetList<T>(this IDbConnection connection)

Example usage:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

var user = connection.GetList<User>();  

Results in

Select * from [User]

Execute a query with where conditions and map the results to a strongly typed List

public static IEnumerable<T> GetList<T>(this IDbConnection connection, object whereConditions)

Example usage:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}
  
var user = connection.GetList<User>(new { Age = 10 });  

Results in

Select * from [User] where Age = @Age

Notes:

• To get all records use an empty anonymous object - new{}
• The where options are mapped as "where [name] = [value]"
• If you need > < like, etc simply use the manual where clause method or Dapper's Query method
• By default the select statement would include all properties in the class - The IgnoreSelect attributes remove items from the select statement

Execute a query with a where clause and map the results to a strongly typed List

public static IEnumerable<T> GetList<T>(this IDbConnection connection, string conditions, object parameters = null)

Example usage:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}
  
var user = connection.GetList<User>("where age = 10 or Name like '%Smith%'");  

or with parameters

var encodeForLike = term => term.Replace("[", "[[]").Replace("%", "[%]");
string likename = "%" + encodeForLike("Smith") + "%";
var user = connection.GetList<User>("where age = @Age or Name like @Name", new {Age = 10, Name = likename});  

Results in

Select * from [User] where age = 10 or Name like '%Smith%'

Notes:

• This uses your raw SQL so be careful to not create SQL injection holes or use the Parameters option
• There is nothing stopping you from adding an order by clause using this method

Execute a query with a where clause and map the results to a strongly typed List with Paging

public static IEnumerable<T> GetListPaged<T>(this IDbConnection connection, int pageNumber, int rowsPerPage, string conditions, string orderby, object parameters = null)

Example usage:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}
  
var user = connection.GetListPaged<User>(1,10,"where age = 10 or Name like '%Smith%'","Name desc");  

Results in (SQL Server dialect)

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Name desc) AS PagedNumber, Id, Name, Age FROM [User] where age = 10 or Name like '%Smith%') AS u WHERE PagedNUMBER BETWEEN ((1 - 1) * 10 + 1) AND (1 * 10)

or with parameters

var user = connection.GetListPaged<User>(1,10,"where age = @Age","Name desc", new {Age = 10});  

Results in (SQL Server dialect)

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Name desc) AS PagedNumber, Id, Name, Age FROM [User] where age = 10) AS u WHERE PagedNUMBER BETWEEN ((1 - 1) * 10 + 1) AND (1 * 10)

Notes:

• This uses your raw SQL so be careful to not create SQL injection holes or use the Parameters option
• It is recommended to use https://github.com/martijnboland/MvcPaging for the paging helper for your views
  • @Html.Pager(10, 1, 100) - items per page, page number, total records

Insert a record

public static int Insert(this IDbConnection connection, object entityToInsert)

Example usage:

[Table("Users")]
public class User
{
   [Key]
   public int UserId { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public int Age { get; set; }

   //Additional properties not in database
   [Editable(false)]
   public string FullName { get { return string.Format("{0} {1}", FirstName, LastName); } }
   public List<User> Friends { get; set; }
   [ReadOnly(true)]
   public DateTime CreatedDate { get; set; }
}

var newId = connection.Insert(new User { FirstName = "User", LastName = "Person",  Age = 10 });  

Results in executing this SQL

Insert into [Users] (FirstName, LastName, Age) VALUES (@FirstName, @LastName, @Age)

Notes:

• Default table name would match the class name - The Table attribute overrides this
• Default primary key would be Id - The Key attribute overrides this
• By default the insert statement would include all properties in the class - The Editable(false), ReadOnly(true), and IgnoreInsert attributes remove items from the insert statement
• Properties decorated with ReadOnly(true) are only used for selects
• Complex types are not included in the insert statement - This keeps the List out of the insert even without the Editable attribute. You can include complex types if you decorate them with Editable(true). This is useful for enumerators.

Insert a record with Guid key

public static int Insert<Guid,T>(this IDbConnection connection, object entityToInsert)

Example usage:

[Table("Users")]
public class User
{
   [Key]
   public Guid GuidKey { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public int Age { get; set; }
}

var newGuid = connection.Insert<Guid,User>(new User { FirstName = "User", LastName = "Person",  Age = 10 });  

Results in executing this SQL

Insert into [Users] (FirstName, LastName, Age) VALUES (@FirstName, @LastName, @Age)

Update a record

public static int Update(this IDbConnection connection, object entityToUpdate)

Example usage:

[Table("Users")]
public class User
{
   [Key]
   public int UserId { get; set; }
   [Column("strFirstName")]
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public int Age { get; set; }

   //Additional properties not in database
   [Editable(false)]
   public string FullName { get { return string.Format("{0} {1}", FirstName, LastName); } }
   public List<User> Friends { get; set; }
}
connection.Update(entity);

Results in executing this SQL

Update [Users] Set (strFirstName=@FirstName, LastName=@LastName, Age=@Age) Where ID = @ID

Notes:

• By default the update statement would include all properties in the class - The Editable(false), ReadOnly(true), and IgnoreUpdate attributes remove items from the update statement

Delete a record

public static int Delete<T>(this IDbConnection connection, int Id)

Example usage:

public class User
{
   public int Id { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public int Age { get; set; }
}
connection.Delete<User>(newid);

Or

public static int Delete<T>(this IDbConnection connection, T entityToDelete)

Example usage:

public class User
{
   public int Id { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public int Age { get; set; }
}

connection.Delete(entity);

Results in executing this SQL

Delete From [User] Where ID = @ID

Delete multiple records with where conditions

public static int DeleteList<T>(this IDbConnection connection, object whereConditions, IDbTransaction transaction = null, int? commandTimeout = null)

Example usage:

connection.DeleteList<User>(new { Age = 10 });

Delete multiple records with where clause

public static int DeleteList<T>(this IDbConnection connection, string conditions, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null)

Example usage:

connection.DeleteList<User>("Where age > 20");

or with parameters

connection.DeleteList<User>("Where age > @Age", new {Age = 20});

Get count of records

public static int RecordCount<T>(this IDbConnection connection, string conditions = "", object parameters = null)

Example usage:

var count = connection.RecordCount<User>("Where age > 20");

or with parameters

var count = connection.RecordCount<User>("Where age > @Age", new {Age = 20});

Custom table and column name resolvers

You can also change the format of table and column names, first create a class implimenting the ITableNameResolver and/or IColumnNameResolver interfaces

public class CustomResolver : SimpleCRUD.ITableNameResolver, SimpleCRUD.IColumnNameResolver
{
    public string ResolveTableName(Type type)
    {
        return string.Format("tbl_{0}", type.Name);
    }

    public string ResolveColumnName(PropertyInfo propertyInfo)
    {
        return string.Format("{0}_{1}", propertyInfo.DeclaringType.Name, propertyInfo.Name);
    }
}

then apply the resolvers when intializing your application

    var resolver = new CustomResolver();
    SimpleCRUD.SetTableNameResolver(resolver);
    SimpleCRUD.SetColumnNameResolver(resolver);

Database support

• There is an option to change database dialect. Default is Microsoft SQL Server but can be changed to PostgreSQL or MySQL. We dropped SQLite support with the .Net Core release.

   SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
    
   SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);

Attributes

The following attributes can be applied to properties in your model

[Table("YourTableName")] - By default the database table name will match the model name but it can be overridden with this.

[Column("YourColumnName"] - By default the column name will match the property name but it can be overridden with this. You can even use the model property names in the where clause anonymous object and SimpleCRUD will generate a proper where clause to match the database based on the column attribute

[Key] -By default the Id integer field is considered the primary key and is excluded from insert. The [Key] attribute lets you specify any Int or Guid as the primary key.

[Required] - By default the [Key] property is not inserted as it is expected to be an autoincremented by the database. You can mark a property as a [Key] and [Required] if you want to specify the value yourself during the insert.

[Editable(false)] - By default the select, insert, and update statements include all properties in the class - The Editable(false) and attribute excludes the property from being included. A good example for this is a FullName property that is derived from combining FirstName and Lastname in the model but the FullName field isn't actually in the database. Complex types are not included in the insert statement - This keeps the List out of the insert even without the Editable attribute.

[ReadOnly(true)] - Properties decorated with ReadOnly(true) are only used for selects and are excluded from inserts and updates. This would be useful for fields like CreatedDate where the database generates the date on insert and you never want to modify it.

[IgnoreSelect] - Excludes the property from selects

[IgnoreInsert] - Excludes the property from inserts

[IgnoreUpdate] - Excludes the property from updates

[NotMapped] - Excludes the property from all operations

Do you have a comprehensive list of examples?

Dapper.SimpleCRUD has a basic test suite in the test project