### Getting Started: First Query with NPoco

Source: https://github.com/schotime/npoco/wiki/Home

Demonstrates how to perform your first database query using NPoco. It shows how to define a model class and fetch data into a list of objects. The example highlights NPoco's automatic mapping from database columns to object properties.

```csharp
public class User 
{
    public int UserId { get;set; }
    public string Email { get;set; }
}

using (IDatabase db = new Database("connStringName")) 
{
    List<User> users = db.Fetch<User>("select userId, email from users");
}
```

```csharp
List<User> users = db.Fetch<User>("select userId, email from users");
```

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

### Application Start Method

Source: https://github.com/schotime/npoco/wiki/Fluent-mappings-including-conventional

A simple method to initiate the database factory setup, typically called during application startup.

```csharp
public void Application_Start()
{
    MyFactory.Setup();
}
```

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

### Database Factory Setup

Source: https://github.com/schotime/npoco/wiki/Fluent-mappings-including-conventional

Demonstrates the setup of a DatabaseFactory, configuring it with fluent mappings, database connection, and a mapper. This ensures mappings are created only once.

```csharp
public static class MyFactory
{
    public static DatabaseFactory DbFactory { get; set; }

    public static void Setup()
    {
        var fluentConfig = FluentMappingConfiguration.Configure(new OurMappings());
        //or individual mappings
        //var fluentConfig = FluentMappingConfiguration.Configure(new UserMapping(), ....);

        DbFactory = DatabaseFactory.Config(x =>
        {
            x.UsingDatabase(() => new Database("connString"));
            x.WithFluentConfig(fluentConfig);
            x.WithMapper(new Mapper());
        });
    }
}
```

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

### Retrieving Database Instance

Source: https://github.com/schotime/npoco/wiki/Fluent-mappings-including-conventional

Shows how to obtain a database instance from the configured DatabaseFactory.

```csharp
var database = MyFactory.DbFactory.GetDatabase();
```

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

### Glimpse ADO Package Installation

Source: https://github.com/schotime/npoco/wiki/Debugging-and-Profiling

Instructions for installing the necessary Glimpse packages to enable ADO profiling within an ASP.NET application.

```powershell
Install-Package Glimpse.ADO
Install-Package Glimpse.Mvc4 (or your mvc version)
```

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

### User Class Example with npoco Attributes

Source: https://github.com/schotime/npoco/wiki/Using-Guid-Column-as-Primary-Key

Provides a complete example of a User class using npoco attributes like TableName, PrimaryKey, Column, ResultColumn, and Ignore.

```csharp
[TableName("Users")]
[PrimaryKey("UserId", AutoIncrement = true)]
public class User
{
    public Guid UserId { get;set; }

    [Column("emailAddress")]
    public string Email { get;set; }

    [ResultColumn]
    public string ExtraInfo { get;set; }

    [Ignore]
    public int Temp { get;set; }
}
```

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

### Dependency Injection with Database Instance

Source: https://github.com/schotime/npoco/wiki/Fluent-mappings-including-conventional

Illustrates how to register the database instance with a dependency injection container, providing access to the database via an interface.

```csharp
For<IDatabase>().Use(() => MyFactory.DbFactory.GetDatabase());
```

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

### Custom SqlServerDatabase Implementation

Source: https://github.com/schotime/npoco/wiki/Upgrading-to-V5

Example of creating a custom database class inheriting from SqlServerDatabase for use with NPoco.

```csharp
public class MyDatabase : NPoco.SqlServer.SqlServerDatabase
{
    public MyDatabase() : base(GetConnectionString())
    {
    }

    private static string GetConnectionString()
    {
        // Replace with your actual connection string retrieval logic
        return "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";
    }
}
```

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

### Fetch<T> Overload Example

Source: https://github.com/schotime/npoco/wiki/Query-Paging

Shows the Fetch<T> method overload, which shares the same parameter signature as Page<T> but returns a List<T> instead of a Page<T> object.

```csharp
List<User> users = db.Fetch<User>(2, 10, "select u.* from users u order by userid");
```

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

### Example Classes for Database Mapping

Source: https://github.com/schotime/npoco/wiki/Version-3

Defines C# classes representing database entities like User, Address, Country, and Permission. It illustrates how properties map to database columns, including relationships and nested objects.

```csharp
public class User {
  public int UserId { get;set; }
  public int Name { get;set; }
  public Address Address { get;set; }              // No DB column. Uses UserId
  public Country CountryOfBirth { get;set; }       // CountryId
  public List<Permission> Permissions { get;set; } // No DB column. Uses UserId
  public dynamic ExtraDetails { get;set; }
}

public class Address {
  public int UserId { get;set; }
  public int StreetNo { get;set; }
  public string StreetName { get;set; }
}

public class Country {
  public int CountryId { get;set; }
  public string Name { get;set; }
}

public class Permission {
  public int PermissionId { get;set; }
  public int UserId { get;set; }
  public int Level { get;set; }
}
```

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

### Page<T> Paging Example

Source: https://github.com/schotime/npoco/wiki/Query-Paging

Demonstrates how to use the Page<T> method to retrieve a paginated list of users. This method returns a Page<T> object containing the current page, total pages, total items, items per page, and the list of items. Requires an ORDER BY clause in the SQL query.

```csharp
IDatabase db = new Database("connStringName");
Page<T> pagedUsers = db.Page<User>(2, 10, "select u.* from users u order by userid");
```

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

