PetaPoco: Binding a GridView with PetaPoco query result.

by Rohit 2. March 2012 19:46

PetaPoco is a simple but fast tiny ORM. It is not a full-fledged ORM like NHibernate but a no-fuss easy implementation of ORM. It is a single C# file which you can attach in your project. You can even use NuGet to directly download and integrate it in your project.

PetaPoco has some interesting features like:

  • Support for T4 templates to generate schema from POCO  classes.
  • Retrieve paged results automatically.
  • SQL support. No need for strict Linq syntax.
  • Fast.
  • No external dependencies. Comes as a single C# file.
  • Rather than using automatic mapping, you can use ExplicitColumns attribute to show only the columns to be mapped.

In this post I show you how to query a table using PetaPoco and how to bind a GridView with the result. As discussed above, here I prefer to use ExplicitMapping of table and individual column.

Here goes our basic class:

public class Book 
     public int Book_Id { get; set; } 
     public string Title { get; set; } 
     public string Author { get; set; } 
     public string Shelf { get; set; } 
     public decimal price { get; set; }

This class is mapped to a Table in my SQL Server database with the name Books. As I said, I am using Explicit Column Mapping to map my class with the Table hence attributes enclosed in [ ] are added. The schema of the table is as below:

TABLE: Books
Book_Id   int Identity PRIMARY KEY
Title         varchar(60)
Author     varchar(30)
Shelf       varchar(50)
Price       numeric(8,2)

I will discuss CRUD operations using PetaPoco in the next post. Here I show you how to query records from an existing table and bind a GridView control on your Web Form.
To query Books Table in my database, I make use of the class above through which we have mapped the columns. I create a method to retrieve existing books list. This method takes an input parameter of type GridView:

public void GetBooks(GridView gridView)
        var db = new PetaPoco.Database(“conBooks”); // This is the name of my connection string in web.config file 
        string queryBooks = “SELECT * FROM Books”; 
        var result = db.Query<Book>(queryBooks); 
        gridView.DataSource = result; 

 PetaPoco’s database class has two methods for retrieving records, Query and Fetch.

Query uses yield return to iterate over the results without loading the set into memory whereas, Fetch returns a List<> of POCO’s.

In the posts to come, I will share more PetaPoco examples.

Tags: , ,


Pingbacks and trackbacks (1)+

Add comment

  • Comment
  • Preview

About Rohit Prakash

Software Craftsman @ Fortune 100 Company. Technical Reviewer of few programming books.

A Single, Writer, Reader and Adventurist.

You can reach me at:

rohit [at]

Month List

Protected by Copyscape Web Plagiarism Software