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<Commodity>(new Commodity()
            {
                CategoryId = 123,
                ImageUrl = "ImageUrl",
                Price = 34567,
                ProductId = 2345,
                Title = "测试数据",
                Url = "Url"
            }).ExecuteCommand();

            List<Commodity> list = sqlSugarClient.Queryable<Commodity>().ToList(); 
            Commodity commodity = sqlSugarClient.Queryable<Commodity>().First();
            commodity.ImageUrl = commodity.ImageUrl + "Test";
            sqlSugarClient.Updateable<Commodity>(commodity).ExecuteCommand(); 
            sqlSugarClient.Deleteable<Commodity>(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<SlaveConnectionConfig>() {
                     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<Commodity>(new Commodity()
            //{
            //    CategoryId = 123,
            //    ImageUrl = "测试数据读写分离",
            //    Price = 34567,
            //    ProductId = 2345,
            //    Title = "测试数据读写分离",
            //    Url = "测试数据读写分离"
            //}).ExecuteCommand();

            //db.Insertable<Commodity>(new Commodity()
            //{
            //    CategoryId = 123,
            //    ImageUrl = "测试数据读写分离",
            //    Price = 34567,
            //    ProductId = 2345,
            //    Title = "测试数据读写分离",
            //    Url = "测试数据读写分离"
            //}).ExecuteCommand();

            ///查询---应该到从库中去查询---如果证明是在从库中查询的呢？---确实是到从库中去查询的 
            //for (int i = 0; i < 20; i++)
            //{
            //    Commodity commodity = db.Queryable<Commodity>().First(c => c.Id == 10011);
            //    Console.WriteLine(commodity.Title);
            //}

            //中间有延迟吗？---有延迟的； 需要大家理解
            {
                db.Insertable<Commodity>(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<Commodity>().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<ICommodityService, CommodityService>();

            #region SqlSugar
            
            services.AddTransient<ISqlSugarClient>(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<SlaveConnectionConfig>() {
                     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 t) where T : class, new()
        {
            return _Client.Insertable<T>(t).ExecuteCommand() > 0;
        }

        public bool Delete<T>(T t) where T : class, new()
        {
            return _Client.Deleteable<T>(t).ExecuteCommand() > 0;
        }

        public List<T> Query<T>() where T : class, new()
        {
            return _Client.Queryable<T>().ToList();
        }

        public bool Update<T>(T t) where T : class, new()
        {
            return _Client.Updateable<T>(t).ExecuteCommand() > 0;
        }
    }

3 实现

 public interface IBaserService
    {
        public bool Add<T>(T t) where T : class, new();

        public List<T> Query<T>() where T : class, new();

        public bool Update<T>(T t) where T : class, new();

        public bool Delete<T>(T t) where T : class, new();

    }

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

public class CommodityService : BaserService, ICommodityService
    {
        public CommodityService(ISqlSugarClient client) : base(client)
        {

        }
　　　　　//这个是分页的方法
        public List<Commodity> PageCommodityList(out int totalCount, string searchString1, string searchString2, int pageIndex = 1, int pageSize = 10)
        {
             
            //Expressionable<Commodity> expressionable = Expressionable.Create<Commodity>();
            //expressionable = expressionable.AndIF(!string.IsNullOrWhiteSpace(searchString1), c => c.Title.Contains(searchString1));
            //expressionable = expressionable.AndIF(!string.IsNullOrWhiteSpace(searchString2), c => c.Title.Contains(searchString2));
            //Expression<Func<Commodity, bool>> expression = expressionable.ToExpression();

            int totalNum = 0;
            //List<Commodity> list = _Client.Queryable<Commodity>().Where(expression)
            List<Commodity> list = _Client.Queryable<Commodity>()
                .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<Commodity> PageCommodityList(out int totalCount,string searchString1, string searchString2, int pageIndex = 1, int pageSize = 10);
    }

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