freeSql的使用与讲解

本文讲解freesql的使用

话不多说,直接上代码,注释的很清楚了,如果还是看不懂,那我认为要不是你基础不够,去补补基础再来学习这个,要不就是..你不适合干开发了

 

增删改查

using System;
using System.Collections.Generic;
using System.Data;
using FreeSql;

namespace ConsoleForTestFreeSql
{
    class Program
    {
        private static IFreeSql _FreeSqlInstance = null;

        static void Main(string[] args)
        {
            try
            {
                CreateFreeSqlInstance();

                int count = 0;//增删改影响的数据行数

                //ado操作
                //查询
                var dt = _FreeSqlInstance.Ado.ExecuteDataTable(@"SELECT * FROM dbo.UserInfoTest");
                //增删改
                count = _FreeSqlInstance.Ado.ExecuteNonQuery(@"UPDATE dbo.UserInfoTest SET LastUpdateDate = GETDATE() WHERE Sex = '男'");
                //都是可以带参数和事务执行的(具体更多大家可以转到定义自己前去查看)

                //新增操作
                var addList = new List<UserInfo>()
                {
                    new UserInfo()
                    {
                        Name = "Ling、bug",
                        Age = 25,
                        Sex = "男",
                        Status = 1,
                        CreateDate = DateTime.Now
                    },
                    new UserInfo()
                    {
                        Name = "张三",
                        Sex = "女",
                        Status = 2,
                        CreateDate = DateTime.Now
                    }
                };

                //新增单条数据,返回新增数据实体集合
                var addResultList = _FreeSqlInstance.Insert(addList[0]).ExecuteInserted();
                //新增单条数据,返回新增行数
                count = _FreeSqlInstance.Insert(addList[0]).ExecuteAffrows();
                //新增单条数据,返回自增字段值(确保要有自增字段)
                var identityValue = _FreeSqlInstance.Insert(addList[0]).ExecuteIdentity();
                //批量新增(至于参数过多会导致异常不用担心,freesql内部进行了事务分批处理)
                count = _FreeSqlInstance.Insert(addList).ExecuteAffrows();
                //不使用参数化新增(可以查看打印的sql,至于不使用参数化担心sql注入问题,freesql内部也进行了处理,不用担心)
                count = _FreeSqlInstance.Insert(addList[0]).NoneParameter().ExecuteAffrows();

                //新增指定列
                count = _FreeSqlInstance.Insert(addList[0]).InsertColumns(addList[0].GetChanged()).ExecuteAffrows();
                //不新增忽略的列
                count = _FreeSqlInstance.Insert(addList[0]).IgnoreColumns(addList[0].GetChanged()).ExecuteAffrows();
                //说明:优先级为:全部列 < 指定列(InsertColumns) < 忽略列(IgnoreColumns)

                //更新操作
                //更新指定列(Update方法的参数指的是主键参数),返回修改的数据实体集合
                var updateResultList = _FreeSqlInstance.Update<UserInfo>(1)
                    .Set(r => r.LastUpdateDate, DateTime.Now)
                    .ExecuteUpdated();
                //更新多个指定列,Set可以拼接多个,返回影响的行数
                count = _FreeSqlInstance.Update<UserInfo>(1)
                    .Set(r => r.LastUpdateDate, DateTime.Now)
                    .Set(r => r.Age, 18)
                    .ExecuteAffrows();
                //表达式目录树指定更新,可以在原有的数据基础上更新,如++,--等等
                count = _FreeSqlInstance.Update<UserInfo>(1)
                    .Set(r => new UserInfo()
                    {
                        LastUpdateDate = DateTime.Now,
                        Age = r.Age + 1
                    })
                    .ExecuteAffrows();
                //自定义where条件
                count = _FreeSqlInstance.Update<UserInfo>()
                    .Set(r => r.LastUpdateDate, DateTime.Now)
                    .Where(r => r.Name == "张三")
                    .ExecuteAffrows();
                var updateModel = new UserInfo()
                {
                    Age = 28,
                    LastUpdateDate = DateTime.Now
                };
                //实体更新
                count = _FreeSqlInstance.Update<UserInfo>()
                    .SetDto(updateModel)
                    .Where(r => r.Name == "张三")
                    .ExecuteAffrows();
                //更新指定列
                count = _FreeSqlInstance.Update<UserInfo>()
                    .SetDto(updateModel)
                    .UpdateColumns(updateModel.GetChanged())
                    .Where(r => r.Name == "张三")
                    .ExecuteAffrows();
                //指定忽略哪些列不更新
                count = _FreeSqlInstance.Update<UserInfo>()
                    .SetDto(updateModel)
                    .IgnoreColumns(updateModel.GetUnChanged())
                    .Where(r => r.Name == "张三")
                    .ExecuteAffrows();
                //说明:优先级和新增的一样
                //还有类似于EF的实体跟踪更新,需要使用FreeSql.Repository扩展,这个我觉得麻烦就不演示了,不难,我相信大家一看就会,毕竟大家都是从EF过来的人,哈哈哈~
                //如果没有更新条件(没有主键参数也没有where参数),freesql为了防止误修改全表数据,不会执行更新操作,要想修改全表数据,可以使用where("1=1")

                //删除操作,这里不做过多介绍,就延时最简单的删除,官方也建议在实际开发中,将删除操作作为软删除去操作,而不要真实删除,毕竟...后期增加个回收站恢复数据功能又可以挣一笔功能钱(手动滑稽哈哈哈~)
                _FreeSqlInstance.Delete<UserInfo>().Where(r => r.UserId == 1).ExecuteAffrows();

                //事务操作:核心(WithTransaction(UnitOfWork.GetOrBeginTransaction()))

                //单线程中,可以使用默认的事务,缺点:不支持异步
                _FreeSqlInstance.Transaction(IsolationLevel.Serializable, () =>
                {
                    var addItem = new UserInfo()
                    {
                        Name = "李四",
                        Age = 18,
                        Status = 1,
                        CreateDate = DateTime.Now
                    };
                    var addItemId = _FreeSqlInstance.Insert(addItem).ExecuteIdentity();

                    count = _FreeSqlInstance.Update<UserInfo>().Set(r => r.Age, 20).Set(r => r.LastUpdateDate, DateTime.Now).Where(r => r.UserId == addItemId).ExecuteAffrows();
                    //默认事务中无需WithTransaction,无需手动提交事务
                });

                //使用DbContext(需要导入包Install-Package FreeSql.DbContext)
                using (var dbContext = _FreeSqlInstance.CreateDbContext())
                {
                    //通过DbContext的当前工作单元获取一个事务,如果没有,则新建一个事务,可以指定事务隔离级别
                    dbContext.UnitOfWork.IsolationLevel = IsolationLevel.Serializable;

                    try
                    {
                        var addItem = new UserInfo()
                        {
                            Name = "王五",
                            Age = 18,
                            Status = 1,
                            CreateDate = DateTime.Now
                        };
                        var addItemId = _FreeSqlInstance.Insert(addItem)
                            .WithTransaction(dbContext.UnitOfWork.GetOrBeginTransaction())
                            .ExecuteIdentity();

                        count = _FreeSqlInstance.Update<UserInfo>()
                            .Set(r => r.Age, 20)
                            .Set(r => r.LastUpdateDate, DateTime.Now)
                            .Where(r => r.UserId == addItemId)
                            .WithTransaction(dbContext.UnitOfWork.GetOrBeginTransaction())
                            .ExecuteAffrows();

                        //需要WithTransaction,需要手动提交事务
                        dbContext.UnitOfWork.Commit();
                    }
                    catch (Exception ex)
                    {
                        dbContext.UnitOfWork.Rollback();
                        throw;
                    }
                }
                //通过当前工作单元获取一个事务,如果没有,则新建一个事务,可以指定事务隔离级别
                using (var unit = _FreeSqlInstance.CreateUnitOfWork())
                {
                    unit.IsolationLevel = IsolationLevel.Serializable;

                    try
                    {
                        var addItem = new UserInfo()
                        {
                            Name = "赵六",
                            Age = 28,
                            Status = 1,
                            CreateDate = DateTime.Now
                        };
                        var addItemId = _FreeSqlInstance.Insert(addItem)
                            .WithTransaction(unit.GetOrBeginTransaction())
                            .ExecuteIdentity();

                        count = _FreeSqlInstance.Update<UserInfo>()
                            .Set(r => r.Age, 20)
                            .Set(r => r.LastUpdateDate, DateTime.Now)
                            .Where(r => r.UserId == addItemId)
                            .WithTransaction(unit.GetOrBeginTransaction())
                            .ExecuteAffrows();

                        //需要WithTransaction,需要手动提交事务
                        unit.Commit();
                    }
                    catch (Exception ex)
                    {
                        unit.Rollback();
                        throw;
                    }
                }

                //通过工作单元统一管理器,创建一个工作单元,然后就跟工作单元操作一样啦
                using (var unitManager = new UnitOfWorkManager(_FreeSqlInstance))
                {
                    //可以开启多个工作单元,但是使用的都是同一个事务
                    using (var unit = unitManager.Begin())
                    {
                        unit.IsolationLevel = IsolationLevel.ReadCommitted;

                        try
                        {
                            var addItem = new UserInfo()
                            {
                                Name = "赵六",
                                Age = 28,
                                Status = 1,
                                CreateDate = DateTime.Now
                            };
                            var addItemId = _FreeSqlInstance.Insert(addItem)
                                .WithTransaction(unit.GetOrBeginTransaction())
                                .ExecuteIdentity();

                            count = _FreeSqlInstance.Update<UserInfo>()
                                .Set(r => r.Age, 20)
                                .Set(r => r.LastUpdateDate, DateTime.Now)
                                .Where(r => r.UserId == addItemId)
                                .WithTransaction(unit.GetOrBeginTransaction())
                                .ExecuteAffrows();

                            //可以在工作单元中再开启工作单元,并且可以指定使用哪个工作单元的事务
                            using (var unit2 = unitManager.Begin())
                            {
                                try
                                {
                                    var addItem2 = new UserInfo()
                                    {
                                        Name = "赵六",
                                        Age = 28,
                                        Status = 1,
                                        CreateDate = DateTime.Now
                                    };
                                    var addItemId2 = _FreeSqlInstance.Insert(addItem)
                                        .WithTransaction(unit.GetOrBeginTransaction())//使用父工作单元的事务
                                        .ExecuteIdentity();

                                    count = _FreeSqlInstance.Update<UserInfo>()
                                        .Set(r => r.Age, 20)
                                        .Set(r => r.LastUpdateDate, DateTime.Now)
                                        .Where(r => r.UserId == addItemId)
                                        .WithTransaction(unit2.GetOrBeginTransaction())//使用自身事务
                                        .ExecuteAffrows();

                                    unit2.Commit();
                                }
                                catch (Exception ex2)
                                {
                                    unit2.Rollback();
                                    throw;
                                }
                            }
                            //需要WithTransaction,需要手动提交事务
                            unit.Commit();
                        }
                        catch (Exception ex)
                        {
                            unit.Rollback();
                            throw;
                        }

                    }

                    using (var unit1 = unitManager.Begin())
                    {
                        var addItem = new UserInfo()
                        {
                            Name = "赵六",
                            Age = 28,
                            Status = 1,
                            CreateDate = DateTime.Now
                        };
                        var addItemId = _FreeSqlInstance.Insert(addItem)
                            .WithTransaction(unit1.GetOrBeginTransaction())
                            .ExecuteIdentity();

                        count = _FreeSqlInstance.Update<UserInfo>()
                            .Set(r => r.Age, 20)
                            .Set(r => r.LastUpdateDate, DateTime.Now)
                            .Where(r => r.UserId == addItemId)
                            .WithTransaction(unit1.GetOrBeginTransaction())
                            .ExecuteAffrows();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("发生异常:" + ex.Message);
            }

            Console.ReadKey();
        }

        private static void CreateFreeSqlInstance()
        {
            //初始化实例对象
            _FreeSqlInstance = new FreeSqlBuilder()
                //指定数据库类型以及数据库连接
                .UseConnectionString(DataType.SqlServer, "Data Source=.;Integrated Security=True;Initial Catalog=Lingbug;Pooling=true;Min Pool Size=1;uid=sa;pwd=pwd;")
                //aop监听sql
                .UseMonitorCommand(cmd =>//执行前
                {
                    Console.WriteLine("--------------------------------------------------执行前begin--------------------------------------------------");
                    Console.WriteLine(cmd.CommandText);
                    Console.WriteLine("--------------------------------------------------执行前end--------------------------------------------------");
                }, (cmd, valueString) =>//执行后
                {
                    Console.WriteLine("--------------------------------------------------执行后begin--------------------------------------------------");
                    Console.WriteLine(cmd.CommandText);
                    Console.WriteLine(valueString);
                    Console.WriteLine("--------------------------------------------------执行后end--------------------------------------------------");
                })
                .UseAutoSyncStructure(true)//CodeFirst自动同步将实体同步到数据库结构(开发阶段必备),默认是true,正式环境请改为false
                .Build();//创建实例(官方建议使用单例模式)
        }
    }
}

 

实体对应(不管是开发还是在CodeFirst时数据库迁移,都很有用)

using System;
using FreeSql.DataAnnotations;

namespace ConsoleForTestFreeSql
{
    //更多的解释,大家可以查看代码转到定义去查看

