DOCSEARCHbyAlgolia
Submit Docs

NPoco Documentation

repository·master·Indexed 21 days ago

https://github.com/schotime/npoco

A micro-ORM for .NET and a fork of PetaPoco that simplifies data access by mapping SQL query results to POCO classes. It features an IDatabase interface for executing queries and a SqlBuilder class for constructing dynamic SQL queries using a token-based template system (e.g., /**select**/, /**where**/).

Tokens
1.4K
Snippets
4
Records
6
Agent score
25%

What's inside NPoco

  1. Get started with your first NPoco query

    master

    To perform a basic query in NPoco, define a POCO (Plain Old CLR Object) class where the property names match the database column names. NPoco performs case-insensitive mapping between column names and properties automatically, so no explicit mapping configuration is required for simple query scenarios.

    1. Define your model class.
    2. Instantiate an IDatabase using a connection string name.
    3. Use the .Fetch<T>() method with your SQL string.
    public class User 
{
    public int UserId { get;set; }
    public string Email { get;set; }
}

// Initialize the database with a connection string name from your config
IDatabase db = new Database("connStringName");

// Execute a query and map results to the User class
List<User> users = db.Fetch<User>("select userId, email from users");

  2. Use SqlBuilder to construct dynamic SQL queries

    master

    The SqlBuilder class allows you to build complex SQL queries by adding various clauses (SELECT, JOIN, WHERE, etc.) and then injecting them into a template using special token syntax: /**token**/.

    When you call methods like .Select() or .Where(), the builder stores these fragments. When you call .AddTemplate(sql, ...) and subsequently access the RawSql or Parameters properties of the returned Template, the builder replaces the tokens in your template with the accumulated clauses.

    Commonly used tokens include:

    • /**select**/
    • /**join**/
    • /**leftjoin**/
    • /**where**/
    • /**where(name)**/
    • /**orderby**/
    • /**groupby**/
    • /**having**/
    var builder = new SqlBuilder();
builder.Select("Id", "Name")
       .Where("Age > @0", 21)
       .OrderBy("Name");

// The template uses the tokens defined by the builder's clauses
var template = builder.AddTemplate("SELECT /**select**/ FROM Users /**where**/ /**orderby**/");

string sql = template.RawSql;
object[] parameters = template.Parameters;

  3. Configure SqlBuilder with default replacement overrides

    master

    You can initialize SqlBuilder with a dictionary of default overrides. This allows you to define what happens when a specific token is missing or empty. A value of null in the dictionary means the token will not be replaced.

    Example dictionary keys:

    • "where" maps to a default like "1=1"
    • "where(name)" maps to a default like "1!=1"
    var overrides = new Dictionary<string, string>
{
    { "where", "1=1" },
    { "where(name)", "1!=1" }
};
var builder = new SqlBuilder(overrides);

  5. Use SqlBuilder.Template to retrieve resolved SQL and parameters

    master

    When you call SqlBuilder.AddTemplate(string sql, params object[] parameters), it returns a Template object. This object is responsible for the final resolution of the SQL string and its associated parameters.

    To get the final results, access:

    • RawSql: Triggers the resolution process (replacing tokens and processing parameters) and returns the final SQL string.
    • Parameters: Triggers the resolution process and returns the array of parameters required by the final SQL.

    Note: The Template also has a TokenReplacementRequired property. If set to true, an exception will be thrown if any tokens defined in the builder's defaults are not used in the template.

    // After creating a template via builder.AddTemplate(...)
string finalSql = template.RawSql;
object[] finalParams = template.Parameters;

  6. Add SQL clauses to SqlBuilder

    master

    The SqlBuilder provides several fluent methods to add specific SQL components. Each method internally calls AddClause and uses specific joiners, prefixes, and postfixes to ensure the resulting SQL is syntactically correct.

    MethodToken UsedDescription
    Select(params string[] columns)/**select**/Adds columns to the SELECT clause.
    Join(string sql, params object[] parameters)/**join**/Adds an INNER JOIN clause.
    LeftJoin(string sql, params object[] parameters)/**leftjoin**/Adds a LEFT JOIN clause.
    Where(string sql, params object[] parameters)/**where**/Adds a filter clause wrapped in parentheses.
    WhereNamed(string name, string sql, params object[] parameters)/**where(name)**/Adds a named filter clause.
    OrderBy(string sql, params object[] parameters)/**orderby**/Adds an ORDER BY clause.
    OrderByCols(params string[] columns)/**orderbycols**/Adds columns to the ORDER BY clause.
    GroupBy(string sql, params object[] parameters)/**groupby**/Adds a GROUP BY clause.
    Having(string sql, params object[] parameters)/**having**/Adds a HAVING clause.