[開源ORM] SqliteSugar 3.x .net Core版本成功上線

孫凱旋發表於2016-10-27

SqliteSqlSugar 3.X API

作為支援.NET CORE 為數不多的ORM之一，除了具有優越的效能外，還擁有強大的功能，不只是滿足你的增,刪,查和改。實質上擁有更多你想像不到的功能,當你需要實現某個功能時會發現有這個功能太棒了。

因為每個版本的貼子API都基本一致，所以我也說一些別的用法讓大家更加了解該ORM

引數多元化的支援：

//Queryable 支援 拉姆達 匿名引數  和字典引數
var list = db.Queryable<Student>().Where(it=>it.id==1).ToList();
var list2=db.Queryable<Student>().Where("id=@id", new { id = 1 }).ToList();
var list3 = db.Queryable<Student>().Where("id=@id", new Dictionary<string, object>() { {"id",1}}).ToList();


//原生Sql查詢支援 匿名物件，SqlParameter和 字典
db.SqlQuery<Student>("select * from student where id=@id", new { id = 1 });
db.SqlQuery<Student>("select * from student where id=@id", new SqlParameter("@id",1));
db.SqlQuery<Student>("select * from student where id=@id", new Dictionary<string, object>() { { "id", 1 } });

//同上
//db.GetScalar()
//db.ExecuteCommand()
//db.GetInt（）
//...

SQL語句使用拉姆達的技巧

 db.Queryable<Student>("(select * from [Student] ) t").Where(it => it.name == "name").ToList();

注意: 這種寫法表名一定要加上關鍵字不然應會讓轉釋成 [(select * from [Student] ) t]

Mssql表名關鍵字是 []

MySql表名關鍵字是``

Sqlite表名關鍵字是[]

ORACLE表名只要大寫就可以了

所有版本

ASP.NET 4.0+ MSSQL https://github.com/sunkaixuan/SqlSugar

ASP.NET CORE MSSQL https://github.com/sunkaixuan/ASP_NET_CORE_ORM_SqlSugar

ASP.NET 4.0+ MYSQL https://github.com/sunkaixuan/MySqlSugar

ASP.NET CORE MYSQL https://github.com/sunkaixuan/ASP_NET_CORE_ORM_MySqlSugar

ASP.NET 4.0+ Sqlite https://github.com/sunkaixuan/SqliteSugar

ASP.NET CORE Sqlite https://github.com/sunkaixuan/ASP_NET_CORE_ORM_MySqlSugar

ASP.NET 4.0+ ORACLE https://github.com/sunkaixuan/OracleSugar

ASP.NET Core Oracle 等官方驅動程式碼已完成

優點

SqlSugar 是一款小巧，並且功能齊全的ORM，並不需要像Dapper一樣依賴第三方擴充套件

SqlSugar 語法易用簡單，有漂亮的拉姆達語法，也支援Dapper SQL和ADO.NET（儲存過程等）的所有功能

SqlSugar 效能達到原生水準，遠超 Dapper和EF CORE。

SqlSugar 支援.NET CORE , 多個資料庫

SqlSugar 體積小巧只有150K是EF的30分之1 ，NUGET直接可以下載

連線資料庫

1.簡單用法

using(var db = new SqlSugarClient(ConnectionString)){

    //use object
    var list=db.Queryable<T>().ToList();

}

2.打包用法，推薦

/// <summary>
/// SqlSugar
/// </summary>
public class SugarDao
{
    private SugarDao()
    {

    }
    public static string ConnectionString
    {
        get
        {
            string reval = "server=.;uid=sa;pwd=sasa;database=SqlSugarTest"; 
            return reval;
        }
    }
    public static SqlSugarClient GetInstance()
    {
        var db = new SqlSugarClient(ConnectionString);
        db.IsEnableLogEvent = true;//Enable log events
        db.LogEventStarting = (sql, par) => { Console.WriteLine(sql + " " + par+"\r\n"); };
        return db;
    }
}

使用打包物件

using (var db = SugarDao.GetInstance())
{
    var list=db.Queryable<T>().ToList();
}

3.高階玩法，基於IOC和指揮官模式的DEMO

https://github.com/sunkaixuan/ASPNETMVCCRUDDEMO

查詢

