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> ; <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