Skip to main content

CRUD operations with PetaPoco and ASP.NET MVC

In this post we are going to see how we can do CRUD operations with ASP.NET MVC and PetaPoco Micro ORM.

What is PetaPoco?


Petapoco is a tiny ORM developed by topten software. As per them it’s a tiny, fast, single-file micro-ORM for .NET and Mono.
  • Like Massive it's a single file that you easily add to any project
  • Unlike Massive it works with strongly typed POCO's
  • Like Massive, it now also supports dynamic Expandos too - read more
  • Like ActiveRecord, it supports a close relationship between object and database table
  • Like SubSonic, it supports generation of poco classes with T4 templates
  • Like Dapper, it's fast because it uses dynamic method generation (MSIL) to assign column values to properties

Features of PetaPoco:


As per topten software it contains following features.
  • Tiny, no dependencies... a single C# file you can easily add to any project.
  • Works with strictly undecorated POCOs, or attributed almost-POCOs.
  • Helper methods for Insert/Delete/Update/Save and IsNew
  • Paged requests automatically work out total record count and fetch a specific page.
  • Easy transaction support.
  • Better parameter replacement support, including grabbing named parameters from object properties.
  • Great performance by eliminating Linq and fast property assignment with DynamicMethod generation.
  • Includes T4 templates to automatically generate POCO classes for you.
  • The query language is SQL... no weird fluent or Linq syntaxes (yes, matter of opinion)
  • Includes a low friction SQL builder class that makes writing inline SQL much easier.
  • Hooks for logging exceptions, installing value converters and mapping columns to properties without attributes.
  • Works with SQL Server, SQL Server CE, MySQL, PostgreSQL and Oracle.
  • Works under .NET 3.5 or Mono 2.6 and later.
  • Experimental support for dynamic under .NET 4.0 and Mono 2.8
  • NUnit unit tests.
  • OpenSource (Apache License)
  • All of this in about 1,500 lines of code


How to download:


There are two way you can download this Micro ORM. From Nuget package and GithHub


Examples:


There are lots of examples available on following page.
http://www.toptensoftware.com/petapoco/

Creating table for CRUD operation:


For this example I am going to use SQL Compact edition database. Following a table called “Employee” with 3 fields EmployeeId,FirstName,Lastname.

TableStructureforPetapoco

Same way I have created following Model class similar to above table.

[PetaPoco.TableName("Person")]
public class Employee
{
    public int EmployeeId { get; set; }
    public string  FirstName { get; set; }
    public string LastName { get; set; }
}

Installing NuGet Package for petapoco:


I have installed Petapoco via following command in package manager console.

PetaPocoCommandForNuget

It will install like following.

NuGetPackageForPetaPoco

This will create a PetaPoco.cs file in Model folder. Now we are ready to code so first thing to do is to put a connection string for database in the web.config.

<add
name="sqlserverce"
connectionString="Data Source=C:\Users\Lenovo\Documents\Visual Studio 11\Projects\MvcApplication1\MvcApplication1\App_Data\Employee.sdf"
providerName="System.Data.SqlServerCe.4.0"
/>

Listing/Displaying Employee in ASP.NET MVC and PetaPOCO:


Now its time to write code for the all the operations in controller. Following is a code for index(Listting) in Employee controller. Here you can see I have used the dataContext.Query method of PetaPOCO to fetch data and convert that into Employee class.

public ActionResult Index()
{
    var dataContext = new PetaPoco.Database("sqlserverce");
    var employees = dataContext.Query<Employee>("Select * from Employee");
    return View(employees);
}

So now Index Action Result method is ready so it’s time to create a strongly type view like below.

CreateViewForEmployeeList

So once you click that it will create a strongly type view for listing employees and when you run the code it will look like following.

image

Creating employee in ASP.NET MVC and PetaPOCO:


