SqlSugar and ormsqlsugar of lightweight and high-performance SQL ORM

SqlSugar and ormsqlsugar of lightweight and high-performance SQL ORM
SqlSugar lightweight ORM

SqlSugar is a lightweight mssql orm with similar and easy-to-use syntaxes as EF in addition to its comparable performance to ADO.

  I. Introduction

Advantages:

1. Superior performance: the query speed using reflection. emit + cache is close to that of manual DataReader

2. A large amount of syntactic sugar, Lambda expression filtering, novel multi-table queries, convenient paging, etc.

3. Support for NOLOCK queries to improve performance

4. Support transactions

5. built-in entity class generation functions without using third-party code generators

6. Easy to use and complete examples.

Disadvantages:

Currently, only MSSQL is supported and will be fully developed in the future

 

 

Composition:

SqlSugar is a unified call mode provided by sqlSugarClientr. sqlSugarClientr is composed of five parts.

1. Functions

2. entity generation

3. Single Table query

4. Multi-Table query

5. Basic functions

 

 

Ii. Tutorial

 

Query  

 

1. Single table or single View query:

You can call the extended functions of db. Queryable () to easily query a single table.

Using (SqlSugarClient db = new SqlSugarClient (connStr) // enable the database connection {// query all var student = db. queryable <Student> (). toList (); // query a single var single = db. queryable <Student> (). single (c => c. id = 1); // obtain 10-20 var page1 = db. queryable <Student> (). where (c => c. id> 10 ). orderBy ("id "). skip (10 ). take (20 ). toList (); // The Simplified Method of the previous sentence. Similarly, 10-20 var page2 = db. queryable <Student> (). where (c => c. id> 10 ). orderBy ("id "). toPageLi St (2, 10); // query number var count = db. queryable <Student> (). where (c => c. id> 10 ). count (); // obtain all var skip = db from the beginning of 2nd. queryable <Student> (). where (c => c. id> 10 ). orderBy ("id "). skip (2 ). toList (); // obtain the first two var take = db. queryable <Student> (). where (c => c. id> 10 ). orderBy ("id "). take (2 ). toList (); // Not like var notLike = db. queryable <Student> (). where (c =>! C. name. contains ("". toString ())). toList (); // ToString and Convert can be used in Lambda, where var convert1 = db is better than EF. queryable <Student> (). where (c => c. name = "". toString ()). toList (); var convert2 = db. queryable <Student> (). where (c => c. id = Convert. toInt32 ("1 ")). toList (); // var convert3 = db. queryable <Student> (). where (c => DateTime. now> Convert. toDateTime ("2015-1-1 ")). toList (); var convert4 = db. queryable <Student> (). where (c => DateTime. now> DateTime. now ). toList (); // supports string Where for you to solve, more complex query var student12 = db. queryable <Student> (). where (c => 1 = 1 ). where ("id> @ id", new {id = 1 }). toList ();}

　　

// If a record exists and returns true, false bool isAny100 = db is returned. queryable <Student> (). any (c => c. id = 100); bool isAny1 = db. queryable <Student> (). any (c => c. id = 1 );

 

2. Advanced query for a single table

 

Query and paging based on conditions

/// <Summary> /// query by condition and by PAGE /// </summary> /// <param name = "name"> </param> /// <param name = "sex"> </param> // <returns> </returns> public static List <Student> GetStudent (string name, string sex, int pageIndex, int pageSize, string orderFileds) {using (SugarDao db = new SugarDao () {var qable = db. queryable <Student> (); if (! String. IsNullOrEmpty (name) {qable = qable. Where (it => it. name. Contains (name);} if (! String. IsNullOrEmpty (sex) {qable = qable. Where (it => it. sex = sex);} if (! String. isNullOrEmpty (orderFileds) // No need to worry about injection {qable = qable. orderBy (orderFileds);} return qable. toPageList (pageIndex, pageSize); // ToPageList executes the database and returns the result set }}

 

New container Conversion

Public List <classNew> GetSelectList (int id) {using (SugarDao db = new SugarDao () {return db. queryable <Student> (). where (c => c. id <10 ). select (c => new classNew {newid = c. id, newname = c. name, xx_name = c. name }). toList (); // Anonymous class conversion is not supported or recommended }}

 

Group Query

 public List<SexTotal> GetSexTotal()        {            using (SugarDao db = new SugarDao())            {                return db.Queryable<Student>().Where(c => c.id < 20).GroupBy("sex").Select<Student, SexTotal>("Sex,Count=count(*)").ToList();            }        }

SELECT Sex, Count = count (*) FROM Student WHERE 1 = 1 AND (id <20) group by Sex -- generate the result

　　

　　

3. Multi-Table query:

Speaking of Multi-table queries, which are not satisfactory in performance or function in many ORM systems, or are not as good as using SQL, the following is my idea and I gave up the strong-type writing method, the code is closer to SQL statement writing, making SQL fully controllable, and solving the performance problem of OMR multi-table.

There are also ORDERBY, GROUPBY, and APPLY, which are not described in the example.

Compare it with EF:

EF query:

 var reval = (from s in db.Student                                join sc in db.School on s.sch_id equals sc.id                                join sb in db.Subject on s.id equals sb.sid                                into ssb                                from sb2 in ssb.DefaultIfEmpty()                                select new {                                s.id,                                s.name,                                s.sch_id,                                s.sex                                }).Where(c=>c.id>1).Where(c=>c.id>2).OrderBy(c=>c.id).ThenByDescending(c=>c.name).Skip(10).Take(10).ToList();

SqlSugar query:

      db.Sqlable().Form("Student", "s")                        .Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER)                        .Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).Where("s.id>@id1").Where("s.id>@id2")                        .SelectToPageList<Models.Student>("s.*", "s.id asc,s.name desc", 2, 10, new { id1=1,id2=2 });

 

For more SqlSugar queries:

// Multi-Table query List <School> dataList = db. sqlable (). form ("school", "s "). join ("student", "st", "st. id "," s. id ", JoinType. INNER ). join ("student", "st2", "st2.id", "st. id ", JoinType. LEFT ). where ("s. id> 100 and s. id <@ id "). selectToList <School> ("st. * ", new {id = 1}); // multi-Table paging List <School> dataPageList = db. sqlable (). form ("school", "s "). join ("student", "st", "st. id "," s. id ", JoinType. INNER ). join ("student", "st2", "st2.id", "st. id ", JoinType. LEFT ). where ("s. id> 100 and s. the id <100 "). selectToPageList <School> ("st. * "," s. id ", 1, 10 );

Dynamic stitching

        public List<Student> GetStudent(int id, string name)        {            using (var db = SugarDao.GetInstance())            {                var sable = db.Sqlable().Form("student", "s").Join("school", "l", "s.sch_id", "l.id", JoinType.INNER);                if (!string.IsNullOrEmpty(name))                {                    sable = sable.Where("s.name=s.@name");                }                if (!string.IsNullOrEmpty(name))                {                    sable = sable.Where("s.id=@id");                }                return sable.SelectToList<Student>("s.*", new { id = id, name = name });            }        }

　　

 4. Use SQL or stored procedure query:

In order to be compatible with situations that cannot be met above, I wrote such a function for emergency response.

Var School = db. sqlQuery <School> ("select * from School"); // obtain id var id = db. sqlQuery <int> ("select top 1 id from School "). single (); // Stored Procedure // var spResult = db. sqlQuery <school> ("exec sp_school @ p1, @ p2", new {p1 = 1, p2 = 2 });

　　

 

 

Add

 

Using (SqlSugarClient db = new SqlSugarClient (connStr) // enable the database connection {School s = new School () {name = "lanxiang "}; // insert a single entry var id2 = Convert. toInt32 (db. insert (s); // Insert Multiple lists <School> sList = new List <School> (); sList. add (s); var ids = db. insertRange (sList );}

　　

 

Modify

// Specify the column to update the database. update <School> (new {name = "lanxiang 2"}, it => it. id = id); // update the entire object. Note that the primary key must be the first attribute db of the object class. update <School> (new School {id = id, name = "lanxiang 2"}, it => it. id = id );

　　

 

 

Delete

Db. delete <School> (id); // note that the primary key must be the first attribute db of the object class. delete <School> (it => it. id> 100); db. delete <School> (new string [] {"100", "101", "102"}); db. falseDelete <school> ("is_del", 100); // false Delete // equivalent to update school set is_del = 0 where id in (100)
Db. FalseDelete <school> ("is_del", it => it. id = 100 );

 

More underlying functions

Db. executeCommand (SQL); db. getDataTable (SQL); db. getList <Student> (SQL); db. getSingle <Student> (SQL + "where id = 1"); using (SqlDataReader read = db. getReader (SQL) {} // DataReader db must be released in a transaction. getScalar (SQL); db. getString (SQL); db. getInt (SQL );

　　

Code Generation  

Using (SqlSugarClient db = new SqlSugarClient (connStr) // enable database connection {// generate object files of all tables based on the current database (parameter: SqlSugarClient, file directory, namespace) db. classGenerating. createClassFiles (db, Server. mapPath ("~ /Models ")," Models "); // generate the object class file db. ClassGenerating. CreateClassFilesByTableNames (db, Server. MapPath ("~ /Models ")," Models "," student "," school "); // generate a class string var str = db based on the table name. classGenerating. tableNameToClass (db, "Student"); // generate the class string var str2 = db according to the SQL statement. classGenerating. sqlToClass (db, "select top 1 * from Student", "student ");}

　　

Transactions

Using (SqlSugarClient db = new SqlSugarClient (connStr) // start the database connection {try {// start the transaction. You can use multiple transaction databases without using the transaction. beginTran (); // sq1 // sql2 // sql3} catch (Exception ex) {// roll back the transaction db. rollbackTran (); throw ex ;}// close the database connection

 

Lock-free Query

When IsNoLock is set to True, the generated SQL statement table name will be followed by With (Nolock)

Using (SqlSugarClient db = new SqlSugarClient (connStr) // enable the database connection {db. sqlable (). isNoLock = true; db. sqlable (). isNoLock = false; db. sqlable (). isNoLock = true;} // closes the database connection

　　

　

Supports writing multi-database Switching

Define a sugarDao class to extend SqlSugar

/// <Summary> /// extended SqlSugarClient // </summary> public class SugarDao {// disable instantiation of private SugarDao () {} public static SqlSugarClient GetInstance () {string connection = "Server = .; uid = sa; pwd = sasa; database = SqlSugarTest "; // here, You can dynamically switch multiple databases based on cookies or sessions. return new SqlSugarClient (connection );}}

　　

No need to input connectionString

      public School GetSingleSchool(int id)        {            using (SqlSugarClient db = SugarDao.GetInstance())            {                return db.Queryable<School>().Single(c => c.id == id);            }        }

　　

　

 

Iii. Performance Testing:

10000 times

 

 

1000 times

 

10000 times

 

 

 

. Net4.52 + EF 6.0 + SQL12 to shame

. NET Framework 4.52 + sql12 + EF6.0, EF performance is obviously up, let it be a champion, I will not test it, after the upgrade of Microsoft's things, performance does not need to be questioned, the integration of multi-table queries and addition, deletion, and modification is also quite smooth.

 

SqlSugar is a lightweight, easy-to-use, and easy-to-use way to control SQL. I hope you will like it or give your valuable comments.

 

 

V1.0 source code:

Http://pan.baidu.com/s/1bnmAXjh