Getting Started PetaPoco: Create POCO and perform CRUD

I love PetaPoco and I wrote a post about which Micro-ORM is better, I picked PetaPoco and every time I want to access database I use it, it’s fast and easy, no need to setup the mapping like ORMs, but I don’t say that Micro-ORMs are better than ORMs, in some situations you need to use an ORM. PetaPoco is better than using the old ADO.NET approach of using Connection, open it, create either DataTable or DataSet with DataAdapter, and fill them with data.

In this post we’ll learn How to create one POCO and perform CRUD on it, I’ll use the famous Northwind database and create POCO for Customers table, let’s see how we can do it.

Get PetaPoco:

We need to get PetaPoco in our project and there are 2 ways:

  1. Install it from NuGet:
    • Use NuGet package manger and if you don’t have NuGet you can download it from here.
    • In NuGet package manager console, write Install-Package PetaPoco.
    • If you see message “Successfully installed ‘PetaPoco 4.0.3′.” then you’re ready to go.SNAG-0067
  2. Download the whole project from GitHub because PetaPoco is open source project you can download it and you can even add your code if you want, after downloading the project add PetaPoco.cs file to your project.

Create POCO:

Now we’ll create POCO for Customers table in Northwind database, so let’s do it. It’s so simple just create new class and for each column in the table create a property for.

CustomerTable

Customer POCO
  1. public class Customer
  2. {
  3.     public string CustomerID { get; set; }
  4.     public string CompanyName { get; set; }
  5.     public string ContactName { get; set; }
  6.     public string ContactTitle { get; set; }
  7.     public string Address { get; set; }
  8.     public string City { get; set; }
  9.     public string Region { get; set; }
  10.     public string PostalCode { get; set; }
  11.     public string Country { get; set; }
  12.     public string Phone { get; set; }
  13.     public string Fax { get; set; }
  14. }

Connect To Database:

In order for PetaPoco to your POCOs, you need to have database Connection String in configuration file or construct one in code, so we’ll add App.config file to the project and add a <connectionString> that refer to Northwind database.

<connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;"
         providerName="System.Data.SqlClient"/>
</connectionStrings>

Now we are ready to make PetaPoco connect to Northwind database, and the connection can be done by Instantiate Database Object and pass the Name of the Connection String to the Contractor.

Database northwindDatabase = new Database("NorthwindConnectionString");

Perform CRUD:

Read:

Read can be done by call Database Query Method and pass the POCO we want to query against.

var getAllCutomers = northwindDatabase.Query<Customer>("SELECT * FROM Customers");

We can add parameters to the query.

var Cutomer = northwindDatabase.Query<Customer>("SELECT * FROM Customers WHERE CustomerID=@0", "ALFKI");

Create:

Create can be done by create new object from Customer POCO and assign its data, call Database Insert Method, and then pass Table name, Primary Key, and the POCO object.

  1. Customer newCustomer = new Customer();
  2.  
  3.             //Set Data
  4.             newCustomer.CustomerID = "EMAD";
  5.             newCustomer.CompanyName = "Microsoft";
  6.             newCustomer.ContactName = "Emad Mokhtar";
  7.             newCustomer.ContactTile = "Developer";
  8.  
  9.             //Create
  10.             northwindDatabase.Insert("Customer", "CustomerId", newCustomer);

We can only pass the POCO object of we decorate our POCO Class with two PetaPoco special Attributes.

  1.     [TableName("Customers")]
  2.     [PrimaryKey("CustomerID")]
  3.     public class Customer
  4.     {
  5.         public string CustomerID { get; set; }
  6.         public string CompanyName { get; set; }
  7.         public string ContactName { get; set; }
  8.         public string ContactTitle { get; set; }
  9.         public string Address { get; set; }
  10.         public string City { get; set; }
  11.         public string Region { get; set; }
  12.         public string PostalCode { get; set; }
  13.         public string Country { get; set; }
  14.         public string Phone { get; set; }
  15.         public string Fax { get; set; }
  16.     }

 

northwindDatabase.Insert(newCustomer);