### One-to-one Relationship Example

Source: https://github.com/schotime/npoco/wiki/Version-3

Illustrates a one-to-one relationship using the Reference attribute, connecting primary keys. This enables LINQ includes and allows holding both the object and its foreign key value.

```csharp
[Reference(ReferenceType.OneToOne, ColumnName = "UserId", ReferenceMemberName = "UserId")]
public Address Address { get;set; }
```

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

### Mappings Class with For<> Method

Source: https://github.com/schotime/npoco/wiki/Fluent-mappings-including-conventional

An alternative way to define mappings by inheriting from Mappings and using the For<> method to specify mappings for different classes.

```csharp
public class OurMappings : Mappings
{
    public OurMappings()
    {
        For<User>().Columns( .... 
    }
}
```

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

### User Class Mapping with Fluent API

Source: https://github.com/schotime/npoco/wiki/Fluent-mappings-including-conventional

Defines a mapping for the User class, specifying the primary key, table name, and column configurations, including ignoring a column and renaming another.

```csharp
public class UserMapping : Map<User>
{
    public UserMapping()
    {
        PrimaryKey(x => x.UserId);
        TableName("Users");
        Columns(x =>
        {
            x.Column(y => y.Name).Ignore();
            x.Column(y => y.Age).WithName("a_ge");
        });        
    }
}
```

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

### SkipTake<T> Paging Example

Source: https://github.com/schotime/npoco/wiki/Query-Paging

Illustrates the usage of the SkipTake<T> method, which is similar to LINQ's Skip and Take. It retrieves a list of items by skipping a specified number of records and then taking a specified number. Returns a List<T>.

```csharp
List<User> users = db.SkipTake<User>(10, 10, "select u.* from users u order by userid");
```

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

### Interface/Abstract Class Mapping with Factory

Source: https://github.com/schotime/npoco/wiki/Version-3

Provides an example of mapping to interfaces or abstract classes by registering a factory function that determines the concrete type based on IDataReader content. This is useful for polymorphic data retrieval.

```csharp
public class Post : ContentBase { }
public class Answer : ContentBase { }
public abstract class ContentBase {
    public string Name { get; set; }
    public string Type { get; set; }
}

db.Mappers.RegisterFactory<ContentBase>(reader => {
    var type = (string)reader["type"];
    if (type == "Post")
        return new Post();
    if (type == "Answer")
        return new Answer();
    return null;
});

var data = db.Fetch<ContentBase>(@"
  select 'NamePost' Name, 'Post' type
  union
  select 'NameAnswer' Name, 'Answer' type
").ToList();
```

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

### Many-to-one Relationship Example

Source: https://github.com/schotime/npoco/wiki/Version-3

Demonstrates a many-to-one relationship using the Reference attribute, linking a foreign key to a primary key. This facilitates LINQ includes and updates referenced primary keys during inserts.

```csharp
[Reference(ReferenceType.Foreign, ColumnName = "CountryId", ReferenceMemberName = "CountryId")]
public Country CountryOfBirth { get;set; }
```

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

### Dynamic Property Mapping with '__' Convention

Source: https://github.com/schotime/npoco/wiki/Version-3

Demonstrates how to use the '__' convention to map properties of type Dictionary<string, object> or dynamic. This example fetches user data and maps an 'ExtraDetails__Age' column to a nested 'Age' property within 'ExtraDetails'.

```csharp
var user = db.Fetch<User>("select u.*, 25 AS ExtraDetails__Age from users").First();
Assert.Equal(user.ExtraDetails.Age, 25);
```

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

### NPoco Get-Only Property Mapping

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Adds support for mapping to 'get' only properties in C# 6.

```csharp
// Add support to map to "get" only properties in c# 6
```

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

### One-to-many Relationship Example

Source: https://github.com/schotime/npoco/wiki/Version-3

Shows a one-to-many relationship using the Reference attribute, linking primary keys to foreign keys. This is used for IncludeMany in LINQ queries and with db.FetchOneToMany.

```csharp
[Reference(ReferenceType.Many, ColumnName = "UserId", ReferenceMemberName = "UserId")]
public List<Permission> Permissions { get;set; }
```

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

### NPoco User Class Mapping Example

Source: https://github.com/schotime/npoco/wiki/Mapping

Demonstrates how to use NPoco attributes to map a C# User class to a 'Users' table with a 'UserId' primary key and a custom column name for the Email property.

```csharp
[TableName("Users")]
[PrimaryKey("UserId")]
public class User
{
    public int UserId { get; set; }
    [Column("emailAddress")]
    public string Email { get; set; }
    [ResultColumn]
    public string ExtraInfo { get; set; }
    [Ignore]
    public int Temp { get; set; }
}
```

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

### NPoco String Method Usage in WHERE Clause

Source: https://github.com/schotime/npoco/wiki/Simple-linq-queries

Provides examples of using common string manipulation methods within NPoco's WHERE clause for filtering data. Supported methods include StartsWith, EndsWith, Contains, ToLower, and ToUpper.

```csharp
var users = db.Query<User>().Where(x => x.Name.StartsWith("Bo")).ToList();
var users = db.Query<User>().Where(x => x.Name.EndsWith("ob")).ToList();
var users = db.Query<User>().Where(x => x.Name.Contains("o")).ToList();
var users = db.Query<User>().Where(x => x.Name.ToLower() == "bob").ToList();
var users = db.Query<User>().Where(x => x.Name.ToUpper() == "BOB").ToList();
```

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

