Comparison of DapperPoco and petapocodapper

Comparison of DapperPoco and petapocodapper
Why repeat the wheel?

I am not satisfied with the existing wheels in some aspects. Let's review them one by one. Welcome to make a brick.

Entity Framework

I like the framework of the usage method with no loss of flexibility.

Although EF is easy to use, it is not flexible enough. For example, in EF Core, you cannot use native SQL to write a multi-table join query (the returned result is the result of Multi-table join)

Simple conditional queries for a single table are fine. The performance of the SQL statements generated during multi-table queries is not flattering. I prefer to write SQL statements by myself, which is more controllable.

EF design is not conducive to project layering. My ideal design is isolation and low coupling. It is good to deal with databases at the Db layer. All database features are isolated within the Db layer, provide silly APIs for external users based on their business needs.
With EF, you cannot achieve real isolation.

Dapper

I like its lightweight and high performance. However, it only supports native SQL to read and write databases, which is not easy to use.

Many common scenarios can encapsulate SQL statements. Like EF, Add an Entity directly.

Although Dapper already has such an encapsulation, it is too rough.

PetaPoco

It can directly Add an Entity like EF, or write native SQL like Dapper, which is already perfect.

However, it does not support batch insertion or update.

DapperPoco

In the absence of a satisfactory framework, DapperPoco was born. It is highly encapsulated based on Dapper, has all the advantages of Dapper, and also makes up for its shortcomings, it has the following features:

It is now open-source and available on github at: https://github.com/md-frank/DapperPoco

How to use it first defines a Poco class

// Indicates a public class Article {public long Id {get; set;} public string Title {get; set ;}public string Content {get; set ;}}

Create DbContext

Class MasterDbContext: DbContext {protected override void OnConfiguring (DbContextOptionsBuilder optionsBuilder) {optionsBuilder. useConnectionString ("connection string"); // use SQL Server database optionsBuilder. useSqlAdapter (new SqlServerAdapter (SqlClientFactory. instance);} // If Poco is not used, the protected override void OnEntitiesBuilding (EntitiesBuilder entityBuilder) {// The attribute name is different from the table column name (column name, you can map the alias entityBuilder here. entity <Article> (). tableName ("T_Article "). columnName (p => p. id, "article_id ");}}

Insert data

Var masterDb = new MasterDbContext (); // insert a Poco object var a = new Article {Title = "hello", Content = "hello word"}; masterDb. insert (a); // Insert two records in masterDb. insert (new Article [] {a, a}); // You can also explicitly specify the table name masterDb. insert (a, "T_Article"); // native SQL inserts this. execute ("insert T_Article (Title, Content) values (@ Title, @ Content)", a); // two records are inserted this. execute ("insert T_Article (Title, Content) values (@ Title, @ Content)", a, a); // two records are inserted this. execute ("insert T_Article (Title, Content) values (@ Title, @ Content)", new Article [] {a, }); // You can also directly write the parameter value this. execute ("insert T_Article (Title, Content) values (@ p0, @ p1)", "hello", "hello word ");

Update Data

Var masterDb = new MasterDbContext (); // first check out and prepare to update var article = masterDb. firstOrDefault <Article> ("select * from T_Article where article_id = @ p0", 1); // update all the columns except the primary key. title = "hello 2"; article. content = "content 1"; masterDb. update (article); // Update only the specified column and specify the table column name article. title = "hello 2"; masterDb. update (article, new [] {"Title"}); // Update only the specified column and specify the object attribute name article. title = "hello 3"; article. content = "content 1"; masterDb. update (article, null, null, p => p. title, p => p. content );

Save data

Var masterDb = new MasterDbContext (); var article = new Article {Id = 1, Title = "hello", Content = "hello word"}; // update if the record exists, if it does not exist, insert masterDb. save (article); // Save and specify the masterDb column name. save (article, new [] {"Title "});

Delete data

Var masterDb = new MasterDbContext (); var article = masterDb. firstOrDefault <Article> ("select * from T_Article where article_id = @ p0", 1); // delete an entity record masterDb. delete (article); // Delete an object record, explicitly specifying the primary key name masterDb. delete (article, "article_id ");

Query data (execute now)

Var masterDb = new MasterDbContext (); // query all records in the T_Article table var articles = masterDb. fetchAll <Article> (); // specify the condition for query. directly write the parameter value var articles = masterDb. fetch <Article> ("select * from T_Article where Title = @ p0 and Content = @ p1", "hello", "hello word"); // query by specified conditions, support List (implemented by the IEnumerable Interface) var articles = masterDb. fetch <Article> ("select * from T_Article where article_id in @ p0", new [] {1, 2, 3}); // query a single record masterDb. firstOrDefault <Article> ("select * from T_Article where article_id = @ p0", 1); // query a single column var count = masterDb. executeScalar <long> ("select count (*) from T_Article"); // query the paging results (1st pages, 20 entries per page) Paged <Article> paged = masterDb. paged <Article> (1, 20, "select * from T_Article where Title = @ p0", "hello "); // The definition of Paged is as follows: public class Paged <T> where T: new () {// current page number public int CurrentPage {get; set ;} // total number of pages public int TotalPages {get; set ;}/// total number of records public long TotalItems {get; set ;}// number of records per page public int ItemsPerPage {get; set ;}// public List of records on the current page <T> Items {get; set ;}}

Query data (delayed execution)

Query is used for delayed Query. Different from Fetch, The results returned by a Query are only used to Query the database.

Var masterDb = new MasterDbContext (); // delay Query var articles = masterDb. Query <Article> ("select * from T_Article where Title = @ p0", "hello ");

Dynamic query Conditions

Var title = "this variable comes from user input"; var sb = new SqlBuilder (); sb. Append ("select * from T_Article"); if (! String. isNullOrEmpty (title) sb. append ("where Title = @ p0", title); var SQL = sb. build (); var articles = masterDb. fetch <Article> (SQL. statement, SQL. parameters );

Transaction support

Using (var trans = this. GetTransaction () {// modify the database here // submit the transaction trans. Complete ();}