mysql sqlsugar_sqlSugar的使用---入门

一，新建.net core  web项目

二.  项目引入包:sqlSugarCore

三.创建两个表:user,   department

四. 新建model(不一定需要与table相同，使用[SugarTable("tableName")]进行model与table的绑定)

namespaceSqlSugarTest01.Models

{

[SugarTable("user")]public classUserModel

{

[SugarColumn(IsPrimaryKey=true,IsIdentity =true)]//如果是主键，此处必须指定，否则会引发InSingle(id)方法异常。

public int id { get; set; }public string userName { get; set; }public string userPassword { get; set; }public int age { get; set; }public DateTime regTime { get; set; }public int departmentId { get; set; }

}

}

5.编写代码进行数据库操作，此处为了简便，我书写在HomeController中，开辟了一个方法区，在Index的return view()之前调用，以便进行调试。

(1)数据库连接以及监听

//创建连接实体对象

SqlSugarClient db = newSqlSugarClient(newConnectionConfig()

{

ConnectionString= "server=localhost;port=3307;uid=root;pwd=root;database=testsqlsugar",

DbType= DbType.MySql,//设置数据库类型

IsAutoCloseConnection = true,//自动释放数据库，如果存在事务，在事务结束之后释放。

InitKeyType = InitKeyType.Attribute//从实体特性中读取主键自增列信息

});//aop监听sql，此段会在每一个"操作语句"执行时都进入....eg:getbyWhere这里会执行两次

db.Aop.OnLogExecuting = (sql, pars) =>{string sqlStempt = sql + "参数值：" + db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it =>it.Value));

};

(2)单表查询

/*查询---单表*/

var list = db.Queryable().ToList();//查询所有

var getById = db.Queryable().InSingle(1);//根据主键查询(model中必须同步标识主键[SugarColumn(IsPrimaryKey =true,IsIdentity =true)])

var total = 0;var getbyWhere = db.Queryable().Where(it => it.age == 18).ToPageList(1, 2, ref total);//根据条件查询/分页.

(3)双表查询

/*查询---多表*/

var list0 = db.Queryable((user, dep) => new object[] {

JoinType.Left,user.departmentId== dep.id}).Select((user, dep) => new { Id = user.id, depId = user.departmentId, name =user.userName }).ToList();//生成的sql: SELECT `user`.`id` AS `Id` , `user`.`departmentId` AS `depId` , `user`.`userName` AS `name` FROM `user` user Left JOIN `department` dep ON( `user`.`departmentId` = `dep`.`id` )//可用预先定义好的dto类自动填充select结果集，自动填充会比较依赖字段取名规则(不指定对应关系)

List list1 = db.Queryable((user, dep) => new object[] { JoinType.Left, user.departmentId == dep.id }).Select().ToList();//可用预先定义好的dto类填充select结果集(指定对应关系)

List list2 = db.Queryable((user, dep) => new object[] { JoinType.Left, user.departmentId == dep.id }).Select((user, dep) =>

new userDepDto { Id = dep.id, userName = user.userName, regTime = user.regTime, name = dep.name }).ToList();

(4)多表查询分页

//多表查询分页

var list4 = db.Queryable((user, dep) => new object[] { JoinType.Left, user.departmentId == dep.id }).Select((user, dep) => new userDepDto { Id =user.id, userName= user.userName, regTime = user.regTime, name = dep.name }).ToPageList(2, 2);

(5)三表查询

//三表查询，返回完整的三表对象。

var list3 = db.Queryable((user, dep, userinfo) => new object[] { JoinType.Left, user.departmentId == dep.id, JoinType.Left, user.id ==userinfo.UserId }).Where((user, dep, userinfo)=> user.age == 18 || dep.id == 1 || userinfo.Sex == "男")

.OrderBy((user)=> user.id).OrderBy((user, dep) => dep.id, OrderByType.Desc).Select((user, dep, userInfo) => new { user = user, dep = dep, userInfo = userInfo }).ToList();

(6)两个Queryable的join

//两个Queryable的join

var q1 = db.Queryable((user, dep) => new object[] { JoinType.Left, user.departmentId == dep.id }).Select((user,dep)=>new userDepDto()

{ Id=user.id,userName=user.userName,regTime=user.regTime,name=dep.name});//在此处，sql语句还没真正执行，aop也监听不到

var q2 = db.Queryable();//inner join

var innerJoinList = db.Queryable(q1, q2, (j1, j2) => j1.Id == j2.id).Select((j1, j2) => j1).ToList();//此处sql才真正执行//left join

var leftJoinList = db.Queryable(q1, q2, JoinType.Left, (j1, j2) => j1.Id == j2.id).Select((j1,j2)=>j1).ToList();

(7)多表简化查询(当我们不需要left join和right join时，使用inner join可以简写查询语句)

//简化查询 双表查询

var list5 = db.Queryable((user, dep) => user.departmentId == dep.id).Select((user, dep) => new{ user.id, user.userName, dep.name }).ToList();//三表查询

var list6 = db.Queryable((user, dep, userinfo) => user.departmentId == dep.id && user.id == userinfo.UserId).Select((user, dep, userinfo) => new { user = user, dep = dep, userinfo =userinfo }).ToList();//3表分页查询

var list7 = db.Queryable((user, dep, userinfo) => user.departmentId == dep.id && user.id == userinfo.UserId).Select((user, dep, userinfo) => new { user.id, user.userName, user.age, dep.name, userinfo.Sex }).ToPageList(1,2);