C # NPOCO lightweight ORM framework (advanced ),

C # NPOCO lightweight ORM framework (advanced ),

Continue to translate NPOCO wiki.

This article translates the following pages on home.

Wiki address: https://github.com/schotime/NPoco/wiki

Previous: http://www.cnblogs.com/Aaxuan/p/7366476.html

 

 

Map from database to existing objects

 

UseSingleInto,SingleOrDefaultInto,FirstIntoAndFirstOrDefaultIntoYou can map database columns to existing objects. Only the columns in the query will be set on the existing object.

1 public class User 
2 {
3     public int UserId { get;set; }
4     public string Email { get;set; }
5 }
6 
7 var user = new User() { UserId = 1 };
8 IDatabase db = new Database("connStringName");
9 db.SingleOrDefaultInto(user, "select Email from users where userid = @0", 1);

 

One-to-multiple queries

 

This helps you map a query that has a one-to-multiple relationship with the nested list object. In view of the following categories:

 1     public class UserDto
 2     {
 3         public int UserId { get; set; }
 4         public string Name { get; set; }
 5         public List<CarDto> Cars { get; set; }
 6     }
 7 
 8     public class CarDto
 9     {
10         public string Make { get; set; }
11         public string Color { get; set; }
12     }

Query:

1 IDatabase db = new Database("connStringName");
2 
3 //v2
4 var users = db.FetchOneToMany<UserDto, CarDto>(x => x.UserId, 
5     "select u.*, c.* from Users u inner join Cars c on u.UserId = c.UserId order by u.UserId");
6 
7 //v3
8 var users = db.FetchOneToMany<UserDto>(x => x.Cars, 
9     "select u.*, c.* from Users u inner join Cars c on u.UserId = c.UserId order by u.UserId");

This will provide you with a list of UserDto objects and fill in the CarDto list for each object.

Note:

Ing nested objects

 

This will help you map queries to objects with nested objects. In view of the following categories:

 1 public class User
 2     {
 3         public int UserId { get; set; }
 4         public string Name { get; set; }
 5         public Address Address { get; set; }
 6     }
 7 
 8     public class Address
 9     {
10         public string Street { get; set; }
11         public string City { get; set; }
12     }

Query:

1 IDatabase db = new Database("connStringName");
2 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 mapped using nested classes.

Note:

 

Dictionary and object array Query

 

Sometimes, you do not know the columns returned by the query. This is where the dictionary <string, object>, or object [] can be mapped. Dictionary and object array query.

1 var users = db.Fetch<Dictionary<string, object>>("select * from users");
2 
3 or
4 
5 var users = db.Fetch<object[]>("select * from users");

Note:

All array types (value types) starting from NPoco version> 1.0.3. String [], int [], double [], DateTime [], etc.) is a valid common parameter.

 

Update tracking changes

 

Use snapshots

Snapshots are used to track changes to objects. Therefore, only changed attributes are updated. In the following example, only the new Name will be sent to the database because its age value is the same as the value when the snapshot is started.

1 IDatabase db = new Database ("connString");
2 var user = db.SingleById <User> (1); // Name = "Ted", Age = 21
3
4 var snapshot = db.StartSnapshot (user); // Any changes after this will be recorded.
5
6 user.Name = "Bobby";
7 user.Age = 21;
8 
9 db.Update (user, snapshot.UpdatedColumns ()); // Update only Name column

Only changes before UpdatedColumns () will be included in the change set.

 

Composite primary key

 

The composite key can be used to specify the names of columns placed between two commas.[PrimaryKey]Attribute.

1 [TableName("Users")]
2 [PrimaryKey("UserId,UserName")]
3 public class User
4 {
5     public int UserId { get; set; }
6     public string UserName { get;set; }
7 }

When the composite key is set, the AutoIncrement attribute is always default.false.

If you want to useSingleByIdTo find an object, you can use the anonymous type.

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

 

Multiple result sets

 

This feature allows you to map multiple queries when only one database is called.

FetchMultipleMethod returns a Tuple <T>, List <T1>.

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

Supported:

This only supports databases that can return multiple result sets. NextResult () is implemented on IDataReader (). Npgsql supports SQL Server and Postgresql.

 

Fluent ing

 

For ing of a class, you can use Map <T> to inherit, where T is the class to be mapped.

1 public class UserMapping : Map<User>
 2 {
 3     public UserMapping()
 4     {
 5         PrimaryKey(x => x.UserId);
 6         TableName("Users");
 7         Columns(x =>
 8         {
 9             x.Column(y => y.Name).Ignore();
10             x.Column(y => y.Age).WithName("a_ge");
11         });        
12     }
13 }

Ing can also be inherited from ing, and all mappings can be specified in a class using the For <> method.