### Guid Parameters Fix

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Corrects the handling of Guid parameters in database operations. This ensures that Guid values are passed and processed correctly.

```C#
public class ParameterBinder {
    public void BindGuid(DbCommand command, string name, Guid value) {
        // ... fixed Guid parameter binding ...
    }
}
```

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

### Instantiating SqlServerDatabase with SqlClientFactory

Source: https://github.com/schotime/npoco/wiki/Upgrading-to-V5

Demonstrates how to instantiate the SqlServerDatabase, specifying SqlClientFactory.Instance for existing projects.

```csharp
// For existing projects, ensure your custom database inherits from SqlServerDatabase
// and is instantiated with SqlClientFactory.Instance
var db = new MyDatabase(Microsoft.Data.SqlClient.SqlClientFactory.Instance);

```

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

### NPoco Guid Serialization

Source: https://github.com/schotime/npoco/wiki/Release-Notes

The FastJsonColumnSerializer now renders a Guid as a string by default.

```csharp
// Change FastJsonColumnSerializer to render a Guid as a string by default
```

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

### NPoco Database Initialization for PostgreSQL

Source: https://github.com/schotime/npoco/wiki/Postgres

Demonstrates how to create a NPoco Database instance for PostgreSQL. The .NET Core / 5 version requires an additional NpgsqlFactory instance in the constructor.

```cs
new Database("host=localhost;user id=postgres;password=password;database=mydb", DatabaseType.PostgreSQL, Npgsql.NpgsqlFactory.Instance);
```

```cs
new Database("host=localhost;user id=postgres;password=password;database=mydb", DatabaseType.PostgreSQL);
```

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

### NPoco SqlServer Package Reference

Source: https://github.com/schotime/npoco/wiki/Upgrading-to-V5

This snippet shows how to add the NPoco.SqlServer NuGet package to a .NET project.

```xml
<PackageReference Include="NPoco.SqlServer" Version="5.0.0" />
```

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

### NPoco Case-Insensitive Column Check

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Checks if a column name starts with 'npoco_' in a case-insensitive manner.

```csharp
// Checks if the column starts with "npoco_" case insensitive. (#298) (@imasm)
```

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

### SqlBuilder Initialization

Source: https://github.com/schotime/npoco/wiki/Sql-Templating

Demonstrates how to initialize and use the SqlBuilder to create a SQL query template with an initial condition.

```csharp
var sqlBuilder = new SqlBuilder();
var template = sqlBuilder.AddTemplate("select * from users where age > @0 and /**where**/", 10);
```

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

### Remove Need for Property on Poco with AutoJoin

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Eliminates the requirement for a property to exist on a POCO when using AutoJoin. This simplifies the setup for auto-joining related entities.

```C#
public class AutoJoinConfig {
    // No longer requires a specific property definition for joins
}
```

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

### Basic NPoco Query with Filtering, Ordering, and Limiting

Source: https://github.com/schotime/npoco/wiki/Simple-linq-queries

Demonstrates how to query a database using NPoco's Query<T> feature. It includes filtering with a WHERE clause, ordering with ORDERBY, and limiting results with LIMIT. Requires a System.Data.Linq reference for IntelliSense.

```csharp
IDatabase db = new Database("connString");
db.Query<User>().Where(x => x.Name == "Bob")
                           .OrderBy(x => x.UserId)
                           .Limit(10, 10)
                           .ToList();
```

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

### Configure npoco Database for SQL Server

Source: https://github.com/schotime/npoco/wiki/Database-Constructor-Alternates

Demonstrates how to instantiate the npoco Database class with a SQL Server connection string. It specifies the database type as SqlServer2012, indicating the minimum supported syntax version, and uses SqlClientFactory for the database provider.

```csharp
var db = new Database("server=${servername};database=${databasename};user id=${userid};password=${password}", 
    DatabaseType.SqlServer2012, 
    System.Data.SqlClient.SqlClientFactory.Instance);
```

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

### NPoco Basic Query

Source: https://github.com/schotime/npoco/blob/master/README.md

Demonstrates how to perform a basic query using NPoco to fetch user data. It maps query results to a C# User object, utilizing case-insensitive matching between column names and property names.

```csharp
public class User 
{
    public int UserId { get;set; }
    public string Email { get;set; }
}

IDatabase db = new Database("connStringName");
List<User> users = db.Fetch<User>("select userId, email from users");
```

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

### Enable Glimpse Connection and Transactions for SqlBulkHelper

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Allows the SqlBulkHelper to support Glimpse for monitoring connections and transactions. This integration aids in debugging and performance analysis.

```C#
public class SqlBulkHelper {
    public void BulkInsert<T>(IEnumerable<T> data) {
        // ... integrates with Glimpse for connection/transaction monitoring ...
    }
}
```

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

### Initial Work on Aliases

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Represents the initial implementation and groundwork for supporting aliases in the system. This sets the stage for more advanced alias functionalities.

```C#
// Initial alias handling logic
```

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

### NPoco Basic Transaction Management

Source: https://github.com/schotime/npoco/wiki/SQL-Transaction-Support

Demonstrates the basic usage of NPoco's transaction support by explicitly calling BeginTransaction and CompleteTransaction.

```cs
using (IDatabase db = new Database("connStringName")) 
{
    db.BeginTransaction();
    //Your CRUD operation here
    db.CompleteTransaction();
}
```

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

