C# .NET ORM 框架 SqlSugar 5.X 版本

╰半夏微凉° 2022-01-28 10:37 580阅读 0赞

# SqlSugar 5.X API #

使用SqlSugar非常简单，而且功能强大。

SqlSugar=一个对象+一个参数=16个函数，

支持数据库：MySql、SqlServer、Sqlite、Oracle 、 postgresql

SqlSugar 16个功能

![è¾å¥å¾çè¯´æ][6369224056499802674782957.jpg_id_1]

创建SqlSugarClient

所有操作都基于SqlSugarClient

SqlSugarClient参数，只有一个ConnectionConfig

public  List<Student> GetStudentList()
    {
        var db= GetInstance();
        var list= db.Queryable<Student>().ToList();//Search
        return list;
    }
    
    /// <summary>
    /// Create SqlSugarClient
    /// </summary>
    /// <returns></returns>
    private SqlSugarClient GetInstance()
    {
        SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "Server=.xxxxx",
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute
            });
        //Print sql
        db.Aop.OnLogExecuting = (sql, pars) =>
        {
            Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value)));
            Console.WriteLine();
        };
        return db;
    }
    
    public class Student
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true]
        public int Id { get; set; }
        public int? SchoolId { get; set; }
        public string Name { get; set; }
    }

[View more >>][View more]

1、查询方法

![è¾å¥å¾çè¯´æ][6369240932997363035197459.png_id_1]

下面简单案例

//easy
    var getAll = db.Queryable<Student>().ToList();
    var getAllNoLock = db.Queryable<Student>().With(SqlWith.NoLock).ToList();
    var getByPrimaryKey = db.Queryable<Student>().InSingle(2);
    var sum = db.Queryable<Student>().Sum(it=>it.Id);
    var isAny = db.Queryable<Student>().Where(it=>it.Id==-1).Any();
    var isAny2 = db.Queryable<Student>().Any(it => it.Id == -1);
    var getListByRename = db.Queryable<School>().AS("Student").ToList();
    var getByWhere = db.Queryable<Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
    var getByFuns = db.Queryable<Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
    var group = db.Queryable<Student>().GroupBy(it => it.Id).Select(it =>new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
    
    //Page
    var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);
    
    //page join
    var pageJoin = db.Queryable<Student, School>((st, sc) =>new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
    .ToPageList(pageIndex, pageSize, ref totalCount);
    
    //top 5
    var top5 = db.Queryable<Student>().Take(5).ToList();
    
    //join Order By (order by st.id desc,sc.id desc)
    var list4 = db.Queryable<Student, School>((st, sc) =>new  JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
    .OrderBy(st=>st.Id,OrderByType.Desc)
    .OrderBy((st,sc)=>sc.Id,OrderByType.Desc)
    .Select<ViewModelStudent>().ToList();

[View more >>][View more 1]

2、更新方法

//update reutrn Update Count
    var t1= db.Updateable(updateObj).ExecuteCommand();
    
    //Only  update  Name 
    var t3 = db.Updateable(updateObj).UpdateColumns(it => new { it.Name }).ExecuteCommand();
    
    //Ignore  Name and TestId
    var t4 = db.Updateable(updateObj).IgnoreColumns(it => new { it.Name, it.TestId }).ExecuteCommand();
    
    //update List<T>
    var t7 = db.Updateable(updateObjs).ExecuteCommand();
    
    //Where By Expression
    var t9 = db.Updateable(it=>new class() { name="a",createtime=p }).Where(it => it.Id == 1).ExecuteCommand();

[View more >>][View more 2]

3、新增方法

//Insert reutrn Insert Count
    var t2 = db.Insertable(insertObj).ExecuteCommand();
    
    //Insert reutrn Identity Value
    var t3 = db.Insertable(insertObj).ExecuteReutrnIdentity();
    
    //Only  insert  Name 
    var t4 = db.Insertable(insertObj).InsertColumns(it => new { it.Name,it.SchoolId }).ExecuteReutrnIdentity();
    
    //Ignore TestId
    var t5 = db.Insertable(insertObj).IgnoreColumns(it => new { it.Name, it.TestId }).ExecuteReutrnIdentity();
    
    //Insert List<T>
    var s9 = db.Insertable(insertObjs).InsertColumns(it => new { it.Name }).ExecuteCommand();

[View more >>][View more 3]

4、删除方法

//by entity
    db.Deleteable<Student>().Where(new Student() { Id = 1 }).ExecuteCommand();
    
    //by primary key
    db.Deleteable<Student>().In(1).ExecuteCommand();
    
    //by primary key array
    db.Deleteable<Student>().In(new int[] { 1, 2 }).ExecuteCommand();
    
    //by expression
    db.Deleteable<Student>().Where(it => it.Id == 1).ExecuteCommand();

5、SQL 语句查询方法

var list = db.SqlQueryable<Student>("select * from student").ToPageList(1, 2);
    var list2 = db.SqlQueryable<Student>("select * from student").Where(it=>it.Id==1).ToPageList(1, 2);
    var list3= db.SqlQueryable<Student>("select * from student").Where("id=@id",new { id=1}).ToPageList(1, 2);

[View more >>][View more 4]

5、保存顺序

[View more >>][View more 5]

# Code generator #

[https://github.com/sunkaixuan/SoEasyPlatform][https_github.com_sunkaixuan_SoEasyPlatform]

# More APi 中文文档： #

[http://www.codeisbug.com/Doc/8][http_www.codeisbug.com_Doc_8]

转载：[https://gitee.com/sunkaixuan/SqlSugar][https_gitee.com_sunkaixuan_SqlSugar]

[6369224056499802674782957.jpg_id_1]: http://www.codeisbug.com/_theme/ueditor/utf8-net/net/upload/image/20190430/6369224056499802674782957.jpg?id=1
[View more]: https://github.com/sunkaixuan/SqlSugar/wiki/0.SqlSugarClient
[6369240932997363035197459.png_id_1]: http://www.codeisbug.com/_theme/ueditor/utf8-net/net/upload/image/20190502/6369240932997363035197459.png?id=1
[View more 1]: https://github.com/sunkaixuan/SqlSugar/wiki/1.Queryable
[View more 2]: https://github.com/sunkaixuan/SqlSugar/wiki/2.Updateable
[View more 3]: https://github.com/sunkaixuan/SqlSugar/wiki/3.Insertable
[View more 4]: https://github.com/sunkaixuan/SqlSugar/wiki/5.SqlQueryable
[View more 5]: https://github.com/sunkaixuan/SqlSugar/wiki/6.queue
[https_github.com_sunkaixuan_SoEasyPlatform]: https://github.com/sunkaixuan/SoEasyPlatform
[http_www.codeisbug.com_Doc_8]: http://www.codeisbug.com/Doc/8
[https_gitee.com_sunkaixuan_SqlSugar]: https://gitee.com/sunkaixuan/SqlSugar