sqlsugar帮助文档
官网
本文将给出如何使用sqlsugar连接pgsql并实现多租户，具体帮助文档请查看官网
- 安装
SqlSugarCore 5.1.4.169 5.1.4.169
- 定义一对一，一对多model
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace SqlSugarHelperDLL.Models
{//实体//[Tenant("sqlsugar_a")][SugarTable("student")]public class Student{[SugarColumn(IsPrimaryKey = true, IsIdentity = true,ColumnName ="student_id")]public long StudentId { get; set; }[SugarColumn(ColumnName = "name")]public string Name { get; set; }[SugarColumn(ColumnName = "school_id")]public long SchoolId { get; set; }//标准配置 推荐[Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是StudentA类里面的public School School { get; set; } //不能赋值只能是null[Navigate(NavigateType.OneToMany, nameof(Book.studenId))]//BookA表中的studenIdpublic List<Book> Books { get; set; }//注意禁止给books手动赋值//非主键模式 需要手动赋值防止插入0这种默认值//[Navigate(NavigateType.OneToOne, nameof(SchoolId),nameof(SchoolA.Id))] }//[Tenant("sqlsugar_b")][SugarTable(TableName = "school")]public class School{[SugarColumn(IsPrimaryKey = true, IsIdentity = true,ColumnName ="school_id")]public long Id { get; set; }[SugarColumn(ColumnName ="school_name")]public string SchoolName { get; set; }}[SugarTable(TableName = "book")]public class Book{[SugarColumn(IsPrimaryKey = true, IsIdentity = true,ColumnName ="book_id")]public long BookId { get; set; }[SugarColumn(ColumnName ="name")]public string Name { get; set; }public long studenId { get; set; }}
}- 定义helper类
using SqlSugar;
using SqlSugarHelperDLL.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace SqlSugarHelperDLL
{public class SqlsugarHelper{private readonly ISqlSugarClient _sqlSugarClient;public SqlsugarHelper(ISqlSugarClient sqlSugarClient){_sqlSugarClient = sqlSugarClient;//_sqlSugarClient.MappingTables.Add("studenta", "sqlsugarA.studenta");}// 初始化数据表public void InitTable(){var DB = _sqlSugarClient.AsTenant().GetConnection("sqlsugar_a");Console.WriteLine(_sqlSugarClient.CurrentConnectionConfig.ConnectionString); DB.CodeFirst.InitTables(typeof(Student)); // Supported by all databases DB.CodeFirst.InitTables(typeof(School));DB.CodeFirst.InitTables(typeof(Book));DB = _sqlSugarClient.AsTenant().GetConnection("sqlsugar_b");DB.CodeFirst.InitTables(typeof(Student)); // Supported by all databases DB.CodeFirst.InitTables(typeof(School));DB.CodeFirst.InitTables(typeof(Book));}//https://www.donet5.com/Home/Doc?typeId=2430public async void Insert(){List<Student> list = new List<Student>();list.Add(new Student(){Name="ellis",School = new School() { SchoolName = "辽工大"},Books = new List<Book>() { new Book() { Name = "Python"},new Book() { Name = "C#"}, new Book() { Name = ".net Core"} }});var DB = _sqlSugarClient.AsTenant().GetConnection("sqlsugar_a");DB.InsertNav(list).Include(x=>x.School).Include(it => it.Books, new InsertNavOptions(){ OneToManyIfExistsNoInsert = true }).ExecuteCommandAsync();DB = _sqlSugarClient.AsTenant().GetConnection("sqlsugar_b");DB.InsertNav(list).Include(x => x.School).Include(it => it.Books, new InsertNavOptions(){ OneToManyIfExistsNoInsert = true }).ExecuteCommandAsync();}public async Task<Student> Query(int id,string schema){var DB = _sqlSugarClient.AsTenant().GetConnection(schema);return await DB.Queryable<Student>().Includes(x => x.Books).Includes(x => x.School).Where(x => x.StudentId == id).FirstAsync();}//https://github.com/DotNetNext/SqlSugar/wiki/2.2-Dynamic-Updatepublic async Task<int> UpdateByDictionary(int id, string schema,string name){var dt = new Dictionary<string, object>();dt.Add("student_id", id);dt.Add("name", name);var DB = _sqlSugarClient.AsTenant().GetConnection(schema);return await DB.Updateable(dt).AS("student").WhereColumns("student_id").ExecuteCommandAsync();}// 级联更新//https://www.donet5.com/Home/Doc?typeId=2432public async Task<bool> Update(int id, string schema){List<Student> list = new List<Student>();list.Add(new Student(){StudentId = id,Name = "ellis",School = new School() { SchoolName = "lgd",Id=1 }});var DB = _sqlSugarClient.AsTenant().GetConnection(schema);return await DB.UpdateNav(list,new UpdateNavRootOptions() { IgnoreColumns = new string[] {"Name"},//忽略的更新属性IsInsertRoot = true, // 主表不存在就插入IsDisableUpdateRoot = true, //主表不更新IsIgnoreAllNullColumns = true//主表null列不更新}).Include(x => x.School).ExecuteCommandAsync();}// 根据主键删除//https://github.com/DotNetNext/SqlSugar/wiki/4.1-Deletepublic async Task<int> Delete(int id,string schema){var DB = _sqlSugarClient.AsTenant().GetConnection(schema);//by entityreturn await DB.Deleteable<Student>().Where(new Student() { StudentId=id }).ExecuteCommandAsync();//by primary keyDB.Deleteable<Student>().In(id).ExecuteCommand();//by primary key arrayDB.Deleteable<Student>().In(new int[] { 1, 2 }).ExecuteCommand();//by expressionDB.Deleteable<Student>().Where(it => it.StudentId == 1).ExecuteCommand();//delete by SubqueryableDB.Deleteable<Student>().Where(p => p.SchoolId == SqlFunc.Subqueryable<School>().Where(s => s.Id == p.SchoolId).Select(s => s.Id)).ExecuteCommand();}//级联删除public async Task<bool> DeleteByExpression(int id,string schema){var DB = _sqlSugarClient.AsTenant().GetConnection(schema);return await DB.DeleteNav<Student>(stu => stu.StudentId == id).Include(stu => stu.School).Include(stu => stu.Books).ExecuteCommandAsync();}// 分页查询//https://www.donet5.com/Home/Doc?typeId=2242public async Task<List<Student>> PageQuery(string schema,int pagesize,int pagenum){List<OrderByModel> orderList = OrderByModel.Create(new OrderByModel() { FieldName = "student_id", OrderByType = OrderByType.Desc },new OrderByModel() { FieldName = "name" } //默认防注入：并且可以用StaticConfig.Check_FieldFunc重写验证机质);RefAsync<int> totalnumber = 0;var DB = _sqlSugarClient.AsTenant().GetConnection(schema);return await DB.Queryable<Student>().OrderBy(orderList).Includes(x=>x.School).Includes(x=>x.Books).ToPageListAsync(pagenum, pagesize, totalnumber);}//开窗//https://www.donet5.com/Home/Doc?typeId=2243public async Task<List<Student>> OverFunc(string schema){var DB = _sqlSugarClient.AsTenant().GetConnection(schema);// 查询符合条件的学生数据var studentList = await DB.Queryable<Student>().Select(it => new{it.StudentId,it.Name,it.SchoolId,RowNum = SqlFunc.RowNumber(it.StudentId,it.Name)// 使用开窗函数//order by id partition by name//多字段排序 order by id asc ,name desc//SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc ",$"{it.Name}")}).MergeTable() // 将子查询的结果合并成一个表.Where(it => it.RowNum == 1) // 只选择 RowNumber 为 1 的记录.Select(it => new Student{StudentId = it.StudentId,Name = it.Name,SchoolId = it.SchoolId}).ToListAsync();// 为每个学生手动加载关联的 School 和 Books 数据foreach (var student in studentList){// 加载关联的 Schoolstudent.School = await DB.Queryable<School>().Where(s => s.Id == student.SchoolId).FirstAsync();// 加载关联的 Books 列表student.Books = await DB.Queryable<Book>().Where(b => b.studenId == student.StudentId).ToListAsync();}return studentList;}}
}- DI
重要的是需要设置SearchPath，ConfigId以及DbLinkName，其中ConfigId以及DbLinkName需要一致，数据库的schema不能有大写
builder.Services.AddScoped<ISqlSugarClient>(opt => new SqlSugarClient(new List<ConnectionConfig>()
{new ConnectionConfig {ConfigId="sqlsugar_a",DbType= DbType.PostgreSQL,ConnectionString="Host=192.168.214.133;Port=32222;Database=postgresdb;Username=postgresadmin;Password=admin123;SearchPath=sqlsugar_a",DbLinkName="sqlsugar_a",InitKeyType = InitKeyType.Attribute, // 从特性初始化表IsAutoCloseConnection = true, // 自动关闭连接//MoreSettings = new ConnMoreSettings//{// PgSqlIsAutoToLower = false // 保持表名和列名大小写不变//}},new ConnectionConfig {ConfigId="sqlsugar_b",DbType= DbType.PostgreSQL,ConnectionString="Host=192.168.214.133;Port=32222;Database=postgresdb;Username=postgresadmin;Password=admin123;SearchPath=sqlsugar_b",DbLinkName="sqlsugar_b",InitKeyType = InitKeyType.Attribute, // 从特性初始化表IsAutoCloseConnection = true, // 自动关闭连接//MoreSettings = new ConnMoreSettings//{// PgSqlIsAutoToLower = false // 保持表名和列名大小写不变//}}}
}));builder.Services.AddScoped<SqlsugarHelper>();- controller
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using SqlSugarHelperDLL;
using SqlSugarHelperDLL.Models;namespace SqlSugarAPI.Controllers
{[Route("api/[controller]/[action]")][ApiController]public class SqlSugarController : ControllerBase{private readonly SqlsugarHelper _sqlsugarHelper;public SqlSugarController(SqlsugarHelper sqlsugarHelper){_sqlsugarHelper = sqlsugarHelper;}[HttpGet]public IActionResult InitDB(){_sqlsugarHelper.InitTable();return Ok("suc");}[HttpPost]public IActionResult Insert(){_sqlsugarHelper.Insert();return Ok("suc");}[HttpGet]public async Task<Student> Query([FromQuery] int id,[FromQuery] string schema){return await _sqlsugarHelper.Query(id, schema);}[HttpPut]public async Task<IActionResult> UpdateByDic([FromQuery] int id, [FromQuery] string schema,[FromQuery] string name){int count = await _sqlsugarHelper.UpdateByDictionary(id, schema,name);return Ok(count);}[HttpPut]public async Task<IActionResult> Update([FromQuery] int id, [FromQuery] string schema){bool count = await _sqlsugarHelper.Update(id, schema);return Ok(count);}[HttpDelete]public async Task<IActionResult> Delete(int id, string schema){var count = await _sqlsugarHelper.Delete(id, schema);return Ok(count);}[HttpDelete]public async Task<IActionResult> DeleteByExpression(int id, string schema){var count = await _sqlsugarHelper.DeleteByExpression(id, schema);return Ok(count);}}
}