1. Queryable 拉姆達查詢

                //查詢所有
                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 singleByPk = db.Queryable<Student>().InSingle(1);
                //查詢單條沒有記錄返回空物件
                var singleOrDefault = db.Queryable<Student>().SingleOrDefault(c => c.id == 11111111);
                //查詢單條沒有記錄返回空物件
                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();

                //取11-20條
                var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).Skip(10).Take(10).ToList();

                //取11-20條  等於 Skip(pageIndex-1)*pageSize).Take(pageSize) 等於  between (pageIndex-1)*pageSize and  pageIndex*pageSize
                var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).ToPageList(2, 10);

                //查詢條數
                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 = "a";
                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();

                //支援字串Where 讓你解決，更復雜的查詢
                var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>@id", new { id = 1 }).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);


                //獲取最大Id
                object maxId = db.Queryable<Student>().Max(it => it.id);
                int maxId1 = db.Queryable<Student>().Max(it => it.id).ObjToInt();//拉姆達
                int maxId2 = db.Queryable<Student>().Max<int>("id"); //字串寫法

                //獲取最小
                int minId1 = db.Queryable<Student>().Where(c => c.id > 0).Min(it => it.id).ObjToInt();//拉姆達
                int minId2 = db.Queryable<Student>().Where(c => c.id > 0).Min<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 listnew = db.Queryable<Student>().Where(it => intArray.Contains(it.name)).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 list6 = db.Queryable<Student>().In(intList).ToList();//不設定欄位預設主鍵

                //分組查詢
                var list7 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy(it => it.sex).Select("sex,count(*) Count").ToDynamic();
                var list8 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy(it => it.sex).GroupBy(it => it.id).Select("id,sex,count(*) Count").ToDynamic();
                List<StudentGroup> list9 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy(it => it.sex).Select<StudentGroup>("Sex,count(*) Count").ToList();
                List<StudentGroup> list10 = db.Queryable<Student>().Where(c => c.id < 20).GroupBy("sex").Select<StudentGroup>("Sex,count(*) Count").ToList();
                //SELECT Sex,Count=count(*)  FROM Student  WHERE 1=1  AND  (id < 20)    GROUP BY Sex --生成結果



                //2表關聯查詢
                var jList = db.Queryable<Student>()
                .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //預設left join
                .Where<School>((s1, s2) => s1.id == 1)
                .Select("s1.*,s2.name as schName")
                .ToDynamic();

                /*等於同於
                 SELECT s1.*,s2.name as schName 
                 FROM [Student]  s1 
                 LEFT JOIN [School]  s2 ON  s1.sch_id  = s2.id 
                 WHERE  s1.id  = 1 */

                //2表關聯查詢並分頁
                var jList2 = db.Queryable<Student>()
                .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //預設left join
                    //如果要用inner join這麼寫
                    //.JoinTable<School>((s1, s2) => s1.sch_id == s2.id ,JoinType.INNER)
                .Where<School>((s1, s2) => s1.id > 1)
                .OrderBy(s1 => s1.name)
                .Skip(10)
                .Take(20)
                .Select("s1.*,s2.name as schName")
                .ToDynamic();

                //3表查詢並分頁
                var jList3 = db.Queryable<Student>()
               .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) // left join  School s2  on s1.id=s2.id
               .JoinTable<School>((s1, s3) => s1.sch_id == s3.id) // left join  School s3  on s1.id=s3.id
               .Where<School>((s1, s2) => s1.id > 1)  // where s1.id>1
               .Where(s1 => s1.id > 0)
               .OrderBy<School>((s1, s2) => s1.id) //order by s1.id 多個order可以  .oderBy().orderby 疊加 
               .Skip(10)
               .Take(20)
               .Select("s1.*,s2.name as schName,s3.name as schName2")//select目前只支援這種寫法
               .ToDynamic();


                //上面的方式都是與第一張表join，第三張表想與第二張表join寫法如下
                List<V_Student> jList4 =
                 db.Queryable<Student>()
                 .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) // left join  School s2  on s1.id=s2.id
                 .JoinTable<School, Area>((s1, s2, a1) => a1.id == s2.AreaId)// left join  Area a1  on a1.id=s2.AreaId  第三張表與第二張表關聯
                 .JoinTable<Area, School>((s1, a1, s3) => a1.id == s3.AreaId)// left join  School s3  on a1.id=s3.AreaId  第四第表第三張表關聯
                 .JoinTable<School>((s1, s4) => s1.sch_id == s4.id) // left join  School s2  on s1.id=s4.id
                 .Select<School, Area, V_Student>((s1, s2, a1) => new V_Student { id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name }).ToList();

                //等同於
                //SELECT id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name  
                //FROM [Student]   s1 
                //LEFT JOIN School  s2 ON  ( s1.sch_id  = s2.id )    
                //LEFT JOIN Area  a1 ON  ( a1.id  = s2.AreaId )     //第三張表與第二張表關聯
                //LEFT JOIN School  s3 ON  ( a1.id  = s3.AreaId )   //第四張表與第三張表關聯
                //LEFT JOIN School  s4 ON  ( s1.sch_id  = s4.id )    
                //WHERE 1=1    


                //Join子查詢語句加分頁的寫法
                var childQuery = db.Queryable<Area>().Where("id=@id").Select(it => new { id = it.id }).ToSql();//建立子查詢SQL
                string childTableName =SqlSugarTool.PackagingSQL(childQuery.Key);//將SQL語句用()包成表
                var queryable = db.Queryable<Student>()
                 .JoinTable<School>((s1, s2) => s1.sch_id == s2.id)  //LEFT JOIN School  s2 ON  ( s1.sch_id  = s2.id )  
                 .JoinTable(childTableName, "a1", "a1.id=s2.areaid", new { id = 1 }, JoinType.INNER) //INNER JOIN (SELECT *  FROM [Area]   WHERE 1=1  AND id=@id   ) a1 ON a1.id=s2.areaid
                 .OrderBy(s1 => s1.id);

                var list = queryable.Select<School, Area, V_Student>((s1, s2, a1) => new V_Student { id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name })
                    .ToPageList(0, 200);
                var count2 = queryable.Count();


                //拼接例子
                var queryable2 = db.Queryable<Student>().Where(it => true);
                if (maxId.ObjToInt() == 1)
                {
                    queryable2.Where(it => it.id == 1);
                }
                else
                {
                    queryable2.Where(it => it.id == 2);
                }
                var listJoin = queryable2.ToList();


                //queryable和SqlSugarClient解耦
                var par = new Queryable<Student>().Where(it => it.id == 1);//聲名沒有connection物件的Queryable
                par.DB = db;
                var listPar = par.ToList();


                //檢視生成的sql和引數
                var id = 1;
                var sqlAndPars = db.Queryable<Student>().Where(it => it.id == id).OrderBy(it => it.id).ToSql();



                //拉姆達支援的函式操作
                var par1 = "2015-1-1"; var par2 = "   我 有空格A, ";
                var r1 = db.Queryable<Student>().Where(it => it.name == par1.ObjToString()).ToList(); //ObjToString會將null轉轉成""
                var r2 = db.Queryable<InsertTest>().Where(it => it.d1 == par1.ObjToDate()).ToList();
                var r3 = db.Queryable<InsertTest>().Where(it => it.id == 1.ObjToInt()).ToList();//ObjToInt會將null轉轉成0
                var r4 = db.Queryable<InsertTest>().Where(it => it.id == 2.ObjToDecimal()).ToList();
                var r5 = db.Queryable<InsertTest>().Where(it => it.id == 3.ObjToMoney()).ToList();
                var r6 = db.Queryable<InsertTest>().Where(it => it.v1 == par2.Trim()).ToList();
                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 => c.name == par2.ToLower()).ToList();
                var convert4 = db.Queryable<Student>().Where(c => c.name == par2.ToUpper()).ToList();
                var convert5= db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
                var c1 = db.Queryable<Student>().Where(c => c.name.Contains("a")).ToList();
                var c2 = db.Queryable<Student>().Where(c => c.name.StartsWith("a")).ToList();
                var c3 = db.Queryable<Student>().Where(c => c.name.EndsWith("a")).ToList();
                var c4 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name)).ToList();
                var c5 = db.Queryable<Student>().Where(c => c.name.Equals("小杰")).ToList();
                var c6 = db.Queryable<Student>().Where(c => c.name.Length > 4).ToList();
                var time = db.Queryable<InsertTest>().Where(c => c.d1>DateTime.Now.AddDays(1)).ToList();
                var time2 = db.Queryable<InsertTest>().Where(c => c.d1 > DateTime.Now.AddYears(1)).ToList();
                var time3 = db.Queryable<InsertTest>().Where(c => c.d1 > DateTime.Now.AddMonths(1)).ToList();

