SqlSugar梳理

SqlSugar梳理

⼀、SqlSugar介绍：

sqlsugar是⼀款⾮常轻量级并且特别强⼤的ORM,⽀持常见的关系型数据库(Oracle , sqlserver , MySQL等)⼆、SqlSugar常⽤⽅法介绍：1. 基础⽅法：

public static void Show() {

SqlSugarClient sqlSugarClient = new SqlSugarClient(new ConnectionConfig() {

DbType = DbType.SqlServer,

ConnectionString = \"Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB;Persist Security Info=True;User ID=sa;Password=sa123\", IsAutoCloseConnection = true });

//获取执⾏的sql

sqlSugarClient.Aop.OnLogExecuting = (sql, pra) => {

Console.WriteLine(\"********************************************\"); Console.WriteLine($\"Sql语句：{sql}\"); };

//新增⼀条数据

sqlSugarClient.Insertable(new Commodity() {

CategoryId = 123,

ImageUrl = \"ImageUrl\", Price = 34567, ProductId = 2345, Title = \"测试数据\", Url = \"Url\"

}).ExecuteCommand();

List list = sqlSugarClient.Queryable().ToList(); Commodity commodity = sqlSugarClient.Queryable().First(); commodity.ImageUrl = commodity.ImageUrl + \"Test\";

sqlSugarClient.Updateable(commodity).ExecuteCommand(); sqlSugarClient.Deleteable(commodity).ExecuteCommand(); }

基础⽅法2：读写分离，经过测试，从库数据会有1.5秒左右的延迟

public static void Show() {

SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() {

ConnectionString = \"Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB;Persist Security Info=True;User ID=sa;Password=sa123\",//主库 DbType = DbType.SqlServer,

InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true, //从库

SlaveConnectionConfigs = new List() {

new SlaveConnectionConfig() { HitRate=10, ConnectionString=\"Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB_001;Persist Security Info=True;User ID=sa;Password=sa123\" } , new SlaveConnectionConfig() { HitRate=10, ConnectionString=\"Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB_002;Persist Security Info=True;User ID=sa;Password=sa123\" }, new SlaveConnectionConfig() { HitRate=10, ConnectionString=\"Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB_003;Persist Security Info=True;User ID=sa;Password=sa123\" } } });

//db.Aop.OnLogExecuting = (sql, pra) => //{

// Console.WriteLine(\"*********************************\"); // Console.WriteLine($\"Sql语句：{sql}\"); //};

/////新增---必然是要操作主库

//db.Insertable(new Commodity() //{

// CategoryId = 123,

// ImageUrl = \"测试数据读写分离\ // Price = 34567, // ProductId = 2345,

// Title = \"测试数据读写分离\ // Url = \"测试数据读写分离\" //}).ExecuteCommand();

//db.Insertable(new Commodity() //{

// CategoryId = 123,

// ImageUrl = \"测试数据读写分离\ // Price = 34567, // ProductId = 2345,

// Title = \"测试数据读写分离\ // Url = \"测试数据读写分离\" //}).ExecuteCommand();

///查询---应该到从库中去查询---如果证明是在从库中查询的呢？---确实是到从库中去查询的 //for (int i = 0; i < 20; i++) //{

// Commodity commodity = db.Queryable().First(c => c.Id == 10011); // Console.WriteLine(commodity.Title); //}

//中间有延迟吗？---有延迟的； 需要⼤家理解 {

db.Insertable(new Commodity()

{

CategoryId = 123,

ImageUrl = \"测试延迟的数据-1\", Price = 34567, ProductId = 2345,

Title = \"测试延迟的数据-1\", Url = \"测试延迟的数据-1\" }).ExecuteCommand(); bool isGoOn = true;

Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); while (isGoOn) {

db.Ado.IsDisableMasterSlaveSeparation = true; //可以⾛主库

Commodity commodity = db.Queryable().OrderBy(c => c.Id, OrderByType.Desc).First(); if (commodity.Title == \"测试延迟的数据-1\") {

Console.WriteLine(\"已经同步了\"); isGoOn = false; stopwatch.Stop();

Console.WriteLine($\"延迟时间：{stopwatch.ElapsedMilliseconds.ToString()}\"); } else {

Console.WriteLine(\"还没有同步\"); } } }

//主从赋值有延迟： //1.忍了

//2.还是基于主库去查询⼀下

//db.Ado.IsDisableMasterSlaveSeparation = true; //可以⾛主库 }

三、.net core项⽬中使⽤该框架1. ConfigureServices中配置如下：

public void ConfigureServices(IServiceCollection services) {

services.AddTransient();

#region SqlSugar

services.AddTransient(option => {

SqlSugarClient client = new SqlSugarClient(new ConnectionConfig() {

ConnectionString = \"Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB;Persist Security Info=True;User ID=sa;Password=sa123\", DbType = DbType.SqlServer,

InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true, //配置从库

SlaveConnectionConfigs = new List() {

new SlaveConnectionConfig() { HitRate=10, ConnectionString=\"Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB_001;Persist Security Info=True;User ID=sa;Password=sa123\" } , new SlaveConnectionConfig() { HitRate=10, ConnectionString=\"Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB_002;Persist Security Info=True;User ID=sa;Password=sa123\" }, new SlaveConnectionConfig() { HitRate=10, ConnectionString=\"Data Source=DESKTOP-T2D6ILD;Initial Catalog=SqlSugarCustomerDB_003;Persist Security Info=True;User ID=sa;Password=sa123\" }, } });

client.Aop.OnLogExecuting = (sql, par) => {

Console.WriteLine($\"Sql语句{sql}\"); };

return client;

});

#endregion

services.AddControllersWithViews(); }

2. Service层中使⽤：新建⼀个具有公共⽅法的 BaserService类

public class BaserService : IBaserService {

protected ISqlSugarClient _Client;

public BaserService(ISqlSugarClient client) {

this._Client = client; }

public bool Add(T t) where T : class, new() {

return _Client.Insertable(t).ExecuteCommand() > 0; }

public bool Delete(T t) where T : class, new() {

return _Client.Deleteable(t).ExecuteCommand() > 0; }

public List Query() where T : class, new() {

return _Client.Queryable().ToList(); }

public bool Update(T t) where T : class, new() {

return _Client.Updateable(t).ExecuteCommand() > 0; } }

3 实现

public interface IBaserService {

public bool Add(T t) where T : class, new(); public List Query() where T : class, new(); public bool Update(T t) where T : class, new(); public bool Delete(T t) where T : class, new(); }

4. 其他service中的类需要继承上⾯的公共⽅法

public class CommodityService : BaserService, ICommodityService {

public CommodityService(ISqlSugarClient client) : base(client) {

}

　　　　　//这个是分页的⽅法

public List PageCommodityList(out int totalCount, string searchString1, string searchString2, int pageIndex = 1, int pageSize = 10) {

//Expressionable expressionable = Expressionable.Create();

//expressionable = expressionable.AndIF(!string.IsNullOrWhiteSpace(searchString1), c => c.Title.Contains(searchString1)); //expressionable = expressionable.AndIF(!string.IsNullOrWhiteSpace(searchString2), c => c.Title.Contains(searchString2)); //Expression> expression = expressionable.ToExpression(); int totalNum = 0;

//List list = _Client.Queryable().Where(expression) List list = _Client.Queryable()

.WhereIF(!string.IsNullOrWhiteSpace(searchString1), c => c.Title.Contains(searchString1)) .WhereIF(!string.IsNullOrWhiteSpace(searchString2), c => c.Title.Contains(searchString2)) .OrderBy(c => c.Id, OrderByType.Asc)

.ToPageList(pageIndex, pageSize, ref totalNum); totalCount = totalNum; return list; } }

5. 接⼝：

public interface ICommodityService: IBaserService {

//public void Add(); //public void Query();

//public void Update();

//public void Delete();

public List PageCommodityList(out int totalCount,string searchString1, string searchString2, int pageIndex = 1, int pageSize = 10); }

更新完成！谢谢学习，共同进步