PetaPoco - Easy and fast way to perform Database related operations in .net

PetaPoco is easy and fast way to perform Database related operations in .net. If you have worked with Entity Framework then you must be knowing how this help developer to write less code as compare to ADO.net to interact with your database to run DDL, DML and DQL queries.

And here I am talking about PetaPoco, and to understand simple work this is quite similar to Entity Framework but have must more functionality as compare to EF for developer like-

1. You can run your SQL query without defining your model as you have to with EF.
2. This have sync and async methods both for all the DB related operations.
3. For select query this also give you option to run paging query without doing anything at your end.
4. This is very easy to install and run with your project.
5. This is secure from SQL injection point of view.

Let's more to some coding test-

Fyi - Below code is done in console application .net core 3.1

1. Install "PetaPoco.Compiled" from nuget into your project.
2. Below example - where I am running select query as page in two different way and as result you can see I am getting items along with total item, page size and total pages.

3. Here you can see how you can access delete method similar to select.

4. Here you can see how you can access insert method similar to select.

5. Here you can see how you can access fetch method similar to select. Difference between Page and Fetch query is that fetch just run select and provide your result but Page is where it run like paging and mainly used grid or lazy loading concept.

6. Here you can see how you can run transaction similar to ACID in sql concept.

7. Here you can see how you can apply left join with my select query.

           sql.LeftJoin("Point_Member b").On("a.id=b.memberid");

8. Here you can see if I want to build just one query so I can use sql builder to build all my query together with the help of append of sql builder.

           var sql = PetaPoco.Sql.Builder
           .Append("select a.*,b.points")

           .Append("frm mmeber a")

           .Append("inner join member_points b")

           .Append("on a.id=b.memberid")

           .Append("where a.id=@0", member_id)

9. Here you can see how you can apply where condition or IN key word with my query.

           sql.Where("a.createdOn>@0", startDateTime);

           //Or in query

           sql.Where("a.id in (@0)", member_ids);

           //here member_ids is list i.e. var member_ids= new List<int>);

10. Here you can see how you can convert my result set into json if do not have any response model class.

           var items = db.Page<dynamic>(pageNumber, itemsPerPage, sql);
           rta = new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };

Decorating Your POCOs - fyi - Instead manual you can achieve this just generating all your DB table as model with "PetaPoco.DBEntityGenerator". I will talk on this in my next article.

Manual Step : In the above examples, it's a pain to have to specify the table name and primary key all over the place, so you can attach this info to your POCO:

[PetaPoco.TableName("Member")]
[PetaPoco.PrimaryKey("id")]
public class Member{
    public string FirstName { get; set; }   
    public string LastName { get; set; }

Now inserts, updates and deletes get simplified to this:

// Insert a record
var a = new Member();
a.FirstName = "Rajeev";
a.LastName = "Jha";
db.Insert(a);

// Update it
a.FirstName = "Rajeev New";
db.Update(a);

// Delete it
db.Delete(a);