Library

PetaPoco

Overview

PetaPoco is "a lightweight and fast micro ORM" developed as a simple data access library for .NET consisting of a single file. In contrast to heavy ORMs like Entity Framework or NHibernate, it features a design that prioritizes simplicity and performance. While leveraging SQL knowledge to greatly reduce manual coding, it provides safe and efficient database operations without limiting the full power of SQL.

Details

PetaPoco 2025 edition is a lightweight ORM solution that has established a solid position in the .NET development ecosystem. Distributed as a single C# file with absolutely no dependencies, making integration extremely simple. Achieves high performance equivalent to Dapper through dynamic method generation (MSIL), supporting both strictly typed POCO objects and dynamic objects. Supports major databases including SQL Server, PostgreSQL, MySQL, SQLite, and runs on .NET Standard 2.0, .NET 4.0/4.5+, and Mono.

Key Features

• Single File Implementation: Composed of a single C# file with no dependencies
• High Performance: Optimized through dynamic method generation via MSIL
• POCO Support: POCO object operations with no attributes or minimal attributes
• Full SQL Support: Complete support for complex custom queries
• Multi-Database Support: Supports all major RDBMS
• T4 Templates: Automated class generation support

Pros and Cons

Pros

• Extremely lightweight with no dependencies, easy to integrate into projects
• High performance comparable to Dapper and EF Core
• Low learning cost as it leverages SQL knowledge
• No restrictions as complex queries can be written in direct SQL
• Wide platform compatibility with .NET Standard support
• Built-in convenient helper methods like paging functionality

Cons

• Limited functionality compared to full-featured ORMs
• No relationship or migration management features
• Manual SQL writing and object mapping required
• No large-scale enterprise features (caching, lazy loading, etc.)
• No LINQ integration or model-driven development like Entity Framework
• Development efficiency may be inferior to full-featured ORMs in some cases

Reference Pages

• PetaPoco GitHub Repository
• PetaPoco NuGet Package
• PetaPoco Official Documentation

Code Examples

Setup

// NuGet package installation
// Install-Package PetaPoco

// Or add directly to project as a single file
// Copy PetaPoco.cs to your project

using PetaPoco;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

Basic Usage

// POCO class definition
[TableName("Users")]
[PrimaryKey("Id", AutoIncrement = true)]
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public int Age { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; }
}

[TableName("Posts")]
[PrimaryKey("Id", AutoIncrement = true)]
public class Post
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateTime CreatedAt { get; set; }
}

// Database connection and CRUD operations
public class UserRepository
{
    private readonly string _connectionString;
    
    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    public async Task<List<User>> GetAllUsersAsync()
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        return await db.FetchAsync<User>("SELECT * FROM Users ORDER BY CreatedAt DESC");
    }
    
    public async Task<User> GetUserByIdAsync(int id)
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        return await db.SingleOrDefaultAsync<User>("SELECT * FROM Users WHERE Id = @0", id);
    }
    
    public async Task<int> CreateUserAsync(User user)
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        user.CreatedAt = DateTime.Now;
        return (int)await db.InsertAsync(user);
    }
    
    public async Task<bool> UpdateUserAsync(User user)
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        return await db.UpdateAsync(user) > 0;
    }
    
    public async Task<bool> DeleteUserAsync(int id)
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        return await db.DeleteAsync<User>(id) > 0;
    }
}

Query Execution

public class AdvancedUserService
{
    private readonly string _connectionString;
    
    public AdvancedUserService(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    // Conditional search
    public async Task<List<User>> SearchUsersAsync(string nameFilter, int? minAge = null, int? maxAge = null)
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        
        var sql = new Sql("SELECT * FROM Users WHERE 1=1");
        
        if (!string.IsNullOrEmpty(nameFilter))
            sql = sql.Append("AND Name LIKE @0", $"%{nameFilter}%");
            
        if (minAge.HasValue)
            sql = sql.Append("AND Age >= @0", minAge.Value);
            
        if (maxAge.HasValue)
            sql = sql.Append("AND Age <= @0", maxAge.Value);
            
        sql = sql.Append("ORDER BY Name");
        
        return await db.FetchAsync<User>(sql);
    }
    
