.Net Core中使用Freesql

之前写过一篇在.net framework中使用freesql的文章，这篇文章讲解在.net core中使用，演示环境为.net 2.1

 

一、Startup中启用和注入：

    public class Startup
    {
        public IFreeSql FrSql { get; private set; }

        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;

            //标准连接字符串
            string baseStrConn = "Data Source=.;Initial Catalog=数据库名称;Pooling=true;Min Pool Size=1;uid=sa;pwd=sa密码;";
            //构建freesql对象
            this.FrSql = new FreeSqlBuilder().UseConnectionString(DataType.SqlServer, baseStrConn).UseAutoSyncStructure(true).Build();
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<CookiePolicyOptions>(options =>
            {
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });

            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

            //注入freesql
            services.AddSingleton(this.FrSql);
            //注入数据库工具类
            services.AddSingleton<ILingbugFreeSqlUtil, LingbugFreeSqlUtil>();
        }

        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseStaticFiles();
            app.UseCookiePolicy();

            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=TestHdeviceIndex}/{id?}");
            });
        }
    }

 

二、工具类（主要讲解事务和指定列修改）：

    public class LingbugFreeSqlUtil : ILingbugFreeSqlUtil
    {
        private IFreeSql _dbContext;

        public LingbugFreeSqlUtil(IFreeSql frSql)
        {
            //依赖注入
            this._dbContext = frSql;
        }

        public void RunWithTransaction(IsolationLevel? transactionLevel, Action<DbTransaction> action)
        {
            using (var uow = _dbContext.CreateUnitOfWork())
            {
                try
                {
                    //指定事务级别
                    if (transactionLevel.HasValue) uow.IsolationLevel = transactionLevel;
                    //开启事务
                    var tran = uow.GetOrBeginTransaction();
                    //记录日志
                    LingbugService.Log("RunWithTransaction - 事务", tran.ToJsonString());

                    //执行逻辑
                    action(tran);

                    //提交事务
                    uow.Commit();
                }
                catch (Exception ex)
                {
                    //回滚事务
                    uow.Rollback();
                    //抛出异常
                    throw;
                }
            }
        }

        public IUpdate<T> Update<T>(T data, DbTransaction transaction) where T : BaseDbModel
        {
            /*
             * 修改注意点：
             * 1.设置实体使用SetSource方法，且实体的主键字段必须赋值，否则无法where到
             * 2.不会修改主键字段，不用担心
             */

            //修改对象
            var up = _dbContext.Update<T>().SetSource(data).IgnoreColumns(data.GetUnChanged());
            //设置事务
            if (transaction != null) up = up.WithTransaction(transaction);
            //返回
            return up;
        }
    }

 