2. SqlQuery 原生SQL查詢

                //轉成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").SingleOrDefault();
                //反回鍵值
                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  id,name from Student limit 0,1").SingleOrDefault();
                //儲存過程
                //var spResult = db.SqlQuery<School>("exec sp_school @p1,@p2", new { p1 = 1, p2 = 2 });

                //儲存過程加Output 
                //var pars = SqlSugarTool.GetParameters(new { p1 = 1,p2=0 }); //將匿名物件轉成SqlParameter
                //db.IsClearParameters = false;//禁止清除引數
                //pars[1].Direction = ParameterDirection.Output; //將p2設為 output
                //var spResult2 = db.SqlQuery<School>("exec sp_school @p1,@p2 output", pars);
                //db.IsClearParameters = true;//啟用清除引數
                //var outPutValue = pars[1].Value;//獲取output @p2的值


                ////儲存過程優化操作
                //var pars2 = SqlSugarTool.GetParameters(new { p1 = 1, p2 = 0 }); //將匿名物件轉成SqlParameter
                //db.CommandType = CommandType.StoredProcedure;//指定為儲存過程可比上面少寫EXEC和引數
                //var spResult3 = db.SqlQuery<School>("sp_school", pars2);
                //db.CommandType = CommandType.Text;//還原回預設


                //獲取第一行第一列的值
                string v1 = db.GetString("select '張三' as name");
                int v2 = db.GetInt("select 1 as name");
                double v3 = db.GetDouble("select 1 as name");
                decimal v4 = db.GetDecimal("select 1 as name");

3.Sqlable 高階SQL拼接器

      //多表查詢
                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");
                }
                if (id > 0)
                {
                    sable = sable.Where("l.id in(1)");//where加子查詢
                }
                var pars = new { id = id, name = name };
                int pageCount = sable.Count(pars);
                var list7 = sable.SelectToPageList<Student>("s.*", "l.id desc", 1, 20, pars);

View Code

插入

支援了高效能批量插入，讓你事辦功倍

 db.Insert(GetInsertItem()); //插入一條記錄 (有主鍵也好，沒主鍵也好，有自增列也好都可以插進去)


                db.InsertRange(GetInsertList()); //批量插入 支援（別名表等功能）


                db.SqlBulkCopy(GetInsertList()); //批量插入 適合海量資料插入



                //設定不插入列
                db.DisableInsertColumns = new string[] { "sex" };//sex列將不會插入值
                Student s = new Student()
                {
                    name = "張" + new Random().Next(1, int.MaxValue),
                    sex = "gril"
                };

                var id = db.Insert(s); //插入

                //查詢剛插入的sex是否有值
                var sex = db.Queryable<Student>().Single(it => it.id == id.ObjToInt()).sex;//無值
                var name = db.Queryable<Student>().Single(it => it.id == id.ObjToInt()).name;//有值


                //SqlBulkCopy同樣支援不挺入列設定
                db.SqlBulkCopy(GetInsertList());

更新

                //指定列更新
                db.Update<School>(new { name = "藍翔14" }, it => it.id == 14); //只更新name列
                db.Update<School, int>(new { name = "藍翔11 23 12", areaId = 2 }, 11, 23, 12);
                db.Update<School, string>(new { name = "藍翔2" }, new string[] { "11", "21" });
                db.Update<School>(new { name = "藍翔2" }, it => it.id == 100);
                var array=new int[]{1,2,3};
                db.Update<School>(new { name = "藍翔2" }, it => array.Contains(it.id));// id in 1,2,3


                //支援字典更新，適合動態許可權
                var dic = new Dictionary<string, string>();
                dic.Add("name", "第十三條");
                dic.Add("areaId", "1");
                db.Update<School, int>(dic, 13);


                //整個實體更新
                db.Update(new School { id = 16, name = "藍翔16", AreaId = 1 });
                db.Update<School>(new School { id = 12, name = "藍翔12", AreaId = 2 }, it => it.id == 18);
                db.Update<School>(new School() { id = 11, name = "青鳥11" });

                //設定不更新列
                db.DisableUpdateColumns = new string[] { "CreateTime" };//設定CreateTime不更新

                TestUpdateColumns updObj = new TestUpdateColumns()
                {
                    VGUID = Guid.Parse("542b5a27-6984-47c7-a8ee-359e483c8470"),
                    Name = "xx",
                    Name2 = "xx2",
                    IdentityField = 0,
                    CreateTime = null
                };

                //CreateTime將不會被更新
                db.Update(updObj);
                //以前實現這種更新需要用指定列的方式實現，現在就簡單多了。



                //批量更新   資料量小時建議使用
                var updateResult = db.UpdateRange(GetUpdateList());

                //批量更新  資料量大時建議使用
                var updateResult2 = db.SqlBulkReplace(GetUpdateList2());

刪除

                //刪除根據主鍵
                db.Delete<School, int>(10);

                //刪除根據表達示
                db.Delete<School>(it => it.id > 100);//支援it=>array.contains(it.id)
 
                //主鍵批量刪除
                db.Delete<School, string>(new string[] { "100", "101", "102" });

                //非主鍵批量刪除
                db.Delete<School, string>(it => it.name, new string[] { "" });
                db.Delete<School, int>(it => it.id, new int[] { 20, 22 });


                //根據實體賦值實體一定要有主鍵，並且要有值。
                db.Delete(new School() { id = 200 });

                //根據字串刪除
                db.Delete<School>("id=@id", new { id = 100 });

                //假刪除
                //db.FalseDelete<school>("is_del", 100);
                //等同於 update school set is_del=1 where id in(100)
                //db.FalseDelete<school>("is_del", it=>it.id==100);

更多功能

1、列舉支援

  var stuList = db.Queryable<Student>().ToList();
                db.Insert<Student>(new Student() { sch_id = SchoolEnum.北大青鳥 });
                db.Update<Student>(new Student() { sch_id = SchoolEnum.it清華, id = 11 });
                var stuList2 = db.Queryable<Student>().Where(it => it.sch_id == SchoolEnum.全智).ToList();

View Code

2、實體生成加T4

View Code

使用T4

詳細教程：

http://www.cnblogs.com/sunkaixuan/p/5751503.html

3、ADO.NET

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


                //引數支援
                var p1 = db.GetDataTable("select * from student where id=@id", new {id=1 });
                var p2 = db.GetDataTable("select * from student where id=@id", new Dictionary<string, object>() { { "id", "1" } });//目前只支援 Dictionary<string, object>和Dictionary<string, string>
                var p3 = db.GetDataTable("select * from student where id=@id", new MySqlParameter("@id",1) );

4、日誌記錄功能

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NewTest.Dao;
using Models;
using System.Data.SqlClient;
using MySqlSugar;

namespace NewTest.Demos
{
    //日誌記錄功能
    public class Log : IDemos
    {
       
        public void Init()
        {
            Console.WriteLine("啟動Log.Init");
            using (var db = SugarDemoDao.GetInstance())
            {

                var a1 = db.Queryable<Student>().Where(it => it.id == 1).ToList();
                var a2 = db.Queryable<Student>().OrderBy(it => it.id).ToList();
            }
        }

        public class SugarConfigs
        {
            public static Action<string, string> LogEventStarting = (sql, pars) =>
            {
                Console.WriteLine("starting:" + sql + " " + pars);
                
                using (var db = SugarDemoDao.GetInstance())
                {
                    //日誌記錄件事件裡面用到資料庫操作 IsEnableLogEvent一定要為false否則將引起死迴圈，並且要新開一個資料例項 像我這樣寫就沒問題。
                    db.IsEnableLogEvent = false;
                    db.ExecuteCommand("select 1");
                }
            };
            public static Action<string, string> LogEventCompleted = (sql, pars) =>
            {
                Console.WriteLine("completed:" + sql + " " + pars);
            };
        }

        /// <summary>
        /// SqlSugar
        /// </summary>
        public class SugarDemoDao
        {

            public static SqlSugarClient GetInstance()
            {
                var db = new SqlSugarClient(SugarDao.ConnectionString);
                db.IsEnableLogEvent = true;//啟用日誌事件
                db.LogEventStarting = SugarConfigs.LogEventStarting;
                db.LogEventCompleted = SugarConfigs.LogEventCompleted;
                return db;
            }
        }
    }
}

5、別名表功能

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NewTest.Dao;
using Models;
using System.Data.SqlClient;
using MySqlSugar;
namespace NewTest.Demos
{
    //別名表的功能
    public class MappingTable : IDemos
    {

        public void Init()
        {
            Console.WriteLine("啟動MappingTable.Init");

            //單個設定
            using (var db = SugarDao.GetInstance())
            {
                var list = db.Queryable<V_Student>("Student").ToList();//查詢的是 select * from student 而我的實體名稱為V_Student
            }


            //全域性設定
            using (var db = SugarFactory.GetInstance())
            {
                var list = db.Queryable<V_Student>().ToList();//查詢的是 select * from student 而我的實體名稱為V_Student
            }
        }


        /// <summary>
        /// 全域性配置類
        /// </summary>
        public class SugarConfigs
        {
            //key類名 value表名
            public static List<KeyValue> MpList = new List<KeyValue>(){
            new KeyValue(){ Key="FormAttr", Value="Flow_FormAttr"},
            new KeyValue(){ Key="Student3", Value="Student"},
            new KeyValue(){ Key="V_Student", Value="Student"}
            };
        }

        /// <summary>
        /// SqlSugar例項工廠
        /// </summary>
        public class SugarFactory
        {

            //禁止例項化
            private SugarFactory()
            {

            }
            public static SqlSugarClient GetInstance()
            {
                string connection = SugarDao.ConnectionString; //這裡可以動態根據cookies或session實現多庫切換
                var db = new SqlSugarClient(connection);

                db.SetMappingTables(SugarConfigs.MpList);//設定關聯表 (引用地址賦值，每次賦值都只是儲存一個記憶體地址)



                //批量設定別名表
                //db.ClassGenerating.ForeachTables(db, tableName =>
                //{
                //    db.AddMappingTable(new KeyValue() { Key = tableName.Replace("bbs.",""), Value =  tableName }); //key實體名，value表名
                //});


                return db;
            }
        }
    }
}

View Code

6、別名列功能

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NewTest.Dao;
using System.Data.SqlClient;
using MySqlSugar;
namespace NewTest.Demos
{
    //別名列的功能
    public class MappingColumns : IDemos
    {

        public void Init()
        {
            Console.WriteLine("啟動MappingColumns.Init");

            //全域性設定
            using (var db = SugarFactory.GetInstance())
            {
                var list = db.Queryable<Student>().Where(it=>it.classId==1).ToList();
            }
        }

        public class Student
        {

            //id
            public int classId { get; set; }

            //name
            public string className { get; set; }

            //sch_id
            public int classSchoolId { get; set; }

            public int isOk { get; set; }
        }

        /// <summary>
        /// 全域性配置別名列（不區分表）
        /// </summary>
        public class SugarConfigs
        {
            //key實體欄位名 value表欄位名 ，KEY唯一否則異常
            public static List<KeyValue> MpList = new List<KeyValue>(){
            new KeyValue(){ Key="classId", Value="id"},
            new KeyValue(){ Key="className", Value="name"},
            new KeyValue(){ Key="classSchoolId", Value="sch_id"}
            };
        }

        /// <summary>
        /// SqlSugar例項工廠
        /// </summary>
        public class SugarFactory
        {

            //禁止例項化
            private SugarFactory()
            {

            }
            public static SqlSugarClient GetInstance()
            {
                string connection = SugarDao.ConnectionString; //這裡可以動態根據cookies或session實現多庫切換
                var db = new SqlSugarClient(connection);
                //注意：只有啟動屬性對映才可以使用SetMappingColumns
                db.IsEnableAttributeMapping = true;
                db.SetMappingColumns(SugarConfigs.MpList);//設定關聯列 (引用地址賦值，每次賦值都只是儲存一個記憶體地址)
                return db;
            }
        }
    }
}

View Code

7、通過屬性來設定別名表和別名列

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NewTest.Dao;
using Models;
using System.Data.SqlClient;
using MySqlSugar;
namespace NewTest.Demos
{
    //通過屬性的方法設定別名表和別名欄位（主鍵和自添列都無需設定 SQLSUGAR會幫你自動處理）
    //注意:【屬性對映和 (SetMappingTables、SetMappingColumns)方式對映 2種選其中一，不清楚底層快取機質不建議同時使用】
    public class AttributesMapping : IDemos
    {

        public void Init()
        {
            Console.WriteLine("啟動AttributesMapping.Init");
            using (var db = DBManager.GetInstance())
            {

                //查詢
                var list = db.Queryable<TestStudent>()
                    .Where(it => it.className.Contains("小")).OrderBy(it => it.classSchoolId).Select<V_Student>(it => new V_Student() { id = it.classId, name = it.className }).ToList();
                var list2 = db.Queryable<TestStudent>()
                    .JoinTable<TestSchool>((s1, s2) => s1.classSchoolId == s2.classId)
                    .OrderBy<TestSchool>((s1, s2) => s1.classId)
                    .Select<TestStudent, TestSchool, V_Student>((s1, s2) => new V_Student() { id = s1.classId, name = s1.className, SchoolName = s2.className }).ToList();

                //新增
                TestStudent s = new TestStudent();
                s.className = "屬性名";
                s.classSchoolId = 1;
                var id = db.Insert(s);
                s.classId = id.ObjToInt();


                db.SqlBulkCopy(new List<TestStudent>() { s });


                //更新
                db.Update(s);
                db.Update<TestStudent, int>(s, 100);
                db.Update<TestStudent>(s, it => it.classId == 100);
                db.SqlBulkReplace(new List<TestStudent>() { s });

                //刪除
                db.Delete<TestStudent>(it => it.classId == 100);

                //根據實體賦值實體一定要有主鍵，並且要有值。
                db.Delete(new TestStudent() {  classId = 200 });
            }
        }

        /// <summary>
        /// 屬性只作為初始化對映，SetMappingTables和SetMappingColumns可以覆蓋
        /// </summary>
        [SugarMapping(TableName = "Student")]
        public class TestStudent
        {

            [SugarMapping(ColumnName = "id")]
            public int classId { get; set; }

            [SugarMapping(ColumnName = "name")]
            public string className { get; set; }

            [SugarMapping(ColumnName = "sch_id")]
            public int classSchoolId { get; set; }

            public int isOk { get; set; }

            /// <summary>
            /// 資料庫並沒有這一列
            /// </summary>
            public string errorField { get; set; }
        }

 
        [SugarMapping(TableName = "School")]
        public class TestSchool
        {

            [SugarMapping(ColumnName = "id")]
            public int classId { get; set; }

            [SugarMapping(ColumnName = "name")]
            public string className { get; set; }

            public int AreaId = 1;
        }
    }

    public class DBManager
    {

        public static SqlSugarClient GetInstance()
        {
            var db = new SqlSugarClient(SugarDao.ConnectionString);
            db.IsEnableAttributeMapping = true;//啟用屬性對映
            db.IsIgnoreErrorColumns = true;//忽略非資料庫列



            db.IsEnableLogEvent = true;//啟用日誌事件
            db.LogEventStarting = (sql, par) => { Console.WriteLine(sql + " " + par + "\r\n"); };


            return db;
        }
    }
}

View Code

8、行列過濾 (行過濾器可以設定多個，以逗號隔開)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NewTest.Dao;
using Models;
using System.Data.SqlClient;
using MySqlSugar;

namespace NewTest.Demos
{
    //行過濾加列過濾
    //許可權管理的最佳設計
    public class Filter2 : IDemos
    {

