I tend to work on open source projects over Christmas break. Last year I developed DTMF and AliaSQL. The year before I developed Dapper.SimpleCRUD and the companion Model Generator but never blogged about them. Since their initial release, SimpleCRUD had over 3000 downloads on Nuget while the Model Generator has had another 1200 or so. Not huge numbers but it is definitely a surprise to me that random folks around the world are using something I made in my free time.
The Github project has 50 or so stars and 30 forks that resulted in quite a backlog of feature requests. Over the past few weeks, I worked on getting these caught up and released a new version. Since I haven't blogged about it before, I will start with a basic rundown of the project and finish up by going over what's new.
Dapper.SimpleCRUD is a single file you can drop in to your project that will extend your IDbConnection interface.
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 7 helpers that will generate basic read/insert/update/delete statements.
- Get(id) - gets one record based on the primary key
- GetList() - gets list of records all records from a table
- GetList(anonymous object for where clause) - gets list of all records matching the where options
- Insert(entity) - Inserts a record and returns the new primary key
- Update(entity) - Updates a record
- Delete(id) - Deletes a record based on primary key
- Delete(entity) - Deletes a record based on the typed entity
As a basic example, this code:
var user = connection.Get<User>(1); Results in executing this SQL:
Select * from [User] where Id = 1
There are plenty of additional examples for insert, update, delete at https://github.com/ericdc1/Dapper.SimpleCRUD
You can autogenerate your POCOs directly from your database using this T4 Template.
You can also install it using Nuget
You specify a connection string (from web.config or app.config) and set a namespace and the template will generate a class per table with necessary attributes. Primary Key fields are marked with the [Key] attribute. The classes for the table names are converted to singular form (Users becomes User) and the [Table("Name")] attribute is applied.
The class and each property is virtual so you can override them in your own classes to add things like data annotations.
Check out the sample website project for example usage.
Based on recent pull requests and issues, the following was added to version 1.6:
- Async methods of all 7 helpers
- Support for Postgres
- Support for GUID (uniqueidentifer) primary keys
- Removed support for SQLCE
- Updated sample website project to use LocalDB
- Added unit tests for table, column, and schema encapsulation
- Added unit tests for Postgres
- Added unit tests for async methods
Please keep the feedback coming.