Sqlsugar-asp.net of lightweight, high-performance SQL ORM

Sqlsugar Light-weight orm

The Sqlsugar is a lightweight MSSQL ORM that has an easy-to-use syntax similar to that of EF in addition to the performance of ADO.

first, a brief introduction

Advantages:

1, superior performance, using Reflection.Emit + cache close to the Pure manual DataReader query speed

2, a large number of grammatical sugars, lambda expression screening, novel multi-table query, convenient paging and so on

3, Support NOLOCK query, improve performance

4. Support Business

5, built-in entity class generation function, no need to use third-party code generator

6, simple and easy to use, complete examples have asked to answer.

Disadvantages:

Currently only support MSSQL, will be fully developed in the future

Composition

Sqlsugar is a unified call pattern provided by SQLSUGARCLIENTR, Sqlsugarclientr is composed of 5 parts

1. Self-function

2. Entity generation

3. Single-Table query

4. Multi-Table Query

5. Base class functions

second, the use of the tutorial

Enquiry  

1, single-table or single-view query:

by calling Db. Queryable () Related extension functions easy to order table query

        using (sqlsugarclient db = new Sqlsugarclient (CONNSTR))//Open database connection {//query There is var student = db. Queryable<student> ().                    ToList (); Query single var single = db. Queryable<student> ().                    Single (c = c.id = = 1); Take 10-20 var page1 = db. Queryable<student> (). Where (c = c.id > 10). ("id"). Skip (10). Take (20).                    ToList (); The simplified wording of the previous sentence also takes 10-20 var page2 = db. Queryable<student> (). Where (c = c.id > 10). ("id").                    Topagelist (2, 10); Number of query bars var count = db. Queryable<student> (). Where (c = c.id > 10).                    Count (); After starting from 2nd, take all var skip = db. Queryable<student> (). Where (c = c.id > 10). ("id"). Skip (2).                    ToList (); Take the first 2 var takes = db. Queryable<student> (). WheRe (c = c.id > 10). ("id"). Take (2).                    ToList (); The not-like var notlike = db. Queryable<student> (). Where (c =!c.name.contains ("a"). ToString ())).                    ToList (); You can use ToString and Convert in Lambda, where var convert1 = db is better than EF. Queryable<student> (). Where (c = = C.name = = "a". 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 (); The support string where lets you solve the more complex query var student12 = db. Queryable<student> (). Where (c = 1 = = 1). Where ("Id> @id", new{id=1}).      ToList (); ｝

　　

                   There is a record reversed true, then no returns false                    bool isAny100 = db. Queryable<student> (). Any (c = c.id = =);                    BOOL ISANY1 = db. Queryable<student> (). Any (c = c.id = = 1);

2, single-table advanced query

Querying and paging based on criteria

   <summary>///query and page by conditions///</summary>//<param name= "name" ></param&gt        ; <param name= "Sex" ></param>///<returns></returns> public static list<student > getstudent (string name, string sex,int pageIndex, int pageSize, string orderfileds) {using (Sugard AO 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 injecting {qable = qable.                (orderfileds); } return qable. Topagelist (pageindex,pagesize);//topagelist executes the database andBack to result set}} 

New Container Conversions

        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 ();//Does not support anonymous class conversions, nor is it recommended            }        }

Group queries

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 <)    GROUP by Sex-Generate results

　　

　　

3, multi-table query:

When it comes to multi-table queries in many ORM, whether it is performance or functionality is not satisfied, or is not as good as SQL, below is my idea, give up the strong typing, let the code closer to SQL statement writing, SQL is fully controllable, also solves the OMR multi-table performance problem.

There are also groupby, and apply, and so on, not introduced in the example.

Compare EF to:

EF query:

var Reval = (from S in db. Student                                join SC in db. School on s.sch_id equals Sc.id                                joins SB in DB. Subject on s.id equals Sb.sid to                                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). (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, new {id1=1,id2=2});

For more Sqlsugar Inquiries:

                    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.id<100"). Selecttopagelist<school> ("st.*", "S.id", 1, 10);

　　