        public void Init()
        {
            Console.WriteLine("啟動Filter2.Init");
            using (SqlSugarClient db = SugarDaoFilter.GetInstance())//開啟資料庫連線
            {
                //設定走哪個過濾器
                db.CurrentFilterKey = "role1";
                //queryable
                var list = db.Queryable<Student>().ToJson(); //where id=1 , 可以檢視id和name


                //設定走哪個過濾器
                db.CurrentFilterKey = "role2";
                //queryable
                var list2 = db.Queryable<Student>().ToJson(); //where id=2 , 可以檢視name

            }
        }
        /// <summary>
        /// 擴充套件SqlSugarClient
        /// </summary>
        public class SugarDaoFilter
        {
            //禁止例項化
            private SugarDaoFilter()
            {

            }
            /// <summary>
            /// 頁面所需要的過濾行
            /// </summary>
            private static Dictionary<string, Func<KeyValueObj>> _filterRos = new Dictionary<string, Func<KeyValueObj>>()
            {
              { "role1",()=>{
                        return new KeyValueObj(){ Key=" id=@id" , Value=new{ id=1}};
                   }
              },
              { "role2",()=>{
                  return new KeyValueObj() { Key = " id=@id", Value = new { id = 2 } };
                  }
              },
            };
            /// <summary>
            /// 頁面所需要的過濾列
            /// </summary>
            private static Dictionary<string, List<string>> _filterColumns = new Dictionary<string, List<string>>()
            {
              { "role1",new List<string>(){"id","name"}
              },
              { "role2",new List<string>(){"name"}
              },
            };
            public static SqlSugarClient GetInstance()
            {
                string connection = SugarDao.ConnectionString; //這裡可以動態根據cookies或session實現多庫切換
                var db = new SqlSugarClient(connection);

                //支援sqlable和queryable
                db.SetFilterFilterParas(_filterRos);

                //列過濾只支援queryable
                db.SetFilterFilterParas(_filterColumns);


                db.IsEnableLogEvent = true;//啟用日誌事件
                db.LogEventStarting = (sql, par) => { Console.WriteLine(sql + " " + par + "\r\n"); };
                return db;
            }
        }
    }
}

View Code

9、自動排除非資料庫列

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NewTest.Dao;
using Models;
using System.Data.SqlClient;
using MySqlSugar;

namespace NewTest.Demos
{
    //排除錯誤列
    public class IgnoreErrorColumns : IDemos
    {

        public void Init()
        {
            Console.WriteLine("啟動IgnoreErrorColumns.Init");
            using (var db = SugarDao.GetInstance())
            {
                db.IsIgnoreErrorColumns = true;

                //Student表並沒有 AreaName
               var id=  db.Insert<STUDENT>(new STUDENT() { name = "張三", AreaName = "北大" });


                db.Update<STUDENT>(new STUDENT() { id=id.ObjToInt() ,name = "張三2", AreaName = "北大" });
            }
        }
        public class STUDENT {
            /// <summary>
            /// Desc:- 
            /// Default:- 
            /// Nullable:False 
            /// </summary>
            public int id { get; set; }

            /// <summary>
            /// Desc:- 
            /// Default:- 
            /// Nullable:True 
            /// </summary>
            public string name { get; set; }

            /// <summary>
            /// Desc:學校ID 
            /// Default:- 
            /// Nullable:True 
            /// </summary>
            public int? sch_id { get; set; }

            /// <summary>
            /// Desc:- 
            /// Default:- 
            /// Nullable:True 
            /// </summary>
            public string sex { get; set; }

            /// <summary>
            /// Desc:- 
            /// Default:- 
            /// Nullable:True 
            /// </summary>
            public Boolean? isOk { get; set; }

            public string SchoolName { get; set; }

            public string AreaName { get; set; }

            public string SubjectName { get; set; }
        }
    }
}

View Code

10、事務

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NewTest.Dao;
using Models;
using System.Data.SqlClient;
using MySqlSugar;

namespace NewTest.Demos
{
    //事務
    public class Tran : IDemos
    {

        public void Init()
        {
            Console.WriteLine("啟動Tran.Init");
            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;
                }
            }
        }
    }
}

11、流水號功能

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NewTest.Dao;
using Models;
using System.Data.SqlClient;
using MySqlSugar;

namespace NewTest.Demos
{
    //流水號的功能
    public class SerialNumber : IDemos
    {

        public void Init()
        {
            Console.WriteLine("啟動SerialNumber.Init");
            using (SqlSugarClient db = SugarFactory.GetInstance())//開啟資料庫連線
            {
                var dientityValue = db.Insert<Student>(new Student() {   });
                var name = db.Queryable<Student>().Single(it => it.id == dientityValue.ObjToInt()).name;
                Console.WriteLine(name);

                var dientityValue2 = db.Insert<School>(new School() { });
                var name2 = db.Queryable<School>().Single(it => it.id == dientityValue2.ObjToInt()).name;
                Console.WriteLine(name2); ;
            }
        }

        /// <summary>
        /// 全域性配置類
        /// </summary>
        public class SugarConfigs
        {
            public static List<PubModel.SerialNumber> NumList = new List<PubModel.SerialNumber>(){
              new PubModel.SerialNumber(){TableName="Student", FieldName="name", GetNumFunc=()=>{ //GetNumFunc在沒有事中使用
                  return "stud-"+DateTime.Now.ToString("yyyy-MM-dd");
              }},
                new PubModel.SerialNumber(){TableName="School", FieldName="name",  GetNumFuncWithDb=db=>{ //事務中請使用GetNumFuncWithDb保證同一個DB物件,不然會出現死鎖
                  return "ch-"+DateTime.Now.ToString("syyyy-MM-dd");
              }}
            };
        }

        /// <summary>
        /// SqlSugar例項工廠
        /// </summary>
        public class SugarFactory
        {

