How to get started with SqlSugar ORM [1], sqlsugarorm

How to get started with SqlSugar ORM [1], sqlsugarorm
Background

SqlSugar is a Chinese-made ORM. Apart from its native performance, it also provides functions that meet various requirements. It is easy to use and can be easily used in one minute.

2. Version x is officially used in internal projects of the company.

The 3.x version is liked by more companies, but it will also complain about many shortcomings.

4. after the release of Version x in May, I got a lot of comments beyond my expectation. I did a lot of unit tests at the time of release, but there are still many bugs, after a large number of user feedback, the product was formed and stabilized around March, and a large number of projects were delivered, which was also well received by users, now I think it is an excellent version, so I started to write this article to introduce it.

5. version x is expected to be released in July this year. version X, with years of in-depth understanding of emit and Lambda parsing. version x will change the ORM, and the specific features will be kept confidential for the time being.

 

Introduction

Supported: Oracle, Mysql, Sqlite, and SqlSever databases. Postgresql will support development in the future, and the supported field data types are also quite complete.

Function: Batch operation, CodeFirst, DbFirst, second-level distributed cache, AOP, read/write splitting, custom SQL function extension, dynamic table alias column alias, attribute table alias column alias, Lambda subquery, JOIN, UNIONALL, insert supports default values, update supports specified column exclusion columns and other common functions

Advantages: Simple performance, syntax, powerful functions, and continuous update and maintenance

Install and download

:

Https://github.com/sunkaixuan/SqlSugar

Nuget:

. Net 4.0 and later versions:Install-Package sqlSugar 

. Net core 2.0:Install-Package sqlSugarCore

 

Connect to database

SqlSugar uses SqlSugarClient to perform database operations. To create SqlSugarClient, We Need To ConnectionConfig this class object.

ConnectionConfig has six attributes:

1. ConnectionString (required ):Connection string

2. DataType(Required):Database Type

3. IsAutoCloseConnection:(False by default) Whether to automatically release the database. If this parameter is set to true, close or Using is not required. We recommend that you use this parameter.

 

4. InitKeyType:(Default SystemTable) method of initializing primary key and auto-increment column information (Note: if the database permission is restricted by management or the primary key cannot be found, set it to attribute)

InitKeyType. SystemTable indicates that information about the auto-increment primary key column is automatically read from the database (suitable for accounts with high permissions such as SA)

If this mode is used, the entity class can be a normal entity class without adding anything.

InitKeyType. Attribute indicates reading information about the primary key and auto-increment columns from the attributes of the object class. (This is suitable for users with independent O & M groups who do not have the system table operation permission)

If this pattern is used, the entity class needs to be changed.

[SugarColumn (IsPrimaryKey = true, IsIdentity = true)] // if it is a primary key and is an auto-incrementing column, two attributes are added.

[SugarColumn (IsPrimaryKey = true)] // only one primary key can be added.

 

5. MoreSettings

Used for some global settings

If MoreSettings. IsAutoRemoveDataCache is set to true, the secondary cache can be automatically deleted.

When MoreSettings. IsWithNoLockQuery is set to true,. With (SqlWith. NoLock) is added by default for table queries. You can use With (SqlWith. Null) to invalidate the global query.

6. ConfigureExternalServices

You can extend the serialization and cache services you want.

 

Implement a simple addition, deletion, query, and modification

SqlSugarClient db = new SqlSugarClient (new ConnectionConfig () {ConnectionString = Config. connectionString, // required. DbType = DbType. sqlServer, // IsAutoCloseConnection = true} is required.) // The default value is InitKey = SystemTablevar list = db. queryable <Student> (). toList (); // query all (use SqlSugarClient to query all LIST) var list2 = db. queryable <Student, School, Student> (st, SC, st2, st3, st4) => new object [] {JoinType. left, st. schoolId = SC. id, JoinType. left, st. id = st2.Id, JoinType. left, st. id = st3.Id, JoinType. left, st. id = st4.Id }). where (st, SC) => SC. id = 1 ). select (st, SC, st2, st3, st4) => new {id = st. id, name = st. name, st4 = st4 }). toList (); // query db in table 5. insertable (insertObj ). executeCommand (); // insert db. updateable (updateObj ). executeCommand (); // update the database. deleteable <Student> (1 ). executeCommand (); // Delete
// Db. Aop Function
// Db. Ado Function
//...

SqlSugarClient objects can perform complex database operations. These functions will be described later.

 

Simplified addition, deletion, query, and modification

Although the SqlSugarClient object is powerful, most users encapsulate a layer of warehousing on the basis of me and simplify addition, deletion, and modification. The complex functions are implemented using SqlSugarClient.

So I integrated the SimpleClient class so that you don't have to write additional code.

 

You can create a class to inherit SimpleClient or directly use SimpleClient

// Expand a SimpleClient named DbSetpublic class DbSet <T>: SimpleClient <T> where T: class, new () {public DbSet (SqlSugarClient context ): the methods in base (context) {}// SimpleClient cannot meet your requirements. You can extend your own public List <T> GetByIds (dynamic [] ids) {return Context. queryable <T> (). in (ids ). toList ();;}}

 

Create a DbContext class that contains Db, StudentDb, and SchoolDb

// Create a DbContext class and use DbSet (or SimpleClient) public class DbContext {public DbContext () {Db = new SqlSugarClient (new ConnectionConfig () {ConnectionString = "xx ", dbType = DbType. sqlServer, IsAutoCloseConnection = true, // enable automatic release mode. I will not explain much about the same principle as EF. // InitKey default SystemTable});} public SqlSugarClient Db; // It is used to process transaction multi-table queries and complex operations public DbSet <Student> StudentDb {get {return new DbSet <Student> (db );}} // Common Operations used to process Student tables public DbSet <School> SchoolDb {get {return new DbSet <School> (db) ;}/// Common Operations used to process School tables}

　　

We only need to inherit DbContext to operate databases conveniently.

　

Use the DbSet object to add, delete, query, and modify StudentDb. getList (StudentDb. getByIdStudentDb. deleteStudentDb. updateStudentDb. insertStudentDb. getPageList uses the SqlSugarClient object Db. ado. useTran () =>{ operations in the transaction}) // transaction operation Db. queryable <T, T2> // complex Query

　　

　

Through this article, we believe that you can easily use SqlSugar ORM to implement simple addition, deletion, query, and modification.

 

Note: The above example uses the default InitKey method. If you use InitKey. Attribute, you need to add a primary key tag on the attributes of the object. This article describes how to connect to the database in detail.

If you do not understand, you can download the https://github.com/sunkaixuan/SqlSugar contains a detailed DEMO and database creation script

 

Next announcement

Describes SimpleClient functions and Use Cases

Dynamic query using the bootstrap-table of mainstream js plug-ins