.Net6 DbContext 事务 MySql / SqlServer

代码

.NET Core/.NET6 使用DbContext 连接数据库，SqlServer和MySql
DbEntitys

using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Linq.Expressions;
using System.Reflection;
using MySqlConnector;//mysql
using Microsoft.Data.SqlClient;//sqlserver



namespace DataBaseApp
{
    public class EntityUsing : IDisposable
    {

        private DbEntitys db = new DbEntitys();
        private MySqlConnection connection;
        private MySqlTransaction transaction;
        private MySqlCommand command;

		//sqlserver
        //private SqlConnection connection;
        //private SqlTransaction transaction;
        //private SqlCommand command;


        public EntityUsing() {
            //mysql
            connection = new MySqlConnection(db.Database.GetDbConnection().ConnectionString);
            
            //sqlserver
            //connection = new SqlConnection(db.Database.GetDbConnection().ConnectionString);
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            transaction = connection.BeginTransaction();
            command = connection.CreateCommand();
            command.Transaction = transaction;

        }




        public int Commit()
        {
            try
            {
            	//提交修改
                var returnValue = db.SaveChanges();
                if (transaction != null)
                {
                    transaction.Commit();
                }
                return returnValue;
            }
            catch (Exception)
            {
                if (transaction != null)
                {
                	//当数据库操作报错时进行回滚
                    this.transaction.Rollback();
                }
                throw;
            }
            finally
            {
                this.Dispose();
            }
        }

        public void Dispose()
        {
            if (transaction != null)
            {
                this.transaction.Dispose();
            }
            this.db.Dispose();
        }


		/// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public TEntity Add<TEntity>(TEntity entity) where TEntity : class
        {
            db.Entry<TEntity>(entity).State = EntityState.Added;
            return entity;
        }

		 /// <summary>
        /// 批量新增
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entitys"></param>
        public void Add<TEntity>(List<TEntity> entitys) where TEntity : class
        {
            foreach (var entity in entitys)
            {
                db.Entry<TEntity>(entity).State = EntityState.Added;
            }
        }

		/// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        public void Update<TEntity>(TEntity entity) where TEntity : class
        {
            db.Set<TEntity>().Attach(entity);
            PropertyInfo[] props = entity.GetType().GetProperties();
            foreach (PropertyInfo prop in props)
            {
            	//判断字段有没有主键特性标记 有则跳过 不会进行修改
            	//如果没有会报错
                if (prop.CustomAttributes.Any(a => a.AttributeType.Name == "KeyAttribute")) continue;

                if (prop.GetValue(entity, null) != null)
                {
                    if (prop.GetValue(entity, null).ToString() == "&nbsp;")
                        db.Entry(entity).Property(prop.Name).CurrentValue = null;
                    db.Entry(entity).Property(prop.Name).IsModified = true;
                }
            }
        }
		/// <summary>
        /// 删除
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        public void Delete<TEntity>(TEntity entity) where TEntity : class
        {
            db.Set<TEntity>().Attach(entity);
            db.Entry<TEntity>(entity).State = EntityState.Deleted;
        }
		
		/// <summary>
        /// 按条件 删除
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        public void Delete<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class
        {
            var entitys = db.Set<TEntity>().Where(predicate).ToList();
            entitys.ForEach(m => db.Entry<TEntity>(m).State = EntityState.Deleted);
        }

        /// <summary>
        /// 根据条件查询一条数据    
        /// 先查询再修改必须用这个，否则会报错
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key"></param>
        /// <returns></returns>
        public T Find<T>(Expression<Func<T, bool>> predicate) where T : class, new()
        {
            return db.Set<T>().AsNoTracking().FirstOrDefault(predicate);
        }


        /// <summary>
        /// 根据主键查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">主键，可多个</param>
        /// <returns></returns>
        public T Find<T>(params object[] key) where T : class, new()
        {
            return db.Set<T>().Find(key);
        }


        /// <summary>
        /// 查询列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public IQueryable<T> GetList<T>(Expression<Func<T, bool>> predicate) where T : class, new()
        {
            return db.Set<T>().Where(predicate);
        }

        /// <summary>
        /// 查询列表 无跟踪
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public IQueryable<T> GetListAsNoTracking<T>(Expression<Func<T, bool>> predicate) where T : class, new()
        {
            return db.Set<T>().AsNoTracking().Where(predicate);
        }
    }
}

使用

			using (EntityUsing db=new EntityUsing())
            {
            	var mod1=new tb_test_1();//表实体
                mod1.recharge_type = (int)RechargeType.短信;
                mod1.recharge_time = DateTime.Now;
                mod1.unit = (int)unitType.条;
                db.Add(mod1);

                tb_test_2 mod2= db.Find<tb_test_2>(x => x.id== 1);

                mod2.name="666";
                db.Update(mod2);
                db.Commit();

            }