### Handling Non-Existent Objects

Source: https://github.com/schotime/npoco/wiki/Query-Single-Object

Demonstrates the use of 'OrDefault' methods to safely retrieve objects when their existence in the database is uncertain. Using 'OrDefault' prevents exceptions if no record is found, unlike the standard methods.

```csharp
// Example using SingleOrDefault<T>() would go here, but is not provided in the source text.
// The text mentions that SingleById<T>() and Single<T>() will throw an exception if no object is found without the 'OrDefault' override.
```

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

### Display Last SQL on ASP.NET Error Page

Source: https://github.com/schotime/npoco/wiki/Debugging-and-Profiling

This example demonstrates how to extend the ASP.NET error page to display the last executed SQL query. It involves overriding the OnException method in NPoco to store the LastSQL and then modifying the error handling in Application_Error to include this information.

```csharp
public class MyDb : Database 
{
    public MyDb(string connectionStringName) : base(connectionStringName) { }

    public override void OnException(Exception e)
    {
        base.OnException(e);
        e.Data["LastSQL"] = this.LastSQL;
    }
}
```

```csharp
void Application_Error(object sender, EventArgs e)
{
    var lastError = Server.GetLastError();

    string sql = null;
    try
    {
        sql = lastError.Data["LastSQL"] as string;
    }
    catch
    {
        // skip it
    }
    if (sql == null) return;

    var ex = new HttpUnhandledException("An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.", lastError);

    Server.ClearError();

    var html = ex.GetHtmlErrorMessage();
    html = html.Insert(html.IndexOf("<b>Stack Trace:</b>"), @"
    <b>Last Sql:</b><br><br>
    <table width='100%' bgcolor='#ffffccc'>
        <tbody>
            <tr>
                <td><code><pre>" + sql + @"</pre></code></td>
            </tr>
        </tbody>
    </table><br>");

    Response.Write(html);
    Response.StatusCode = 500;
    Response.End();
}
```

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

### Query Data with Filtering, Ordering, and Pagination

Source: https://github.com/schotime/npoco/wiki/Query-Provider

Demonstrates how to retrieve a collection of 'User' objects from the database. It filters users by 'UserId' greater than 50, orders them by 'Name', and applies pagination with a limit of 20 records and an offset of 40. The query execution is deferred until a materializing method like ToList() is called.

```csharp
var users = db.Query<User>()
   .Where(x => x.UserId > 50)
   .OrderBy(x => x.Name)
   .Limit(20, 40)
   .ToList();
```

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

### C# NPoco Query for Nested Object Mapping

Source: https://github.com/schotime/npoco/wiki/Mapping-to-Nested-Objects

Demonstrates how to use NPoco's `Fetch` method to retrieve data and map it to the `User` object, including populating the nested `Address` object. It highlights the importance of column order in the SQL query.

```csharp
IDatabase db = new Database("connStringName");

// Maps to User objects includes populating Address property
var users = db.Fetch<User>("select u.UserId, u.Name, u.Street, u.City from Users");
```

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

### NPoco .NET Core Support

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Initial work and upgrades to support .NET Core, including version 1.0 and RC2.

```csharp
// Upgrade to .net core 1.0
// Update to .NET Core RC2
// Initial work to support .NET Core
```

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

### FetchMultiple with Tuple Deconstruction (C# 7)

Source: https://github.com/schotime/npoco/wiki/Multiple-Result-Sets

Shows a simplified way to use FetchMultiple with C# 7 tuple deconstruction syntax for easier access to the returned lists.

```csharp
IDatabase db = new Database("connStringName");
var (users, addresses) = db.FetchMultiple<User, Address>("select * from users;select * from addresses;");
```

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

### Chaining SqlBuilder Methods

Source: https://github.com/schotime/npoco/wiki/Sql-Templating

Illustrates how to chain multiple SqlBuilder methods to construct a more complex query, including multiple WHERE clauses.

```csharp
sqlBuilder
    .Where("height >= @0", 176)
    .Where("weight > @0 and weight < @1", 30, 60);
```

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

### Executing the Built Query

Source: https://github.com/schotime/npoco/wiki/Sql-Templating

Shows how to execute a query built with SqlBuilder using a Database object.

```csharp
var db = new Database("conn");
db.Fetch<User>(template);
```

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

### SqlBuilder API Documentation

Source: https://github.com/schotime/npoco/wiki/Sql-Templating

Provides documentation for the methods available in the SqlBuilder class for constructing SQL queries.