1 public class OurMappings : Mappings
2 {
3     public OurMappings()
4     {
5         For<User>().Columns( ....
6     }
7 }

 

Database factory settings:

 

You only need to create a ing once. We use the database factory to complete this operation.

1 public void Application_Start ()
2 {
3 MyFactory.Setup ();
4}
 

  1 public static class MyFactory
  2 {
  3 public static DatabaseFactory DbFactory {get; set;}
  4
  5 public static void Setup ()
  6 {
  7 var fluentConfig = FluentMappingConfiguration.Configure (new OurMappings ());
  8 // or single mapping
  9 // var fluentConfig = FluentMappingConfiguration.Configure (new UserMapping (), ....);
10
11 DbFactory = DatabaseFactory.Config (x =>
12 {
13 x.UsingDatabase (() => new Database ("connString"));
14 x.WithFluentConfig (fluentConfig);
15 x.WithMapper (new Mapper ());
16});
17}
18}

Then you can use it in your code. 

1 var database = MyFactory.DbFactory.GetDatabase();

If you are using a container, you can use something similar.

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

 

Simple LINQ Query

 

Query <T>

NPoco introduces a simple method to obtain objects using the LINQ query. Here is a simple example.

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

Available LINQ keywords:

• Projectid
• Count
• Any
• Where
• OrderBy
• OrderByDescending
• ThenBy
• ThenByDescending
• Limit
• Include
• Includemo-

Here is how you use an IN clause:

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

You can also use many string methods in the where clause. Here are several examples:

1 var users = db.Query<User>().Where(x => x.Name.StartsWith("Bo")).ToList();
2 var users = db.Query<User>().Where(x => x.Name.EndsWith("ob")).ToList();
3 var users = db.Query<User>().Where(x => x.Name.Contains("o")).ToList();
4 var users = db.Query<User>().Where(x => x.Name.ToLower() == "bob").ToList();
5 var users = db.Query<User>().Where(x => x.Name.ToUpper() == "BOB").ToList();

Note: not all operations have been implemented.

 

Query provision

 

Query <T>

Search for all users whose user IDs are greater than 50 and sort by name. Only 20 records are returned.

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

Note:

The query will only run in the ToList (), ToEnumerable (), or the scalar method that returns 1 value.

 

UpdateMany <T>

If necessary, use Where to update all types of T.

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

// Only update the Name field of the UserId object in (1, 2, 3, 4) passed by the template
// If you use the ExecuteDefaults method, the default attribute is not set to null, or 0
db.UpdateMany <User> ()
     .Where (x => x.UserId.In (list))
     //. ExcludeDefaults ()
     .OnlyFields (x => x.Name)
     .Execute (new User () {Name = "test"});

Note:

The query runs only when the call is executed.

 

DeleteMany <T>

If necessary, use Where to delete all types of T.

1 var list = new[] {1, 2, 3, 4};
2 
3 db.DeleteMany<User>()
4     .Where(x => list.Contains(x.UserId))
5     .Execute();

Note:

The query runs only when the call is executed.

 

Version column support

 

[VersionColumn]

A numeric field can be used to detect conflicting updates:

1 [TableName("Users")]
2 [PrimaryKey("UserId")]
3 public class User
4 {
5     public int UserId { get;set; }
6 
7     [VersionColumn("VersionInt", VersionColumnType.Number)]
8     public long VersionInt { get; set; }
9 }

The update will automatically check and add versions. If it is out of date, a DBConcurrencyException will be thrown.

This can be disabled by setting: VersionException = VersionExceptionHandling. Ignore.

 

In SQL Server, rowversion timestamp Data Type

It can be used with VersionColumnType. RowVersion version columns:

1 [TableName("Users")]
2 [PrimaryKey("UserId")]
3 public class User
4 {
5     public int UserId { get;set; }
6 
7     [VersionColumn("Version", VersionColumnType.RowVersion)]
8     public byte[] Version { get; set; }
9 }

 

SQL Template

 

You can use SqlBuilder to create queries where conditions exist, such as columns and orderby.

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

Here you can specify an SQL token for Where, Select, Join, LeftJoin, OrderBy, OrderByCols, GroupBy, and Having.

 1 /// Adds a filter. The Where keyword still needs to be specified. Uses /**where**/
 2     public SqlBuilder Where(string sql, params object[] parameters)
 3 
 4     /// Replaces the Select columns. Uses /**select**/
 5     public SqlBuilder Select(params string[] columns)
 6 
 7     /// Adds an Inner Join. Uses /**join**/
 8     public SqlBuilder Join(string sql, params object[] parameters)
 9 
10     /// Adds a Left Join. Uses /**leftjoin**/
11     public SqlBuilder LeftJoin(string sql, params object[] parameters)
12 
13     /// Adds an Order By clause. Uses /**orderby**/
14     public SqlBuilder OrderBy(string sql, params object[] parameters)
15 
16     /// Adds columns in the Order By clause. Uses /**orderbycols**/
17     public SqlBuilder OrderByCols(params string[] columns)
18 
19     /// Adds a Group By clause. Uses /**groupby**/
20     public SqlBuilder GroupBy(string sql, params object[] parameters)
21 
22     /// Adds a Having clause. Uses /**having**/
23     public SqlBuilder Having(string sql, params object[] parameters)

The statement can be linked, and the parameters of each new statement start from 0.

1 sqlBuilder
2     .Where("height >= @0", 176)
3     .Where("weight > @0 and weight < @1", 30, 60);
4 
5 var db = new Database("conn");
6 db.Fetch<User>(template);

Templates Can be used wherever SQL classes can be used.

 

 

 

So far, except for debugging, there are two or three articles on the wiki homepage on the right.

The translation process is quite uncomfortable. I can understand the English language and the code. I often understand the English meaning after reading the code ..

However, it is okay that the translation means almost the same thing. It may be understandable. If the translation is wrong, please point it out for correction.

 

Translation time: