(一)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);
}
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战