(一)SqlSugar(在.net core中)

 支持复杂得sql查询：(SimpleClient以面向对象的思维对单个对象进行增，删，查，改得基础操作)，（SqlSugarClient对复杂得sql查询，事务操作，如批量操作加事务）

1:在nuget里面下载SqlSugarCore.dll包

2：链接类

public class SqlsugarContext
{

//链接字符串
string conn = "Data Source=PC-202103271034\\SQLEXPRESS;Initial Catalog=dmscqdb;Persist Security Info=True;User ID=sa;Password=123456;Connect Timeout=500;";

//获取操作数据库得链接

public SqlSugarClient OpenConnection()
{
SqlSugarClient sqlSugarClient= new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = conn,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.SystemTable
});
sqlSugarClient.Aop.OnExecutingChangeSql = (sql, pars) =>
{
Console.WriteLine(sql);
return new KeyValuePair(sql, pars);
};
return sqlSugarClient;
}

3：对单表的基础操作(做成一个泛型类,方便继承)

public class SqlSugarDbGet where TEntity : class, new()
{

public SqlsugarContext _sqlsugarContext;
public SqlSugarDbGet(SqlsugarContext sqlsugarContext)
{
_sqlsugarContext = sqlsugarContext;
}
public static SimpleClient Getinstance => new SimpleClient(HttpContext.GetService().DB);
//批量添加
public bool AddRange(IEnumerable ts)
{
using (SqlSugarClient client = _sqlsugarContext.OpenConnection())
{
try
{
client.BeginTran();
int result = client.Insertable(ts).ExecuteCommand();
client.CommitTran();
return true;
}
catch (Exception e)
{
string msg = e.Message;
client.RollbackTran();
return false;

}
}
}

//批量修改
public bool UpdateRange(IEnumerable ts)
{
using (SqlSugarClient client = _sqlsugarContext.OpenConnection())
{
try
{
client.BeginTran();
int result = _sqlsugarContext.DB.Updateable(ts).ExecuteCommand();
client.CommitTran();
return true;
}
catch (Exception e)
{
string msg = e.Message;
client.RollbackTran();
return false;

}
}
}
//批量删除
public bool DeleteRange(IEnumerable ts)
{
using (SqlSugarClient client = _sqlsugarContext.OpenConnection())
{
try
{
client.BeginTran();
int result = _sqlsugarContext.DB.Deleteable(ts).ExecuteCommand();
client.CommitTran();
return true;
}
catch (Exception e)
{
string msg = e.Message;
client.RollbackTran();
return false;

}
}
}

4：对复杂表的查询

//以对象的方式进行查询

using (SqlSugarClient client = _sqlsugarContext.OpenConnection())
{
var dss = client.Queryable((a, b, c) => new JoinQueryInfos(JoinType.Inner, a.id == b.t1id, JoinType.Inner, b.id == c.t3id)).Where(a => a.name == "666").Select((a, b, c) => new vtt()
{
id = a.id,
name1 = c.name4

}).ToList();
}

//以sql的方式进行查询(呢种要用的多些，方便些)
using (SqlSugarClient client = _sqlsugarContext.OpenConnection())
{
int totalnum = 0;
int totalPage = 0;
string sql = "select T3.id,T3.name1 from T1 join T3 on T1.id=T3.t1id";
List datas = client.SqlQueryable(sql).Where(m => 1 == 1).OrderBy(m => m.name1, SqlSugar.OrderByType.Desc).Select(m => new vtt { name1 = SqlFunc.AggregateSum(SqlFunc.ToString(m.name1)) }).ToPageList(1, 10, ref totalnum, ref totalPage);
}