            //禁止例項化
            private SugarFactory()
            {

            }
            public static SqlSugarClient GetInstance()
            {
                string connection = SugarDao.ConnectionString; //這裡可以動態根據cookies或session實現多庫切換
                var db = new SqlSugarClient(connection);
                db.SetSerialNumber(SugarConfigs.NumList);//設定流水號
                return db;
            }
        }
    }
}

View Code

12新增VS知能提示，讓你立馬上手。

http://www.cnblogs.com/sunkaixuan/p/5851425.html

N[開源].NET CORE與MySql更配, MySqlSugar ORM框架 3.x
2016-10-22
MySqlSqlSugarORM框架
ASP.NET Core 3.x API版本控制
2020-06-02
ASP.NETAPI
Asp.net 高效能 Sqlite ORM 框架之 sqliteSugar
2016-08-14
ASP.NETSQLiteORM框架
輕量級.NET ORM、高效能.NET ORM 之 SqlSugar 開源ORM - ASP.NET
2015-07-22
ORMSqlSugarASP.NET
.NET 開源核心類庫：.NET Core
2014-11-16
.NET 開源SqlServer ORM框架 SqlSugar 3.0 API
2016-09-27
ServerORM框架SqlSugarAPI
龍芯開源社群上線.NET主頁
2020-07-10
[開源] .Net orm FreeSql 1.5.0 最新版本（番號：好久不見）
2020-05-25
ORMSQL
使用 .NET Core 3.x 構建 RESTFUL Api
2020-08-09
RESTAPI
[開源] .Net ORM 訪問 Firebird 資料庫
2022-07-07
ORM資料庫
使用 .NET Core 3.x 構建 RESTFUL Api （續）
2020-08-11
RESTAPI
.NET Core 2.0 開源Office元件 NPOI
2017-08-22
元件
[開源] .Net ORM FreeSql 1.10.0 穩步向前
2020-10-22
ORMSQL
.Net Core 3.0 MVC 中使用 SqlSugar ORM框架
2020-10-21
MVCSqlSugarORM框架
[Rbatis ORM官網上線]
2020-10-09
BATORM
ClickHouse ORM 3.x 釋出啦
2023-01-12
ORM
gRPC by .net core 3.x——概念、語法、編譯
2020-07-03
RPC編譯
用 .SqlSugar ORM 來實現報表功能 .NET CORE /.NET
2021-07-05
SqlSugarORM
[開源] .Net 使用 ORM 訪問華為GaussDB資料庫
2020-11-08
ORM資料庫
.NET ORM框架 SqlSugar4.0 功能快速預覽【開源】
2017-06-18
ORM框架SqlSugar
開源：Taurus.Idempotent 分散式冪等性鎖框架，支援 .Net 和 .Net Core 雙系列版本
2024-03-05
IDE分散式框架
Moon.Orm 5.0(MQL版)及之前版本的開源計劃
2013-09-07
ORMMQ
.NET Core跨平臺：使用.NET Core開發一個初心源商城總括
2017-02-25
.Net orm 開源專案 FreeSql 2.0.0（滿意的答卷）
2020-11-24
ORMSQL
.NET Core中的事件溯源開源專案
2018-09-14
事件
微軟開源.NET Core的執行引擎CoreCLR
2015-02-06
微軟
.Net 常用ORM框架對比：EF Core、FreeSql、SqlSuger
2020-12-20
ORM框架SQL
使用VS Code開發asp.net core (上)
2018-02-16
ASP.NET
ASP.NET Core WebApi版本控制
2021-02-21
ASP.NETWebAPI
一個.NET Core下的開源外掛框架
2020-11-05
框架
.net上的開源工作流
2007-01-04
ASP.NET Core 3.x控制IHostedService啟動順序淺探
2020-09-09
ASP.NET
ASP.NET Core 修改開源協議為MIT，.NET全平臺 MIT協議開源了
2021-08-25
ASP.NET協議MIT
UOS LoongArch 上成功安裝.NET Core 3.1
2021-09-04
開源框架 - 新程式碼生成器 WebFirst / .NET Core
2021-10-07
框架Web
基於.NET Core的優秀開源專案合集
2021-01-22
asp.net core 比較好的開源專案
2020-09-29
ASP.NET
.NET Core/.NET5/.NET6 開源專案彙總13：模板引擎
2021-08-13

[開源ORM] SqliteSugar 3.x .net Core版本成功上線

SqliteSqlSugar 3.X API

引數多元化的支援：

SQL語句使用拉姆達的技巧

所有版本

優點

連線資料庫

查詢

插入

更新

刪除

更多功能

1、列舉支援

2、實體生成加T4

3、ADO.NET

4、日誌記錄功能

5、別名表功能

6、別名列功能

7、通過屬性來設定別名表和別名列

8、行列過濾 (行過濾器可以設定多個，以逗號隔開)

9、自動排除非資料庫列

10、事務

11、流水號功能

12新增VS知能提示，讓你立馬上手。

相關文章