三、使用：

    public class HomeController : Controller
    {
        private IFreeSql _dbContext;

        private ILingbugFreeSqlUtil _freesqlUtil;

        public HomeController(IFreeSql frSql, ILingbugFreeSqlUtil freesqlUtil)
        {
            //依赖注入
            this._dbContext = frSql;
            this._freesqlUtil = freesqlUtil;
        }

        #region 自带

        public IActionResult Index()
        {
            //try
            //{
            //    //string pwd = Encrypt.MD5Encrypt("zwh123");
            //    //LogManager.Info(pwd);
            //    //var source = Encoding.GetEncoding("gb2312");
            //    //var target = Encoding.UTF8;
            //    //var bytes = source.GetBytes(pwd);
            //    //var newBytes = Encoding.Convert(source, target, bytes);
            //    //string newPwd = target.GetString(newBytes);
            //    //string sql = @"SELECT * FROM dbo.LingbugTaskInfo";
            //    //var dt = _FrSql.Ado.ExecuteDataTable(sql);
            //    //string json = JsonConvert.SerializeObject(dt);
            //    //var list = JsonConvert.DeserializeObject<List<TaskModel>>(json);
            //    //var taskList = _FrSql.Ado.Query<TaskModel>(sql);
            //    //var assembly = Assembly.GetAssembly(typeof(CacheHelper));
            //    //var types = assembly.GetTypes();
            //    LogManager.LogDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Logs");
            //    //LogManager.Info(JsonConvert.SerializeObject(types.Select(r => r.Name).Distinct().OrderBy(r => r)));
            //    //string flag = "Success";
            //}
            //catch (Exception ex)
            //{
            //    LogManager.Error(ex);
            //}
            return View();
        }

        public IActionResult About()
        {
            ViewData["Message"] = "Your application description page.";

            return View();
        }

        public IActionResult Contact()
        {
            ViewData["Message"] = "Your contact page.";

            return View();
        }

        public IActionResult Privacy()
        {
            return View();
        }

        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }

        public IActionResult TestHdeviceIndex()
        {
            return View();
        }

        #endregion

        private void Aop<T>(object sender, T e, string methodName)
        {
            //需要记录日志的属性名
            var procNames = "Log，ElapsedTicks，ElapsedMilliseconds，Command，ExecuteResult，Sql".Split('，');
            //类型
            var t = e.GetType();
            //属性集合
            var procList = t.GetProperties();
            //找到属性
            var logProc = procList.FirstOrDefault(r => r.Name == "Log");
            if (logProc == null)
            {
                //返回
                return;
            }
            //数据
            var data = new JObject();
            //事件名
            data.Add("EventName", JToken.FromObject(methodName));
            //日志
            data.Add("Log", JToken.FromObject(logProc.GetValue(e)));
            参数类型
            //data.Add("ParameterType", JToken.FromObject($"{t.Name}（{t.FullName}）"));
            属性名
            //data.Add("ProcList", JToken.FromObject(procList.Select(r => r.Name)));
            //procNames.ForEach(name =>
            //{
            //    //找到属性
            //    var proc = procList.FirstOrDefault(r => r.Name == name);
            //    if (proc != null)
            //    {
            //        //读取数据
            //        var value = proc.GetValue(e);
            //        //添加到实体中
            //        data.Add(name, JToken.FromObject(value));
            //    }
            //});
            //记录日志
            LingbugService.Log(methodName, data.ToString());
            //LogHelper.Debug(data.ToString());
            //LogHelper.Debug($"{methodName} - {nameof(e)}：type = {t.Name}（{t.FullName}），属性：{procList.Select(r => $"{r.Name}【{r.PropertyType.Name}】").Join()}");
        }

        public async Task<string> TestFreeSql()
        {
            try
            {
                #region Aop

                //过滤器
                _dbContext.Aop.CommandBefore += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "CommandBefore");
                };
                _dbContext.Aop.CommandAfter += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "CommandAfter");
                };
                _dbContext.Aop.AuditValue += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "AuditValue");
                };
                _dbContext.Aop.SyncStructureBefore += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "SyncStructureBefore");
                };
                _dbContext.Aop.SyncStructureAfter += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "SyncStructureAfter");
                };
                _dbContext.Aop.CurdBefore += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "CurdBefore");
                };
                _dbContext.Aop.CurdAfter += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "CurdAfter");
                };
                _dbContext.Aop.TraceBefore += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "TraceBefore");
                };
                _dbContext.Aop.TraceAfter += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "TraceAfter");
                };
                _dbContext.Aop.ConfigEntityProperty += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "ConfigEntityProperty");
                };
                _dbContext.Aop.ConfigEntity += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "ConfigEntity");
                };
                _dbContext.Aop.ParseExpression += (sender, e) =>
                {
                    //记录日志
                    Aop(sender, e, "ParseExpression");
                };


                #endregion

                查询
                //var list = _dbContext.Select<WorkLogInfo>().ToList();
                查询
                //var dt = _dbContext.Ado.ExecuteDataTable(@"SELECT * FROM dbo.WorkLogInfo a ORDER BY a.CreateDate DESC");
                记录日志
                //LogHelper.Debug(dt.ToJsonString());

                新增
                //var newId = _dbContext.Insert(new WorkLogInfo()
                //{
                //    Content = "店铺国家校验非空",
                //    LogType = 1,
                //    CreateDate = DateTime.Now,
                //    IsDel = false
                //}).ExecuteIdentity();

                新增产品
                //var productList = await _dbContext.Insert("西装，手表，电脑".Split('，').Select(r => new ProductInfo()
                //{
                //    Name = r,
                //    OperateDate = DateTime.Now
                //})).ExecuteInsertedAsync();

                新增评论
                //var reviewCount = await _dbContext.Insert(productList.SelectMany(p =>
                //{
                //    return Enumerable.Range(1, 100).Select(r => new ReviewInfo()
                //    {
                //        ProductId = p.Id,
                //        Review = $"这个{p.Name}太棒了 - {r}",
                //        OperateDate = DateTime.Now
                //    }).ToList();
                //})).ExecuteInsertedAsync();

                查询
                //var list = _dbContext.Select<ProductInfo>().IncludeMany(r => r.ReviewList).ToList();
                记录日志
                //LingbugService.Log(null, list.ToJsonString());

                using (var uow = _dbContext.CreateUnitOfWork())
                {
                    try
                    {
                        //指定事务级别
                        uow.IsolationLevel = IsolationLevel.Serializable;
                        //事务
                        var tran = uow.GetOrBeginTransaction();
                        //记录日志
                        LingbugService.Log("事务", tran.ToJsonString());
                        //查询
                        var list = _dbContext.Select<ProductInfo>().WithTransaction(tran).ToList();
                        //休息
                        Thread.Sleep(30 * 1000);
                        //新增
                        _dbContext.Insert(new ProductInfo()
                        {
                            Name = "裤子456",
                            OperateDate = DateTime.Now
                        }).WithTransaction(tran).ExecuteAffrows();

                        //新增
                        _dbContext.Insert(new ProductInfo()
                        {
                            Name = "皮带456",
                            OperateDate = DateTime.Now
                        }).WithTransaction(tran).ExecuteAffrows();

                        //提交事务
                        uow.Commit();
                    }
                    catch (Exception ex)
                    {
                        //回滚事务
                        uow.Rollback();
                        //抛出异常
                        throw;
                    }
                }

                //返回
                return new
                {
                    IsSuccess = true,
                    Message = "ok"
                }.ToJsonString();
            }
            catch (Exception ex)
            {
                //返回
                return new
                {
                    IsSuccess = false,
                    Message = ex.Message,
                    Exception = ex
                }.ToJsonString();
            }
        }

        public async Task<string> TestFreeSqlNew()
        {
            try
            {
                查询
                //var list = _dbContext.Select<WorkLogInfo>().ToList();
                查询
                //var dt = _dbContext.Ado.ExecuteDataTable(@"SELECT * FROM dbo.WorkLogInfo a ORDER BY a.CreateDate DESC");
                记录日志
                //LogHelper.Debug(dt.ToJsonString());

                新增
                //var newId = _dbContext.Insert(new WorkLogInfo()
                //{
                //    Content = "店铺国家校验非空",
                //    LogType = 1,
                //    CreateDate = DateTime.Now,
                //    IsDel = false
                //}).ExecuteIdentity();

                新增产品
                //var productList = await _dbContext.Insert("西装，手表，电脑".Split('，').Select(r => new ProductInfo()
                //{
                //    Name = r,
                //    OperateDate = DateTime.Now
                //})).ExecuteInsertedAsync();

                新增评论
                //var reviewCount = await _dbContext.Insert(productList.SelectMany(p =>
                //{
                //    return Enumerable.Range(1, 100).Select(r => new ReviewInfo()
                //    {
                //        ProductId = p.Id,
                //        Review = $"这个{p.Name}太棒了 - {r}",
                //        OperateDate = DateTime.Now
                //    }).ToList();
                //})).ExecuteInsertedAsync();

                查询
                //var list = _dbContext.Select<ProductInfo>().IncludeMany(r => r.ReviewList).ToList();
                记录日志
                //LingbugService.Log(null, list.ToJsonString());

                //新增
                await _dbContext.Insert(new ProductInfo()
                {
                    Name = "其他方法新增的裤子",
                    OperateDate = DateTime.Now
                }).ExecuteAffrowsAsync();

                //返回
                return new
                {
                    IsSuccess = true,
                    Message = "ok"
                }.ToJsonString();
            }
            catch (Exception ex)
            {
                //返回
                return new
                {
                    IsSuccess = false,
                    Message = ex.Message,
                    Exception = ex
                }.ToJsonString();
            }
        }

        public async Task<string> TestFreeSqlUtil()
        {
            try
            {
                修改对象
                //await _freesqlUtil.Update(new ReviewInfo()
                //{
                //    Id = 1,
                //    Review = "这个西装太棒了 - 1 - 222",
                //    OperateDate = DateTime.Now
                //}, null).ExecuteAffrowsAsync();

                _freesqlUtil.RunWithTransaction(IsolationLevel.Serializable, tran =>
               {
                   //记录日志
                   LingbugService.Log("RunWithTransaction - 事务对象", tran.ToJsonString());

                   //新增
                   _dbContext.Insert(new ProductInfo()
                   {
                       Name = "皇冠fdsfdsfdfds真的吗？",
                       OperateDate = DateTime.Now
                   }).WithTransaction(tran).ExecuteAffrows();

                   //抛异常
                   throw new Exception("出错了");

                   //要修改的对象
                   var updateModel = new ReviewInfo()
                   {
                       Id = 2,
                       Review = "这个西装太棒了 - 1 - fdsfdsfdfds真的吗？",
                       OperateDate = DateTime.Now
                   };
                   //修改对象
                   _freesqlUtil.Update(updateModel, tran).ExecuteAffrows();
                   //修改对象
                   //_dbContext.Update<ReviewInfo>().SetSource(updateModel).WithTransaction(tran).ExecuteAffrows();
               });

                //返回
                return new
                {
                    IsSuccess = true,
                    Message = "ok"
                }.ToJsonString();
            }
            catch (Exception ex)
            {
                //返回
                return new
                {
                    IsSuccess = false,
                    Message = ex.Message,
                    Exception = ex
                }.ToJsonString();
            }
        }
    }

 

Ending~