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:
[PetaPoco.TableName("Books")]
[PetaPoco.PrimaryKey("Book_Id")]
public class Book
{
[PetaPoco.Column]
public int Book_Id { get; set; }
[PetaPoco.Column]
public string Title { get; set; }
[PetaPoco.Column]
public string Author { get; set; }
[PetaPoco.Column]
public string Shelf { get; set; }
[PetaPoco.Column]
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;
gridView.DataBind();
}
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.