```APIDOC
SqlBuilder:
  AddTemplate(string sql, params object[] parameters)
    Adds a SQL template with placeholders for dynamic clauses.
    Parameters:
      sql: The base SQL query string.
      parameters: Initial parameters for the query.
    Returns: A SqlBuilder instance.

  Where(string sql, params object[] parameters)
    Adds a filter clause to the query. Requires the /**where**/ token in the template.
    Parameters:
      sql: The WHERE clause SQL string.
      parameters: Parameters for the WHERE clause.
    Returns: The SqlBuilder instance for chaining.

  Select(params string[] columns)
    Replaces the SELECT columns in the query. Requires the /**select**/ token.
    Parameters:
      columns: An array of column names or expressions to select.
    Returns: The SqlBuilder instance for chaining.

  Join(string sql, params object[] parameters)
    Adds an INNER JOIN clause to the query. Requires the /**join**/ token.
    Parameters:
      sql: The JOIN clause SQL string.
      parameters: Parameters for the JOIN clause.
    Returns: The SqlBuilder instance for chaining.

  LeftJoin(string sql, params object[] parameters)
    Adds a LEFT JOIN clause to the query. Requires the /**leftjoin**/ token.
    Parameters:
      sql: The LEFT JOIN clause SQL string.
      parameters: Parameters for the LEFT JOIN clause.
    Returns: The SqlBuilder instance for chaining.

  OrderBy(string sql, params object[] parameters)
    Adds an ORDER BY clause to the query. Requires the /**orderby**/ token.
    Parameters:
      sql: The ORDER BY clause SQL string.
      parameters: Parameters for the ORDER BY clause.
    Returns: The SqlBuilder instance for chaining.

  OrderByCols(params string[] columns)
    Adds columns to the ORDER BY clause. Requires the /**orderbycols**/ token.
    Parameters:
      columns: An array of column names to order by.
    Returns: The SqlBuilder instance for chaining.

  GroupBy(string sql, params object[] parameters)
    Adds a GROUP BY clause to the query. Requires the /**groupby**/ token.
    Parameters:
      sql: The GROUP BY clause SQL string.
      parameters: Parameters for the GROUP BY clause.
    Returns: The SqlBuilder instance for chaining.

  Having(string sql, params object[] parameters)
    Adds a HAVING clause to the query. Requires the /**having**/ token.
    Parameters:
      sql: The HAVING clause SQL string.
      parameters: Parameters for the HAVING clause.
    Returns: The SqlBuilder instance for chaining.
```

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

### NPoco Constructor Parameter Handling

Source: https://github.com/schotime/npoco/wiki/POCO-Constructors

Illustrates how NPoco handles constructors with parameters by providing default values before mapping. This behavior is crucial for compatibility with C# 9 records.

```csharp
/*
 * From version 5, class/records with non parameterless constructors will be supported.
 *
 * However unlike other libraries, the constructor parameters are filled with default values, eg null, 0 etc.
 * and then mapping occurs as per usual.
 *
 * This is needed to support c# 9 records.
 *
 * Note: If there is a parameterless constructor (public or private), it will be used by default.
 */

// Example of a class with a non-parameterless constructor
public class MyRecord
{
    public int Id { get; set; }
    public string Name { get; set; }

    // NPoco will provide default values for these parameters (e.g., 0 for int, null for string)
    public MyRecord(int id, string name)
    {
        Id = id;
        Name = name;
    }
}

// Example of a class with a parameterless constructor (preferred by NPoco if available)
public class AnotherClass
{
    public int Value { get; set; }

    public AnotherClass() { }

    public AnotherClass(int value)
    {
        Value = value;
    }
}
```

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

### npoco FetchOneToMany Usage

Source: https://github.com/schotime/npoco/wiki/One-to-Many-Query-Helpers

Demonstrates how to use the FetchOneToMany helper method in npoco to fetch data with one-to-many relationships. It shows two versions of the method call.

```csharp
IDatabase db = new Database("connStringName");

//v2
var users = db.FetchOneToMany<UserDto, CarDto>(x => x.UserId, 
    "select u.*, c.* from Users u inner join Cars c on u.UserId = c.UserId order by u.UserId");

//v3
var users = db.FetchOneToMany<UserDto>(x => x.Cars, 
    "select u.*, c.* from Users u inner join Cars c on u.UserId = c.UserId order by u.UserId");
```

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

### NPoco CreateCommand Public

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Makes the `CreateCommand` method public.

```csharp
// Make CreateCommand public (#116)
```

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

### Async Methods for First/FirstOrDefault/Single/SingleOrDefault

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Version 3.9.0 adds asynchronous counterparts for First, FirstOrDefault, Single, and SingleOrDefault methods, improving performance for I/O-bound operations.

```cs
var user = await db.FirstOrDefaultAsync<User>(x => x.Id == userId);
var product = await db.SingleAsync<Product>(p => p.Sku == sku);
```

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

### Fetch All Users (Eager)

Source: https://github.com/schotime/npoco/wiki/Query-List

Fetches all records of type User from the database using an eager loading strategy. This is the simplest way to retrieve all data.

```csharp
List<User> users = db.Fetch<User>();
```

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

### FetchMultiple with Tuple

Source: https://github.com/schotime/npoco/wiki/Multiple-Result-Sets

Demonstrates fetching multiple result sets into a Tuple of Lists using the FetchMultiple method. This requires a database provider that supports returning multiple result sets.

```csharp
IDatabase db = new Database("connStringName");
Tuple<List<User>, List<Address>> data = db.FetchMultiple<User, Address>("select * from users;select * from addresses;");
var users = data.Item1;
var addresses = data.Item2;
```

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

### NPoco FetchBy Removal

Source: https://github.com/schotime/npoco/wiki/Release-Notes

The `FetchBy` method has been removed; use `Query<T>` instead.

```csharp
// Remove FetchBy (use `Query<T>` now)
```

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

### NPoco Npgsql Parameter Support

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Adds support for Npgsql 3.x, including the use of parameter names.

```csharp
// Support Npgsql 3.x with parameter names
```

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

### SqlBulkCopyOptions for BulkInsert Helper

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Makes SqlBulkCopyOptions available to the BulkInsert helper. This allows for more granular control over the bulk insert operation, such as setting timeouts or batch sizes.