    //Name可以指定对应数据库哪张表(如果表名和类名相同,无需指定)
    [Table(Name = "TableName")]
    public class ClassInfo
    {
        //IsIdentity:是否自增标识列
        [Column(IsIdentity = true)]
        public int IdentityId { get; set; }

        //IsPrimary:是否主键
        //Name:对应数据库表列名(属性名和数据库字段名相同时无需指定)
        //如果列是guid类型,插入时会自动创建主键id,无需手动赋值(并且是支持分布式的)
        [Column(IsPrimary = true, Name = "Id")]
        public Guid Id { get; set; }

        //DbType:对应的数据库类型:如DbType = "nvarchar(50)"
        //IsNullable:是否可以为空
        [Column(DbType = "nvarchar(50)", IsNullable = false)]
        public string ClassName { get; set; }

        //StringLength:在指定数据库类型时,如果是string类型,除了自己指定,这里也可以指定最大长度
        [Column(StringLength = 300)]
        public string Des { get; set; }

        //IsIgnore:是否忽略列,不新增,不迁移
        [Column(IsIgnore = true)]
        public string Remark { get; set; }

        //IsVersion:版本号(乐观锁),修改时会使用到,一个表最多只允许一列版本号
        [Column(IsVersion = true)]
        public decimal Money { get; set; }

        //CanUpdate:false是修改时不修改此列
        //ServerTime:对应数据库服务器时间(插入时使用,是个枚举)
        [Column(CanUpdate = false, ServerTime = DateTimeKind.Local, IsNullable = false)]
        public DateTime CreateDate { get; set; }

        //CanInsert:false时新增时不新增此列
        [Column(CanInsert = false, ServerTime = DateTimeKind.Local)]
        public DateTime? LastUpdateDate { get; set; }
    }
}

 

Ending~

 

 

  • 5
    点赞
  • 19
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值