If you run the code an exception will be thrown because when you specify the PrimaryKey for the POCO, PetaPoco will deal with it as it’s Identity column and CustomerID isn’t identity column.

SQL generated
exec sp_executesql N'INSERT INTO [Customers] ([CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax]) VALUES (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9);
SELECT SCOPE_IDENTITY() AS NewID;',N'@0 nvarchar(4000),@1 nvarchar(4000),@2 nvarchar(4000),@3 nvarchar(4000),@4 nvarchar(4000),@5 nvarchar(4000),@6 nvarchar(4000),@7 nvarchar(4000),@8 nvarchar(4000),@9 nvarchar(4000)',@0=N'Microsoft',@1=N'Emad Mokhtar',@2=N'Developer',@3=NULL,@4=NULL,@5=NULL,@6=NULL,@7=NULL,@8=NULL,@9=NULL

We will remove PrimaryKey POCO Attribute and our new customer will be inserted.

SQL generated
exec sp_executesql N'INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax]) VALUES (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10)',N'@0 nvarchar(4000),@1 nvarchar(4000),@2 nvarchar(4000),@3 nvarchar(4000),@4 nvarchar(4000),@5 nvarchar(4000),@6 nvarchar(4000),@7 nvarchar(4000),@8 nvarchar(4000),@9 nvarchar(4000),@10 nvarchar(4000)',@0=N'EMAD',@1=N'Microsoft',@2=N'Emad Mokhtar',@3=N'Developer',@4=NULL,@5=NULL,@6=NULL,@7=NULL,@8=NULL,@9=NULL,@10=NULL

Update:

As we did in Insert Method we can do with Update Method, but again Customers table doesn’t have identity column and this method will not work.

newCustomer.ContactTitle = ".NET Developer";

northwindDatabase.Update(newCustomer);

 

SQL generated
exec sp_executesql N'UPDATE [Customers] SET [CustomerID] = @0, [CompanyName] = @1, [ContactName] = @2, [ContactTitle] = @3, [Address] = @4, [City] = @5, [Region] = @6, [PostalCode] = @7, [Country] = @8, [Phone] = @9, [Fax] = @10 WHERE [ID] = @11',N'@0 nvarchar(4000),@1 nvarchar(4000),@2 nvarchar(4000),@3 nvarchar(4000),@4 nvarchar(4000),@5 nvarchar(4000),@6 nvarchar(4000),@7 nvarchar(4000),@8 nvarchar(4000),@9 nvarchar(4000),@10 nvarchar(4000),@11 nvarchar(4000)',@0=N'EMAD',@1=N'Microsoft',@2=N'Emad Mokhtar',@3=N'.NET Developer',@4=NULL,@5=NULL,@6=NULL,@7=NULL,@8=NULL,@9=NULL,@10=NULL,@11=NULL

PetaPoco have the flexibility to get the update statement that the user can write, and we’ll use this to perform the update.

northwindDatabase.Update<Customer>("Set ContactTitle=@0 WHERE CustomerID=@1",newCustomer.ContactTitle,newCustomer.CustomerID);

 

SQL generated
exec sp_executesql N'UPDATE [Customers] Set ContactTitle=@0 WHERE CustomerID=@1',N'@0 nvarchar(4000),@1 nvarchar(4000)',@0=N'.NET Developer',@1=N'EMAD'

Delete:

Delete is an helper Method like Insert and Update if you are using Identity column as the Table key, you can also write your Delete statement.

northwindDatabase.Delete(newCustomer);

 

SQL genetared
exec sp_executesql N'DELETE FROM [Customers] WHERE [ID]=@0',N'@0 nvarchar(4000)',@0=NULL

We’ll write our Delete statement by pass the CustomerID we want to delete.

northwindDatabase.Delete<Customer>("WHERE CustomerID=@0",newCustomer.CustomerID);

 

SQL generated
exec sp_executesql N'DELETE FROM [Customers] WHERE CustomerID=@0',N'@0 nvarchar(4000)',@0=N'EMAD'

You can get the whole project from GitHub.