```C#
public class BulkInsertHelper {
    public void BulkInsert<T>(IEnumerable<T> data, SqlBulkCopyOptions options) {
        // ... implementation using SqlBulkCopyOptions ...
    }
}
```

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

### NPoco Async Methods

Source: https://github.com/schotime/npoco/wiki/Release-Notes

NPoco provides asynchronous methods for querying, updates, inserts, and deletes, enhancing performance and responsiveness in applications.

```csharp
// Add Async methods for querying, updates, inserts and deletes
```

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

### NPoco FetchMultiple Mapping Fix

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Fixes the `FetchMultiple` method after mapping to weak types caused a breakage.

```csharp
// Fix `FetchMultiple` after mapping to weak types broke it.
```

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

### Npgsql Provider Configuration for NPoco

Source: https://github.com/schotime/npoco/wiki/Postgres

Shows the XML configuration required in app.config or web.config to register the Npgsql data provider for use with NPoco on .NET Framework.

```xml
<system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider"
           invariant="Npgsql2"
           support="FF"
           description=".Net Framework Data Provider for Postgresql Server"
           type="Npgsql.NpgsqlFactory, Npgsql, Version=2.2.5.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
  </system.data>
```

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

### NPoco Integration with MiniProfiler

Source: https://github.com/schotime/npoco/wiki/Debugging-and-Profiling

This code shows how to integrate NPoco with MiniProfiler for performance profiling. It overrides the OnConnectionOpened method to wrap the database connection with a profiled connection.

```csharp
public class MyDb : Database 
{
    public MyDb(string connectionStringName) : base(connectionStringName) { }
    public override IDbConnection OnConnectionOpened(IDbConnection conn)
    {
        return new ProfiledDbConnection((DbConnection)conn, MiniProfiler.Current);
    }
}
```

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

### NPoco Fluent Config Builder Version

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Adds a missing `Version` overload to the fluent configuration builder.

```csharp
// Add missing Version overload to fluent config builder
```

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

### First and FirstOrDefault Methods

Source: https://github.com/schotime/npoco/wiki/Query-Single-Object

Explains the 'First<T>()' and 'FirstOrDefault<T>()' methods, which are used to retrieve the first record from a query result. These methods do not throw an exception if multiple records are returned, unlike methods designed for unique results.

```csharp
// Example usage of First<T>() and FirstOrDefault<T>() would go here, but is not provided in the source text.
// The text states their behavior regarding multiple records and exceptions.
```

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

### Database Shortcut Method for Snapshotting

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Introduces a shortcut database method to update snapshotted POCOs. This enhancement likely improves performance and simplifies the process of updating data that has been snapshotted.

```C#
// Example of a potential shortcut method signature
public void UpdateSnapshotPoco(object poco, string[] snapshotColumns);
```

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

### Manual SQL Logging with NPoco

Source: https://github.com/schotime/npoco/wiki/Debugging-and-Profiling

This snippet demonstrates how to manually log the last executed SQL command by overriding the OnExecutingCommand method in a custom NPoco Database class. It writes the formatted command to a log file.

```csharp
public class MyDb : Database 
{
    public MyDb(string connectionStringName) : base(connectionStringName) { }
    public override void OnExecutingCommand(IDbCommand cmd)
    {
        File.WriteAllText("log.txt", FormatCommand(cmd));
    }
}
```

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

### NPoco Dynamic Parameters

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Allows `Dictionary<string, object>` to be passed as the first parameter for dynamic parameter binding.

```csharp
// Allow `Dictionary<string, object>` to be passed as first parameter for dynamic parameters (@andersjonsson)
```

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

### NPoco Firebird Async Support

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Fixes asynchronous support for the Firebird database provider.

```csharp
// Fix Async support in Firebird (#297)
```

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

### User Property and AutoIncrement Configuration

Source: https://github.com/schotime/npoco/wiki/Using-Guid-Column-as-Primary-Key

Demonstrates the correct property type for UserId as System.Guid and how to set AutoIncrement to true for primary keys in the npoco framework.

```csharp
public Guid UserId { get; set; }
```

```csharp
[PrimaryKey("UserId", AutoIncrement = true)]
```

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

### Fetch Users with Raw SQL (Lazy)

Source: https://github.com/schotime/npoco/wiki/Query-List

Fetches User records using a custom raw SQL query with a lazy loading strategy. The query executes only when the results are iterated over, which can be more memory-efficient for large datasets but requires careful understanding.

```csharp
List<User> users = db.Query<User>("select u.* from users where u.isActive = 1");
```

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

### Async Database Operations

Source: https://github.com/schotime/npoco/wiki/Release-Notes

NPoco 6.0.0 introduces full asynchronous support for database operations, including IAsyncDatabase, cancellation tokens, and async methods for Open, Close, BeginTran, and AbortTran, targeting netstandard 2.1 and net8.

```cs
public interface IAsyncDatabase {
    Task OpenAsync();
    Task CloseAsync();
    Task BeginTransactionAsync();
    Task AbortTransactionAsync();
    // ... other async methods
}
```

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

### Database Interceptor Interfaces

Source: https://github.com/schotime/npoco/wiki/Version-3

Defines the interfaces for database interceptors, allowing custom logic to be executed at various stages of database operations. These include executing commands, managing connections, handling exceptions, data manipulation, and transactions.