    // Pagination
    public async Task<Page<User>> GetUsersPagedAsync(int page, int itemsPerPage)
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        return await db.PageAsync<User>(page, itemsPerPage, "SELECT * FROM Users ORDER BY CreatedAt DESC");
    }
    
    // Complex JOIN queries
    public async Task<List<UserWithPostCount>> GetUsersWithPostCountAsync()
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        
        var sql = @"
            SELECT u.*, COUNT(p.Id) as PostCount
            FROM Users u
            LEFT JOIN Posts p ON u.Id = p.UserId
            WHERE u.IsActive = 1
            GROUP BY u.Id, u.Name, u.Email, u.Age, u.CreatedAt, u.IsActive
            ORDER BY PostCount DESC";
            
        return await db.FetchAsync<UserWithPostCount>(sql);
    }
    
    // Custom mapping
    public async Task<List<UserSummary>> GetUserSummariesAsync()
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        
        return await db.FetchAsync<UserSummary>(@"
            SELECT 
                Id,
                Name,
                Email,
                CASE WHEN Age >= 18 THEN 'Adult' ELSE 'Minor' END as AgeGroup,
                DATEDIFF(YEAR, CreatedAt, GETDATE()) as YearsSinceJoined
            FROM Users
            ORDER BY CreatedAt DESC");
    }
}

// Custom POCO classes
public class UserWithPostCount
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public int Age { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; }
    public int PostCount { get; set; }
}

public class UserSummary
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string AgeGroup { get; set; }
    public int YearsSinceJoined { get; set; }
}

Data Operations

// Practical usage example
public class BlogService
{
    private readonly string _connectionString;
    
    public BlogService(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    // Post creation with user validation
    public async Task<int> CreatePostAsync(int userId, string title, string content)
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        
        // User existence check
        var user = await db.SingleOrDefaultAsync<User>("SELECT * FROM Users WHERE Id = @0 AND IsActive = 1", userId);
        if (user == null)
            throw new ArgumentException("Active user not found");
            
        var post = new Post
        {
            UserId = userId,
            Title = title,
            Content = content,
            CreatedAt = DateTime.Now
        };
        
        return (int)await db.InsertAsync(post);
    }
    
    // Bulk operations
    public async Task<int> CreateMultipleUsersAsync(List<User> users)
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        
        int insertedCount = 0;
        using (var transaction = db.GetTransaction())
        {
            try
            {
                foreach (var user in users)
                {
                    user.CreatedAt = DateTime.Now;
                    await db.InsertAsync(user);
                    insertedCount++;
                }
                transaction.Complete();
            }
            catch
            {
                transaction.Dispose(); // Rollback
                throw;
            }
        }
        
        return insertedCount;
    }
    
    // Statistical information retrieval
    public async Task<BlogStatistics> GetBlogStatisticsAsync()
    {
        using var db = new Database(_connectionString, "System.Data.SqlClient");
        
        var stats = await db.SingleAsync<BlogStatistics>(@"
            SELECT 
                (SELECT COUNT(*) FROM Users WHERE IsActive = 1) as ActiveUserCount,
                (SELECT COUNT(*) FROM Users WHERE IsActive = 0) as InactiveUserCount,
                (SELECT COUNT(*) FROM Posts) as TotalPostCount,
                (SELECT COUNT(*) FROM Posts WHERE CreatedAt >= DATEADD(day, -30, GETDATE())) as RecentPostCount,
                (SELECT AVG(CAST(Age as FLOAT)) FROM Users WHERE IsActive = 1) as AverageUserAge");
                
        return stats;
    }
}

public class BlogStatistics
{
    public int ActiveUserCount { get; set; }
    public int InactiveUserCount { get; set; }
    public int TotalPostCount { get; set; }
    public int RecentPostCount { get; set; }
    public double AverageUserAge { get; set; }
}

Configuration and Customization

// Database configuration management
public class DatabaseConfiguration
{
    public static Database CreateDatabase(string connectionString, string providerName = "System.Data.SqlClient")
    {
        var db = new Database(connectionString, providerName);
        
        // Global settings
        db.CommandTimeout = 30; // 30 second timeout
        
        // Enable SQL logging (development environment only)
        #if DEBUG
        db.OnExecutingCommand = command => 
        {
            Console.WriteLine($"Executing: {command.CommandText}");
            foreach (var param in command.Parameters.Cast<IDataParameter>())
            {
                Console.WriteLine($"  {param.ParameterName}: {param.Value}");
            }
        };
        #endif
        
        return db;
    }
    