Following is the code for adding a employee in employee controller. I have created two Action Result Method one for empty form when we create new and another with the httppost attribute to insert employee in database and once insertion is complete it will redirect to list page. If you see the second method I have used db.Insert method to insert data in PetaPOCO. Where we need to pass the table name primary key name and object of employee and it will insert the data.

public ActionResult Create()
{
    return View();  
}

[HttpPost]
public ActionResult Create(Employee employee)
{
    var dataContext = new PetaPoco.Database("sqlserverce");
    dataContext.Insert("Employee", "EmployeeId", employee);
    return RedirectToAction("Index");
}

Now lets create a View for that view via right click view and add view.

AddView

Click on add will create a strongly typed view for Employee and when you click on create new.

image

Updating employee records with ASP.NET MVC and PetaPOCO:


Same way like the Add I have created two Action Result for editing/updating records one will existing record and another will update the existing record with petapoco and redirect page to the listing page.

public ActionResult Edit(int id)
{
    var dataContext = new PetaPoco.Database("sqlserverce");
    var employee = dataContext.Single<Employee>("Select * from Employee where employeeId=@0",
                                                     id);
    return View(employee);
}

[HttpPost]
public ActionResult Edit(Employee employee)
{
    var dataContext = new PetaPoco.Database("sqlserverce");
    dataContext.Update("Employee", "EmployeeId", employee);
    return RedirectToAction("Index");
}

In the first Action Result method I have fetch the single records for that employee Id via Single Method of Petapco. While the second method will update the database with update method of PetaPOCO.

Now it’s time to create Edit view. I have created edit view like following.

image

Now when you click edit page from the listing page it will look like following.

image

Creating Employee detail page with ASP.NET MVC And PetaPOCO:


Creating detailing page is very similar to Edit Page loading except that it will not save or update anything. Following is a code for that.

public ActionResult Details(int id)
{
    var dataContext = new PetaPoco.Database("sqlserverce");
    var employee = dataContext.Single<Employee>("Select * from Employee where employeeId=@0",
                                                     id);
    return View(employee);
}

Now code is ready so I have created strongly typed view for detail like following.

image

Now when you run the page in the browser it will look like following.

image

Creating Delete Page with ASP.NET MVC and PetaPOCO:


You can very easily delete the employee with PetaPOCO with delete method of PetaPOCO where we need to pass type and Id of record just like following. First will load data and ask for confirmation and another will delete the employee record with delete method where we are passing Id and type to delete from the table.

public ActionResult Delete(int id)
{
    var dataContext = new PetaPoco.Database("sqlserverce");
    var employee = dataContext.Single<Employee>("Select * from Employee where employeeId=@0",
                                                     id);
    return View(employee);
}

[HttpPost]
public ActionResult Delete(int id,FormCollection formCollection)
{
    var dataContext = new PetaPoco.Database("sqlserverce");
    dataContext.Delete<Employee>(id);
    return RedirectToAction("Index");
}

Now its to time to create a strongly typed view for delete like following.

image

Now when you run on application the browser it will look like following.

image

Once you click delete it will return back to index/listing page.

That’s it. Hope you like it. Stay tuned more..