4. Query using SQL or stored procedure:

In order to be compatible with the above-mentioned situation, I wrote this function for emergency purposes.

                    var School = db. Sqlquery<school> ("SELECT * from School");                    Gets the 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))//Open database connection        {                    School s = new School ()                    {                        name = "Blue Xiang" c5/>};                    Insert a single                    var id2 = Convert.ToInt32 (db). Insert (s));                    Insert multiple                    list<school> sList = new list<school> ();                    Slist.add (s);                    var ids = db. Insertrange (sList);         }

　　

Modify

                    Specifies that the column updates                    db. Update<school> (New {name = "Blue Xiang 2"}, it = It.id = = ID);                    The entire entity is updated, noting that the primary key must be the first attribute of the entity class                    db. Update<school> (new School {id = id, name = "Blue Xiang 2"}, it = It.id = = ID);

　　

Delete 

                    Db. Delete<school> (ID);//Note the primary key must be the first attribute of the entity class                    db. delete<school> (it = it.id >);                    Db. Delete<school> (new string[] {"n", "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)) {}  //Transaction must be released DataReader                    DB. Getscalar (SQL);                    Db. GetString (SQL);                    Db. GETINT (SQL);

　　

code Generation  

using (sqlsugarclient db = new Sqlsugarclient (CONNSTR))//Open database Connection {                  //Generate entity class files for all tables based on the current database (parameters: sqlsugarclient, file directory, namespace)                    db. Classgenerating.createclassfiles (Db,server.mappath ("~/models"), "Models");                    Generates the entity class file DB based on the table name                    . Classgenerating.createclassfilesbytablenames (DB, Server.MapPath ("~/models"), "Models", "Student", "school");                    Generates a class string                    var str = db based on the table name. Classgenerating.tablenametoclass (db, "Student");                    Generates a class string                    var str2 = db based on the SQL statement. Classgenerating.sqltoclass (db, "select top 1 * from Student", "Student");}

　　

Transactions 

using (sqlsugarclient db = new Sqlsugarclient (CONNSTR))//Open database connection {   try{       //Open transaction, you can use no transaction, or multiple transaction        db. Begintran ();                SQ1        //sql2        //sql3     }catch (Exception ex) {       //ROLLBACK TRANSACTION       DB. Rollbacktran ();       Throw ex;    }} To close a database connection

Lock-Free query 

When Isnolock is set to True, the resulting SQL statement table name will be followed by a with (Nolock)

using (sqlsugarclient db = new Sqlsugarclient (CONNSTR))//Open database connection {          db. Sqlable (). Isnolock = true;          Db. Sqlable (). Isnolock = false;          Db. Sqlable (). Isnolock = true;} To close a database connection

　　

　

support for multi-Library switching 

Define a Sugardao class to extend the Sqlsugar

  <summary>    //Extended sqlsugarclient///</summary> public class Sugardao    {        //disallow instantiation        Private Sugardao () {         } public        static Sqlsugarclient getinstance ()        {            string connection = "Server =.; Uid=sa;pwd=sasa;database=sqlsugartest "; It is possible to dynamically switch the            return new Sqlsugarclient (connection) from the cookie or session.}    }

　　

Use without passing in connectionstring

      Public School getsingleschool (int id)        {            using (sqlsugarclient db = Sugardao.getinstance ())            {                Return DB. Queryable<school> (). Single (c = c.id = = id);}        }

　　

　

Third, performance testing:

10,000 plays

1000 plays

10,000 plays

. NET4.52+EF 6.0+SQL12 to wash the shame　

The. NET Framework 4.52+ sql12 +ef6.0, EF performance obviously up, let it be a champion, I do not go to test, Microsoft's things after upgrading performance without question, in the multi-table query and the addition of the combination of the basic tie down.

Sqlsugar is looking for a lightweight, fast, easy-to-use SQL control, and I hope you can like or offer your valuable advice.

V1.0 Source:

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

Sqlsugar-asp.net of lightweight, high-performance SQL ORM