```csharp
public interface IExecutingInterceptor : IInterceptor
{
    void OnExecutingCommand(IDatabase database, IDbCommand cmd);
    void OnExecutedCommand(IDatabase database, IDbCommand cmd);
}

public interface IConnectionInterceptor : IInterceptor
{
    IDbConnection OnConnectionOpened(IDatabase database, IDbConnection conn);
    void OnConnectionClosing(IDatabase database, IDbConnection conn);
}

public interface IExceptionInterceptor : IInterceptor
{
    void OnException(IDatabase database, Exception x);
}

public interface IDataInterceptor : IInterceptor
{
    bool OnInserting(IDatabase database, InsertContext insertContext);
    bool OnUpdating(IDatabase database, UpdateContext updateContext);
    bool OnDeleting(IDatabase database, DeleteContext deleteContext);
}

public interface ITransactionInterceptor : IInterceptor
{
    void OnBeginTransaction(IDatabase database);
    void OnAbortTransaction(IDatabase database);
    void OnCompleteTransaction(IDatabase database);
}
```

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

### Fetch Data as Dictionary or Object Array

Source: https://github.com/schotime/npoco/wiki/Dictionary-and-Object-Array-Queries

Demonstrates how to fetch query results into a Dictionary<string, object> or an object[] when the exact columns are not known beforehand. This is useful for dynamic queries.

```csharp
var users = db.Fetch<Dictionary<string, object>>("select * from users");
```

```csharp
var users = db.Fetch<object[]>("select * from users");
```

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

### Nested Object Mapping - Old Convention (V2 vs V3)

Source: https://github.com/schotime/npoco/wiki/Version-3

Compares the nested object mapping behavior between V2 and V3. V2 relied on the order of generic arguments, while V3 uses a depth-first traversal of properties, mapping columns based on property order.

```csharp
//v2
db.Fetch<User, Address>("select u.*, a.* from users u inner join addresses a on u.userid = a.userid");
//v3
db.Fetch<User>("select u.*, a.* from users u inner join addresses a on u.userid = a.userid");
```

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

### Nested Object Mapping with Naming Convention

Source: https://github.com/schotime/npoco/wiki/Version-3

Demonstrates how to map nested objects in C# using a naming convention with double underscores (__) in SQL queries. This allows fetching related data into nested class properties.

```sql
db.Fetch<User>("select userid, name, streetno AS address__streetno, streetname AS address__streetname from users");
// When its not
db.Fetch<User>(@"select userid, name, streetno AS address__streetno, streetname AS address__streetname
                 from users u inner join addresses a on u.userid = a.userid");
```

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

### Fetching Single Record with Tuples

Source: https://github.com/schotime/npoco/wiki/Tuples

Demonstrates how to retrieve a single record from the database directly into a C# tuple. This simplifies mapping simple query results to strongly-typed variables.

```cs
var (foo, bar) = db.Single<(string, string)>(@"select 'foo', 'bar'");

Assert.AreEqual(foo, "foo");
Assert.AreEqual(bar, "bar");
```

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

### NPoco Projection to Anonymous Class

Source: https://github.com/schotime/npoco/wiki/Simple-linq-queries

Illustrates how to select specific columns from a table and project them into an anonymous class using NPoco's ProjectTo method. This is useful for retrieving only the necessary data, improving performance.

```csharp
var users = db.Query<User>().Where(x => x.Name.StartsWith("Bo")).ProjectTo(x => new { x.Id, x.Name }).ToList();
```

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

### Fetch Users with Raw SQL (Eager)

Source: https://github.com/schotime/npoco/wiki/Query-List

Fetches User records using a custom raw SQL query. This provides maximum flexibility for complex data retrieval scenarios.

```csharp
List<User> users = db.Fetch<User>("select u.* from users where u.isActive = 1");
```

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

### NPoco Composite Key Handling

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Improvements have been made to handle composite keys, including fixes for `Exists` calls and the `IsNew<T>` method.

```csharp
// Fix `Exists` call when using composite keys
// Fix `IsNew<T>` for composite keys
```

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

### NPoco SqlOfTContext Implementation

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Implements `SqlOfTContext` for enhanced SQL context management.

```csharp
// Implement SqlOfTContext (@zpqrtbnk)
```

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

### C# Classes for Nested Object Mapping

Source: https://github.com/schotime/npoco/wiki/Mapping-to-Nested-Objects

Defines the C# classes `User` and `Address` which will be used to demonstrate mapping query results to objects with nested properties. The `User` class contains an `Address` object.

```csharp
public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
}
```

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

### Select Object by Primary Key

Source: https://github.com/schotime/npoco/wiki/Query-Single-Object

Loads a single object from the database by providing its primary key to the SingleById<T>() method. This is the most straightforward way to fetch an object.

```csharp
IDatabase db = new Database("connStringName");
User u = db.SingleById<User>(3);
```

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

### NPoco Firebird Support

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Adds support for the Firebird database system.

```csharp
// Add Firebird support
```

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

### NPoco EnumMapper Exception

Source: https://github.com/schotime/npoco/wiki/Release-Notes

The `EnumMapper` now throws a more informative exception when an error occurs.

```csharp
// EnumMapper now throws a useful exception to indicate something went wrong
```

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

### Support New Oracle Client

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Adds support for a new Oracle client. This likely involves adapting to changes in Oracle's data access interfaces or features.

```C#
public class OracleProvider {
    // ... implementation updated for new Oracle client ...
}
```

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

