一、介簡
easyliter框架的升級版本，並且正式命名為SqliteSugar框架，另外Sugar系列還有 MySql和MsSql版本，Oracle版本待開發中（因為客戶端太大一直在憂鬱當中）
用Sqlite 也不址什麼高效能了，好用為主。
經過一天的努力，Sqlite 版本所有的例子都已經測試通過：
Sqlite .net4.0+
https://github.com/sunkaixuan/SqliteSugar
MySql .NET 4.0+
https://github.com/sunkaixuan/MySqlSugar
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、基類函式
SqliteSugar教程
和MSSQL版本基本一樣但是個別例子也會有些細節上的區別
一、查詢
1、拉姆達查詢（以後會支援多表JOIN）
語法簡潔和EF相似，同時相容 Where("Id>@id",new{id=1}) 這種字串的寫法
//針對單表或者檢視查詢 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(it=>it.id).Skip(2).Take(3).ToList(); //上一句的簡化寫法，同樣取10-20條 var page2 = db.Queryable<Student>().Where(c => c.id > 1).OrderBy(it => it.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(it => it.id).Skip(2).ToList(); //取前2條 var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.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"); //order By var orderList = db.Queryable<Student>().OrderBy("id desc,name asc").ToList();//字串支援多個排序 //可以多個order by表達示 var order2List = db.Queryable<Student>().OrderBy(it => it.name).OrderBy(it => it.id, OrderByType.desc).ToList(); // order by name as ,order by id desc //In var intArray = new[] { "5", "2", "3" }; var intList = intArray.ToList(); var list0 = db.Queryable<Student>().In(it => it.id, 1, 2, 3).ToList(); var list1 = db.Queryable<Student>().In(it => it.id, intArray).ToList(); var list2 = db.Queryable<Student>().In("id", intArray).ToList(); var list3 = db.Queryable<Student>().In(it => it.id, intList).ToList(); var list4 = db.Queryable<Student>().In("id", intList).ToList(); //分組查詢 var list7 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy(it => it.sex).Select("sex,count(*) as Count").ToDynamic(); var list8 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy(it => it.sex).GroupBy(it => it.id).Select("id,sex, count(*) as Count").ToDynamic(); List<SexTotal> list9 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy(it => it.sex).Select<Student, SexTotal>("Sex, count(*) as Count").ToList(); List<SexTotal> list10 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy("sex").Select<Student, SexTotal>("Sex, count(*) as Count").ToList(); //SELECT Sex,Count=count(*) FROM Student WHERE 1=1 AND (id < 20) GROUP BY Sex --生成結果
2、sqlable 接近SQL的一個路線。
放棄了傳統的EF寫法，讓他更接近SQL，可以取代傳統的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();//匿名類轉換 }
二、更新
可以指定列更新，也可對實體整個更新，也可以排除不更新欄位非常的方便好用
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;//啟用無鎖查詢 db.CommandTimeOut = 30000;//設定超時時間 try { db.BeginTran();//開啟事務 //db.BeginTran(IsolationLevel.ReadCommitted);+3過載可以設定事世隔離級別 db.CommitTran();//提交事務 } catch (Exception) { db.RollbackTran();//回滾事務 throw; } }
十、保留了ADO功能
using (SqlSugarClient db = SugarDao.GetInstance())//開啟資料庫連線 { var r1= db.GetDataTable("select * from student"); var r2= db.GetSingle<Student>("select * from student limit 0,1"); var r3 = db.GetScalar("select count(1) from student"); var r4 = db.GetReader("select count(1) from student"); r4.Dispose(); var r5 = db.GetString("select name from student limit 0,1"); var r6 = db.ExecuteCommand("select 1"); }