    // Multi-database support
    public static Database CreateSQLiteDatabase(string filePath)
    {
        var connectionString = $"Data Source={filePath};Version=3;";
        return new Database(connectionString, "System.Data.SQLite");
    }
    
    public static Database CreateMySQLDatabase(string server, string database, string username, string password)
    {
        var connectionString = $"Server={server};Database={database};Uid={username};Pwd={password};";
        return new Database(connectionString, "MySql.Data.MySqlClient");
    }
    
    public static Database CreatePostgreSQLDatabase(string host, string database, string username, string password)
    {
        var connectionString = $"Host={host};Database={database};Username={username};Password={password};";
        return new Database(connectionString, "Npgsql");
    }
}

// Custom POCO Mapper
public class CustomMappingExample
{
    public class UserProfile
    {
        public int UserId { get; set; }
        public string FullName { get; set; }
        public string ContactInfo { get; set; }
        public DateTime LastActivity { get; set; }
    }
    
    public async Task<List<UserProfile>> GetUserProfilesAsync(string connectionString)
    {
        using var db = DatabaseConfiguration.CreateDatabase(connectionString);
        
        // Custom mapping logic
        return await db.FetchAsync<UserProfile>(@"
            SELECT 
                u.Id as UserId,
                CONCAT(u.Name, ' (', u.Email, ')') as FullName,
                u.Email as ContactInfo,
                COALESCE(p.MaxCreatedAt, u.CreatedAt) as LastActivity
            FROM Users u
            LEFT JOIN (
                SELECT UserId, MAX(CreatedAt) as MaxCreatedAt
                FROM Posts 
                GROUP BY UserId
            ) p ON u.Id = p.UserId
            WHERE u.IsActive = 1
            ORDER BY LastActivity DESC");
    }
}

Error Handling

public class ErrorHandlingService
{
    private readonly string _connectionString;
    private readonly ILogger _logger;
    
    public ErrorHandlingService(string connectionString, ILogger logger)
    {
        _connectionString = connectionString;
        _logger = logger;
    }
    
    public async Task<Result<User>> CreateUserSafelyAsync(User user)
    {
        try
        {
            using var db = DatabaseConfiguration.CreateDatabase(_connectionString);
            
            // Duplicate check
            var existingUser = await db.SingleOrDefaultAsync<User>(
                "SELECT * FROM Users WHERE Email = @0", user.Email);
                
            if (existingUser != null)
            {
                return Result<User>.Failure("Email already exists");
            }
            
            user.CreatedAt = DateTime.Now;
            user.Id = (int)await db.InsertAsync(user);
            
            _logger.LogInformation($"User created successfully: {user.Id}");
            return Result<User>.Success(user);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to create user: {Email}", user.Email);
            return Result<User>.Failure($"Database error: {ex.Message}");
        }
    }
    
    public async Task<Result<int>> BulkInsertWithRetryAsync(List<User> users, int maxRetries = 3)
    {
        for (int attempt = 1; attempt <= maxRetries; attempt++)
        {
            try
            {
                using var db = DatabaseConfiguration.CreateDatabase(_connectionString);
                
                int insertedCount = 0;
                using (var transaction = db.GetTransaction())
                {
                    foreach (var user in users)
                    {
                        user.CreatedAt = DateTime.Now;
                        await db.InsertAsync(user);
                        insertedCount++;
                    }
                    transaction.Complete();
                }
                
                return Result<int>.Success(insertedCount);
            }
            catch (Exception ex) when (attempt < maxRetries)
            {
                _logger.LogWarning(ex, "Bulk insert attempt {Attempt} failed, retrying...", attempt);
                await Task.Delay(TimeSpan.FromSeconds(Math.Pow(2, attempt))); // Exponential backoff
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Bulk insert failed after {MaxRetries} attempts", maxRetries);
                return Result<int>.Failure($"Failed after {maxRetries} attempts: {ex.Message}");
            }
        }
        
        return Result<int>.Failure("Unexpected error");
    }
}

// Result class
public class Result<T>
{
    public bool IsSuccess { get; private set; }
    public T Data { get; private set; }
    public string Error { get; private set; }
    
    private Result(bool isSuccess, T data, string error)
    {
        IsSuccess = isSuccess;
        Data = data;
        Error = error;
    }
    
    public static Result<T> Success(T data) => new(true, data, null);
    public static Result<T> Failure(string error) => new(false, default, error);
}