### NPoco Config Cache

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Configuration is now cached per `DatabaseFactory` to improve performance.

```csharp
// Cache config per DatabaseFactory
```

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

### Mapping Database Columns to Existing Objects in C#

Source: https://github.com/schotime/npoco/wiki/Map-to-an-Existing-Object

Demonstrates how to use npoco's `SingleOrDefaultInto` method to populate properties of an existing C# object from database query results. This is useful for updating specific fields of an object without re-instantiating it.

```csharp
public class User 
{
    public int UserId { get;set; }
    public string Email { get;set; }
}

var user = new User() { UserId = 1 };
IDatabase db = new Database("connStringName");
db.SingleOrDefaultInto(user, "select Email from users where userid = @0", 1);
```

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

### NPoco LINQ Fixes

Source: https://github.com/schotime/npoco/wiki/Release-Notes

This version includes numerous fixes for LINQ queries, addressing issues with Contains, StartsWith, Substring, Projections, and Constant Boolean Expressions.

```csharp
// Numerous LINQ fixes (!Contains, !x.Name.StartsWith, Substring, Projections, Constant Boolean Expressions)
```

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

### Passing Tuples as Parameters

Source: https://github.com/schotime/npoco/wiki/Tuples

Shows how C# tuples can be used to pass multiple parameters to SQL queries executed via NPoco. This provides a concise way to bundle related data for updates or inserts.

```cs
var record = (2, "Timmy", 5);
db.Execute("update kids set name = @Item2, age = @Item3 where id = @Item1", record);
```

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

### SQL Server Table Design for User Table

Source: https://github.com/schotime/npoco/wiki/Using-Guid-Column-as-Primary-Key

Specifies the required settings for creating the 'Users' table in SQL Server, including data type, nullability, RowGuid, and default value for the UserId column.

```sql
Data Type: uniqueidentifier
Allow Nulls: false
RowGuid: Yes
Default Value or Binding: (newid())
```

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

### Private Constructor with Snapshotter

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Enables the snapshotter to work with POCOs that have private constructors. This broadens the applicability of snapshotting to classes with restricted instantiation.

```C#
public class Snapshotter {
    public void Snapshot(object target) {
        // ... handles private constructors ...
    }
}
```

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

### NPoco JsonNet Integration

Source: https://github.com/schotime/npoco/wiki/Release-Notes

NPoco now includes Json.NET internally for serialized columns and offers a separate NPoco.JsonNet package, using fastJSON internally by default.

```csharp
// Add JSON.NET internally for serialized column
// Add separate NPoco.JsonNet package and use fastJSON internally by default
```

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

### NPoco SqlBulkCopy SqlParameters

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Allows `SqlParameters` to be used within `SqlBulkCopy` operations.

```csharp
// Allow SqlParameters to be used in SqlBulkCopy
```

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

### Tests for Linq Provider

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Introduces comprehensive tests for the LINQ provider. This signifies a commitment to quality and stability for the LINQ query capabilities.

```C#
public class LinqProviderTests {
    [Fact]
    public void Test_Where_Clause() {
        // ... test cases for LINQ provider ...
    }
}
```

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

### NPoco IDatabase XML Comments

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Adds XML comments to the `IDatabase` interface for better documentation and IntelliSense.

```csharp
// Add XML comments to IDatabase
```

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

### Page<T> Class Definition

Source: https://github.com/schotime/npoco/wiki/Query-Paging

Defines the structure of the Page<T> class, which holds metadata for paginated results, including current page, total pages, total items, items per page, and the list of items.

```csharp
public class Page<T> 
{
    public long CurrentPage { get; set; }
    public long TotalPages { get; set; }
    public long TotalItems { get; set; }
    public long ItemsPerPage { get; set; }
    public List<T> Items { get; set; }
}
```

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

### Fix Underscore Mapping and Extensions

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Addresses issues related to mapping properties with underscores and associated extensions. This improves the handling of naming conventions.

```C#
public class MappingService {
    public void MapPocoToRow(object poco, DataRow row) {
        // ... improved underscore mapping ...
    }
}
```

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

### NPoco SqlBuilder Brackets

Source: https://github.com/schotime/npoco/wiki/Release-Notes

The `SqlBuilder` now correctly places brackets around each call to `Where()`.

```csharp
// SqlBuilder will put brackets around each call to Where()
```

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

### Allow Mappings to be Inherited

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Enables mapping configurations to be inherited. This allows for defining base mapping rules that can be extended by derived classes.

```C#
public abstract class BaseMapper<T> {
    protected virtual void ConfigureMapping(PocoData pocoData) { /* ... */ }
}

public class DerivedMapper : BaseMapper<MyPoco> {
    // Inherits mapping configuration
}
```

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

### Fetch Users with Criteria (Eager)

Source: https://github.com/schotime/npoco/wiki/Query-List

Fetches User records that match a specified criteria, such as 'isActive = 1'. This method allows for filtering data before it's loaded into memory.

```csharp
List<User> users = db.Fetch<User>("where isActive = 1");
```

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

### Firebird Database Type Addition

Source: https://github.com/schotime/npoco/wiki/Release-Notes

Introduces support for the Firebird database system. This expands the range of compatible databases.

```C#
public class DatabaseFactory {
    public IDatabase CreateDatabase(string connectionString, string providerName) {
        if (providerName == "Firebird") {
            // ... Firebird specific implementation ...
        }
        // ... other providers ...
    }
}
```