一、介簡 SqlSugar ORM框架一直在升級當中，昨天將EMIT架構進行了重構，讓類型轉換更加智能，EMIT轉換後的性能和原生ADO同水準(以前只是接近)，為了提高性能、穩定、有問必答、有需求必改、堅持更新。例如資料庫類型為BIT我們在程式裡面可以使用 INT接收也可以用BOOL接收，不影響一絲 ...
一、介簡
SqlSugar ORM框架一直在升級當中，昨天將EMIT架構進行了重構，讓類型轉換更加智能，EMIT轉換後的性能和原生ADO同水準(以前只是接近)，為了提高性能、穩定、有問必答、有需求必改、堅持更新。例如資料庫類型為BIT我們在程式裡面可以使用 INT接收也可以用BOOL接收，不影響一絲性能，這些都是SQLSUGAR以前沒有的功能。
經過一天的努力，MySql版本所有的例子都已經測試通過：
MySql .NET 4.0+
https://github.com/sunkaixuan/MySqlSugar
MySql .NET CORE
下周更新
MSQL .NET 4.0+下載地址：
https://github.com/sunkaixuan/SqlSugar
MSSQL .NET Core版本
https://github.com/sunkaixuan/ASP_NET_CORE_ORM_SqlSugar
優點：
1、優越的性能，查詢使用 reflection.emit 創建IL語言+委托綁定 然後對該對象進行 cache ,datareader直接賦值給cache對象,高性能拉姆達解析，查詢速度達到原生最高性能的寫法水準
2、大量語法糖，拉姆達表達示篩選，新穎的多表查詢 ，方便的分頁等
3、支持事務
4、內置實體類生成函數，無需使用第三方代碼生成器
5、簡單好用、例子齊全有問必答。
組成：
sqlSugar是由sqlSugarClient提供統一調用模式 ，sqlSugarClient是由5個部分組成
1、自身函數
2、實體生成
3、單表查詢
4、多表查詢
5、基類函數
MySql教程
和MSSQL版本基本一樣但是個別例子也會有些細節上的區別
一、查詢
1、單表查詢
語法簡潔和EF相似，但有區別的SqlSugar更適合字元串操作，例如Where("Id>@id",new{id=1})
using (var db = SugarDao.GetInstance()) { //---------Queryable<T>,擴展函數查詢---------// //針對單表或者視圖查詢 var dt= db.GetDataTable("select * from Student"); //查詢所有 var student = db.Queryable<Student>().ToList(); var studentDynamic = db.Queryable<Student>().ToDynamic(); var studentJson = db.Queryable<Student>().ToJson(); //查詢單條 var single = db.Queryable<Student>().Single(c => c.id == 1); //查詢單條沒有記錄返回空對象 var single2 = db.Queryable<Student>().Where(c => c.id == 1).SingleOrDefault(); //查詢第一條 var first = db.Queryable<Student>().Where(c => c.id == 1).First(); var first2 = db.Queryable<Student>().Where(c => c.id == 1).FirstOrDefault(); //取10-20條 var page1 = db.Queryable<Student>().Where(c => c.id > 2).OrderBy("id").Skip(2).Take(3).ToList(); //上一句的簡化寫法，同樣取10-20條 var page2 = db.Queryable<Student>().Where(c => c.id > 1).OrderBy("id").ToPageList(2, 3); //查詢條數 var count = db.Queryable<Student>().Where(c => c.id > 10).Count(); //從第2條開始以後取所有 var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList(); //取前2條 var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList(); // Not like string conval = "三"; var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList(); //Like conval = "三"; var like = db.Queryable<Student>().Where(c => c.name.Contains(conval)).ToList(); // 可以在拉姆達使用 ToString和 Convert,比EF出色的地方 var convert1 = db.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(); //支持字元串Where 讓你解決，更複雜的查詢 var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList(); var student13 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100 and id in( select 1)").ToList(); //存在記錄反回true，則否返回false bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100); bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1); int maxId = db.Queryable<Student>().Max<Student, int>("id"); int minId = db.Queryable<Student>().Where(c => c.id > 0).Min<Student, int>("id"); //In var list1= db.Queryable<Student>().In("id", "1", "2", "3").ToList(); var list2=db.Queryable<Student>().In("id", new string[] { "1", "2", "3" }).ToList(); var list3=db.Queryable<Student>().In("id", new List<string> { "1", "2", "3" }).ToList(); var list4 = db.Queryable<Student>().Where(it=>it.id<10).In("id", new List<string> { "1", "2", "3" }).ToList(); //分組查詢 var list5 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy("sex").Select<Student, SexTotal>("Sex,count(sex) AS count").ToList(); //SELECT Sex,Count=count(*) FROM Student WHERE 1=1 AND (id < 20) GROUP BY Sex --生成結果 }
2、多表查詢
放棄了傳統的EF寫法，讓他更接近SQL
using (var db = SugarDao.GetInstance()) { //---------Sqlable,創建多表查詢---------// //多表查詢 List<School> dataList = db.Sqlable() .From("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") .Where("1=1")//可以多個WHERE .OrderBy("id") .SelectToList<School/*新的Model我這裡沒有所以寫的School*/>("st.*", new { id = 1 }); //多表分頁 List<School> dataPageList = db.Sqlable() .From("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); //多表分頁WHERE加子查詢 List<School> dataPageList2 = db.Sqlable() .From("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 and s.id in (select 1 )" /*這裡面寫子查詢都可以*/) .SelectToPageList<School>("st.*", "s.id", 1, 10); //--------轉成List Dynmaic 或者 Json-----// //不分頁 var list1 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToDynamic("*", new { id = 1 }); var list2 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToJson("*", new { id = 1 }); var list3 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToDataTable("*", new { id = 1 }); //分頁 var list4 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToPageDynamic("s.*", "l.id", 1, 10, new { id = 1 }); var list5 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToPageTable("s.*", "l.id", 1, 10, new { id = 1 }); var list6 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToPageDynamic("s.*", "l.id", 1, 10, new { id = 1 }); //--------拼接-----// Sqlable sable = db.Sqlable().From<Student>("s").Join<School>("l", "s.sch_id", "l.id", JoinType.INNER); string name = "a"; int id = 1; if (!string.IsNullOrEmpty(name)) { sable = sable.Where("s.name=@name"); } if (!string.IsNullOrEmpty(name)) { sable = sable.Where("s.id=@id or s.id=100"); } var pars = new { id = id, name = name }; int pageCount = sable.Count(pars); var list7 = sable.SelectToPageList<Student>("s.*","l.id desc",1,20, pars); }
3、對原生SQL查詢
using (var db = SugarDao.GetInstance()) { //轉成list List<Student> list1 = db.SqlQuery<Student>("select * from Student"); //轉成list帶參 List<Student> list2 = db.SqlQuery<Student>("select * from Student where id=@id", new { id = 1 }); //轉成dynamic dynamic list3 = db.SqlQueryDynamic("select * from student"); //轉成json string list4 = db.SqlQueryJson("select * from student"); //返回int var list5 = db.SqlQuery<int>("select id from Student limit 0,1").First(); //反回鍵值 Dictionary<string, string> list6 = db.SqlQuery<KeyValuePair<string, string>>("select id,name from Student").ToDictionary(it => it.Key, it => it.Value); //反回List<string[]> var list7 = db.SqlQuery<string[]>("select name from Student").First(); //存儲過程 //var spResult = db.SqlQuery<School>("exec sp_school @p1,@p2", new { p1=1,p2=2 }); }
4、容器的轉換
using (SqlSugarClient db = SugarDao.GetInstance()) { var list2= db.Queryable<Student>().Where(c => c.id < 10).Select(c => new classNew { newid = c.id, newname = c.name, xx_name = c.name }).ToList();//不支持匿名類轉換,也不建議使用 var list3 = db.Queryable<Student>().Where(c => c.id < 10).Select(c => new { newid = c.id, newname = c.name, xx_name = c.name }).ToDynamic();//匿名類轉換 }View Code
二、更新
可以指定列更新，也可對實體整個更新，也可以排除不更新欄位非常的方便好用
int id = 1; using (var db = SugarDao.GetInstance()) { //指定列更新 db.Update<School>(new { name = "藍翔2" }, it => it.id == id); db.Update<School, int>(new { name = "藍翔2" }, 1, 3, 12); db.Update<School, string>(new { name = "藍翔2" }, new string[] { "1", "2" }); //整個實體更新 db.Update(new School { id = id, name = "藍翔2" }); db.Update<School>(new School { id = id, name = "藍翔2" }, it => it.id == id); //設置不更新列 db.DisableUpdateColumns = new string[] { "CreateTime" };//設置CreateTime不更新 TestUpdateColumns updObj = new TestUpdateColumns() { VGUID = Guid.Parse("6DF396EB-D1C8-48A5-8BE1-D58D685646A7"), Name = "xx", Name2 = "xx2", IdentityField = 0, CreateTime = null }; //CreateTime將不會被更新 db.Update(updObj); //以前實現這種更新需要用指定列的方式實現，現在就簡單多了。 }更新
三、刪除
using (var db = SugarDao.GetInstance()) { //真刪除 db.Delete<Student, int>(10);//註意主鍵必需為實體類的第一個屬性 db.Delete<School>(it => it.id > 100); db.Delete<School, string>(new string[] { "100", "101", "102" }); //假刪除 //db.FalseDelete<school>("is_del", 100); //等同於 update school set is_del=0 where id in(100) //db.FalseDelete<school>("is_del", it=>it.id==100); }刪除
四、插入
Student s = new Student() { name = "張" + new Random().Next(1, int.MaxValue) }; db.Insert(s); //插入一條記錄 (有主鍵也好，沒主鍵也好，有自增列也好都可以插進去) List<Student> list = new List<Student>() { new Student() { name="張"+new Random().Next(1,int.MaxValue) }, new Student() { name="張"+new Random().Next(1,int.MaxValue) } }; db.InsertRange(list); //批量插入插入
五、枚舉支持
using (SqlSugarClient db = SugarDao.GetInstance()) { var stuList= db.Queryable<Student>().ToList(); db.Insert<Student>(new Student() { sch_id = SchoolEnum.藍翔2 }); db.Update<Student>(new Student() { sch_id=SchoolEnum.藍翔2 , id=1} ); var stuList2 = db.Queryable<Student>().Where(it=>it.sch_id==SchoolEnum.藍翔1).ToList(); }枚舉
六、實體生成
using (var db = SugarDao.GetInstance()) { //根據當前資料庫生成所有表的實體類文件 （參數：SqlSugarClient ，文件目錄，命名空間） db.ClassGenerating.CreateClassFiles(db,"e:\\mysqlModel","Models"); //根據表名生成實體類文件 //db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school"); //根據表名生成class字元串 var str = db.ClassGenerating.TableNameToClass(db, "Student"); //根據SQL語句生成class字元串 var str2 = db.ClassGenerating.SqlToClass(db, "select * from Student", "student"); }實體生成
除了上面代碼還有T4生成看DEMO不懂可以隨時問我
七、全局過濾器
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using MySqlSugar; using Models; namespace WebTest.Demo { /// <summary> /// 過濾器 /// </summary> public partial class Filter : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDaoFilter.GetInstance())//開啟資料庫連接 { //設置走哪個過濾器 db.CurrentFilterKey = "role"; //queryable var list = db.Queryable<Student>().ToList(); //通過全局過濾器對需要許可權驗證的數據進行過濾 //相當於db.Queryable<Student>().Where("id=@id",new{id=1}) //sqlable var list2 = db.Sqlable().From<Student>("s").SelectToList<Student>("*"); //同上 //sqlQuery var list3 = db.SqlQuery<Student>("select * from Student where 1=1"); //同上 } } } /// <summary> /// 擴展SqlSugarClient /// </summary> public class SugarDaoFilter { //禁止實例化 private SugarDaoFilter() { } /// <summary> /// 頁面所需要的過濾函數 /// </summary> private static Dictionary<string, Func<KeyValueObj>> _filterParas = new Dictionary<string, Func<KeyValueObj>>() { { "role",()=>{ return new KeyValueObj(){ Key=" id=@id" , Value=new{ id=1}}; } }, { "org",()=>{ return new KeyValueObj(){ Key=" orgId=@orgId" , Value=new{ orgId=1}}; } }, }; public static SqlSugarClient GetInstance() { string connection = System.Configuration.ConfigurationManager.ConnectionStrings[@"sqlConn"].ToString(); //這裡可以動態根據cookies或session實現多庫切換 var reval = new SqlSugarClient(connection); reval.SetFilterFilterParas(_filterParas); return reval; } } }過濾器
八、別名錶
using (SqlSugarClient db = SugarDao.GetInstance()) { //-----單個-------// var list = db.Queryable<V_Student>("Student").ToList();//查詢的是 select * from student 而我的實體名稱為V_Student //-----全局多個設置-------// //設置 Mapping Table 如果沒這方面需求可以傳NULL List<KeyValue> mappingTableList = new List<KeyValue>(){ new KeyValue(){ Key="FormAttr", Value="Flow_FormAttr"}, new KeyValue(){ Key="Student3", Value="Student"} }; db.SetMappingTables(mappingTableList); }別名錶
九、事務
using (SqlSugarClient db = SugarDao.GetInstance())//開啟資料庫連接 { db.IsNoLock = true;//