Comments

  1. Hi Jalpesh,

    Can we use store procedure in petapoco if yes then provide way how to use

    ReplyDelete
  2. Hello Kirti,

    Yes its possible. I have already posted a blog entry for this.

    http://www.dotnetjalps.com/2011/06/petapoco-with-parameterised-stored.html



    Regards,
    Jalpesh

    ReplyDelete
  3. Nice article its very usefull,

    We can also submit our dotnet related article links on http://www.dotnettechy.com to increase trafic. it is kind of social networking for dotnet professionals only

    ReplyDelete
  4. No Linq = Turn Off. It might be good for spikes, but for an enterprise level application it will fall on it's head.. !!

    ReplyDelete
  5. I don't understand what you are saying can you please tell me in brief.

    ReplyDelete
  6. Hi, There is one issue that i am facing is that, With Sql sub qeury there is count(*) records and in maing query i am refrencing with Petapoco.Resultcolumn, but counter always showing 0, please help me if any idea..There is not much documentaion for Petapoco Result column with sub query count(*) records, on their official site Count(*) to result column example is with main query not with sub query...

    Regards

    ReplyDelete
  7. Hi Kalpesh,


    Can you please send me a code.

    ReplyDelete
  8. This is a very nice article on "CRUD Operations Using Entity Framework in ASP.NET MVC". I have find out some other articles link from which I have learnt "CURD operations using Ajax Model Dialog in ASP.NET MVC!". I think this is very helpful for developers like me.

    http://www.mindstick.com/Articles/279bc324-5be3-4156-a9e9-dd91c971d462/?CRUD%20operation%20using%20Modal%20d

    http://www.dotnet-tricks.com/Tutorial/mvc/42R0171112-CRUD-Operations-using-jQuery-dialog-and-Entity-Framework---MVC-Razor.html

    ReplyDelete
  9. How to insert an image and retrieve,update from database in mvc,

    Please give me complete example on this

    ReplyDelete
  10. please post as soon as possible,

    Send me simple&complete code example for this
    send me my mailid: mandla.anilbabu@gmail.com

    ReplyDelete
  11. very nice explanation of petapoco with MVC....thank you..1

    ReplyDelete

Post a Comment

Your feedback is very important to me. Please provide your feedback via putting comments.

Popular posts from this blog

How to create Rest API(Web API) with ASP.NET Core 1.0

Recently Microsoft has released ASP.NET Core 1.0 RC2, I am getting lots of request from readers that about creating Web API. So I thought it will be a good idea to write a blog post about how to create Rest API(Web API) with ASP.NET Core 1.0.

So let’s get started via creating an ASP.NET Core 1.0 Web Application like following.



Once you click on ASP.NET Web Application, It will ask whether you need to create Web Application or Web API application. We are going to Web API so I am going to select Web API Application like following. Please note that in ASP.NET Core 1.0 there is no separate libraries or DLLs required for creating web APIs. This is just a project template.



Now once you click on OK It will create a Web API application with default values controller and program.cs.  As you know Program.cs is now starting point for the ASP.NET Core 1.0 application so It contains all the required configuration and startup items. Following is a code for that.
using System.IO; using Microsoft.Asp…

How to use migration with Entity Framework Core

Entity framework core is  the lightweight, extensible and cross-platform version of Entity Framework. Before some time, Microsoft has Released a new version of Entity Framework RC2. I have written a couple of blog post about Entity framework code first migration earlier for Entity framework 6.0. So there was a couple of request coming for writing a blog post about Entity Framework Core RC2 migration. So I thought it will be a good idea to give an overview how database migration works in Entity Framework Core RC2. This post will cover a basic scenario where we are going to create the database with existing ASP.NET Identity migration and then we are going to create a new model and have that migration applied in the database.

How to use Entity Framework Migrations: Let’s get started, To demonstrate entity framework core migrations, I am going to create a sample asp.net core web application like following.



Once we select asp.net core application it will appear the following dialog.



Now w…

Solution: gulp is not recognized as internal or external command- Visual Studio

Recently, After Upgrading to the newer version of Gulp, I was getting this error.  After doing lots of  finding online and in my system I was able to figure out that Modules like Gulp does not installed to the path. So when you try to run it from the command line it was showing below error.
gulp is not recognized as internal or external command How to solve : gulp is not recognized as internal or external command To solve this error we just need to create a new environment variable and set the NPM modules path there.

To create an environment variable,  Right click My Computer/ This Pc and click on properties.  It will open following windows.



Go to Advance System Settings and it will open system properties windows like below.



Click on Environment Variables it will open an environment and system variables windows like below.



Click on new System variable and put NODE_PATH in system variable name and %AppData%\npm\node_modules into the variable value like below.



Click on Ok. That’s it. N…