NPoco的使用方法

鉴于目前百度找不到，本文从以下网址复制粘贴汇总：https://github.com/schotime/NPoco/wiki，供有缘者参考。

Home 

Welcome to the NPoco wiki! NPoco is a fork of PetaPoco with a handful of extra features.

Getting Started: Your first query

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");
}

Note: Database needs to be disposed to close the connection (think of it as your connection object).

This works by mapping the column names to the property names on the User object. This is a case-insensitive match.
There is no mapping setup needed for this (query only) scenario.

Mapping

By default no mapping is required. It will be assumed that the table name will be the class name and the primary key will be 'Id' if its not specified with the attributes below.

Basic mapping is done via attributes. The most used ones are:

1. [TableName] This takes a "name" parameter which indicates the table for which the Poco class will be mapped to.

2. [PrimaryKey] This has a "columnName" parameter which takes the column name of the primary key for the table. Multiple primary keys can be specified by separating the columns with a comma. There is also an "autoIncrement" property which is used to indicate whether the primary key column is auto incremented e.g. identity column in Sql Server. By default this is true. For Oracle there is also a "sequenceName" property which will be used when inserting data with Oracle.

3. [Column] This is used if the column name does not match the property name.

4. [Ignore] This property will be ignored and cannot be mapped to.

5. [ResultColumn] Properties marked with the Result column can be mapped into, however these properties will not be included in inserts or updates. Note: These columns will need to be explicitly specified in the SQL. It will not be included in the auto generated SQL.

6. [ComputedColumn] Properties with the Computed column attribute work the same way as the Result column attributes however they will be auto selected in the SQL.

7. [SerializedColumn] (v3+ only) Properties with the Serialized column attribute will serialize their data with the default serializer unless changed by implementing a IColumnSerializer. There is a NPoco.JsonNet library which allows you to use the JsonNetColumnSerializer. These are configured once by:

DatabaseFactory.ColumnSerializer = new JsonNetColumnSerializer();

Example

[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; }
}

If you don't like attribute based mapping, the take a look at fluent / convention based mapping

Query Single Object

schotime edited this page on 27 Apr 2012 · 3 revisions

Selecting an object from the database can be done in a few different ways.

By Id

The easiest way to load an object from the database is by passing the primary key to the SingleById<T>() method.

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

Via SQL

Below you can see that only the where is specified. If you don't explicitly supply the select clause it will be automatically generated for you and the where will then be appended.

User u = db.Single<User>("where emailaddress = @0", "email@domain.com");
or
User u = db.Single<User>("select u.* from users u where emailaddress = @0", "email@domain.com");

Both these methods have a 'OrDefault' method if you are unsure that the object will exist. If it doesn't exist and you don't use the 'OrDefault' override it will throw an exception.

There are also First<T> and FirstOfDefault<T> which will not throw an exception if more than 1 record is returned.

Create Read Update Delete

This wiki page refers to the User class found here.

Inserting a new record

IDatabase db = new Database("connStringName");
User u = new User() 
{
    Email = "name@domain.com",
    LastLoggedIn = DateTime.UtcNow
};

db.Insert(u);

This will insert the User object into the users table generating a new identity value. This value will be populated back into the object after the insert statement. For example, the following would be true.

Reading the new record

var user = db.SingleById(u.UserId);
Assert.AreEqual(u.Email, user.Email);

Updating the record

Once I have the object, I can update its properties. After calling the Update method, those changes will be persisted to the database.

var user = db.SingleById(1);
user.Email = "new@domain.com";
db.Update(user);

Deleting the record

If I decide I no longer need the record I can delete it in a very similar fashion to Insert and Update. That is by passing the object to the Delete method. Just the primary key value can also be passed to the Delete method, however the generic type parameter will need to be specified.

var user = db.SingleById(1);
db.Delete(user);
or

db.Delete<User>(1);

Upsert the record

This will insert a record if it is new or update an existing record if it already exists. Its existence is determined by its primary key.

IDatabase db = new Database("connStringName");
User u = new User() 
{
    Email = "name@domain.com",
    LastLoggedIn = DateTime.UtcNow
};

db.Save(u);

Query List

Here are some of the ways to fetch multiple rows from the database.

Eager

1: Fetch all

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

　　

2: Fetch with criteria

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

3: Fetch with raw SQL

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

Lazy

Warning: The following method Query<T> uses the yield keyword. It will only run the query when the results are being iterated over. Please use the Fetch<T> method if you don't fully understand this concept.

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

Query Paging

There are two main methods used for paging.

Page<T>

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

where Page<T> is defined by:

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; }
}

Note: You must provide an order by statement in your SQL statement so that the query knows in which order you want your data to be paged.

The first parameter to the Page<T> method is the page number. This number begins at 1 for the first page. The second parameter is the size of the page. In the example above, the second page with 10 users in it will be returned.

SkipTake<T>

