Sqlsugar ORM Primer to mastering the "one" introductory article

Background

Sqlsugar is a home-made ORM, in addition to the performance of the same as native to meet the needs of a variety of functions, simple and easy to use a minute to get started.

2.x version officially used in self-company internal projects

The 3.x version has been more liked by the company but will complain that there are a lot of deficiencies

4.x version May 17 released after the evaluation exceeded my expectations, just released the time to do a lot of unit testing, but still have a lot of bugs, after a lot of user feedback around August molding and stability, and a large number of projects have been delivered, but also by users of the praise, Now I think it's a better version, so I started writing this article about it.

The 5.x release is expected to be the perfect 4 for this year's May beta release. x version, with a few years of deep understanding of emit and lambda parsing, the 5.x version will have the change of the ORM, the specific features of the temporary secrecy.

Introduced

support : Oracle, Mysql, Sqlite, sqlsever Four kinds of database, PostgreSQL years will also support is already in development, supported by the field data type is also quite complete

features : bulk operations, Codefirst, Dbfirst, two levels of distributed cache, AOP, read/write separations, extensions of custom SQL functions, dynamic table alias aliases, attribute table alias names, Ramdad queries, Join,unionall, Insert supports default values, updates support common features such as column exclusion columns

Benefits : Performance, simple syntax, powerful and continuous update maintenance

Install download

：

Https://github.com/sunkaixuan/SqlSugar

Nuget:

. NET 4.0 and later:Install-Package sqlSugar 

. NET Core version 2.0:Install-Package sqlSugarCore

Connecting to a database

Sqlsugar is the operation of the database through sqlsugarclient, and creating sqlsugarclient we need to connectionconfig this class object

The Connectionconfig has 6 properties, respectively:

1.ConnectionString ( required ): connection string

2.DataType( required ): Database type

3.IsAutoCloseConnection: (default false) whether to automatically release the database, set to true we do not need close or using operation, compare recommended

4.InitKeyType: (default systemtable) How to initialize primary key and self-increment column information (note: If the database permissions are restricted or the primary key cannot be found, it must be set to attribute)

initkeytype.systemtable indicates that the primary key self-increment information is automatically read from the database (suitable for high-privileged accounts such as SA)

If this is the mode entity class, then the ordinary entity class can be, no need to add anything

Initkeytype.attribute indicates that the primary key and the self-increment information are read from the attributes of the entity class (for users with separate operations groups without the system table operation permission)

If this is the pattern entity class needs to change

[Sugarcolumn (Isprimarykey=true,isidentity=true)]//If it is a primary key and is a self-increment column, add 2 properties

[Sugarcolumn (Isprimarykey=true)]//if only the primary key can only add one

5.MoreSettings

For some global settings

Moresettings. Isautoremovedatacache True indicates that level two caches can be automatically deleted

Moresettings. Iswithnolockquery is a true table-type query when the default is added. With (Sqlwith.nolock), can be used with (sqlwith.null) to let the global failure

6.ConfigureExternalServices

You can expand the services you want to serialize and how you cache

To achieve a simple increase, delete, check and change

Sqlsugarclient db = new Sqlsugarclient (new Connectionconfig () {ConnectionString = config.connectionstring,//Required DBTYPE = D Btype.sqlserver,//Required isautocloseconnection = true}); The default Initkey=systemtablevar list=db. Queryable<student> (). ToList ();//query all (using sqlsugarclient query all to list) var list2 = db. Queryable<student, School, Student, Student, student> (St, SC, ST2, ST3, st4) = = new Object[] {Jointype.left,st.s Choolid==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 ();//5 table Query db. Insertable (insertobj). ExecuteCommand ();//Insert db. Updateable (updateobj). ExecuteCommand ();//Update db. Deleteable<student> (1). ExecuteCommand ();//delete
Db. AOP Features
Db. ADO Features
//...

The Sqlsugarclient object can perform very complex database exercises, which are described later

Simplification of the increase, deletion, search and change

Sqlsugarclient Object Although strong but most users will be on my basis in the packaging of a layer of warehousing and then simplify the addition and deletion of the change, complex functions in the sqlsugarclient implementation

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

You can build a class to inherit simpleclient or use SimpleClient directly

We're going to expand a simpleclient named Dbsetpublic class dbset<t>:simpleclient<t> where T:class, new () {public    DbSet (sqlsugarclient context): The method in base (context)    {                }    //simpleclient can't meet you, you can extend your own method public    List <T> getbyids (Dynamic [] IDs)    {        return context.queryable<t> (). In (IDS). ToList ();;    }}

Let's create a DbContext class that contains the DB, Studentdb, and Schooldb

Create a DbContext class that uses Dbset (or simpleclient) public class dbcontext{public        DbContext ()        {            Db = new Sqlsugarclient (New Connectionconfig ()            {                ConnectionString = "xx",                DbType = Dbtype.sqlserver,                Isautocloseconnection = true,//Turn on auto release mode and EF principle I don't have much to explain.                //initkey default systemtable            });        }        Public sqlsugarclient db;//is used to handle transactional multi-table queries and complex operations public        dbset<student> Studentdb {get {return new dbset< student> (DB); }//used to handle student table common operations public        dbset<school> Schooldb {get {return new dbset<school> (db);}} Common operations for handling school tables}

　　

As long as we inherit dbcontext, we can operate the database conveniently.

Use DbSet object to implement additions and deletions and change studentdb.getlist ( StudentDb.GetByIdStudentDb.DeleteStudentDb.UpdateStudentDb.InsertStudentDb.GetPageList using Sqlsugarclient objects Db.Ado.UseTran (() =>{operation})//Transaction operations Db.ado.queryable<t,t2>//implement complex queries

　　

Through this article I believe you can easily use the Sqlsugar ORM to achieve simple increase, delete, check and change

Note: The above example uses the default Initkey method, if the Initkey.attribute need to add a primary key tag on the entity's attributes, the connection database in this article is described in detail.

If you don't understand it, you can download the Https://github.com/sunkaixuan/SqlSugar with detailed demo and build library scripts.

Next trailer

Introducing SimpleClient specific functions and usage scenarios

Combining the main JS plugin bootstrap-table to realize the dynamic polling function

Sqlsugar ORM Primer to mastering the "one" introductory article