Back to Posts

Dapper Tutorial - Part 3: Simple CRUD

Posted in C#, Dapper

Intro

SimpleCRUD is class library and extension of Dapper writen by Eric Coffman. This extension contains 8 helper methods.

  • 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
  • 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

Installation

  • You can either download the SimpleCRUD.cs file from here, and if you want dynamic support like async you need to dofnload this file also SimpleCRUDAsync.cs.
  • You can install it from NuGet with this command Install-Package Dapper.SimpleCRUD or via NuGet Package Explorer too

Attributes

This attributes are used to modify behavior of SimpleCRUD by applying attributes to classes and properties.

  • [Table(“YourTableName”)]: By default table name should be same as class name, this tag overrides this behavior
  • [Column(“YourColumnName”]: By default column name shold be same as property name, this tag overrides this behavior
  • [Key]: By default for Key will be considered integer property named Id, but with this tag you can set any property to be Key
  • [Required]: By default Key propery is not inserted and is expected to be autogenerated from DB. You can mark property with Key and Required if you want to insert non-generated Key
  • [Editable(false)]: Mark properties that are computed (like FullName that is combined from FirstName and LastName) and that you want to be excluded from all CRUD operations
  • [ReadOnly(true)]: Use this attribute to exclude properties from insert and update
  • [IgnoreSelect]: Exclude property from select
  • [IgnoreInsert]: Exclude property from insert
  • [IgnoreUpdate]: Exclude property from update
  • [NotMapped]: Excludes the property from all CRUD operations

Usage

Get(int id)

var entity = Db.Get<Models.Model>(150);

result

{"Id":150,"Name":"640I","Title":" - 640i","SViews":0,"BrandId":8,"Brand":null}

GetList()

var entity = Db.GetList<Models.Brand>();

[{"Id":1,"Name":"ACURA","Title":"Acura","SViews":0,"CountryId":null,"Country":null},
{"Id":2,"Name":"ALFA","Title":"Alfa Romeo","SViews":0,"CountryId":null,"Country":null},
..]

GetList(object whereConditions)

var entity = Db.GetList<Models.Brand>(new {SViews = 10});

[{"Id":4,"Name":"ASTON","Title":"Aston Martin","SViews":10,"CountryId":null,"Country":null},
{"Id":8,"Name":"BMW","Title":"BMW","SViews":10,"CountryId":null,"Country":null},
{"Id":43,"Name":"MCLAREN","Title":"McLaren","SViews":10,"CountryId":null,"Country":null}]

GetList(string whereConditions, object objWhereConditons = null)

var entity = Db.GetList<Models.Brand>("where SViews = 10");

[{"Id":1,"Name":"ACURA","Title":"Acura","SViews":0,"CountryId":null,"Country":null},
{"Id":2,"Name":"ALFA","Title":"Alfa Romeo","SViews":0,"CountryId":null,"Country":null},
..]

GetListPaged(int pageNumber, int rowsPerPage, string conditions, string orderBy, object parameters = null)

var entity = Db.GetListPaged<Models.Brand>(1, 5, "where Name like '%A%'", "SViews desc");

Select first page, with 5 results per page, filtered by name, and ordered by SViews in descending order. The result is:

[{"Id":4,"Name":"ASTON","Title":"Aston Martin","SViews":10,"CountryId":null,"Country":null},
{"Id":43,"Name":"MCLAREN","Title":"McLaren","SViews":10,"CountryId":null,"Country":null},
{"Id":49,"Name":"NISSAN","Title":"Nissan","SViews":0,"CountryId":null,"Country":null},
{"Id":55,"Name":"RAM","Title":"RAM","SViews":0,"CountryId":null,"Country":null},
{"Id":58,"Name":"SAAB","Title":"Saab","SViews":0,"CountryId":null,"Country":null}]

and for the second page

var entity = Db.GetListPaged<Models.Brand>(2, 5, "where Name like '%A%'", "SViews desc");

[{"Id":59,"Name":"SATURN","Title":"Saturn","SViews":0,"CountryId":null,"Country":null},
{"Id":61,"Name":"SMART","Title":"smart","SViews":0,"CountryId":null,"Country":null},
{"Id":66,"Name":"TESLA","Title":"Tesla","SViews":0,"CountryId":null,"Country":null},
{"Id":67,"Name":"TOYOTA","Title":"Toyota","SViews":0,"CountryId":null,"Country":null},
{"Id":1,"Name":"ACURA","Title":"Acura","SViews":0,"CountryId":null,"Country":null}]

Insert(object entityToInsert)

var entityId = Db.Insert(new Models.Brand { Name = "brandName", Title = "brandTitle"});

returns the Id of the newly inserted entity

Update(object entityToUpdate)

var rowsAffected = Db.Update(someBrand);

returns number of affected rows.

Delete(object entityToDelete)

var rowsAffected = Db.Delete(someBrand);

returns number of affected rows.

Delete(object whereCondition)

var rowsAffected = Db.DeleteList<Models.Brand>(new {SViews = 0});

returns number of affected rows.

Delete(string whereCondition, object objWhereCondition = null)

var rowsAffected = Db.DeleteList<Models.Brand>("where SViews < 5");

returns number of affected rows.

RecordCount(string whereCondition = “”, object objWhereCondition = null)

var rowsAffected = Db.RecordCount<Models.Brand>("where SViews < 5");
//or
var rowsAffected = Db.RecordCount<Models.Brand>("where SViews < @SV", new {SV = 5});

returns number of affected rows.

Source
Read Next

Dapper Tutorial - Part 2: Dapper Queries