The SkipTake<T> method is very similar to the Skip and Take methods in LINQ. It has the same number of parameters as the Page<T> method, but instead of the first parameter being the page number, it is the number of records to skip. The second parameter is the number of records to return after x number of records have been skipped. To return the same results as the Page<T>method, the query would be as follows:

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

The difference between the two methods is that SkipTake<T> returns a List<T> whereas Page<T>returns a Page<T> object, which has extra properties convenient for rendering a pager.

There is also an overload of Fetch<T> which has the same parameter signature of the Page<T>method but returns a List<T>.

SQL Transaction Support

This wiki page refers to NPoco transaction support.

Example 1

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

Example 2

using (IDatabase db = new Database("connStringName")) 
{
    using (var transaction = db.GetTransaction())
    {
        //Your CRUD operation here
        transaction.Complete();
    }
}

Map to an Existing Object

Using the methods SingleInto, SingleOrDefaultInto, FirstInto and FirstOrDefaultInto it is possible to map columns from the database onto an existing object. Only the columns in the query will be set on the existing object.

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);

One to Many Query Helpers

These helpers allow you to map a query that has a one-to-many relationship to nested list objects. Given the following classes:

public class UserDto
    {
        public int UserId { get; set; }
        public string Name { get; set; }
        public List<CarDto> Cars { get; set; }
    }

    public class CarDto
    {
        public string Make { get; set; }
        public string Color { get; set; }
    }

and the following query:

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");

This will give you a list of UserDto objects and for each of these the list of CarDto's will also be populated.

Note:
1. The first parameter to this method is a lambda expression which identifies the unique property of the first object.
2. The order of the columns is extremely important. It must align with the order of the generic parameters defined. In this example the query specifies u.* then c* which maps to <UserDto, CarDto>.
3. If you are mapping to objects that are also used for inserting data then you will need to make sure you ignore the List<> property using the [Ignore] attribute.
4. If you are using an outer join for your query and you are expecting that some of the many's will not be populated, make sure to use the second lambda in the FetchOneToMany call. This should return the property of the primary of the many object. In the above example this might be x => x.CarId.
5. FetchOneToMany cannot be used to fetch nested one-to-many relationships, or multiple one-to-many relationships. For example, if UserDto also had a list of BoatsDto, only the CarDto list or the BoatsDto list can be loaded and mapped with one query.

Mapping to Nested Objects

Michael Ganss edited this page on 11 Jul 2016 · 4 revisions

These helpers allow you to map a query to an object that has nested objects. Given the following classes:

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; }
    }

and the following query:

IDatabase db = new Database("connStringName");
var users = db.Fetch<User, Address>("select u.UserId, u.Name, u.Street, u.City from Users");

This will give you a list of User objects with the nested class Address mapped.

Note:

1. The order of the columns is extremely important. The columns in the query need to be specified in the same order as the generic parameters are specified. eg. The User columns are specified first, then the Address columns next.
2. If you are mapping to objects that are also used for inserting data then you will need to make sure you ignore the Address property using the [ResultColumn] attribute. From v3, you will additionally need the [ComplexMapping] attribute.

Dictionary and Object Array Queries

Sometimes, you don't know the columns that will be returned from a query. This is where being able to map to a Dictionary<string,object>, or object[] comes in handy.

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

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

Note: From NPoco version > 1.0.3, all array types (value types eg. string[], int[], double[], DateTime[] etc) will be a valid generic parameter.

Change tracking for updates

Using the Snapshotter

The snapshot is used to track the changes to the entity, so that only the properties that have changed will be updated. In the following example only the new Name will be sent to the database, as the Age value is the same as it was when the snapshot was started.

IDatabase db = new Database("connString");
var user = db.SingleById<User>(1);  // Name = "Ted", Age = 21

var snapshot = db.StartSnapshot(user);  // Any changes after this will be recorded.

user.Name = "Bobby";
user.Age = 21;

db.Update(user, snapshot.UpdatedColumns());  // Only the Name column will be updated

Only the changes that have been made before UpdatedColumns() is called will be included in the change-set.

Composite Primary Keys

Composite keys can be specified by placing a comma between the two column names in the [PrimaryKey] attribute.

[TableName("Users")]
[PrimaryKey("UserId,UserName")]
public class User
{
    public int UserId { get; set; }
    public string UserName { get;set; }
}

When setting a composite key, the AutoIncrement attribute will always default false.

If you want to get one of these objects from the database using the SingleById group of methods then you can use an anonymous type.

IDatabase db = new Database("connStringName");
var user = db.SingleById<User>(new {UserId = 1, UserName = "user"});

Multiple Result Sets

This feature enables you to map multiple queries with only one call to the database. The FetchMultiple method returns a Tuple<List<T>, List<T1>>.

Support: This is only supported on databases which can return multiple result sets, eg. NextResult() on IDataReader is implemented. Sql Server and Postgresql via Npgsql support this.

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;

Fluent mappings including conventional

Fluent Mappings

To a mapping for a class you can inherit from Map<T> where T is the class you are mapping for.

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");
        });        
    }
}

Mappings can also inherit from Mappings and specify all mappings in once class using the For<>method.

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

