IRohitable | PetaPoco: Binding a GridView with PetaPoco query result.

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:

[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.

Tags: , ,

Technology

Pingbacks and trackbacks (1)+

Add comment

biuquote
  • Comment
  • Preview
Loading

About Rohit Prakash

Software Craftsman and Technology Enthusiast (not a Guru).

Technical Reviewer of a book on open-source programming IDE.

My day job keeps me engaged with Microsoft Technologies (ASP.NET, C# and SQL Server) hence most of the posts are related to these technologies. I, however, love to play with other open-source technologies. I also have interest in IT Security and therefore you will find posts on Malware Analysis, Cryptography and Anti-Virus programs.

Few people have contacted me for Guest Posts. You will find these posts as well.

 

You can reach me at:

rohit [at] irohitable.com

-----------------------------------------

LinkedIn:

in.linkedin.com/in/rohitpkhare

Twitter:

@BuzzRohit

-----------------------------------------
Google+

Search with this name: Rohit Prakash

Month List

Protected by Copyscape Web Plagiarism Software