(一)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<string, SugarParameter[]>(sql, pars);
};
return sqlSugarClient;
}

}

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

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

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

}
}
}

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

}
}
}
//批量删除
public bool DeleteRange(IEnumerable<TEntity> ts)
{
using (SqlSugarClient client = _sqlsugarContext.OpenConnection())
{
try
{
client.BeginTran();
int result = client .Deleteable<TEntity>(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<T1, T3, T4>((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;

sqlsugar在Queryable得时候采用得延迟加载,直接sql赛选和where后面赛选查询条件差不多,只有转tolist或者.count()得时候才会往返数据库真正从数据库获取数据

//条件加载where得labdb后面

string sql = "select T3.id,T3.name1 from T1 join T3 on T1.id=T3.t1id";
List<vtt> datas = client.SqlQueryable<vtt>(sql).Where(m => 1 == 1).OrderBy(m => m.name1, SqlSugar.OrderByType.Desc).Select(m => new vtt { t1id = m.t1id }).ToPageList(1, 10, ref totalnum, ref totalPage);

//直接在sql里面加条件查询

string sql1 = "select T3.id,T3.name1 from T1 join T3 on T1.id=T3.t1id where T3.name1=@name1";

List<vtt> datas1 = client.SqlQueryable<vtt>(sql1).AddParameters(new { name1 = "1122",ss="" }).Where(m => 1 == 1).OrderBy(m => m.name1, SqlSugar.OrderByType.Desc).Select(m => new vtt { t1id = m.t1id }).ToPageList(1, 10, ref totalnum, ref totalPage);

//按name1分组求t1id相加得和,name1 必须包含在分组GroupBy后面,不然不能查询呢个字段

string sql = "select * from T3";
List<vtt> datas = client.SqlQueryable<vtt>(sql).Select(m => new vtt { t1id = SqlFunc.AggregateSum(m.t1id), name1 = m.name1 }).GroupBy(m=>m.name1).ToPageList(1, 10, ref totalnum, ref totalPage);

}

posted @   yingxianqi  阅读(1225)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示