Database Factory Setup

You only want to create the mappings once and we do this using a Database Factory.

public void Application_Start()
{
    MyFactory.Setup();
}
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());
        });
    }
}

Then you can use it like so in your code.

var database = MyFactory.DbFactory.GetDatabase();

If you are using a container then you could have something like

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

Simple linq queries

Query<T>

NPoco introduces a simple way to fetch an object using LINQ query. Here is a simple example.

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

The LINQ keywords that are available are:

• ProjectTo
• Count
• Any
• Where
• OrderBy
• OrderByDescending
• ThenBy
• ThenByDescending
• Limit
• Include
• IncludeMany

Here is how you do an IN clause:

var users = db.Query<User>().Where(x => new[] {1,2,3,4}.Contains(x.UserId)).ToList();
// or using the 'In' extension method
var users = db.Query<User>().Where(x => x.UserId.In(new[] {1,2,3,4})).ToList();

There are also a number of string methods that can be used in the where clause. Here are a few examples:

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();

Note. Not all operators have been implemented so if you find one that is not but should be, please create an issue.

Query Provider

Query<T>

Find all the users with a UserId greater than 50, order it by Name and only return 20 records offset by 40 (eg. page 3)

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

Note: The query will only be run when ToList(), ToEnumerable(), or any of the scalar methods that return 1 value are called (eg. Single, Count etc.)

UpdateMany<T>

Update all of type T using a Where if necessary

var list = new[] {1, 2, 3, 4};

// Update only the Name field using the template User passed 
// into Execute where the UserId in (1,2,3,4)
// If you turn ExecuteDefaults on it won't 
// set default properties (eg. null, or 0 for int)
db.UpdateMany<User>()
    .Where( x => x.UserId.In(list))
    //.ExcludeDefaults()
    .OnlyFields(x => x.Name)
    .Execute(new User() {Name = "test"});

Note: The query will only be run when Execute is called.

DeleteMany<T>

Delete all of type T using a Where if necessary

var list = new[] {1, 2, 3, 4};

db.DeleteMany<User>()
    .Where(x => list.Contains(x.UserId))
    .Execute();

Note: The query will only be run when Execute is called.

Version column support

[VersionColumn]

A numeric version field can be used to detect conflicting updates:

[TableName("Users")]
[PrimaryKey("UserId")]
public class User
{
    public int UserId { get;set; }

    [VersionColumn("VersionInt", VersionColumnType.Number)]
    public long VersionInt { get; set; }
}

Updates will automatically check and increment the version and throw DBConcurrencyException if it is outdated. This can be disabled by setting Database.VersionException = VersionExceptionHandling.Ignore.

In SQL Server a rowversion timestamp datatype can be used for the version column with VersionColumnType.RowVersion:

[TableName("Users")]
[PrimaryKey("UserId")]
public class User
{
    public int UserId { get;set; }

    [VersionColumn("Version", VersionColumnType.RowVersion)]
    public byte[] Version { get; set; }
}

Sql Templating

You can use the SqlBuilder to build queries up with conditional wheres, columns, orderby's etc.

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

Here you can specify one of Where, Select, Join, LeftJoin, OrderBy, OrderByCols, GroupByand Having The corresponding sql tokens are specified below.

/// Adds a filter. The Where keyword still needs to be specified. Uses /**where**/
    public SqlBuilder Where(string sql, params object[] parameters)

    /// Replaces the Select columns. Uses /**select**/
    public SqlBuilder Select(params string[] columns)

    /// Adds an Inner Join. Uses /**join**/
    public SqlBuilder Join(string sql, params object[] parameters)

    /// Adds a Left Join. Uses /**leftjoin**/
    public SqlBuilder LeftJoin(string sql, params object[] parameters)

    /// Adds an Order By clause. Uses /**orderby**/
    public SqlBuilder OrderBy(string sql, params object[] parameters)

    /// Adds columns in the Order By clause. Uses /**orderbycols**/
    public SqlBuilder OrderByCols(params string[] columns)

    /// Adds a Group By clause. Uses /**groupby**/
    public SqlBuilder GroupBy(string sql, params object[] parameters)

    /// Adds a Having clause. Uses /**having**/
    public SqlBuilder Having(string sql, params object[] parameters)

The statements can be chained and the parameters start from 0 for each new statement.

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

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

The templates can be used anywhere the Sql class can be used.

Debugging and Profiling

There are a few ways to debug/profile NPoco. They are listed below, and are commonly done by inheriting from Database and overriding a specific method. Note: Make sure you instantiate your new class (MyDb as below) when creating a Database from then on.

Manual

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

MiniProfiler

http://miniprofiler.com/

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

Glimpse

http://getglimpse.com/

Glimpse will usually hook itself up by installing the following packages.

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

Show last SQL executed on the ASP.NET error page

Credit: Sam Saffron

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

    public override void OnException(Exception e)
    {
        base.OnException(e);
        e.Data["LastSQL"] = this.LastSQL;
    }
}
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();
}